对表进行碎片整理
1. move表移动后相关索引和统计信息会失效, 需要重创建或收集1-1. 普通 heap 表# 生成新数据段, 块内数据重新配置, data_object_id 会发生改变alter table t move [ pctfree | pctused | initrans ] [tablespace ts_name];1-2. 索引组织表 (IOT)alter table t move online;1-3. 分区表1-3-1. 分区单位的移动alt...
UNDO相关查询
1. undo 相关视图dba_rollback_segsdba_undo_extentsv$transactionv$rollstatv$rollnamev$undostatx$ktuxe2. 查看 undo 相关参数select name,value,issys_modifiable from v$parameter where name like '%undo%';show parameter undo3. 查看所有 undo 表空间与数据文件select tablespace_name,status,reten...
MyStat2
set verify offselect a.name, b.value V, to_char(b.value-&V,'999,999,999,999') difffrom v$statname a, v$mystat bwhere a.statistic# = b.statistic# and lower(a.name) l...
MyStat
set verify offcolumn value new_val Vcolumn value format 999,999,999,999define S="&1"set autotrace offselect a.name, b.value valuefrom v$statname a, v$mystat b where a.statistic# = b.statistic#...
获得Trace文件名
-- 专用服务器模式的连接下 -- user_dump_dest-- 共享服务器模式的连接下 -- background_dump_dest-- 9iR1 中 无需实例名-- 可以设置 trace 标识符go@DEMO> alter session set tracefile_identifier = 'Look_For_Me';go@DEMO> select name, value from v$parameter where name = 'tracefile_identifier';NAME &...
show_space2
-- 执行用户必须具有查看DBA View的相关权限create or replace procedure show_space2( p_segname_1 in varchar2,p_space in varchar2 default 'auto',p_type_1 in varchar2 default 'table' ,p_analyzed in varchar2 default 'n',p_owner_1 in varchar2 default user)asp_segname varchar2(100);p_type varchar2...
查看表空间使用空间情况
column tbs_name format a30select a.tablespace_name TBS_NAME, round(b.total/1024/1024) "TOTAL(M)", round((b.total-a.free)/1024/1024) "USED(M)", round(a.free/1024/1024) "FREE(M)", round((b.total-a.free)/b.t...
查看用户占用空间
select owner, sum(bytes)/1024/1024 as "SIZE(M)", sum(blocks) as "TOTAL_BLOCKS", sum(extents) as "TOTAL_EXTENTS"from dba_segmentsgroup by ownerhaving owner=upper('&user')/
查看主外键关系
1 指定表约束查询column owner format a10column table_name format a20column column_name format a20column constraint_name format a20column constraint_type format a20select a.owner, a.table_name, b.column_name, a.constra...
查看闪回目录的空间
select name, round(space_limit/1024/1024/1024,2) "LIMIT(G)", round(space_used/1024/1024/1024,2) "USED(G)", round(space_reclaimable/1024/1024,2) "RECL(M)", number_of_filesfrom v$recovery...
查看未加索引的外键
column columns format a30 word_wrappedcolumn tablename format a15 word_wrappedcolumn constraint_name format a15 word_wrappedselect table_name, constraint_name, cname1 || nvl2(cname2,',' || cname2,null) || &nbs...
查看隐藏参数
1.column name format a30column value format a20column describ format a40select x.ksppinm name, y.ksppstvl value, x.ksppdesc describfrom sys.x$ksppi x, sys.x$ksppcv ywhere x.inst_id...
查看数据库启动以来回滚率
select a.value commits, b.value rollbacks, to_char(b.value * 100/ (a.value + b.value), '90.99') || '%' rollbacks_ratefrom( select name, value from v$sysstat where name = 'user commits') a,( &nbs...
查看回滚段信息
set linesize 200 pagesize 1400;SELECT r.segment_name, r.owner, r.tablespace_name, r.status, r.initial_extent/1024 "INITIAL_EXTENT(K)", r.next_extent/1024 "NEXT_EXTENT(K)", &...
查看数据文件大小
column file_name format a50select tablespace_name, file_id, file_name, round(bytes/1024/1024) "SIZE(M)"from dba_data_filesunion allselect tablespace_name, file_id...
查看数据库中的对象
break on owner skip 1select owner, object_type, status, count(*) count#from all_objectsgroup by owner, object_type, statusorder by &...
查看用户的目录操作权限
column grantee format a10column grantor format a10column dir_name format a20column dir_path format a50column privilege format a10break on dir_nameselect d.directory_name dir_name, d.directory_path dir_path, p.privilege, &nbs...
用外部表实现Alert日志的查看
-- 可以查询 alert 日志中的任何信息-- 创建 bdump 目录sys@DEMO> create directory b_dump_dir as 'D:\oracle\product\10.2.0\admin\demo\bdump';Directory created.-- sys@DEMO> select * from all_directories;OWNER DIRECTORY_NAME DIRECTORY_PATH----- --------------- ------------------------...
每天产生REDO归档日志量
SELECT trunc(completion_time) "DATE", count(*) as count, round(((sum(blocks*block_size))/1024/1024),2) mbFROM v$archived_logGROUP BY trunc(completion_time)ORDER BY 1/
REDO日志切换频率
select b.SEQUENCE#, b.FIRST_TIME, a.SEQUENCE#, a.FIRST_TIME, round(((a.FIRST_TIME-b.FIRST_TIME)*24)*60,2) minutesfrom v$log_history a, v$log_history bwhere a.SEQUENC...