收集oracle信息的2个脚本
info_new.sql[*]set pages 20000 trims on;
[*]set linesize 500;
[*]col "Global Name" format a20;
[*]col "DB Version" format a20;
[*]col "Host Name" format a10;
[*]column today noprint new_value xdate
[*]select to_char(sysdate,'yyyy-mm-dd') today from dual;
[*]column name noprint new_value dbname
[*]select name from v$database;
[*]spool info_&dbname._&xdate..sql
[*]select a.name "DB Name", e.global_name "Global Name", b.banner "DB Version", c.host_name "Host Name", c.instance_name "Instance Name" ,c.startup_time "Instance Start Time", DECODE(c.logins,'RESTRICTED','YES','NO') "Restricted Mode",a.log_mode"Archive Log Mode"FROM v$database a, v$version b, v$instance c,global_name eWHERE b.banner LIKE '%Oracle%';
[*]col "Parameter Name" format a40;
[*]col "Value" format a40;
[*]SELECT parameter "Options" FROM v$option WHEREvalue='TRUE';
[*]show sga;
[*]SELECT name "Name", value/1024 "Size (K)" FROM v$sga ORDER BY name;
[*]select substr(name,0,512) "Parameter Name",substr(value,0,512) "Value",isdefault "Default",issys_modifiable "Dynamic" from v$parameter order by name;
[*]selectb.value "Max No. of Processes" from (select count(*) count from v$session) a, (select value from v$parameter where name='processes') b;
[*]select SESSIONS_HIGHWATERfrom v$license;
[*]col RESOURCE_NAME format a25
[*]col INIT_ALLOCATION format a10
[*]col LIMIT_VALUE format a10
[*]select RESOURCE_NAME, CURRENT_UTILIZATION "CURRENT_UTILIZ" ,
[*]MAX_UTILIZATION "MAX__UTILIZ", INITIAL_ALLOCATION "INIT_ALLOCATION",
[*]LIMIT_VALUE from v$resource_limit;
[*]SELECT owner "Schema", object_type "Object Type", COUNT(*) "# Objects" FROM dba_objects WHERE owner'SYS' AND owner'SYSTEM' GROUP BY owner, object_type ORDER BY owner, object_type;
[*]col "Table" format a30
[*]col "Tablespace" format a10
[*]col "Table Owner" for a12
[*]select a.owner "Table Owner", a.segment_name "Table", b.tablespace_name "Tablespace", sum(a.bytes) "Total Space Used (Bytes)",sum(a.extents) "Total Extents" from dba_segments a, dba_tables b where a.owner'SYS' and a.owner'SYSTEM' and a.segment_type = 'TABLE' and a.segment_name = b.table_name and a.owner = b.owner group by a.owner, a.segment_name, b.tablespace_name;
[*]col "Schema" format a20
[*]col "Name" format a40
[*]select owner "Schema", object_name "Name", object_id "Id #",object_type "Type", status "Status" from dba_objects where status != 'VALID';
[*]SELECT DISTINCT d.username "Username", d.account_status "Account Status", d.expiry_date "Expire Date", d.default_tablespace "Default Tablespace", d.temporary_tablespace "Temporary Tablespace", d.profile "Profile", d.created "Created", decode(p.sysdba,'TRUE', 'TRUE','') "SYSDBA",decode(p.sysoper,'TRUE','TRUE','')"SYSOPER" FROM dba_users d, v$pwfile_users p WHERE p.username (+) = d.username ORDER BY initcap(d.username);
[*]select grantee "Username", granted_role "Role",admin_option "Admin Option", default_role "Default" from sys.dba_role_privs order by grantee;
[*]col "Name" format a60
[*]SELECT name "Name", status "Status" from v$controlfile;
[*]SELECT d.status "Status", d.tablespace_name "Name", d.contents "Type", TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99G999G990D900') "Size (M)", TO_CHAR(NVL(a.bytes - NVL(f.bytes, 0),0)/1024/1024, '99G999G990D900') "Used (M)", TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), '990D00') "Used %" FROM sys.dba_tablespaces d, (select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name) a, (select tablespace_name, sum(bytes) bytes from dba_free_space group by tablespace_name) f WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = f.tablespace_name(+);
[*]col "Name" format a40
[*]col "Tablespace" format a20
[*]SELECT v.status "Status", d.file_name "Name", d.tablespace_name "Tablespace", TO_CHAR((d.bytes / 1024 / 1024), '99999990D000') "Size (M)", TO_CHAR(NVL(d.bytes - s.bytes, d.bytes)/1024/1024 , '99999990D000')|| '/' ||d.autoextensible "Used (M)", TO_CHAR((NVL(d.bytes - s.bytes, d.bytes) / d.bytes * 100), '990D00') "Used %" FROM sys.dba_data_files d, v$datafile v, (SELECT file_id, SUM(bytes) bytesFROM sys.dba_free_spaceGROUP BY file_id) s WHERE (s.file_id (+)= d.file_id) AND (d.file_name = v.name);
[*]col file_name format a40
[*]col status format a8
[*]select a.group# "group", b.member "file_name", a.bytes/1024/1024 "size(M)", a.status "status", b.status "status"
[*]from v$log a, v$logfile b where a.group# = b.group# order by 1,2;
[*]col usn format 9999
[*]col SHRINKS format 9999999
[*]select usn,EXTENTS,RSSIZE,OPTSIZE,HWMSIZE,SHRINKS,STATUS,CUREXT,WRAPS,WAITS from v$rollstat;
[*]col "Segment" format a30
[*]select segment_name "Segment",segment_type "Segment Type",tablespace_name "Tablespace",extents "# of Extents",max_extents "Max Extents" from dba_segments where max_extents - 10 < extents and max_extents != 0 order by tablespace_name;
[*]col "Tablespace" format a30
[*]select t.tablespace_name "Tablespace",owner "Schema", table_name "Table",next_extent "Next Extent" from dba_tables t where t.next_extent > (select max(bytes) from dba_free_space f where t.tablespace_name=f.tablespace_name);
[*]select t.tablespace_name "Tablespace",owner "Schema",
[*] segment_name "Segment",segment_type "Segment Type",
[*] next_extent "Next Extent" from dba_segments t
[*] where t.next_extent > (select max(bytes) from dba_free_space f
[*] where t.tablespace_name=f.tablespace_name);
[*]col "Datafile" format a50;
[*]select df.TABLESPACE_NAME"Tablespace",df.file_name"Datafile",
[*]to_char(NVL(i.phyrds ,0),'9G999G999G990') "Physical reads",
[*]to_char(NVL(i.phywrts,0),'9G999G999G990') "Physical writes",
[*]to_char(NVL(i.phyblkrd,0),'9G999G999G990')"Physical Blks Read",
[*]to_char(NVL(i.phyblkwrt,0), '9G999G999G990')"Physical Blks Wrtn",
[*]to_char(NVL(readtim,0),'9G999G999G999G990') "Read Time",
[*]to_char(NVL(writetim,0),'9G999G999G999G990') "Write Time",
[*]to_char(NVL(i.phyblkrd/i.phyrds,0),'9G999G999G990D999') "Blocks per Read",
[*]to_char(NVL(i.phyblkwrt/nvl(decode(i.phywrts,0,1),1),0),'9G999G999G990D999') "Blocks per Write" from sys.v_$filestat i , sys.dba_data_files df where i.phyrds > 0
[*] and df.file_id = i.file# order by df.tablespace_name;
[*]spool off
[*]
health_check.sql
[*]PROMPT ****************************************************************************
[*]PROMPT **************** ***********************
[*]PROMPT **************** Database HealthCheck Report ***********************
[*]PROMPT **************** ***********************
[*]PROMPT ****************************************************************************
[*]Script
[*]break on today
[*]column today noprint new_value xdate
[*]select to_char(sysdate,'yyyy-mm-dd') today from dual;
[*]column name noprint new_value dbname
[*]select name from v$database;
[*]set heading on
[*]set pages 40000 lines 300 trims on
[*]set feedback off
[*]spool db_check_&dbname._&xdate..sql
[*]select substr(to_char(sysdate,'fmMonth DD, YYYY HH:MI:SS P.M.'),1,35) today from dual;
[*]prompt
[*]prompt **********************************************************
[*]prompt ***** Database Information *****
[*]prompt **********************************************************
[*]ttitle left "DATABASE:"dbname" (AS OF:"xdate")"
[*]select name, created, log_mode from v$database;
[*]prompt
[*]prompt **********************************************************
[*]prompt ***** Database Insatll Option *****
[*]prompt **********************************************************
[*]col parameter format a40
[*]col value format a10
[*]select * from v$option where value = 'TRUE';
[*]clear columns
[*]prompt
[*]prompt **********************************************************
[*]prompt ***** Database Version *****
[*]prompt **********************************************************
[*]set heading off
[*]select * from v$version;
[*]set heading on
[*]prompt
[*]prompt **********************************************************
[*]prompt ***** Database Use Session Status *****
[*]prompt **********************************************************
[*]select SESSIONS_CURRENT , SESSIONS_HIGHWATERfrom v$license;
[*]prompt
[*]prompt **********************************************************
[*]ttitle off
[*]clear breaks
[*]clear computes
[*]set feedback off
[*]set heading on
[*]set termout on
[*]column "Physical Reads" format 99,999,999,999
[*]column "Consistent Gets" format 99,999,999,999
[*]column "DB Block Gets" format 99,999,999,999
[*]column "Percent (Above 90% ?)" format 999.99
[*]TTitle left skip 1 - left "*****Database:"dbname", DB Block Buffers Hit Ratio*****" skip 2
[*]select phys.value "Physical Reads", con.value "Consistent Gets", cur.value "DB Block Gets",
[*]100*(cur.value + con.value - phys.value)/(cur.value + con.value) "Percent (Above 90% ?)"
[*]from v$sysstat cur,v$sysstat con,v$sysstat phys,v$statname ncu,v$statname nco,v$statname nph
[*]where cur.statistic# = ncu.statistic#
[*] and ncu.name = 'db block gets'
[*] and con.statistic# = nco.statistic#
[*] and nco.name = 'consistent gets'
[*] and phys.statistic# = nph.statistic#
[*] and nph.name = 'physical reads';
[*]set heading off
[*]set termout off
[*]ttitle off
[*]clear breaks
[*]clear computes
[*]set heading on
[*]set termout on
[*]ttitle left skip 1 - left "********** Show Sga Size **********" skip 1
[*]SELECT name "Name", value/1024 "Size (K)" FROM v$sga ORDER BY name;
[*]column "Executions" format 999,999,990
[*]column "Cache Misses Executing" format 999,999,990
[*]column "Data Dictionary Gets" format 999,999,999
[*]column "Get Misses" format 999,999,999
[*]ttitle left skip 1 - left "********** Shared Pool Size (Execution Misses) **********" skip 1
[*]select sum(pins) "Executions",
[*] sum(reloads) "Cache Misses Executing",
[*] (sum(reloads)/sum(pins)*100) "% Ratio (STAY UNDER 1%)"
[*]from v$librarycache;
[*]ttitle left "********** Shared Pool Size (Dictionary Gets) **********"skip 1
[*]select sum(gets) "Data Dictionary Gets",
[*] sum(getmisses) "Get Misses",
[*] 100*(sum(getmisses)/sum(gets)) "% Ratio (STAY UNDER 10%)"
[*]from v$rowcache;
[*]ttitle off
[*]ttitle left "********** Log Buffers **********" skip 1
[*]selectsubstr(name,1,25) Name,
[*] substr(value,1,15) "VALUE (Near 0?)"
[*]from v$sysstat
[*]where name in ('redo log space requests','redo log space wait time');
[*]ttitle off
[*]ttitle left "********** Latch Information **********" skip 1
[*]selectsubstr(ln.name,1,25) Name,
[*] l.gets, l.misses,
[*] 100*(l.misses/l.gets) "% Ratio (STAY UNDER 1%)"
[*]from v$latch l, v$latchname ln
[*]where ln.name in ('cache buffers lru chain')
[*]and ln.latch# = l.latch#;
[*]ttitle off
[*]ttitle left "********** Sort Information **********" skip 1
[*]select a.value "Sort(Disk)", b.value "Sort(Memory)",
[*]round(100*(a.value/decode((a.value+b.value), 0,1,(a.value+b.value))),2) "% Ratio (STAY UNDER 5%)"
[*]from v$sysstat a, v$sysstat b
[*]where a.name = 'sorts (disk)'
[*]and b.name = 'sorts (memory)';
[*]ttitle off
[*]column name noprint new_value xdbname
[*]select name from v$database;
[*]prompt
[*]prompt *******************************
[*]prompt Show Tablespaces and Datafiles
[*]prompt *******************************
[*]clear breaks
[*]clear computes
[*]clear columns
[*]ttitle left "********** Show Tablespaces and Datafiles **********" skip 1
[*]set heading on
[*]column tablespace_name heading 'Tablespace' justify left format a10 truncated
[*]column file_id heading 'File|ID' justify center format 999
[*]column file_name heading 'Datafile' justify center format a55 word_wrapped
[*]column size Heading 'Size|in MG.' justify center format 99,999,990.99
[*]break on tablespace_name skip 1 on report skip 2
[*]compute sum label 'TS SIZE:' of size on tablespace_name
[*]compute sum label 'DB SIZE:' of size on report
[*]select tablespace_name,
[*] file_id,
[*] file_name,
[*] bytes/1024/1024 "size"
[*]from dba_data_files
[*]order by tablespace_name, file_id, file_name;
[*]clear breaks
[*]clear computes
[*]column "Total Bytes" format 99,999,999,999,999
[*]column "Bytes Free" format 99,999,999,999
[*]column "Bytes Used" format 99,999,999,999
[*]column "% Free" format 9999.999
[*]column "% Used" format 9999.999
[*]break on report
[*]compute sum of "Total Bytes" on report
[*]compute sum of "SQL Blocks" on report
[*]compute sum of "VMS Blocks" on report
[*]compute sum of "Bytes Free" on report
[*]compute sum of "Bytes Used" on report
[*]compute avg of "% Free" on report
[*]compute avg of "% Used" on report
[*]TTitle left "******* Database:"dbname", Current Tablespace Usage *******" skip 1
[*]col df.file_name for a52
[*]selectfs.tablespace_name,
[*] df.file_name ,
[*] df.bytes "Total Bytes",
[*] df.blocks "DB Blocks",
[*] sum(fs.bytes) "Bytes Free",
[*] (100*((sum(fs.bytes))/df.bytes)) "% Free",
[*] df.bytes-sum(fs.bytes) "Bytes Used",
[*] (100*((df.bytes-sum(fs.bytes))/df.bytes)) "% Used"
[*]from sys.dba_data_files df, sys.dba_free_space fs
[*]where df.file_id(+) = fs.file_id
[*]group by fs.tablespace_name, df.file_name, df.bytes, df.blocks
[*]order by fs.tablespace_name, df.file_name;
[*]clear columns
[*]ttitle off
[*]column "File Total" format 9,999,999,990
[*]ttitle" ***** Database:"dbname", DataFile's Disk Activity *****"
[*]select substr(df.file#,1,2) "ID",
[*] rpad(substr(name,1,52),52,'.') "File Name",
[*] rpad(substr(phyrds,1,10),10,'.') "Phy Reads",
[*] rpad(substr(phywrts,1,10),10,'.') "Phy Writes",
[*] rpad(substr(phyblkrd,1,10),10,'.') "Blk Reads",
[*] rpad(substr(phyblkwrt,1,10),10,'.') "Blk Writes",
[*] rpad(substr(readtim,1,9),9,'.') "Read Time",
[*] rpad(substr(writetim,1,10),10,'.') "Write Time",
[*] (sum(phyrds+phywrts+phyblkrd+phyblkwrt+readtim)) "File Total"
[*]from v$filestat fs, v$datafile df
[*]where fs.file# = df.file#
[*]group by df.file#, df.name, phyrds, phywrts, phyblkrd,
[*] phyblkwrt, readtim, writetim
[*]order by sum(phyrds+phywrts+phyblkrd+phyblkwrt+readtim) desc, df.name;
[*]ttitle off
[*]prompt
[*]prompt ****************************************************
[*]prompt Tablespace Fragmentation
[*]prompt ****************************************************
[*]ttitle left "********** Tablespace Fragmentation **********" skip 1
[*]clear breaks
[*]clear computes
[*]clear columns
[*]set heading on
[*]column tablespace_name heading Tablespace format a19
[*]column object_count heading 'Objects|(#)' format 999990
[*]column mb heading Mb format 99,990
[*]column sum(frags) heading 'Frags|(#)' format 99999
[*]column avail heading 'Max|(Mb)' format 99,999.99
[*]column free heading 'Free|(%)' format 999.9
[*]column bytesize heading 'Size|(Mb)' format 99,999,999
[*]column byteused heading 'Used|(Mb)' format 99,999,990
[*]column bytefree heading 'Free|(Mb)' format 99,999,990
[*]column init_ext heading 'Initial|(K)' format 999999
[*]column next_ext heading 'Next|(K)' format 999999
[*]column max_ext heading 'Max_Extent|(Number)' format 9999999999
[*]column contents heading 'Contents|' Format A9
[*]break on report
[*]compute sum of object_count bytesize byteused bytefree on report
[*]selecttablespace_name,
[*] sum(obj_cnt) object_count,
[*] sum(ini_ext) init_ext,
[*] sum(nex_ext) next_ext,
[*] sum(max_ext) max_ext,
[*] min(contents) contents,
[*] sum(byte)/1048576 bytesize,
[*] (sum(byte)/1048576)- (sum(fbyte)/1048576) byteused,
[*] sum(fbyte)/1048576 bytefree,
[*] sum(frags),
[*] sum(largest)/1048576 avail,
[*] (sum(fbyte)/decode(sum(byte),0,1,sum(byte)))*100 free
[*]from
[*] (select tablespace_name,
[*] 0 obj_cnt,
[*] 0 ini_ext,
[*] 0 nex_ext,
[*] 0 max_ext,
[*] null contents,
[*] 0 byte,
[*] sum(bytes) fbyte,
[*] count(*) frags,
[*] max(bytes) largest
[*] from dba_free_space
[*] group by tablespace_name
[*] union
[*] select tablespace_name,
[*] 0, 0, 0, 0, Null, sum(bytes), 0, 0, 0
[*] from dba_data_files
[*] group by tablespace_name
[*] union
[*] select tablespace_name, 0,
[*] initial_extent/1024 ini_ext,
[*] decode(next_extent/1024,null,0,next_extent/1024) nex_ext,
[*] decode(max_extents,null,0,max_extents) max_ext,
[*] contents,
[*] 0,0,0,0
[*] from dba_tablespaces
[*] union
[*] select tablespace_name,
[*] count(*) obj_cnt,
[*] 0, 0 , 0, null,0, 0, 0, 0
[*] from dba_segments
[*] group by tablespace_name)
[*]group by tablespace_name;
[*]clear columns
[*]clear computes
[*]set heading on
[*]set termout on
[*]ttitle left " ***** Database:"dbname", Segment DEFRAGMENTATION NEED, AS OF: " xdate " *****"
[*]selectsubstr(de.owner,1,8) "Owner",
[*] substr(de.segment_type,1,8) "Seg Type",
[*] substr(de.segment_name,1,35) "Table Name (Segment)",
[*] substr(de.tablespace_name,1,20) "Tablespace Name",
[*] extents "Frag NEED"
[*]from sys.dba_segments de
[*]where de.owner'SYS'
[*]and de.segment_type in ('TABLE','INDEX')
[*]and extents > 20
[*]order by extents desc;
[*]ttitle off
[*]TTitle left "*** Database:"dbname", Rollback Information ( As of:" xdate ") ***" skip 2
[*]selectsubstr(sys.dba_rollback_segs.SEGMENT_ID,1,5) "ID#",
[*] substr(sys.dba_segments.OWNER,1,8) "Owner",
[*] substr(sys.dba_segments.TABLESPACE_NAME,1,17) "Tablespace Name",
[*] substr(sys.dba_segments.SEGMENT_NAME,1,12) "Rollback Name",
[*] substr(sys.dba_rollback_segs.INITIAL_EXTENT,1,10) "INI_Extent",
[*] substr(sys.dba_rollback_segs.NEXT_EXTENT,1,10) "Next Exts",
[*] substr(sys.dba_segments.MIN_EXTENTS,1,5) "MinEx",
[*] substr(sys.dba_segments.MAX_EXTENTS,1,5) "MaxEx",
[*] substr(sys.dba_segments.PCT_INCREASE,1,5) "%Incr",
[*] substr(sys.dba_segments.BYTES,1,15) "Size (Bytes)",
[*] substr(sys.dba_segments.EXTENTS,1,6) "Extent#",
[*] substr(sys.dba_rollback_segs.STATUS,1,10) "Status"
[*]from sys.dba_segments, sys.dba_rollback_segs
[*]where sys.dba_segments.segment_name = sys.dba_rollback_segs.segment_name and
[*] sys.dba_segments.segment_type = 'ROLLBACK'
[*]order by sys.dba_rollback_segs.segment_id;
[*]ttitle off
[*]TTitle left " " skip 2 - left "*** Database:"dbname", Rollback Status ( As of:" xdate " )***" skip 2
[*]select substr(V$rollname.NAME,1,20) "Rollback_Name",
[*] substr(V$rollstat.EXTENTS,1,6) "EXTENT",
[*] v$rollstat.RSSIZE, v$rollstat.WRITES,
[*] substr(v$rollstat.XACTS,1,6) "XACTS",
[*] v$rollstat.GETS,
[*] substr(v$rollstat.WAITS,1,6) "WAITS",
[*] v$rollstat.HWMSIZE, v$rollstat.SHRINKS,
[*] substr(v$rollstat.WRAPS,1,6) "WRAPS",
[*] substr(v$rollstat.EXTENDS,1,6) "EXTEND",
[*] v$rollstat.AVESHRINK,
[*] v$rollstat.AVEACTIVE
[*]from v$rollname, v$rollstat
[*]where v$rollname.USN = v$rollstat.USN
[*]order by v$rollname.USN;
[*]ttitle off
[*]TTitle left " " skip 2 - left "*** Database:"dbname", Rollback Segment Mapping ( As of:"xdate " ) ***" skip 2
[*]selectr.name Rollback_Name,
[*] p.pid Oracle_PID,
[*] p.spid OS_PID,
[*] nvl(p.username,'NO TRANSACTION') Transaction,
[*] p.terminal Terminal
[*]from v$lock l, v$process p, v$rollname r
[*]where l.addr = p.addr(+)
[*] and trunc(l.id1(+)/65536)=r.usn
[*] and l.type(+) = 'TX'
[*] and l.lmode(+) = 6
[*]order by r.name;
[*]ttitle off
[*]prompt
[*]prompt **************************
[*]prompt Control file Status
[*]prompt **************************
[*]col name format a50
[*]select * from v$controlfile;
[*]prompt
[*]prompt *****************************
[*]prompt Online Redo Logfiles Status
[*]prompt *****************************
[*]clear breaks
[*]clear computes
[*]clear columns
[*]column member heading 'Logfile' justify center format a50 word_wrapped
[*]column group heading 'Group|Number' justify center format 99
[*]column size heading 'Size|in MG.' justify center format 990.99
[*]select f.member "member",
[*] f.group# "group",
[*] l.bytes/1024/1024 "size",
[*] l.status
[*]from v$logfile f, v$log l
[*]where f.group#=l.group#
[*]order by f.group#,f.member;
[*]prompt
[*]prompt ********************************
[*]prompt The Parameter has been modified:
[*]prompt ********************************
[*]clear breaks
[*]clear computes
[*]clear columns
[*]column name heading 'Name' format a35 word_wrapped
[*]column pvalue heading 'Value' format a80 word_wrapped
[*]select name, rtrim(value) "pvalue"
[*]from v$parameter
[*]where isdefault = 'FALSE'
[*]order by name;
[*]prompt
[*]prompt ******************************************
[*]prompt User usage
[*]prompt ******************************************
[*]prompt
[*]prompt *****************************************
[*]Prompt Show the unsuitable Temporary Tablespace
[*]prompt *****************************************
[*]select a.username , a.temporary_tablespace "Temporary Tablespace" , b.contents
[*]from dba_users a , dba_tablespaces b
[*]where a.temporary_tablespace=b.tablespace_name
[*]and b.contents'TEMPORARY';
[*]col grantee format a20
[*]col granted_role format a30
[*]col admin_option format a10
[*]col privilege format a30
[*]prompt
[*]prompt **************************************
[*]Prompt Show the unsuitable Role and Privilege
[*]prompt **************************************
[*]select grantee, granted_role, admin_option
[*]from sys.dba_role_privs
[*]wheregranted_role in ('DBA', 'AQ_ADMINISTRATOR_ROLE',
[*] 'EXP_FULL_DATABASE', 'IMP_FULL_DATABASE',
[*] 'OEM_MONITOR')
[*]andgrantee not in ('SYS', 'SYSTEM', 'OUTLN', 'AQ_ADMINISTRATOR_ROLE',
[*] 'DBA', 'EXP_FULL_DATABASE', 'IMP_FULL_DATABASE',
[*] 'OEM_MONITOR', 'CTXSYS', 'DBSNMP', 'IFSSYS',
[*] 'IFSSYS$CM', 'MDSYS', 'ORDPLUGINS', 'ORDSYS',
[*] 'TIMESERIES_DBA');
[*]select grantee, privilege, admin_option
[*]from sys.dba_sys_privs
[*]where(privilege like '% ANY %'
[*]or privilege in ('BECOME USER', 'UNLIMITED TABLESPACE')
[*]or admin_option = 'YES')
[*] and grantee not in ('SYS', 'SYSTEM', 'OUTLN', 'AQ_ADMINISTRATOR_ROLE',
[*] 'DBA', 'EXP_FULL_DATABASE', 'IMP_FULL_DATABASE',
[*] 'OEM_MONITOR', 'CTXSYS', 'DBSNMP', 'IFSSYS',
[*] 'IFSSYS$CM', 'MDSYS', 'ORDPLUGINS', 'ORDSYS',
[*] 'TIMESERIES_DBA');
[*]prompt
[*]prompt **************************************************
[*]prompt Object usage
[*]prompt **************************************************
[*]prompt
[*]prompt ***********************************************************************
[*]prompt List objects in the SYSTEM tablespace that doesn't belong SYS or SYSTEM
[*]prompt ***********************************************************************
[*]col SEGMENT_NAME format a30
[*]col SEGMENT_TYPE format a15
[*]col OWNER format a20
[*]select substr(owner,1,20) OWNER, substr(segment_type,1,15) SEGMENT_TYPE,
[*] substr(segment_name,1,30) SEGMENT_NAME
[*] from sys.dba_segments
[*]where owner not in ('PUBLIC', 'SYS', 'SYSTEM')
[*]and tablespace_name = 'SYSTEM';
[*]prompt
[*]prompt ***********************************************************************
[*]prompt List Invalid objects
[*]prompt ***********************************************************************
[*]prompt
[*]selectsubstr(obj.owner,1,8) "Owner",
[*] obj.object_type "Object Type",
[*] substr(obj.object_name,1,35) "Object Name",
[*] obj.status "Status"
[*]from sys.dba_objects obj
[*]where obj.status'VALID'
[*]order by owner, object_type, object_name desc;
[*]set heading on
[*]set termout on
[*]spool off
[*]set feedback on
[*]
[*]
[*]
[*]
[*]-- all indexes
[*]select i.owner,i.table_name,i.index_name,i.index_type,c.column_name,i.last_analyzed
[*]fromdba_indexes i,dba_ind_columns c
[*]where i.index_name=c.index_name
[*]and i.table_name=c.table_name
[*]and i.owner=c.index_owner
[*]and i.owner not in ('SYS','SYSTEM','WMSYS','SCOTT','OUTLN')
[*]/
[*]
[*]-- all partitions
[*]
[*]select table_owner,table_name,partition_name,subpartition_count,
[*] partition_position,tablespace_name
[*]from dba_tab_partitions
[*]/
[*]
[*]
在sqlplus下用@/home/oracle/***.sql载入运行
好资源，必须支持楼主。
页:
[1]