常用包使用（包括STA）

ORACLE 常见系统包及常用方法

一、dbms_metadata.get_ddl:生成数据库对象的ddl信息:

1）获得表、索引、视图、存储过程、函数的DDL

select dbms_metadata.get_ddl('TABLE','T','SCOTT') from dual;

select dbms_metadata.get_ddl('INDEX','INDEX_NAME','INDEX_OWNER') from dual;

select dbms_metadata.get_ddl('VIEW','VIEW_NAME','VIEW_OWNER') from dual;

select dbms_metadata.get_ddl('PROCEDURE','PROCEDURE_NAME','PROCEDURE_OWNER') from dual;

select dbms_metadata.get_ddl('FUNCTION','FUNCTION_NAME','FUNCTION_OWNER') from dual;

这个脚本用于获得某个schema下所有的表、索引、视图、存储过程、函数的DDL

下面这个脚本用于获得某个schema下所有的表、索引、视图、存储过程、函数的DDL

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;

2）获得表空间的DDL

select dbms_metadata.get_ddl('TABLESPACE','USERS') from dual;

SELECT DBMS_METADATA.GET_DDL('TABLESPACE', TS.tablespace_name)
FROM DBA_TABLESPACES TS;

3）获得用户的DDL

select dbms_metadata.get_ddl('USER','EPAY_USER') from dual;

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

二、dbms_xplan包sql执行计划管理

实例一：

explain plan for select * from scott.emp;

select * from table(dbms_xplan.display);

使用了dbms_xplan.display方法来显示PLAN_TABLE中保存的解释计划，如果想要显示执行计划，就需要使用到DMBS_XPLAN.DISPLAY_CURSOR方法

使用display_cursor方法查看最近一条语句的执行计划

select /*+ gather_plan_statistics */ count(*) from scott.emp;

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

select * from table(dbms_xplan.display_cursor('5qxmkvh40yw0p',0,'ALLSTATS LAST'));

通过format参数定制执行计划输出信息

explain plan for select * from t;

select * from table(dbms_xplan.display(format=>'ALL'));

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

使用dbms_stats搜集统计信息

1：创建统计信息历史保留表 

 exec dbms_stats.create_stat_table(ownname => 'scott',stattab => 'stat_table') ; 

2：导出整个scheme的统计信息 

exec dbms_stats.export_schema_stats(ownname => 'scott',stattab => 'stat_table') ; 

3：分析数据

（1）：分析scheme

