Oracle 常用系统

常用系统包

查看系统包

SELECT * FROM all_objects WHERE object_type = 'PACKAGE' AND owner = 'SYS' AND object_name = '包名称';

AUTO_SERVER_PKG

AUTO_SERVER_PKG 在Oracle数据库的上下文中，并不是一个标准的Oracle系统包或内置功能。然而，在参考文章中提到的 sys.auto_server_pkg 似乎是一个自定义的包（package），用于执行一些数据库管理任务，如解锁表、解锁用户、停止作业（JOB）、杀掉会话（SESSION）等。由于这是自定义的，因此它的具体实现、可用性以及所包含的函数（如 unlock_table、unlock_user、stop_job、kill_session 等）将完全取决于该包的创建者和其内部的实现细节。

自定义包 AUTO_SERVER_PKG 的可能用途

1. 解锁表：

   • 允许数据库管理员或具有适当权限的用户解锁被锁定的表。
   • 示例命令：execute sys.auto_server_pkg.unlock_table('TABLE_OWNER', 'TABLE_NAME');

2. 解锁用户：

   • 允许解锁被锁定的数据库用户账户。
   • 示例命令：execute sys.auto_server_pkg.unlock_user('USERNAME');

3. 停止作业：

   • 允许停止正在运行的数据库作业。
   • 示例命令：execute sys.auto_server_pkg.stop_job(JOB_ID);

