table常用脚本

1.1、查看表定义
select dbms_metadata.get_ddl('TABLE','TABLE_NAME','TABLE_OWNER') from dual;

1.2、取得一个表的所有字段名并用逗号分割
select max(substr(SYS_CONNECT_BY_PATH(COLUMN_NAME, ','),2)) col from (
select COLUMN_NAME,column_id from user_tab_columns where table_name='&tablename')
start with column_id=1 connect by column_id=rownum;

1.3、查询某张表被哪些存储过程或者视图用到
--V表示视图，P表示存储过程，tablename为需要查询被引用的表名称
select distinct object_name(id) from syscomments  where id in (select id from sysobjects where type in('V','P')) and text like '%&tablename%';
--或者
select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like '%tablename%';

1.4、查找某表或某索引增长的历史信息
通过AWR来查找一段时间内，数据库段对象(堆表、索引)等的空间增长信息。
在Oracle 10g开始awr自动负载仓库引入了dba_hist_seg_stat视图，该视图记录了快照时间内segment-level段级的历史统计信息
column owner format a16
column object_name format a36
column start_day format a11
column block_increase format 9999999999

select obj.owner, obj.object_name,
         to_char(sn.BEGIN_INTERVAL_TIME,'RRRR-MON-DD') start_day,
         sum(a.db_block_changes_delta) block_increase
from dba_hist_seg_stat a,
         dba_hist_snapshot sn,
         dba_objects obj
where sn.snap_id = a.snap_id
and obj.object_id = a.obj#
and obj.owner not in ('SYS','SYSTEM')
and end_interval_time between to_timestamp('01-MAR-2020','DD-MON-RRRR')
         and to_timestamp('30-MAR-2020','DD-MON-RRRR')
and object_name like '%&object_name%' 
group by obj.owner, obj.object_name,
         to_char(sn.BEGIN_INTERVAL_TIME,'RRRR-MON-DD')
order by obj.owner, obj.object_name
/

1.5、物理IO前10的表
SELECT TABLE_NAME,TOTAL_PHYS_IO
FROM ( SELECT OWNER||'.'||OBJECT_NAME AS TABLE_NAME,
             SUM(VALUE) AS TOTAL_PHYS_IO
      FROM  V$SEGMENT_STATISTICS
      WHERE OWNER!='SYS' AND OBJECT_TYPE='TABLE'
 AND STATISTIC_NAME IN ('physical reads','physical reads direct',
                      'physical writes','physical writes direct')
      GROUP BY OWNER||'.'||OBJECT_NAME
      ORDER BY TOTAL_PHYS_IO DESC)
WHERE ROWNUM <=10;

1.6、碎片程度高的表
Set linesize 1000 pagesize 1000
SELECT TABLE_NAME,(BLOCKS*8192/1024/1024)"理论大小M",
(NUM_ROWS*AVG_ROW_LEN/1024/1024/0.9)"实际大小M",
round((NUM_ROWS*AVG_ROW_LEN/1024/1024/0.9)/(BLOCKS*8192/1024/1024),3)*100||'%' "实际使用率%"
FROM USER_TABLES where blocks>100 and (NUM_ROWS*AVG_ROW_LEN/1024/1024/0.9)/(BLOCKS*8192/1024/1024)<0.3
order by (NUM_ROWS*AVG_ROW_LEN/1024/1024/0.9)/(BLOCKS*8192/1024/1024) desc;

1.7、集群因子clustering_factor高的表
集群因子越接近块数越好，接近行数则说明索引列的列值相等的行分布极度散列，可能不走索引扫描而走全表扫描
select tab.table_name,tab.blocks,tab.num_rows,ind.index_name,ind.clustering_factor,
round(nvl(ind.clustering_factor,1)/decode(tab.num_rows,0,1,tab.num_rows),3)*100||'%' "集群因子接近行数"
from dba_tables tab, dba_indexes ind where tab.table_name=ind.table_name
and tab.blocks>100 and nvl(ind.clustering_factor,1)/decode(tab.num_rows,0,1,tab.num_rows) between 0.35 and 3;