Exec dbms_stats.gather_schema_stats( 

（2）：分析表

exec dbms_stats.gather_table_stats(ownname => 'scott',tabname => 'work_list',estimate_percent => 10,method_opt=> 'for all indexed columns') ; 

（3）：分析索引

exec dbms_stats.gather_index_stats(ownname => 'SCOTT',indname => 'PK_DEPT',estimate_percent => '10',degree => '2') ;

如果发现执行计划走错，删除表的统计信息

dbms_stats.delete_table_stats(ownname => 'scott',tabname => 'work_list') ;

导入表的历史统计信息

exec dbms_stats.import_table_stats(ownname => 'scott',tabname => 'work_list',stattab =>  'stat_table') ; 

如果进行分析后，大部分表的执行计划都走错，需要导回整个scheme的统计信息

exec dbms_stats.import_schema_stats(ownname => 'scott',stattab => 'stat_table');

exec dbms_stats.import_index_stats(ownname => 'SCOTT',indname => 'IDX_ADM_PERMISSION_PID_MID',stattab => 'stat_table') 

-检查是否导入成功的语句

 select table_name,num_rows,a.blocks,a.last_analyzed from all_tables a where a.table_name='';

四：dbms_advisor包（Segment Advisor）

测试案例：

create table advisor_test as select * from dba_objects;

insert into advisor_test select * from advisor_test;

/

/ commit;

delete advisor_test where rownum<100000;

/ commit;

declare
  my_task_id  number;
  obj_id      number;
  my_task_name  varchar2(100);
  my_task_desc  varchar2(500);
 begin
  my_task_name :='advisor_test tab Advice';  --运行任务名，可以任意指定
  my_task_desc :='Manual Segment Advisor Run'; -运行任务描述，可以任意指定
 -----step 1
  dbms_advisor.create_task(           --创建一个段顾问任务
  advisor_name => 'Segment Advisor',   --运行段顾问这个参数必须指定为Segment Advisor
  task_id => my_task_id,
  task_name => my_task_name,
  task_desc =>my_task_desc);
 -----step 2为这个任务分配一个对象
  dbms_advisor.create_object(
  task_name=>my_task_name,
  object_type=>'TABLE',
  attr1=>'TEST',  --如果在表对象级别运行，这个属性为用户名，表空间级别这个属性为 --表空间名字
  attr2 => 'ADVISOR_TEST',  ---如果在表对象级别运行，这个属性为表名，表空间级别这个属性为null
  attr3 => NULL,
  attr4=>null,
  attr5=>null,
  object_id=>obj_id);
 -----step 3设置任务参数

/* 设置段顾问运行参数"ecommend_all"的值,为TRUE则为所有类型的对象的生成建议，FALSE则仅生成与空间相关的建议 */
 /* 另一个滚问运行参数"time_limit",制定顾问运行的时间限制，默认值为无限制 */
  dbms_advisor.set_task_parameter(
  task_name => my_task_name,
  parameter=>'recommend_all',
  value=>'TRUE');
 -----step 4执行这个任务
  dbms_advisor.execute_task(my_task_name);
 end;
 /

执行下面语句：

declare

  my_task_id number;

  obj_id number;

  my_task_name varchar2(100);

  my_task_desc varchar2(500);

 begin

  my_task_name :='advisor_test tab Advice';

  my_task_desc :='Manual Segment Advisor Run';

  dbms_advisor.create_task(

  advisor_name => 'Segment Advisor',

  task_id => my_task_id,

  task_name => my_task_name,

  task_desc =>my_task_desc);

  dbms_advisor.create_object(

  task_name=>my_task_name,

  object_type=>'TABLE',

  attr1=>'TEST', 

  attr2 => 'ADVISOR_TEST', 

  attr3 => NULL,

  attr4=>null,

  attr5=>null,

  object_id=>obj_id);

  dbms_advisor.set_task_parameter(

  task_name => my_task_name,

  parameter=>'recommend_all',

  value=>'TRUE');

  dbms_advisor.execute_task(my_task_name);

 end;

 /

select

 'Task name    :'||f.task_name||chr(10)||

 'Segment name  :'||o.attr2    ||chr(10)||

 'Sement type  :'||o.type    ||chr(10)||

 'partition name:'||o.attr3    ||chr(10)||

 'Message      :'||f.message  ||chr(10)||

 'More info    :'||f.more_info TASK_ADVICE

 from dba_advisor_findings f,dba_advisor_objects o

 where o.task_id=f.task_id

   and o.object_id=f.object_id

 and f.task_name = 'advisor_test tab Advice'

 order by f.task_name;

案例2（表空间级别运行）

declare
  my_task_id number;
  obj_id number;
  my_task_name varchar2(100);
  my_task_desc varchar2(500);
 begin
  my_task_name :='Tablespace Advice';
  my_task_desc :='Manual Segment Advisor Run';
 -----step 1
  dbms_advisor.create_task(
  advisor_name => 'Segment Advisor',
  task_id => my_task_id,
  task_name => my_task_name,
  task_desc =>my_task_desc);
 -----step 2
  dbms_advisor.create_object(
  task_name=>my_task_name,
  object_type=>'TABLESPACE',
  attr1=>'USERS',
  attr2 => null,
  attr3 => NULL,
  attr4=>null,
  attr5=>null,
  object_id=>obj_id);
 -----step 3
  dbms_advisor.set_task_parameter(
  task_name => my_task_name,
  parameter=>'recommend_all',
  value=>'TRUE');
 -----step 4
  dbms_advisor.execute_task(my_task_name);
 end;
 /

删除任务命令：

exec dbms_advisor.delete_task(task_name => 'advisor_test tab Advice');

alter table my_objects_move move;

alter index I_MY_OBJECTS_MOVE rebuild;

alter table my_objects enable row movement;

alter table my_objects shrink space;

analyze table my_table compute statistics;  

analyze table my_table compute statistics for table for all indexes for all columns;   

SELECT table_name,                                        

ROUND((blocks * 8), 2) "高水位空间 k",                    

ROUND((num_rows * avg_row_len / 1024), 2) "真实使用空间 k",

ROUND((blocks * 10 / 100) * 8, 2) "预留空间(pctfree) k",  

ROUND((blocks * 8 - (num_rows * avg_row_len / 1024) -     

blocks * 8 * 10 / 100),                                   

2) "浪费空间 k"                                           

FROM dba_tables                                           

WHERE table_name = 'ADVISOR_TEST';    

五：SQL tuning advisor(STA)自动优化SQL

一、使用STA优化awr中SQL的脚本

无绑定变量SQL的优化

declare

  my_task_name VARCHAR2(30);

  my_sqltext   CLOB;

BEGIN

  my_sqltext   := 'select * from orabpel.cube_scope';

  my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_text    => my_sqltext,

                                                  user_name   => 'orabpel',

                                                  scope       => 'COMPREHENSIVE',

                                                  time_limit  => 60,  --优化限时60s

                                                  task_name   => 'wxw_sql_tuning_task',

                                                  description => 'tune the bad sql');

  dbms_sqltune.Execute_tuning_task(task_name => 'wxw_sql_tuning_task');

END;

--查看优化结果

set long 10000 

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('wxw_sql_tuning_task') FROM DUAL;

二、通过SQL_ID进行优化

    这种情况通常用于v$sql中的异常SQL优化

declare

  l_tuning_task varchar2(30);

