数据库管理脚本

-- 段头信息查询
select header_file,header_block from dba_segments where segment_name='T' and owner = 'GO';

-- dump段头数据的Block
alter system dump datafile 4 block 18499;
在 $ORACLE/admin/sid/udump 下

-- 数据行所在数据文件ID和块ID
select dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid) from bb;

-- 对象在Buffer Cache中的使用情况
select file#,block# from v$bh where objd=(select data_object_id from user_objects where object_name='T');

-- Session 进程查询
select username, sid, serial#, program, process from v$session;
alter system kill session 'sid serial# ';

-- 临时表空间使用状态
select x.sid, x.username, y.tablespace, y.blocks from v$session x, v$tempseg_usage y where x.saddr=y.session_addr;

-- 查看数字对应的ascii码
select chr(to_number('31', 'xx')) from dual;

-- 查询高负载的SQL
SELECT ADDRESS,substr(SQL_TEXT,1,20) Text,buffer_gets,executions,buffer_gets/executions AVG FROM v$sqlarea WHERE executions>0 AND buffer_gets > 100000 ORDER BY 5;

-- 索引包含哪些列
select * from dba_ind_columns where table_name='T';

-- 聚簇因子
select a.index_name,b.num_rows,b.blocks,a.clustering_factor from user_indexes a, user_tables b where index_name in ('INDEX_NAME') and a.table_name = b.table_name;

-- 2进制转对应10进制数值查询
select utl_raw.cast_to_number('c20b05') from dual;