1.8、获取XX用户下所有表的表名与其数据量
--1、创建存储过程和基础表table_rows
create or replace procedure testhwb(own in VARCHAR2)
is
  v_table varchar(100);
  v_num   number;
  v_sql   varchar(500);
  cursor c1 is
    select table_name from dba_tables where owner = "own";
  --如果没有dba权限则修改成：select table_name from user_tables;
begin
  DBMS_OUTPUT.ENABLE(buffer_size => null); --表示输出buffer不受限制
  open c1;
  loop
    fetch c1
      into v_table;
    if c1%found then
      v_sql := 'select count(*) from ||own||"."||v_table';
      execute immediate v_sql
        into v_num;
      dbms_output.put_line('table_name：' || v_table || ' count_rows： ' ||
                           v_num);
      insert into table_rows values (v_table, v_num);
    else
      exit;
    end if;
  end loop;
  commit;
  close c1;
end;

--2、执行存储过程
begin
  testhwb('NWPP_TEST');
end;

1.9、查看高水位线
HWM = total_blocks- Unused Blocks +1
--1、创建存储过程
create or replace procedure show_space(p_segname   in varchar2,
                                       p_owner     in varchar2 default user,
                                       p_type      in varchar2 default 'TABLE',
                                       p_partition in varchar2 default NULL) as
  l_total_blocks       number;
  l_total_bytes        number;
  l_unused_blocks      number;
  l_unused_bytes       number;
  l_LastUsedExtFileId  number;
  l_LastUsedExtBlockId number;
  l_last_used_block    number;
  procedure p(p_label in varchar2, p_num in number) is
  begin
    dbms_output.put_line(rpad(p_label, 40, '.') || p_num);
  end;
begin
  dbms_space.unused_space(segment_owner             => p_owner,
                          segment_name              => p_segname,
                          segment_type              => p_type,
                          partition_name            => p_partition,
                          total_blocks              => l_total_blocks,
                          total_bytes               => l_total_bytes,
                          unused_blocks             => l_unused_blocks,
                          unused_bytes              => l_unused_bytes,
                          last_used_extent_file_id  => l_LastUsedExtFileId,
                          last_used_extent_block_id => l_LastUsedExtBlockId,
                          last_used_block           => l_last_used_block);
  p('Total Blocks', l_total_blocks);
  p('Total Bytes', l_total_bytes);
  p('Unused Blocks', l_unused_blocks);
  p('Unused Bytes', l_unused_bytes);
  p('Last Used Ext FileId', l_LastUsedExtFileId);
  p('Last Used Ext BlockId', l_LastUsedExtBlockId);
  p('Last Used Block', l_last_used_block);
end;
/

--2、查看ssys.aud$的HWM线
set serveroutput on
exec show_space(p_segname=>'AUD$',p_owner =>'SYS',p_type => 'TABLE');  

1.10、一键检查数据库高水位表并回收
--1、高水位表排序
SELECT 'alter table '||table_name||' move' ,table_name,
       tablespace_name,
       status,
       last_analyzed,
       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 user_tables
 WHERE temporary = 'N'
 ORDER BY 9 DESC;
--2、回收高水位表
//第一种方法：MOVE回收高水位(move需要额外的空间空间，move后需要重建索引)
alter table my_objects move;
//第二种方法：shrink回收高水位(shrink后不需要重建索引,如果一个表的索引比较多，shrink过程中用来维护index的成本也会比较高，，shrink不影响dml操作，能在线)
alter table my_objects enable row movement;
alter table my_objects shrink space;
--重新收集统计信息(针对表手动收集)
ANALYZE TABLE  TABLE_NAME  COMPUTE STATISTICS;