begin

  l_tuning_task := dbms_sqltune.create_tuning_task(sql_id => ' 953bgyvrvryq1');

  dbms_sqltune.execute_tuning_task(l_tuning_task);

  dbms_output.put_line(l_tuning_task);--A需要跟据此处输出值进行查询

end;

--查看执行计划

set long 10000 

select dbms_sqltune.report_tuning_task('task_27888') from dual; --此处标红值是--A此的输出

三、通过指定AWR中的Snap_id进行优化

declare

     my_task_name varchar2(30);

begin

     dbms_sqltune.drop_tuning_task(task_name => 'wxw_sql_tuning_task');

     my_task_name := dbms_sqltune.create_tuning_task(

          begin_snap      => 24365,

          end_snap        => 24366,

          sql_id          => 'd40kghyfbg8sj',

          plan_hash_value => null,

          scope           => 'comprehensive',

          time_limit      => 60,

          task_name       => 'wxw_sql_tuning_task',

          description     => 'tune the bad sql'

     );

     dbms_sqltune.execute_tuning_task (task_name => 'wxw_sql_tuning_task');

  end;

--查看sql优化结果

set long 10000 

 select dbms_sqltune.report_tuning_task('wxw_sql_tuning_task') from dual;

六：DBMS_LOGMNR

sqlplus / as sysdba

sql>@?/rdbms/admin/dbmslm.sql

sql>@?/rdbms/admin/dbmslmd.sql

把数据字典内容映射到一个文本文件里面

alter system set utl_file_dir='/mc/oracle/logmnr' scope=spfile

创建字典文件

exec dbms_logmnr_d.build(dictionary_filename => 'mcdict.ora',dictionary_location => '/mc/oracle/logmnr');

exec dbms_logmnr.add_logfile('/opt/oracle11g/starboss/redo03.log',dbms_logmnr.new);

exec dbms_logmnr.add_logfile('/opt/oracle11g/starboss/redo02.log',dbms_logmnr.addfile);

exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);

drop table test_log;

create table test_log tablespace users

as select * from v$logmnr_contents;

execute dbms_logmnr.end_logmnr;

select * from test_log

select  from v$logmnr_contents where table_name='T1'

select SCN,sql_redo,sql_undo from v$logmnr_contents  where table_name = 'T1';

七：dbms_flashback

获取当前scn

select to_char(dbms_flashback.GET_SYSTEM_CHANGE_NUMBER) from dual;

实例1：Flashback Query

Flashback Query 是利用多版本读一致性的特性从UNDO 表空间读取操作前的记录数据！

1、As of timestamp的示例

 create table tab_test as select rownum id from dba_users;

 delete from tab_test where id<5;

 commit; 

假设当前距离删除数据已经有1分钟左右的话： 

select count(*) from tab_test as of timestamp sysdate-1/1440 where id<10; 

 insert into tab_test select * from tab_test as of timestamp sysdate-1/1440 where id<5;

commit;

2、As of scn的示例

获取当前scn 的方式非常多，

select current_scn from v$database;

select dbms_flashback.get_system_change_number from dual;

 delete tab_test where id>5;  commit; 

 select * from tab_test as of scn 191460; 

 insert into tab_test select * from tab_test as of scn 191460 where id not in (select id from tab_test);

 commit; 

select scn,to_char(time_dp,'yyyy-mm-dd hh24:mi:ss') time_dp from sys.smon_scn_time; 

实例2:  Flashback Table

Flashback table  [objName]  to  before  drop

Drop table 

select object_name,original_name,operation,type,createtime,droptime,dropscn from recyclebin; 

flashback table  table_name to before drop;

 

如果重名：

flashback table tab_test to before drop rename to tab_test_bak;

purge table tab_test;

purge table "BIN$JhA057bpRxKICIe/vNahyQ==$0";

清空回收站：

purge recyclebin;

 

实例3：flashback Database

 select flashback_on,force_logging from v$database;

 

配置：

1：archive log list; 必须归档模式！

2：设置闪回恢复区

alter system set db_recovery_file_dest_size=1g scope=spfile;

alter system set db_recovery_file_dest='/opt/flashback_area' scope=spfile;

alter system set db_flashback_retention_target=1440 scope=both;   (单位是分钟，1440 一天）

3:打开闪回功能

shutdown immediate;

startup mount;

alter database flashback on;

alter database open;

select flashback_on,force_logging from v$database;

mount 下关闭

alter database flashback off;

 

 测试：

 select scn,to_char(time_dp,'yyyy-mm-dd hh24:mi:ss') time_dp from sys.smon_scn_time; 

模拟误删除操作  做一些DML/DDL操作  

重新启动到mount 状态，然后执行恢复

select dbms_flashback.get_system_change_number from dual;

conn /as sysdba 

shutdown immediate

startup mount 

flashback database to scn  xxx; 

 提示：此处flashback database to timestamp 也是一样的，不过你需要估计好恢复到的时间点。  

alter database open resetlogs 打开数据库 ，当然，指定scn 或者timestamp 时间点之后产生的数据统统丢失。