DBA常用的一些SQL与 视图

监控索引是否使用
alter index &index_name monitoring usage;
alter index &index_name nomonitoring usage;
select * from v$object_usage where index_name = &index_name;
--求数据文件的I/O分布
select df.name,phyrds,phywrts,phyblkrd,phyblkwrt,singleblkrds,readtim,writetim
from v$filestat fs,v$dbfile df
where fs.file#=df.file# order by df.name;
--求某个隐藏参数的值
col ksppinm format a54
col ksppstvl format a54
select ksppinm, ksppstvl
from x$ksppi pi, x$ksppcv cv
where cv.indx=pi.indx and pi.ksppinm like '\_%' escape '\' and pi.ksppinm like '%meer%';
--求系统中较大的latch
select name,sum(gets),sum(misses),sum(sleeps),sum(wait_time)
from v$latch_children
group by name having sum(gets) > 50 order by 2;
--求归档日志的切换频率(生产系统可能时间会很长)
select start_recid,start_time,end_recid,end_time,minutes from (select test.*, rownum as rn
from (select b.recid start_recid,to_char(b.first_time,'yyyy-mm-dd hh24:mi:ss') start_time,
a.recid end_recid,to_char(a.first_time,'yyyy-mm-dd hh24:mi:ss') end_time,round(((a.first_time-b.first_time)*24)*60,2) minutes
from v$log_history a,v$log_history b where a.recid=b.recid+1 and b.first_time > sysdate - 1
order by a.first_time desc) test) y where y.rn < 30
--求回滚段正在处理的事务
select a.name,b.xacts,c.sid,c.serial#,d.sql_text
from v$rollname a,v$rollstat b,v$session c,v$sqltext d,v$transaction e
where a.usn=b.usn and b.usn=e.xidusn and c.taddr=e.addr
and c.sql_address=d.address and c.sql_hash_value=d.hash_value order by a.name,c.sid,d.piece;
--求出无效的对象
select 'alter procedure '||object_name||' compile;'
from dba_objects
where status='INVALID' and wner='&' and object_type in ('PACKAGE','PACKAGE BODY');
/
select owner,object_name,object_type,status from dba_objects where status='INVALID';
--求process/session的状态
select p.pid,p.spid,s.program,s.sid,s.serial#
from v$process p,v$session s where s.paddr=p.addr;
--求当前session的状态
select sn.name,ms.value
from v$mystat ms,v$statname sn
where ms.statistic#=sn.statistic# and ms.value > 0;
--求表的索引信息
select ui.table_name,ui.index_name
from user_indexes ui,user_ind_columns uic
where ui.table_name=uic.table_name and ui.index_name=uic.index_name
and ui.table_name like '&table_name%' and uic.column_name='&column_name';
--显示表的外键信息
col search_condition format a54
select table_name,constraint_name
from user_constraints
where constraint_type ='R' and constraint_name in (select constraint_name from user_cons_columns where column_name='&1');
select rpad(child.table_name,25,' ') child_tablename,
rpad(cp.column_name,17,' ') referring_column,rpad(parent.table_name,25,' ') parent_tablename,
rpad(pc.column_name,15,' ') referred_column,rpad(child.constraint_name,25,' ') constraint_name
from user_constraints child,user_constraints parent,
user_cons_columns cp,user_cons_columns pc
where child.constraint_type = 'R' and child.r_constraint_name = parent.constraint_name and
child.constraint_name = cp.constraint_name and parent.constraint_name = pc.constraint_name and
cp.position = pc.position and child.table_name ='&table_name'
order by child.owner,child.table_name,child.constraint_name,cp.position;
--显示表的分区及子分区(user_tab_subpartitions)
col table_name format a16
col partition_name format a16
col high_value format a81
select table_name,partition_name,HIGH_VALUE from user_tab_partitions where table_name='&table_name'
--使用dbms_xplan生成一个执行计划
explain plan set statement_id = '&sql_id' for &sql;
select * from table(dbms_xplan.display);
--求某个事务的重做信息(bytes)
select s.name,m.value
from v$mystat m,v$statname s
where m.statistic#=s.statistic# and s.name like '%redo size%';
--求cache中缓存超过其5%的对象
select o.owner,o.object_type,o.object_name,count(b.objd)
from v$bh b,dba_objects o
where b.objd = o.object_id
group by o.owner,o.object_type,o.object_name
having count(b.objd) > (select to_number(value)*0.05 from v$parameter where name = 'db_block_buffers');
--求谁阻塞了某个session(10g)
select sid, username, event, blocking_session,
seconds_in_wait, wait_time
from v$session where state in ('WAITING') and wait_class != 'Idle';
--求session的OS进程ID
col program format a54
select p.spid "OS Thread", b.name "Name-User", s.program
from v$process p, v$session s, v$bgprocess b
where p.addr = s.paddr and p.addr = b.paddr
UNION ALL
select p.spid "OS Thread", s.username "Name-User", s.program
from v$process p, v$session s where p.addr = s.paddr and s.username is not null;
--查会话的阻塞
col user_name format a32
select /*+ rule */ lpad(' ',decode(l.xidusn ,0,3,0))||l.oracle_username user_name, o.owner,o.object_name,s.sid,s.serial#
from v$locked_object l,dba_objects o,v$session s
where l.object_id=o.object_id and l.session_id=s.sid order by o.object_id,xidusn desc ;
col username format a15
col lock_level format a8
col owner format a18
col object_name format a32
select /*+ rule */ s.username, decode(l.type,'tm','table lock', 'tx','row lock', null) lock_level, o.owner,o.object_name,s.sid,s.serial#
from v$session s,v$lock l,dba_objects o
where l.sid = s.sid and l.id1 = o.object_id(+) and s.username is not null ;
--求等待的事件及会话信息/求会话的等待及会话信息
select se.sid,s.username,se.event,se.total_waits,se.time_waited,se.average_wait
from v$session s,v$session_event se
where s.username is not null and se.sid=s.sid and s.status='ACTIVE' and se.event not like '%SQL*Net%' order by s.username;
select s.sid,s.username,sw.event,sw.wait_time,sw.state,sw.seconds_in_wait
from v$session s,v$session_wait sw
where s.username is not null and sw.sid=s.sid and sw.event not like '%SQL*Net%' order by s.username;
--求会话等待的file_id/block_id
col event format a24
col p1text format a12
col p2text format a12
col p3text format a12
select sid,event,p1text, p1, p2text, p2, p3text, p3
from v$session_wait
where event not like '%SQL%' and event not like '%rdbms%' and event not like '%mon%' order by event;
select name,wait_time from v$latch l where exists (select 1 from (select sid,event,p1text, p1, p2text, p2, p3text, p3
from v$session_wait
where event not like '%SQL%' and event not like '%rdbms%' and event not like '%mon%'
) x where x.p1= l.latch#);
--求会话等待的对象
col owner format a18
col segment_name format a32
col segment_type format a32
select owner,segment_name,segment_type
from dba_extents
where file_id = &file_id and &block_id between block_id and block_id + blocks - 1;
--求buffer cache中的块信息
select o.OBJECT_TYPE, substr(o.OBJECT_NAME,1,10) objname , b.objd , b.status, count(b.objd)
from v$bh b, dba_objects o
where b.objd = o.data_object_id and o.owner = '&1' group by o.object_type, o.object_name,b.objd, b.status ;
--求日志文件的空间使用
select le.leseq current_log_sequence#, 100*cp.cpodr_bno/le.lesiz percentage_full
from x$kcccp cp,x$kccle le
where le.leseq =cp.cpodr_seq;
--求等待中的对象
select /*+rule */ s.sid, s.username, w.event, o.owner, o.segment_name, o.segment_type,
o.partition_name, w.seconds_in_wait seconds, w.state
from v$session_wait w, v$session s, dba_extents o
where w.event in (select name from v$event_name where parameter1 = 'file#'
and parameter2 = 'block#' and name not like 'control%')
and o.owner <> 'sys' and w.sid = s.sid and w.p1 = o.file_id and w.p2 >= o.block_id and w.p2 < o.block_id + o.blocks
--求当前事务的重做尺寸
select value
from v$mystat, v$statname
where v$mystat.statistic# = v$statname.statistic# and v$statname.name = 'redo size';
--唤醒smon去清除临时段
column pid new_value Smon
set termout off
select p.pid from sys.v_$bgprocess b,sys.v_$process p where b.name = 'SMON' and p.addr = b.paddr
/
set termout on
oradebug wakeup &Smon
undefine Smon
--求回退率
select b.value/(a.value + b.value),a.value,b.value from v$sysstat a,v$sysstat b
where a.statistic#=4 and b.statistic#=5;
--求DISK READ较多的SQL
select st.sql_text from v$sql s,v$sqltext st
where s.address=st.address and s.hash_value=st.hash_value and s.disk_reads > 300;
--求DISK SORT严重的SQL
select sess.username, sql.sql_text, sort1.blocks
from v$session sess, v$sqlarea sql, v$sort_usage sort1
where sess.serial# = sort1.session_num
and sort1.sqladdr = sql.address
and sort1.sqlhash = sql.hash_value and sort1.blocks > 200;
--求对象的创建代码
column column_name format a36
column sql_text format a99
select dbms_metadata.get_ddl('TABLE','&1') from dual;
select dbms_metadata.get_ddl('INDEX','&1') from dual;
--求表的索引
set linesize 131
select a.index_name,a.column_name,b.status, b.index_type
from user_ind_columns a,user_indexes b
where a.index_name=b.index_name and a.table_name='&1';
求索引中行数较多的
select index_name,blevel,num_rows,CLUSTERING_FACTOR,status from user_indexes where num_rows > 10000 and blevel > 0
select table_name,index_name,blevel,num_rows,CLUSTERING_FACTOR,status from user_indexes where status <> 'VALID'
--求当前会话的SID,SERIAL#
select sid, serial# from v$session where audsid = SYS_CONTEXT('USERENV','SESSIONID');
--求表空间的未用空间
col mbytes format 9999.9999
select tablespace_name,sum(bytes)/1024/1024 mbytes from dba_free_space group by tablespace_name;
--求表中定义的触发器
select table_name,index_type,index_name,uniqueness from user_indexes where table_name='&1';
select trigger_name from user_triggers where table_name='&1';
--求未定义索引的表
select table_name from user_tables where table_name not in (select table_name from user_ind_columns);
--执行常用的过程
exec print_sql('select count(*) from tab');
exec show_space2('table_name');
--求free memory
select * from v$sgastat where name='free memory';
select a.name,sum(b.value) from v$statname a,v$sesstat b where a.statistic# = b.statistic# group by a.name;
查看一下谁在使用那个可以得回滚段,或者查看一下某个可以得用户在使用回滚段,
找出领回滚段不断增长的事务,再看看如何处理它,是否可以将它commit,再不行
就看看能否kill它,等等,查看当前正在使用的回滚段的用户信息和回滚段信息:
set linesize 121
SELECT r.name "ROLLBACK SEGMENT NAME ",l.sid "ORACLE PID",p.spid "SYSTEM PID ",s.username "ORACLE USERNAME"
FROM v$lock l, v$process p, v$rollname r, v$session s
WHERE l.sid = p.pid(+) AND s.sid=l.sid AND TRUNC(l.id1(+)/65536) = r.usn AND l.type(+) = 'TX' AND l.lmode(+) = 6 ORDER BY r.name;
--查看用户的回滚段的信息
select s.username, rn.name from v$session s, v$transaction t, v$rollstat r, v$rollname rn
where s.saddr = t.ses_addr and t.xidusn = r.usn and r.usn = rn.usn
--生成执行计划
explain plan set statement_id='a1' for &1;
--查看执行计划
select lpad(' ',2*(level-1))||operation operation,options,OBJECT_NAME,position from plan_table
start with id=0 and statement_id='a1' connect by prior id=parent_id and statement_id='a1'
执行计划
   1)根据SID,从v$sql中找到相应SQL的HASH_VALUE和ADDRESS ;
   SELECT a.sql_text , a.address , a.hash_value
   FROM   v$sql a , v$session b
   where  a.hash_value = b.sql_hash_value
   and    b.sid = &sid ;