1.11、recover_truncate_tab_plsql
用于恢复truncate table，恢复的主要思路：
1)通过logmnr找到被truncate表上一次的data_object_id。有一种特殊情况：如果表只被truncate一次，那么上次的data_object_id和object_id是一致的。
2)通过dba_free_space获取所有free block。
3)通过dbms_rowid.rowid_create来创建rowid。
4)利用rowid来抽取数据。
-- 用于恢复truncate table
-- 作者：Oracle恢复实录公众号作者
-- 2020-01-13
-- 详细请参考文章《TRUNCATE TABLE恢复系列二：PL/SQL恢复》https://www.modb.pro/db/14957
declare
   v_fno number;
   v_s_bno number;
   v_e_bno number;
   v_rowid rowid;
   nrows number;
begin
   for i in (select relative_fno,block_id,blocks
               from dba_extents
                          where owner='RESCUREORA' and segment_name='RESCUREORA_TABLE' and extent_id=0  -- RESCUREORA和RESCUREORA_TABLE 均为原表
             union all
             select relative_fno,block_id,blocks
               from dba_free_space
              where tablespace_name in
                          (select tablespace_name
                             from dba_tables
                                where owner='RESCUREORA' and table_name='RESCUREORA_TABLE')  -- RESCUREORA和RESCUREORA_TABLE 均为原表
                         union all
                         select relative_fno,block_id,blocks from (
                         select relative_fno,block_id,blocks,row_number()over(partition by owner,segment_name,PARTITION_NAME order by extent_id desc) rn
               from dba_extents
              where tablespace_name in
                          (select tablespace_name
                             from dba_tables
                                where owner='RESCUREORA' and table_name='RESCUREORA_TABLE') and extent_id>0)  -- RESCUREORA和RESCUREORA_TABLE 均为原表
                           where rn=1) loop
   v_fno:=i.relative_fno;
   v_s_bno:=i.block_id;
   v_e_bno:=i.block_id+i.blocks-1;
       for j in v_s_bno .. v_e_bno loop
        begin
         for x in 0 .. 999 loop
           v_rowid:=dbms_rowid.rowid_create(1, 87903,v_fno,j,x); -- 87903 为dataobj#
           insert into sys.rescureora_table select * from rescureora.rescureora_table where rowid=v_rowid;
            -- 其中：sys.rescureora_table 为目标表，rescureora.rescureora_table为原表
         end loop;
        exception
          when others then
            null;
        end;
        commit;
       end loop;
    end loop;
end;
/

1.12、查询热点块和热点块的操作
---查询热块对象
SELECT *  
  FROM (SELECT O.OWNER, O.OBJECT_NAME, O.OBJECT_TYPE, SUM(TCH) TOUCHTIME
          FROM X$BH B, DBA_OBJECTS O  
         WHERE B.OBJ = O.DATA_OBJECT_ID  
           AND B.TS# > 0  
         GROUP BY O.OWNER, O.OBJECT_NAME, O.OBJECT_TYPE  
         ORDER BY SUM(TCH) DESC)  
 WHERE ROWNUM <= 10;   
--查找热点块操作语句  
SELECT /*+rule*/
 HASH_VALUE, SQL_ID,SQL_TEXT
  FROM V$SQLTEXT
 WHERE (HASH_VALUE, ADDRESS) IN
       (SELECT A.HASH_VALUE, A.ADDRESS
          FROM V$SQLTEXT A,
               (SELECT DISTINCT A.OWNER, A.SEGMENT_NAME, A.SEGMENT_TYPE
                  FROM DBA_EXTENTS A,
                       (SELECT DBARFIL, DBABLK
                          FROM (SELECT DBARFIL, DBABLK
                                  FROM X$BH
                                 ORDER BY TCH DESC)
                         WHERE ROWNUM < 11) B
                 WHERE A.RELATIVE_FNO = B.DBARFIL
                   AND A.BLOCK_ID <= B.DBABLK
                   AND A.BLOCK_ID + A.BLOCKS > B.DBABLK) B
         WHERE A.SQL_TEXT LIKE '%' || B.SEGMENT_NAME || '%'
           AND B.SEGMENT_TYPE = 'TABLE')
 ORDER BY HASH_VALUE, ADDRESS, PIECE;
 
