1、表
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; |