Alan Lee(160921) 22:58:07
2)根据hash_value和address的值,从v$sql_plan中找到真实的执行计划。
   set line 200;
   col oper format a100;
   select lpad(oper,length(oper)+level*2,' ') oper,cost
   from   (
           select object_name||':'||operation||' '||options as oper,cost,id,parent_id
           from   v$sql_plan
           where  hash_value = &hash_value
           and    address = '&address'
          )
   start with id=0
   connect by prior id = parent_id;
Alan Lee(160921) 22:58:26
这2步,就可以找出实际正在跑的SQL使用的是什么执行计划
set autotrace traceonly statistics
set autotrace traceonly explain
set autotrace traceonly on explain
--查看内存中存的使用
select decode(greatest(class,10),10,decode(class,1,'Data',2,'Sort',4,'Header',to_char(class)),'Rollback') "Class",
sum(decode(bitand(flag,1),1,0,1)) "Not Dirty",sum(decode(bitand(flag,1),1,1,0)) "Dirty",
sum(dirty_queue) "On Dirty",count(*) "Total"
from x$bh group by decode(greatest(class,10),10,decode(class,1,'Data',2,'Sort',4,'Header',to_char(class)),'Rollback');
--查看表空间状态
select tablespace_name,extent_management,segment_space_management from dba_tablespaces;
select table_name,freelists,freelist_groups from user_tables;
--查看系统请求情况
SELECT DECODE (name, 'summed dirty write queue length', value)/
DECODE (name, 'write requests', value) "Write Request Length"
FROM v$sysstat WHERE name IN ( 'summed dirty queue length', 'write requests') and value>0;
--计算data buffer命中率
select a.value + b.value "logical_reads", c.value "phys_reads",
round(100 * ((a.value+b.value)-c.value) / (a.value+b.value)) "BUFFER HIT RATIO"
from v$sysstat a, v$sysstat b, v$sysstat c
where a.statistic# = 40 and b.statistic# = 41 and c.statistic# = 42;
SELECT name, (1-(physical_reads/(db_block_gets+consistent_gets)))*100 H_RATIO FROM v$buffer_pool_statistics;
--查看内存使用情况
select least(max(b.value)/(1024*1024),sum(a.bytes)/(1024*1024)) shared_pool_used,
max(b.value)/(1024*1024) shared_pool_size,greatest(max(b.value)/(1024*1024),sum(a.bytes)/(1024*1024))-
(sum(a.bytes)/(1024*1024)) shared_pool_avail,((sum(a.bytes)/(1024*1024))/(max(b.value)/(1024*1024)))*100 avail_pool_pct
from v$sgastat a, v$parameter b where (a.pool='shared pool' and a.name not in ('free memory')) and b.name='shared_pool_size';
--查看用户使用内存情况
select username, sum(sharable_mem), sum(persistent_mem), sum(runtime_mem)
from sys.v_$sqlarea a, dba_users b
where a.parsing_user_id = b.user_id group by username;
--查看对象的缓存情况
select OWNER,NAMESPACE,TYPE,NAME,SHARABLE_MEM,LOADS,EXECUTIONS,LOCKS,PINS,KEPT
from v$db_object_cache where type not in ('NOT LOADED','NON-EXISTENT','VIEW','TABLE','SEQUENCE')
and executions>0 and loads>1 and kept='NO' order by owner,namespace,type,executions desc;
select type,count(*) from v$db_object_cache group by type;
--查看库缓存命中率
select namespace,gets, gethitratio*100 gethitratio,pins,pinhitratio*100 pinhitratio,RELOADS,INVALIDATIONS from v$librarycache
--查看某些用户的hash
select a.username, count(b.hash_value) total_hash,count(b.hash_value)-count(unique(b.hash_value)) same_hash,
(count(unique(b.hash_value))/count(b.hash_value))*100 u_hash_ratio
from dba_users a, v$sqlarea b where a.user_id=b.parsing_user_id group by a.username;
--查看字典命中率
select (sum(getmisses)/sum(gets)) ratio from v$rowcache;
--查看undo段的使用情况
SELECT d.segment_name,extents,optsize,shrinks,aveshrink,aveactive,d.status
FROM v$rollname n,v$rollstat s,dba_rollback_segs d
WHERE d.segment_id=n.usn(+) and d.segment_id=s.usn(+);
--无效的对象
select owner,object_type,object_name from dba_objects where status='INVALID';
select constraint_name,table_name from dba_constraints where status='INVALID';
--求出某个进程,并对它进行跟踪
select s.sid,s.serial# from v$session s,v$process p where s.paddr=p.addr and p.spid=&1;
exec dbms_system.SET_SQL_TRACE_IN_SESSION(&1,&2,true);
exec dbms_system.SET_SQL_TRACE_IN_SESSION(&1,&2,false);
--求出锁定的对象
select do.object_name,session_id,process,locked_mode
from v$locked_object lo, dba_objects do where lo.object_id=do.object_id;
--求当前session的跟踪文件
SELECT p1.value || '/' || p2.value || '_ora_' || p.spid || '.ora' filename
FROM v$process p, v$session s, v$parameter p1, v$parameter p2
WHERE p1.name = 'user_dump_dest' AND p2.name = 'instance_name'
AND p.addr = s.paddr AND s.audsid = USERENV('SESSIONID') AND p.background is null AND instr(p.program,'CJQ') = 0;
--求对象所在的文件及块号
select segment_name,header_file,header_block
from dba_segments where segment_name like '&1';
--求对象发生事务时回退段及块号
select a.segment_name,a.header_file,a.header_block
from dba_segments a,dba_rollback_segs b
where a.segment_name=b.segment_name and b.segment_id='&1'
--9i的在线重定义表
/*如果在线重定义的表没有主键需要创建主键*/
exec dbms_redefinition.can_redef_table('cybercafe','announcement');
create table anno2 as select * from announcement
exec dbms_redefinition.start_redef_table('cybercafe','announcement','anno2');
exec dbms_redefinition.sync_interim_table('cybercafe','announcement','anno2');
exec dbms_redefinition.finish_redef_table('cybercafe','announcement','anno2');
drop table anno2
exec dbms_redefinition.abort_redef_table('cybercafe','announcement','anno2');
--常用的logmnr脚本(cybercafe)
exec sys.dbms_logmnr_d.build(dictionary_filename =>'esal',dictionary_location =>'/home/oracle/logmnr');
exec sys.dbms_logmnr.add_logfile(logfilename=>'/home/oracle/oradata/esal/archive/1_24050.dbf', ptions=>sys.dbms_logmnr.new);
exec sys.dbms_logmnr.add_logfile(logfilename=>'/home/oracle/oradata/esal/archive/1_22912.dbf', ptions=>sys.dbms_logmnr.addfile);
exec sys.dbms_logmnr.add_logfile(logfilename=>'/home/oracle/oradata/esal/archive/1_22913.dbf', ptions=>sys.dbms_logmnr.addfile);
exec sys.dbms_logmnr.add_logfile(logfilename=>'/home/oracle/oradata/esal/archive/1_22914.dbf', ptions=>sys.dbms_logmnr.addfile);
exec sys.dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle/logmnr/esal.ora');
create table logmnr2 as select * from v$logmnr_contents;
死锁问题:1)查找死锁的进程:
sqlplus "/as sysdba"
SELECT s.username,l.OBJECT_ID,l.SESSION_ID,s.SERIAL#,l.ORACLE_USERNAME,
l.OS_USER_NAME,l.PROCESS FROM V$LOCKED_OBJECT l,V$SESSION S WHERE l.SESSION_ID=S.SID;

