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.csdn.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.csdn.net/wu_wei_jie/article/details/6439387

DBMS_STATS

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

dbms_stats.gather_table_stats

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

-- 收集test表的统计信息
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(ownname          => 'SCOTT',
                                tabname          => 'TEST',
                                estimate_percent => 15, -- 采样比是15%
                                method_opt       => 'for table',
                                cascade          => false);  -- 默认是true，级联收集
END;
/

dbms_stats.gather_index_stats

用于收集指定统计信息

-- 收集索引idx_text的统计信息
BEGIN
  DBMS_STATS.GATHER_INDEX_STATS(ownname          => 'SCOTT',
                                indname          => 'IDX_TEST',
                                estimate_percent => 100,
								);
END;
/

dbms_stats.gather_schema_stats

用于收集指定schema下的所有对象统计信息

-- 收集scott用户下的所有对象
BEGIN
  DBMS_STATS.GATHER_SCHEMA_STATS(ownname		 => 'SCOTT',
                                cascade			 =>true,
                                granularity=>'ALL', -- 收集分区表
								);
END;
/

dbms_stats.gather_database_stats

用于收集全库所有的统计信息

-- 收集全库的统计信息
BEGIN
  DBMS_STATS.GATHER_DATABASE_STATS(estimate_percent=>100,
                                   degree=>8,
                                   cascade=>true,
                                   granularity=>'ALL', -- 收集分区表
								);
END;
/

dbms_stats.get_system_stats

收集系统统计信息

dbms_stats.gather_dictionary_stats

收集字段对象的统计信息

dbms_stats.gather_table_stats

删除表的统计信息

dbms_stats.gather_index_stats

删除索引的统计信息

dbms_stats.export_table_stats

输出表的统计信息

dbms_stats.set_table_stats

设置表的统计

dbms_stats.auto_sample_size

estimate_percent的值

Use the constant DBMS_STATS.AUTO_SAMPLE_SIZE to have Oracle determine the appropriate sample size for good statistics. This is the default

DBMS_RANDOM

作用: 提供了内置的随机数生成器,可以用于快速生成随机数.

-- 随机查表中的数据
select *
  from (select rownum,c.empno,c.ename,c.job
          from (select * from scott.emp order by dbms_random.value) c
          where rownum <=6);

DBMS_ROWID

作用: 用于在PL/SQL程序和SQL语句中取得行标识符的信息并建立ROWID,通过该包可以取得行所在的文件号,行所在文件的数据块号,行所在数据块的行号,以及数据库对象号等消息.

-- 查看rowid内容
SELECT ROWID,
       DBMS_ROWID.ROWID_OBJECT(ROWID) AS OBJECT,
       DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) AS FILENUM,
       DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) AS BLOCK,
       DBMS_ROWID.ROWID_ROW_NUMBER(ROWID) AS ROWN
  FROM DEPT;


1.rowid_create
建立ROWID
语法如下:
dbms_rowid.rowid_create (
rowid_type in number,object_number in number,
relative_fno in n umber,block_number in number,
row_number in number)
return rowid;
注:rowid_type用于指定ROWID类型(0:受限ROWID,1:扩展ROWID);object_number用于指定数据对象号;relative_fno用于指定相对文件号;block_number用于指定在文件中的数据块号;row_number用于指定在数据块中的行号.
2.rowid_info
用于取得特定ROWID的详细信息.
3.rowid_type
用于返回特定ROWID的类型
4.rowid_object
用于取得特定ROWID所对应的数据对象号
5.rowid_relative_fno
用于取得特定ROWID所对应的相对文件号
6.rowid_block_number
用于返回特定ROWID在数据文件中所对应的数据块号.
7.rowid_row_number
用于返回特定ROWID在数据块中所对应的行号.
8.rowid_to_obsolute_fno
用于返回特定ROWID所对应的绝对文件号
9.rowid_to_extended
用于将受限rowid转变为扩展rowid
10.rowid_to_restricted
用于将扩展rowid转换为受限rowid
11.rowid_verify
检查是否可以将受限rowid转变为扩展rowid

DBMS_OUTPUT

作用: 用于输入和输出信息,使用过程PUT和PUT_LINES可以将信息发送到缓冲区,使用过程GET_LINE和GET_LINES可以显示缓冲区信息.

例如，如果您想输出一个叫做“Hello, world!”的语句，您可以使用以下代码：

DECLARE
str VARCHAR2(100) := 'Hello, world!';
BEGIN
DBMS_OUTPUT.PUT_LINE(str);
END;

详细使用

-- 1.enable
该过程用于激活对过程PUT,PUT_LINE,GET_LINE,GET_LINES的调用
语法如下:
dbms_output.enable(buffer_size in integer default 20000);

-- 2.disable
该过程用于禁止对过程PUT,PUT_LINE,GET_LINE,GET_LINES的调用
语法如下:
dbms_output.disable;