1.13、查找lobsegment、lobindex对应的表
当表含有LOB字段时，Oracle会为含有LOB字段的列单独创建一个lobsegment，同时还会创建一个lobindex。那么lobsegment、lobindex是如何与表关联起来的呢？
select owner, table_name, column_name, segment_name, index_name  from dba_lobs where segment_name='I_TRANSACTION_XML_BLOB'


1.14、大表删除如何监控delele进度
大表删除时需要注意观察UNDO表空间使用率（观察连接的是哪个实例，然后看对应的UNDO空间，假设UDNOTBS1使用了比较多空间，就需要连接到RFDB2实例，充分利用UNDO空间），归档空间情况（虽然已经nologging，但归档日志还是有发生切换，需要注意空间使用情况）,数据库服务器负载和告警日志。
--1、v$transaction查看删除进度
select start_time,start_scnb,used_ublk,used_urec,log_io,phy_io from v$transaction;
说明：
start_time --> start_time 
start_scnb --> 开始的scn
used_ublk --> 占用的undo block
used_urec --> undo记录的行数
log_io    --> 逻辑io 注意并非Consistent gets，有专门一列：CR_GET
phy_io    --> 物理io
--2、查询正运行事务
用如下SQL查询到正在运行的事务，如其used_urec字段不断增加，说明该事务正在继续，如果该字段不断下降，说明该事物正在回滚。
SELECT a.sid, a.username, b.xidusn, b.used_urec, b.used_ublk from v$session a, v$transaction b WHERE a.saddr = b.ses_addr;
--3、监控数据库中的回滚事务
对于数据库中的回滚事务，可以查看下面的视图监控：
V$FAST_START_SERVERS
V$FAST_START_TRANSACTIONS
分别查看回滚的进程信息和事务信息

1.15、在线调整oracle回滚的速度及查看回滚速度
关于fast_start_parallel_rollback参数，此参数决定了回滚启动的并行次数，在繁忙的系统或者IO性能较差的系统，如果出现大量回滚操作，会显著影响系统系统，可以通过调整此参数来降低影响。
FAST_START_PARALLEL_ROLLBACK specifies the degree of parallelism used when recovering terminated transactions. Terminated transactions are transactions that 
are active before a system failure. If a system fails when there are uncommitted parallel DML or DDL transactions, then you can speed up transaction recovery 
during startup by using this parameter.
Values:
    FALSE:
    Parallel rollback is disabled
    LOW:
    Limits the maximum degree of parallelism to 2 * CPU_COUNT
    HIGH:
    Limits the maximum degree of parallelism to 4 * CPU_COUNT
If you change the value of this parameter, then transaction recovery will be stopped and restarted with the new implied degree of parallelism.
--1、查看回滚进度
USN：事务对应的undo段
STATE：事务的状态，可选的值为（BE RECOVERED, RECOVERED, or RECOVERING）       
UNDOBLOCKSDONE：已经完成的undo块
UNDOBLOCKSTOTAL：总的undo数据块
CPUTIME：已经回滚的时间，单位是秒
RCVSERVERS：回滚的并行进程数

select usn,
       state,
       undoblocksdone,
       undoblockstotal,
       CPUTIME,
       pid,
       xid,
       rcvservers
  from v$fast_start_transactions;
--或者以下sql
select ktuxeusn USN, ktuxeslt Slot, ktuxesqn Seq, ktuxesta State, ktuxesiz Undo from sys.ktuxe
  where ktuxesta <> 'INACTIVE'
  and ktuxecfl like '%DEAD%'
  order by ktuxesiz asc;

--2、计算回滚时间
select  undoblockstotal "Total", undoblocksdone "Done", undoblockstotal-undoblocksdone
"ToDo",decode(cputime,0,'unknown',to_char(sysdate+(((undoblockstotal-undoblocksdone)
/ (undoblocksdone / cputime)) / 86400),'yyyy-mm-dd hh24:mi:ss'))
"Estimated time to complete"，to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')
from v$fast_start_transactions;

--3、设置fast_start_parallel_rollback加快undo回滚的速度，在线修改，立即生效
alter system set fast_start_parallel_rollback=HIGH scope=both;