2)kill掉这个死锁的进程:
alter system kill session 'sid,serial#'; (其中sid=l.session_id)
3)如果还不能解决,
select pro.spid from v$session ses,v$process pro where ses.sid=XX and ses.paddr=pro.addr;
其中sid用死锁的sid替换。
exit

--与权限相关的字典
ALL_COL_PRIVS表示列上的授权,用户和PUBLIC是被授予者
ALL_COL_PRIVS_MADE表示列上的授权,用户是属主和被授予者
ALL_COL_RECD表示列上的授权,用户和PUBLIC是被授予者
ALL_TAB_PRIVS表示对象上的授权,用户是PUBLIC或被授予者或用户是属主
ALL_TAB_PRIVS_MADE表示对象上的权限,用户是属主或授予者
ALL_TAB_PRIVS_RECD表示对象上的权限,用户是PUBLIC或被授予者
DBA_COL_PRIVS数据库列上的所有授权
DBA_ROLE_PRIVS显示已授予用户或其他角色的角色
DBA_SYS_PRIVS已授予用户或角色的系统权限
DBA_TAB_PRIVS数据库对象上的所有权限
ROLE_ROLE_PRIVS显示已授予用户的角色
ROLE_SYS_PRIVS显示通过角色授予用户的系统权限
ROLE_TAB_PRIVS显示通过角色授予用户的对象权限
SESSION_PRIVS显示用户现在可利用的所有系统权限
USER_COL_PRIVS显示列上的权限,用户是属主、授予者或被授予者
USER_COL_PRIVS_MADE显示列上已授予的权限,用户是属主或授予者
USER_COL_PRIVS_RECD显示列上已授予的权限,用户是属主或被授予者
USER_ROLE_PRIVS显示已授予给用户的所有角色
USER_SYS_PRIVS显示已授予给用户的所有系统权限
USER_TAB_PRIVS显示已授予给用户的所有对象权限
USER_TAB_PRIVS_MADE显示已授予给其他用户的对象权限,用户是属主
USER_TAB_PRIVS_RECD显示已授予给其他用户的对象权限,用户是被授予者
--如何用dbms_stats分析表及模式?
exec dbms_stats.gather_schema_stats(ownname=>'&USER_NAME',estimate_percent=>dbms_stats.auto_sample_size,
method_opt => 'for all columns size auto',degree=> DBMS_STATS.DEFAULT_DEGREE);
exec dbms_stats.gather_schema_stats(ownname=>'&USER_NAME',estimate_percent=>dbms_stats.auto_sample_size,cascade=>true);
/*
FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]
FOR COLUMNS [size clause] column|attribute [size_clause] [,column|attribute [size_clause]...],
where size_clause is defined as size_clause := SIZE {integer | REPEAT | AUTO | SKEWONLY}
integer--Number of histogram buckets. Must be in the range [1,254].
REPEAT--Collects histograms only on the columns that already have histograms.
AUTO--Oracle determines the columns to collect histograms based on data distribution and the workload of the columns.
SKEWONLY--Oracle determines the columns to collect histograms based on the data distribution of the columns
*/
常用系统表,视图和作用
查看有关用户的信息:dba_users
查看有关角色的信息:dba_roles,dba_role_privs,role_sys_privs
查看有关系统权限的信息:dba_sys_privs
查看当前数据库表空间状况:dba_tablespaces
查看用户的系统权限:user_sys_privs
查看某个用户对另外一个用户授予的权限:user_tab_privs_made
查看某个用户对另外一个用户授予的列级权限:user_col_privs_made
查看某个用户接受的权限:user_tab_privs_recd
查看某个用户接受的列级权限:user_col_privs_recd
查看有关用户的角色信息:user_role_privs
查看有关授予某个角色的系统权限信息:role_sys_privs
查看有关授予某个角色的对象权限信息:role_tab_privs
查看当前用户所拥有的表信息:user_tables
查看当前用户有权限访问的表信息:all_tables
查看当前用户所拥有的所有表的列信息:user_tab_columns
查看当前用户可以访问的表中的列信息:all_tab_columns
查看当前用户所拥有的所有约束信息:user_constraint
查看当前用户所拥有的所有约束和列的关系:user_cons_constraint
查看表中注释内容:user_tab_comments
查看表中列注释内容:user_col_comments
提供练习的表:dual
查看相关时区的名称和简称:v$timezone_names
V$OPTION:显示已安装的Oracle选项
select * from v$option;
取得Oracle版本的详细信息
select * from v$version;
取得初始化参数的详细信息
select name,value,description from v$parameter;
取得当前例程的详细信息
select * from v$instance;