-- 3.put和put_line
过程put_line用于将一个完整行的信息写入到缓冲区中,过程put则用地分块建立行信息,   当使用过程put_line时,会自动在行的尾部追加行结束符;当使用过程put时,需要使用过程   new_line追加行结束符.
示例如下:
set serverout on
begin
dbms_output.put_line('伟大的中华民族');
dbms_output.put('中国');
dbms_output.put(',伟大的祖国');
dbms_output.new_line;
end;
/


-- 4.new_line
该过程用于在行的尾部追加行结束符.当使用过程PUT时,必须调用NEW_LINE过程来结束行.

-- 5.get_line和get_lines
过程get_line用于取得缓冲区的单行信息,过程get_lines用于取得缓冲区的多行信息.

DBMS_XPLAN

-- 查看byte大小
DBMS_XPLAN.FORMAT_SIZE(BYTES)

-- 查看执行计划
select * from table(dbms_xplan.display_cursor( format=> 'allstats last' ));
select * from table(dbms_xplan.display_cursor('7w3a9ufg0xb9x',0,'all iostats last'));

DBMS_SQL

DBMS_SQL包包含了一系列过程和函数，可以让您在SQL命令行界面上执行动态SQL语句。

例如，如果您想在SQL命令行界面上创建一个新表，您可以使用以下代码：

DECLARE
l_cursor INTEGER;
BEGIN
l_cursor := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(l_cursor, 'CREATE TABLE test (id NUMBER, name VARCHAR2(100))', DBMS_SQL.NATIVE);
DBMS_SQL.CLOSE_CURSOR(l_cursor);
END;

通过上述的代码，您可以在SQL命令行界面上创建一个新表。

DBMS_LOGMNR

作用: 通过使用包DBMS_LOGMNR和DBMS_LOGMNR_D,可以分析重做日志和归档日志所记载的事务变化,最终确定误操作(例如DROP TABLE)的时间,跟踪用户事务操作,跟踪并还原表的DML操作.

DBMS_FLASHBACK

作用: 用于激活或禁止会话的flashback特征,为了使得普通用户可以使用该包,必须要将执行该包的权限授予这些用户.

DBMS_LOB

DBMS_JOB

作用: 用于安排和管理作业队列,通过使用作业,可以使ORACLE数据库定期执行特定的任务.

-- 1.submit
用于建立一个新作业.当建立作业时,需要给作业要执行的操作,作业的下次运行日期及运行时间间隔.
语法如下:
dbms_out.submit (
job out binary_integer,what in varchar2,
next_date in date default sysdate,
interval in varchar2 default 'null',
no_parse in boolean default false,
instance in binary_integer default any_instance,
force in boolean default false
);
注:job用于指定作业编号;what用于指定作业要执行的操作;next_date用于指定作业的下次运行日期;interval用于指定运行作业的时间间隔;no_parse用于指定是否解析与作业相关的过程;instance用于指定哪个例程可以运行作业;force用于指定是否强制运行与作业相关的例程.
示例如下:
var jobno number
begin
dbms_job.submit(:jobno,
'dbms_ddl.analyze_object(''table'',
''scott'',''emp'',''compute'');',
sysdate,'sysdate+1');
commit;
end;
/

-- 2.remove
删除作业队列中的特定作业
示例如下:
SQL>exec dbms_job.remove(1);

-- 3.change
用于改变与作业相关的所有信息,包括作业操作,作业运行日期以及运行时间间隔等.
示例如下:
SQL>exec dbms_job.change(2,null,null,'sysdate+2');

-- 4.what
用于改变作业要执行的操作
示例如下:
SQL>exec dbms_job.what(
2,'dbms_stats.gather_table_stats->(''scott'',''emp'');');

-- 5.next_date
用于改变作业的下次运行日期
示例如下:
SQL>exec dbms_job.next_date('2','sysdate+1');

-- 6.instance
用于改变作业的例程
示例如下:
SQL>exec dbms_job.instance(2,1);

-- 7.interval
用于改变作业的运行时间间隔
示例如下:
SQL>exec dbms_job.interval(2,'sysdate+1/24/60');

-- 8.broken
用于设置作业的中断标识
示例如下:
SQL>exec dbms_job.broken(2,true,'sysdate+1');

-- 9.run
用于运行已存在的作业
示例如下:
sql>exec dbms_job.run(1);

DBMS_TRANSACTION

作用:用于在过程,函数,和包中执行SQL事务处理语句.

1.read_only
用于开始只读事务,其作用与SQL语句SET TRANSACTION READ ONLY完全相同
2.read_write
用于开始读写事务,------------------------------------WRITE-------
3.advise_rollback
用于建议回退远程数据库的分布式事务
4.advise_nothing
用于建议远程数据库的分布式事务不进行任何处理
5.advise_commit
用于建议提交远程数据库的分布式事务
6.user_rollback_segment
用于指定事务所要使用的回滚段
7.commit_comment
用于在提交事务时指定注释.
8.commit_force
用于强制提交分布式事务.
9.commit
用于提交当前事务
10.savepoint
用于设置保存点
11.rollback
用于回退当前事务
12.rollback_savepoint
用于回退到保存点
13.rollback_force
用于强制回退分布式事务
14.begin_discrete_transaction
用于开始独立事务模式
15.purge_mixed
用于清除分布式事务的混合事务结果
16.purge_lost_db_entry
用于清除本地数据库所记载的远程事务入口,该事务入口操作因为远程数据库问题未能在远程数据库完成.
17.local_transaction_id
用于返回当前事务的事务标识号
18.step_id
用于返回排序DML事务的惟一正整数