4. 杀掉会话：

   • 允许终止特定的数据库会话。
   • 示例命令：execute sys.auto_server_pkg.kill_session(SID, SERIAL#);

注意事项

• 权限：执行这些操作通常需要较高的数据库权限，如SYSDBA或相应的角色权限。
• 风险：直接杀掉会话或停止作业可能会导致数据不一致或业务中断，因此在执行这些操作之前应该仔细考虑其潜在影响。
• 自定义性：由于这是一个自定义包，其功能和可用性可能因数据库环境的不同而有所差异。

auto_server_pkg.unlock_package

-- 解锁存储过程
set serveroutput on
execute sys.auto_server_pkg.unlock_package('PKG_OWNER','PKG_NAME');

auto_server_pkg.unlock_table

-- 解锁表
set serveroutput on
execute sys.auto_server_pkg.unlock_table('TABLE_OWNER', 'TABLE_NAME');

auto_server_pkg.stop_job

-- 停止正在运行的JOB
set serveroutput on
execute sys.auto_server_pkg.stop_job(JOB_ID);

auto_server_pkg.grant_pris

-- 用户授权
set serveroutput on
execute sys.auto_server_pkg.grant_pris ('USERNAME');

auto_server_pkg.unlock_user

-- 解锁用户
set serveroutput on
execute sys.auto_server_pkg.unlock_user('USERNAME');

auto_server_pkg.kill_session

-- KILL SESSION
set serveroutput on
execute sys.auto_server_pkg.kill_session(SID,SERAIL#);

DBMS_LOCK

dbms_lock.sleep(10);

DBMS_DDL

作用: 提供了在PL/SQL块中执行DDL语句的方法,并且也提供了一些DDL的特殊管理方法.

DBMS_METADATA

dbms_metadata.get_ddl

DBMS_METADATA包GET_DDL函数用于获取数据库对表、视图、索引、全文索引、存储过程、函数、包、序列、同义词、约束、触发器等的DDL语句。

语法定义

DBMS_METADATA.GET_DDL (
object_type IN VARCHAR2,
name IN VARCHAR2,
schema IN VARCHAR2 DEFAULT NULL,
version IN VARCHAR2 DEFAULT 'COMPATIBLE',
model IN VARCHAR2 DEFAULT 'ORACLE',
transform IN VARCHAR2 DEFAULT 'DDL')
RETURN CLOB;

重点参数详解

OBJECT_TYPE

对象类型。包括表、视图、物化视图、索引、全文索引、存储过程、函数、包、目录等，详情请见OPEN参数详解。其中，OBJECT_TYPE只能为大写。

OBJECT_TYPE的类型包括：

类型名称	含义	说明
CLASS	类类型	默认返回类头类体
CLASS_HEAD	类型名	无
CLASS_BODY	类型体	无
COL_STATISTICS	列统计	无
COMMENT	注释	无
CONSTRAINT	约束	不包括聚集主键和非空约束
CONTEXT	上下文	无
DATABASE_EXPORT	数据库下的所有对象	库级导出
DB_LINK	数据库链接	因此类对象具有所有者,因此将其视为模式级对象。 对于公有连接，它们的所有者是PUBLIC；对于私有链接，它们的创建者就是它们的所有者
DIRECTORY	目录	无
DOMAIN	域	无
FUNCTION	存储函数	无
INDEX	索引	不包括系统内部定义的索引
INDEX_STATISTICS	索引统计	无
JOB	任务	无
OBJECT_GRANT	对象权限	无
PACKAGE	包	默认返回包头包体
PKG_SPEC	包头	无
PKG_BODY	包体	无
POLICY	策略	无
PROCEDURE	存储过程	无
ROLE	角色	无
ROLE_GRANT	角色权限	无
SCHEMA_EXPORT	模式下的所有对象	模式级导出
SEQUENCE	序列	无
SYNONYM	同义词	私有同义词为模式对象，公有同义词为命名对象
SYSTEM_GRANT	系统权限	无
TABLE	表	无
TABLE_STATISTICS	表统计信息	无
TABLE_EXPORT	表及与其相关的元数据	表级导出
TABLESPACE	表空间	无
TRIGGER	触发器	无
USER	用户	无
VIEW	视图	无
TYPE	用户自定义类型	无
MATERIALIZED_VIEW	物化视图	无
MATERIALIZED_VIEW_LOG	物化视图日志	无

NAME

对象名称，区分大小写。

SCHEMA

模式，默认是当前用户模式。

返回值

以DDL返回对象元数据中的DDL语句。

错误处理

• INVALID_ARGVAL：如果输入参数中存在空值或非法值。
• OBJECT_NOT_FOUND：如果指定的对象在数据库中不存在。

注意

• 需要进行下列格式化，特别需要对long进行设置，否则无法显示完整的SQL

• 参数要使用大写，否则会查不到

set linesize 180
set pages 999
set long 1000

使用场景

表空间的DDL语句

set linesize 180
set pages 999
set long 10000



select 'select dbms_metadata.get_ddl(''TABLESPACE'','''|| tablespace_name || ''') from dual;' from dba_tablespaces;
SELECT DBMS_METADATA.GET_DDL('TABLESPACE', TS.tablespace_name) FROM DBA_TABLESPACES TS;
SELECT DBMS_METADATA.GET_DDL('TABLESPACE', 'USERS') FROM dual;

用户的ddl

set linesize 180
set pages 999
set long 10000

SELECT DBMS_METADATA.GET_DDL('USER',U.username) FROM DBA_USERS U;
SELECT DBMS_METADATA.GET_DDL('USER','HR') FROM dual;



-- 详细的获取用户ddl
select dbms_metadata.get_ddl('USER', du.username) AS DDL_SCRIPT
from dba_users du
where du.username = 'TYPE_USER_NAME'
union all
select dbms_metadata.get_granted_ddl('TABLESPACE_QUOTA', dtq.username) AS DDL_SCRIPT
from dba_ts_quotas dtq
where dtq.username = 'TYPE_USER_NAME'
and rownum = 1
union all
select dbms_metadata.get_granted_ddl('ROLE_GRANT', drp.grantee) AS DDL_SCRIPT
from dba_role_privs drp
where drp.grantee = 'TYPE_USER_NAME'
and rownum = 1
union all
select dbms_metadata.get_granted_ddl('SYSTEM_GRANT', dsp.grantee) AS DDL_SCRIPT
from dba_sys_privs dsp
where dsp.grantee = 'TYPE_USER_NAME'
and rownum = 1
union all
select dbms_metadata.get_granted_ddl('OBJECT_GRANT', dtp.grantee) AS DDL_SCRIPT
from dba_tab_privs dtp
where dtp.grantee = 'TYPE_USER_NAME'
and rownum = 1
union all
select dbms_metadata.get_granted_ddl('DEFAULT_ROLE', drp.grantee) AS DDL_SCRIPT
from dba_role_privs drp
where drp.grantee = 'TYPE_USER_NAME'
and drp.default_role = 'YES'
and rownum = 1;

角色的ddl

set linesize 180
set pages 999
set long 10000

SELECT DBMS_METADATA.GET_DDL('ROLE','ROLENAME') FROM DUAL;

查看表的SQL定义语句

set linesize 180
set pages 999
set long 10000


-- 查看当前用户表的SQL
select dbms_metadata.get_ddl('TABLE','EMPLOYEES') from dual;
SELECT table_name,DBMS_METADATA.GET_DDL('TABLE',table_name) FROM user_tables;
-- 查看其它用户下的表结构
select dbms_metadata.get_ddl('TABLE','EMPLOYEES','HR') from dual;
SELECT DBMS_METADATA.get_ddl ('TABLE',table_name,owner) FROM dba_tables WHERE owner = UPPER('username');

查看索引的SQL定义语句

set linesize 180
set pages 999
set long 10000

-- 查看所需表的索引
select INDEX_NAME, INDEX_TYPE, TABLE_NAME from all_indexes WHERE table_name='EMPLOYEES';
-- 查看当前用户索引的SQL
select dbms_metadata.get_ddl('INDEX','EMP_EMP_ID_PK') from dual;
-- 查看其他用户索引的SQL 
select dbms_metadata.get_ddl('INDEX','EMP_EMP_ID_PK','HR') from dual;
SELECT DBMS_METADATA.GET_DDL('INDEX', INDEX_NAME) FROM USER_INDEXES WHERE INDEX_TYPE ='NORMAL';

获取用户下约束的SQL定义语句

set linesize 180
set pages 999
set long 10000


-- 查看所需表的约束
select owner, table_name, constraint_name, constraint_type from user_constraints where table_name='EMPLOYEES';
-- 查看创建主键的SQL
SELECT DBMS_METADATA.GET_DDL('CONSTRAINT','EMP_EMP_ID_PK','HR') FROM DUAL;
-- 查看创建外键的SQL
SELECT DBMS_METADATA.GET_DEPENDENT_DDL('REF_CONSTRAINT','OBJECT_NAME','SCHEMA_NAME') from dual;
SELECT DBMS_METADATA.GET_DDL('REF_CONSTRAINT','EMP_DEPT_FK','HR') FROM DUAL;

查看创建视图的SQL定义语句

set linesize 180
set pages 999
set long 10000

-- 查看当前用户视图的SQL
SELECT view_name,DBMS_METADATA.GET_DDL('VIEW',view_name) FROM user_views;
SELECT dbms_metadata.get_ddl('VIEW', 'EMP_DETAILS_VIEW') from dual ;
-- 查看其他用户视图的SQL
SELECT dbms_metadata.get_ddl('VIEW', 'EMP_DETAILS_VIEW','HR') FROM DUAL;
SELECT DBMS_METADATA.GET_DDL('VIEW',U.OBJECT_NAME, U.OWNER) FROM DBA_OBJECTS U WHERE U.OBJECT_TYPE = 'VIEW' AND OWNER='USER_NAME';

-- 查看创建视图的SQL也可以
select text from all_views where view_name='EMP_DETAILS_VIEW';

获取物化视图的SQL定义语句

set linesize 180
set pages 999
set long 10000


select dbms_metadata.get_ddl('MATERIALIZED_VIEW','MV_EMP','HR') FROM DUAL;

获取用户下的触发器的SQL定义语句

set linesize 180
set pages 999
set long 10000


-- 查询TRIGGER
SELECT trigger_name,DBMS_METADATA.GET_DDL('TRIGGER',trigger_name) FROM user_triggers;
select DBMS_METADATA.GET_DDL('TRIGGER','UPDATE_JOB_HISTORY','HR') FROM DUAL;

-- 也可以使用系统表获取ddl
select text from user_source t where t.name='TR_TEST';

获取存储过程的SQL定义语句

set linesize 180
set pages 999
set long 10000


select DBMS_METADATA.GET_DDL('PROCEDURE','ADD_JOB_HISTORY','HR') from dual;
SELECT DBMS_METADATA.GET_DDL('PROCEDURE',U.OBJECT_NAME, U.OWNER) FROM DBA_OBJECTS U WHERE U.OBJECT_TYPE = 'PROCEDURE' AND OWNER='USER_NAME';

获取用户下函数的SQL定义语句

set linesize 180
set pages 999
set long 10000

select DBMS_METADATA.GET_DDL('FUNCTION','TEST','HR') from DUAL;
SELECT DBMS_METADATA.GET_DDL('FUNCTION',U.OBJECT_NAME, U.OWNER) FROM DBA_OBJECTS U WHERE U.OBJECT_TYPE = 'FUNCTION'AND OWNER='USER_NAME';

获取包的SQL定义语句

set linesize 180
set pages 999
set long 10000


SELECT DBMS_METADATA.GET_DDL('PACKAGE','OBJECT_NAME','SCHEMA_NAME') FROM DUAL;
select DBMS_METADATA.GET_DDL('PACKAGE BODY','PACKAGEBODYNAME','SCHEMA_NAME') from dual;

获取用户下序列的SQL定义语句

set linesize 180
set pages 999
set long 10000


-- 查询SEQUENCE
SELECT sequence_name,DBMS_METADATA.GET_DDL('SEQUENCE',sequence_name) FROM user_sequences;
select DBMS_METADATA.GET_DDL('SEQUENCE','DEPARTMENTS_SEQ','HR') from DUAL;

获取用户下同义词的SQL定义语句

set linesize 180
set pages 999
set long 10000


select DBMS_METADATA.GET_DDL('SYNONYM','EMP','HR') from DUAL;
-- 也可以查看系统视图获取
select 'create synonym '||synonym_name||' for '||table_owner||'.'||table_name||';' from dba_synonyms;

获取dblink的ddl

set linesize 180
set pages 999
set long 10000



SELECT dbms_metadata.get_ddl('DB_LINK','DBLINKNAME','USERNAME') stmt FROM dual;

获取远程数据库对象的定义

SELECT DBMS_LOB.SUBSTR@dblinkname(DBMS_METADATA.GET_DDL@dblinkname('TABLE', 'TABLENAME', 'USERNAME')) FROM DUAL@dblinkname

得到一个用户下的所有表，索引，存储过程，函数的ddl

SELECT DBMS_METADATA.GET_DDL(U.OBJECT_TYPE, u.object_name) FROM USER_OBJECTS u where U.OBJECT_TYPE IN ('TABLE','INDEX','PROCEDURE','FUNCTION');

SELECT DBMS_METADATA.GET_DDL(O.OBJECT_TYPE, O.object_name,O.OWNER) FROM DBA_OBJECTS O where O.OBJECT_TYPE IN ('TABLE','INDEX','PROCEDURE','FUNCTION') and ONWER = 'ONWERNAME';



OR：


set pagesize 0
set long 90000
set feedback off
set echo off
spool schema_ddl.sql
SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name,u.owner) FROM DBA_TABLES u;
SELECT DBMS_METADATA.GET_DDL('VIEW',u.view_name,u.owner) FROM DBA_VIEWS u;
SELECT DBMS_METADATA.GET_DDL('INDEX',u.index_name,u.owner) FROM DBA_INDEXES u;
select dbms_metadata.get_ddl('PROCEDURE',u.object_name, u.owner,) from dba_objects u where u.object_type = 'PROCEDURE';
select dbms_metadata.get_ddl('FUNCTION',u.object_name, u.owner,) from dba_objects u where u.object_type = 'FUNCTION';
spool off;

获取某个schema下的对象ddl

-- 获取一个SCHEMA下的所有建表、视图和建索引的语法，以scott为例
set pagesize 0
set long 90000
set feedback off
set echo off
spool schema.sql
connect scott/tiger;
SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name) FROM USER_TABLES u;
SELECT DBMS_METADATA.GET_DDL('VIEW',u.VIEW_name) FROM USER_VIEWS u;
SELECT DBMS_METADATA.GET_DDL('INDEX',u.index_name) FROM USER_INDEXES u;
spool off;


-- 获取某个SCHEMA的建全部存储过程的语法
set pagesize 0
set long 90000
set feedback off
set echo off
spool procedures.sql  
select DBMS_METADATA.GET_DDL('PROCEDURE',u.object_name) from user_objects u where object_type = 'PROCEDURE';
spool off;


-- 获取某个SCHEMA的建全部函数的语法
set pagesize 0
set long 90000
set feedback off
set echo off
spool function.sql  
select DBMS_METADATA.GET_DDL('FUNCTION',u.object_name) from user_objects u where object_type = 'FUNCTION';
spool off;

去除storage等多余参数

EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);

参考资料

https://blog.****.net/lirenkai2000/article/details/12659195

https://www.modb.pro/db/101274

dbms_metadata.get_dependent_ddl

SELECT DBMS_METADATA.GET_DEPENDENT_DDL('REF_CONSTRAINT','OBJECT_NAME','SCHEMA_NAME') from dual;

dbms_metadata.get_granted_ddl

可以用于获取用户的授权信息。Oracle支持的授权相关信息类型包括：OBJECT_GRANT、SYSTEM_GRANT、ROLE_GRANT、DEFAULT_ROLE、TABLESPACE_QUOTA和PROXY（即object_type的可选值）。

-- GET_GRANTED_DDL:     Return the metadata for objects granted to a
--      grantee as DDL.
--      This interface is meant for casual browsing (e.g., from SQLPlus)
--      vs. the programmatic OPEN / FETCH / CLOSE interfaces above.
-- PARAMETERS:
--      object_type     - The type of object to be retrieved.
--      grantee         - Name of the grantee.
--      version         - The version of the objects' metadata.
--      model           - The object model for the metadata.
--      transform       - XSL-T transform to be applied.
--      object_count    - maximum number of objects to return
-- RETURNS:     Metadata for the object as a CLOB.

  FUNCTION get_granted_ddl (
                object_type     IN  VARCHAR2,
                grantee         IN  VARCHAR2 DEFAULT NULL,
                version         IN  VARCHAR2 DEFAULT 'COMPATIBLE',
                model           IN  VARCHAR2 DEFAULT 'ORACLE',
                transform       IN  VARCHAR2 DEFAULT 'DDL',
                object_count    IN  NUMBER   DEFAULT 10000)
        RETURN CLOB;

示例：

-- You can get any user’s system grant ( create script ) as follows.
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','SCHEMA_NAME') from dual;
select dbms_metadata.get_granted_ddl('SYSTEM_GRANT', dsp.grantee) AS DDL_SCRIPT
from dba_sys_privs dsp
where dsp.grantee = 'TYPE_USER_NAME'
and rownum = 1;


-- You can get any user’s role grant ( create script ) as follows.
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','SCHEMA_NAME') from dual;
select dbms_metadata.get_granted_ddl('ROLE_GRANT', drp.grantee) AS DDL_SCRIPT
from dba_role_privs drp
where drp.grantee = 'TYPE_USER_NAME'
and rownum = 1;


-- You can get any user’s object grant ( create script ) as follows.
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','SCHEMA_NAME') from dual;
select dbms_metadata.get_granted_ddl('OBJECT_GRANT', dtp.grantee) AS DDL_SCRIPT
from dba_tab_privs dtp
where dtp.grantee = 'TYPE_USER_NAME'
and rownum = 1;



select dbms_metadata.get_granted_ddl('TABLESPACE_QUOTA', dtq.username) AS DDL_SCRIPT
from dba_ts_quotas dtq
where dtq.username = 'TYPE_USER_NAME'
and rownum = 1;


select dbms_metadata.get_granted_ddl('DEFAULT_ROLE', drp.grantee) AS DDL_SCRIPT
from dba_role_privs drp
where drp.grantee = 'TYPE_USER_NAME'
and drp.default_role = 'YES'
and rownum = 1;

说明：

如果指定查询的授权不存在，并不是简单的返回未选定行，而是还会显示错误信息

SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', 'TEST') FROM DUAL;
ERROR:
ORA-31608: 找不到类型为 SYSTEM_GRANT 的指定对象
ORA-06512: 在"SYS.DBMS_SYS_ERROR", line 86
ORA-06512: 在"SYS.DBMS_METADATA", line 631
ORA-06512: 在"SYS.DBMS_METADATA", line 1339
ORA-06512: 在line 1

最后还要说明一点，DBMS_METADATA的GET_GRANTED_DDL不会显示SYSDBA和SYSOPER权限。

参考资料：

https://blog.****.net/wu_wei_jie/article/details/6439387

DBMS_STATS

作用: 用于搜集,查看,修改数据库对象的优化统计信息.

dbms_stats.gather_table_stats

用于收集目标表、列和索引的统计信息

-- 收集test表的统计信息