1、用户
  查看当前用户的缺省表空间
  SQL>select username,default_tablespace from user_users;
  查看当前用户的角色
  SQL>select * from user_role_privs;
  查看当前用户的系统权限和表级权限
  SQL>select * from user_sys_privs;或
select  username,  default_tablespace,  temporary_tablespace, priv granted_role,  default_role  from dba_users u,      (select grantee,granted_role priv,default_role           from dba_role_privs          union all         select grantee,privilege  priv,''           from dba_sys_privs c       ) r where u.username = r.grantee order by username ;
  SQL>select * from user_tab_privs;
  显示当前会话所具有的权限
  SQL>select * from session_privs;
  显示指定用户所具有的系统权限
  SQL>select * from dba_sys_privs where grantee='GAME';
2、表
  查看用户下所有的表
  SQL>select * from user_tables;
  查看名称包含log字符的表
  SQL>select object_name,object_id from user_objects
  where instr(object_name,'LOG')>0;
  查看某表的创建时间
  SQL>select object_name,created from user_objects where object_name=upper('&table_name');
  查看某表的大小
  SQL>select sum(bytes)/(1024*1024) as "size(M)" from user_segments
  where segment_name=upper('&table_name');
  查看放在ORACLE的内存区里的表
  SQL>select table_name,cache from user_tables where instr(cache,'Y')>0;