DBMS_SESSION

作用: 提供了使用PL/SQL实现ALTER SESSION命令,SET ROLE命令和其他会话信息的方法.

1.set_identifier
用于设置会话的客户ID号
2.set_context
用于设置应用上下文属性
3.clear_context
用于清除应用上下文的属性设置
4.clear_identifier
用于删除会话的set_client_id.
5.set_role
用于激活或禁止会话角色
6.set_sql_trace
用于激活或禁止当前会话的SQL跟踪
语法如下:
dbms_session.set_sql_trace(sql_strace boolean);
7.set_nls
用于设置NLS特征
语法如下:
dbms_session.set_nls(param varchar2,value varchar2);
8.close_database_link
用于关闭已经打开的数据库链
9.reset_package
用于复位当前会话的所有包,并且会释放包状态
10.modify_package_state
用于修改当前会话的PL/SQL程序单元的状态
语法如下:
dbms_session.modify_package_state(action_flags in pls_integer);
11.unique_session_id
用于返回当前会话的惟一ID标识符
12.is_role_enabled
用于确定当前会话是否激活了特定角色.
语法如下:
dbms_session.is_role_enabled(rolename varchar2)
return boolean;
13.is_session_alive
用于确定特定会话是否处于活动状态.
14.set_close_cached_open_cursors
用于打开或关闭close_cached_open_cursors
15.free_unused_user_meory
用于在执行了大内在操作(超过100K)之后回收未用内存
16.set_context
设置应用上下文属性的值
17.list_context
用于返回当前会话原命名空间和上下文列表
18.swith_current_consumer_group
用于改变当前会话的资源使用组

DBMS_RLS

作用: 只适用于Oracle Enterprise Edition,它用于实现精细访问控制,并且精细访问控制是通过在SQL语句中动态增加谓词(WHERE子句)来实现的.通过使用ORACLE的精细访问控制特征,可以使不同数据库用户在执行相同SQL语句时操作同一张表上的不同数据.

DBMS_SHARED_POOL

作用: 提供了对共享池的一些过程和函数访问,它使用户可以显示共享池中的对象尺寸,绑定对象到共享池,清除绑定到共享池的对象.为了使用该包,必须运行dbmspool.sql脚本来建立该包.

-- 清理SQL缓存
select ADDRESS,HASH_VALUE from v$sqlarea where SQL_ID='g6gu1n3x0h1h4';
exec dbms_shared_pool.purge('ADDRESS,HASH_VALUE','C');

DBMS_OBFUSCATION_TOOLKIT

作用: 用于加密和解密数据,另外还可以生成密码检验和.通过加密输入数据,可以防止黑客或其他用户窃取私有数据;而通过结合使用加密和密码检验和,可以防止黑客破坏初加密的数据.当使用该包加密数据时,要求被加密数据的长度必须为8字节的整数倍.当使用DES算法加密数据时,密钥长度不能低于8字节;当使用DES3算法加密数据时,密钥长度不能低于16字节.

DBMS_SPACE

作用: 用于分析段增长和空间的需求

DBMS_SPACE_ADMIN

作用: 提供了局部管理表空间的功能

DBMS_TTS

作用: 用于检查表空间集合是否是自包含的,并在执行了检查之后,将违反自包含规则的信息写入到临时表TRANSPORT_SET_VIOLATIONS中.

-- 检查表空间自包含（用户里面所有的对象都在这个表空间里面，不能跨表空间，即为自包含）
begin
  sys.dbms_tts.transport_set_check('TEST02', true, true);
end;
/

select * from sys.transport_set_violations;

DBMS_REPAIR

作用: 用于检测,修复在表和索引上的损坏数据块.

DBMS_RESOURCE_MANAGER

作用: 用于维护资源计划,资源使用组和资源计划指令;包dbms_resource_manager_privs用于维护与资源管理相关的权限.

UTL_INADDR

作用: 用于取得局域网或Internet环境中的主机名和IP地址.

-- 根据主terminal查询客户端的IP
select UTL_INADDR.get_host_address(terminal) from v$session where username is not null;

UTL_FILE

UTL_FILE包包含了一系列过程和函数，可以对本地文件系统进行操作。使用这些过程和函数，您可以读取、写入、创建和删除本地文件。

例如，如果您想创建一个新的文本文件，您可以使用以下代码：

DECLARE
fileHandle UTL_FILE.FILE_TYPE;
BEGIN
fileHandle := UTL_FILE.FOPEN('DIRECTORY', '文件名', 'W');
UTL_FILE.FCLOSE(fileHandle);
END;

其中，“DIRECTORY”代表文件的目录，“文件名”代表文件的名称。

参考资料

https://docs.oracle.com/cd/E11882_01/appdev.112/e40758/toc.htm

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/arpls/index.html#Oracle%C2%AE-Database