3、索引
  查看索引个数和类别
  SQL>select index_name,index_type,table_name from user_indexes order by table_name;
  查看索引被索引的字段
  SQL>select * from user_ind_columns where index_name=upper('&index_name');
  查看索引的大小
  SQL>select sum(bytes)/(1024*1024) as "size(M)" from user_segments
  where segment_name=upper('&index_name');
4、序列号
  查看序列号,last_number是当前值
  SQL>select * from user_sequences;
5、视图
  查看视图的名称
  SQL>select view_name from user_views;
  查看创建视图的select语句
  SQL>set view_name,text_length from user_views;
  SQL>set long 2000; 说明:可以根据视图的text_length值设定set long 的大小
  SQL>select text from user_views where view_name=upper('&view_name');
6、同义词
  查看同义词的名称
  SQL>select * from user_synonyms;
7、约束条件
  查看某表的约束条件
  SQL>select constraint_name, constraint_type,search_condition, r_constraint_name
  from user_constraints where table_name = upper('&table_name');
  SQL>select c.constraint_name,c.constraint_type,cc.column_name
  from user_constraints c,user_cons_columns cc
  where c.owner = upper('&table_owner') and c.table_name = upper('&table_name')
  and c.owner = cc.owner and c.constraint_name = cc.constraint_name
  order by cc.position;
8、存储函数和过程
  查看函数和过程的状态
  SQL>select object_name,status from user_objects where object_type='FUNCTION';
  SQL>select object_name,status from user_objects where object_type='PROCEDURE';
  查看函数和过程的源代码
  SQL>select text from all_source where wner=user and name=upper('&plsql_name');
changefilter.sql oracle的连接数查看和连接用户的查看.sql Sun30新建oracle用户.sql user_job.sql xaview.sql 修改分区索引.SQL 修改数据库核心字符集.txt 僵死进程查询.sql 创建ORACLE表空间.sql 回滚段等待.SQL 在数据库中建JOB.sql 外连接.SQL 外部例程.SQL 建6节点回滚段.SQL 建回滚段.SQL 建立一个与现存数据库相同但不含数据的空库.sql 扩展超过100M查询.sql 整理表空间碎片.sql 新建表.sql 显示数据文件信息.sql 查插入表的性能.sql 查看session正在执行什么SQL.sql 查看session正在等待何种系统资源.sql 查看哪些session正在使用哪些回滚段.sql 查看某个进程正在执行什么SQL语句.sql 查看用户表所占空间的大小.sql 查看系统SGA区状态.sql 查看系统中使用了哪些设备文件.sql 查看系统中每个表空间的使用情况.sql 查看系统中每个表空间的大小.sql 查看系统联接数.sql 查看系统锁.sql 查看表空间使用情况.sql 查看进程占用回滚段的情况.sql 查看那些数据库对象被修改过.sql 用oupput的ora过程.sql 用户命令查询.sql 用户进程查询.sql 监控数据库性能的SQL.sql 看user_job.sql 索引表清除sql生成.sql 索引表空间使用情况查询.sql 索引表空间整理.sql 统计.sql 获取数据库版本信息.sql 表空间使用情况.txt 表空间使用查询.sql 表空间剩余空间查看.sql 表空间回收.sql 过滤表清除sql生成.sql 进程使用回滚段查询.sql 锁表查询.sql
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值