database.sql

本是古典何须时尚 2022-01-09 04:37 194阅读 0赞

spool check\_database\_liuzhou.log

prompt

prompt \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\# Execution Time \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#

set linesize 140

set pagesize 9999

select sysdate as current\_date from dual;

prompt \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\# Database Version \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#

select \* from v$version;

prompt \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\# Instance Information \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#

col instance\_number for 99999999

col INSTANCE\_NAME for a14

col host\_name for a12

col status for a8

col STARTUP\_TIME for a16

col THREAD\# for 999999999

col paraller for a6

select INSTANCE\_NUMBER,INSTANCE\_NAME,host\_name,STATUS,STARTUP\_TIME,THREAD\#,parallel from Gv$instance;

prompt \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\# Database Information \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#

set linesize 240

col dbid for 999999999999999

col "Database Name" for a8

col open\_mode for a12

col created for a16

col log\_mode for a12

col checkpoint\_change\# for 999999999999999

col controlfile\_type for a12

col controlfile\_created for a12

col controlfile\_change\# for 999999999999999

col controlfile\_time for a12

col resetlogs\_change\# for 999999999999999

col resetlogs\_time for a12

select dbid, name "Database Name",open\_mode,flashback\_on,open\_mode, log\_mode from v$database;

select created ,checkpoint\_change\# ,controlfile\_type,controlfile\_created,controlfile\_change\#,controlfile\_time,resetlogs\_change\#,resetlogs\_time from v$database;

prompt \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\# SCN Information \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#

col time for a20

col scn for 99999999999999999999999

col Headroom for 999999999999999

SELECT to\_char(tim,'yyyy-mm-dd hh24:mi:ss') time,scn,round((chk16kscn-scn)/24/3600/16/1024,1) Headroom

FROM

(

select tim, scn,

((

((to\_number(to\_char(tim,'YYYY'))-1988)\*12\*31\*24\*60\*60) +

((to\_number(to\_char(tim,'MM'))-1)\*31\*24\*60\*60) +

(((to\_number(to\_char(tim,'DD'))-1))\*24\*60\*60) +

(to\_number(to\_char(tim,'HH24'))\*60\*60) +

(to\_number(to\_char(tim,'MI'))\*60) +

(to\_number(to\_char(tim,'SS')))

) \* (16\*1024)) chk16kscn

from

(select sysdate tim,checkpoint\_change\# scn from v$database))

ORDER BY tim;

prompt \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\# Parameter File \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#

col name for a8

col value for a80

select name,value from gv$parameter where name='spfile';

prompt \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\# List Spfiles Kept In ASM Instance \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#

SELECT full\_path, dir, sys

FROM

(SELECT

CONCAT('+'||gname,SYS\_CONNECT\_BY\_PATH(aname,'/')) full\_path,

dir, sys FROM

(SELECT g.name gname,

a.parent\_index pindex, a.name aname,

a.reference\_index rindex, a.ALIAS\_DIRECTORY dir,

a.SYSTEM\_CREATED sys

FROM v$asm\_alias a, v$asm\_diskgroup g

WHERE a.group\_number = g.group\_number)

START WITH (MOD(pindex, POWER(2, 24))) = 0

CONNECT BY PRIOR rindex = pindex

ORDER BY dir desc, full\_path asc)

WHERE UPPER(full\_path) LIKE '%SPFILE%';

prompt \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\# Non-default Parameter \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#

col name for a40

col Instance\_name for a12

col value for a70

SELECT p.name,i.instance\_name ,p.value

FROM gv$parameter p, gv$instance  i

WHERE p.inst\_id = i.inst\_id

and  isdefault='FALSE'

ORDER BY p.name , i.instance\_name;

prompt \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\# ASM Disk Groups User Space Information \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#

SELECT    name                                     group\_name

, sector\_size                              sector\_size

, block\_size                               block\_size

, allocation\_unit\_size                     allocation\_unit\_size

, state                                    state

, type                                     type

, total\_mb                                 total\_mb

, (total\_mb - free\_mb)                     used\_mb

, ROUND((1- (free\_mb / total\_mb))\*100, 2)  pct\_used

FROM v$asm\_diskgroup

ORDER BY name;

prompt \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\# Memory Size \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#

col name for a16

col "SIZE(M)" for a14

select 'SGA' AS NAME,ROUND(sum(value)/1024/1024,2)||'M' AS "SIZE(M)" from v$sga

UNION

select 'PGA' AS NAME,ROUND(value/1024/1024,2)||'M' AS "SIZE(M)" from v$pgastat where name='total PGA allocated'

UNION

select 'TOTAL' AS NAME,((SELECT ROUND(sum(value)/1024/1024,2) from v$sga)+(select ROUND(value/1024/1024,2) from v$pgastat where name='total PGA allocated'))||'M' AS "SIZE(M)" FROM DUAL ;

prompt \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\# Control file \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#

col name for a60

SELECT NAME FROM v$CONTROLFILE;

prompt \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\# Database Size \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#

col Datasize for a15

col Freesize for a15

col usedsize for a15

col tempsize for a15

select (select round(sum(bytes/1024/1024/1024),2)||'G' from dba\_data\_files) Datasize,

(select round(sum(bytes/1024/1024/1024),2)||'G' from dba\_free\_space) Freesize,

(select round(sum(bytes/1024/1024/1024),2) from dba\_data\_files)-(select round(sum(bytes/1024/1024/1024),2) from dba\_free\_space)||'G' usedsize,

(select round(sum(bytes/1024/1024/1024),2)||'G' from dba\_temp\_files) tempsize from dual;

prompt \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\# Tablespace Information \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#

col name for a34

col tablespace\_name for a30

col file\_name for a38

SELECT D.TABLESPACE\_NAME,SPACE "SUM\_SPACE(M)",SPACE-NVL(FREE\_SPACE,0) "USED\_SPACE(M)",

ROUND((1-NVL(FREE\_SPACE,0)/SPACE)\*100,2) "USED\_RATE(%)",FREE\_SPACE "FREE\_SPACE(M)" FROM

(SELECT TABLESPACE\_NAME,ROUND(SUM(BYTES)/(1024\*1024),2)

SPACE,SUM(BLOCKS) BLOCKS FROM DBA\_DATA\_FILES GROUP BY TABLESPACE\_NAME) D,

(SELECT TABLESPACE\_NAME,ROUND(SUM(BYTES)/(1024\*1024),2) FREE\_SPACE

FROM DBA\_FREE\_SPACE

GROUP BY TABLESPACE\_NAME) F

WHERE D.TABLESPACE\_NAME = F.TABLESPACE\_NAME(+)

UNION ALL

SELECT D.TABLESPACE\_NAME,SPACE "SUM\_SPACE(M)",

USED\_SPACE "USED\_SPACE(M)",ROUND(NVL(USED\_SPACE,0)/SPACE\*100,2)

"USED\_RATE(%)",

NVL(FREE\_SPACE,0) "FREE\_SPACE(M)" FROM

(SELECT TABLESPACE\_NAME,ROUND(SUM(BYTES)/(1024\*1024),2)

SPACE,SUM(BLOCKS) BLOCKS

FROM DBA\_TEMP\_FILES

GROUP BY TABLESPACE\_NAME) D,

(SELECT TABLESPACE\_NAME,ROUND(SUM(BYTES\_USED)/(1024\*1024),2) USED\_SPACE,

ROUND(SUM(BYTES\_FREE)/(1024\*1024),2) FREE\_SPACE

FROM v$TEMP\_SPACE\_HEADER

GROUP BY TABLESPACE\_NAME) F

WHERE D.TABLESPACE\_NAME = F.TABLESPACE\_NAME(+);

prompt \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\# Undo Tablespace Information \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#

col tablespace\_name for a30

col status for a12

col "Bytes(M)" for a14

SELECT   tablespace\_name, status, SUM (bytes) / 1024 / 1024 "Bytes(M)"

FROM   dba\_undo\_extents

GROUP BY   tablespace\_name, status;

prompt \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\# Datafile Information \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#

col tablespace\_name for a30

col file\_name for a30

col AUTOEXTENSIBLE for a8

col status for a12

SELECT T.TABLESPACE\_NAME,D.FILE\_NAME,

D.AUTOEXTENSIBLE,D.MAXBYTES,D.STATUS

FROM DBA\_TABLESPACES T,

DBA\_DATA\_FILES D

WHERE T. TABLESPACE\_NAME =D. TABLESPACE\_NAME

ORDER BY TABLESPACE\_NAME,FILE\_NAME;

prompt \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\# Datafile Header Information \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#

col ts\# for 99999

col file\# for 99999

col error

col tablespace\_name for a30

col status for a12

select ts\#,file\#,TABLESPACE\_NAME,status,ERROR,FORMAT,recover,FUZZY,

CREATION\_TIME CREATE\_TIME,

checkpoint\_change\# "SCN",

RESETLOGS\_CHANGE\# "RESETLOGS SCN"

from v$datafile\_header

order by 1,2;

/\*

fhsta

64  normal rman fuzzy

4   normal fuzzy

8192 system good

8196 system fuzzy

0   normal good

8256 system rman fuzzy

\*/

select hxfil FILENUMBER, fhsta STATUS, fhscn SCN, fhrba\_seq SEQUENCE from x\\\\$kcvfh;

prompt \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\# Datafile I/O Performance Information \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#

SELECT ts.name AS ts,fs.phyrds "Reads",fs.phywrts "Writes",fs.phyblkrd  AS br,fs.phyblkwrt AS bw,fs.readtim/100  "RTime\*s",fs.writetim/100  "WTime\*s"

FROM v$tablespace ts, v$datafile df, v$filestat fs

WHERE ts.ts\# = df.ts\# AND df.file\# = fs.file\#

UNION

SELECT ts.name  AS ts,ts.phyrds "Reads",ts.phywrts "Writes",ts.phyblkrd  AS br,ts.phyblkwrt AS bw,ts.readtim /100  "RTime\*s",ts.writetim/100  "WTime\*s"

FROM v$tablespace ts, v$tempfile tf, v$tempstat ts

WHERE ts.ts\# = tf.ts\# AND tf.file\# = ts.file\#

ORDER BY 1;

prompt \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\# Redo Information \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#

SELECT thread\#,a.group\#,a.sequence\#,a.bytes/1024/1024 "SIZE(M)",first\_change\# "First SCN",a.FIRST\_TIME,a.ARCHIVED "Archive",a.status,MEMBER

FROM gv$log a, gv$logfile b

WHERE a.group\# = B.GROUP\#

ORDER BY thread\#,a.sequence\# DESC;

prompt \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\# Redo Log Switch Frequency Information \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#

select INST\_ID,THREAD\#,sequence\#,to\_char(first\_time, 'yyyymmdd\_hh24:mi:ss') firsttime,

round((first\_time - lag(first\_time) over(order by first\_time)) \* 24 \* 60,2) minutes

from gv$log\_history

where first\_time > sysdate - 1

order by first\_time, minutes;

prompt \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\# LGWR I/O Performance Information \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#

select total\_waits,time\_waited,average\_wait,time\_waited / total\_waits as avg\_time from gv$system\_event

where event = 'log file parallel write';

prompt \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\# Flashback Diagnostic Information \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#

WITH flashback\_database\_log AS

(SELECT ROUND((SYSDATE - oldest\_flashback\_time) \* 24 \* 60, 2) oldest\_log\_minutes,

retention\_target retention\_target\_minutes,

flashback\_size / 1048576 flashback\_size\_mb,

estimated\_flashback\_size / 1048576 estimated\_flashback\_size\_mb

FROM v$flashback\_database\_log),

flashback\_database\_logfile AS

(SELECT COUNT(\*) logs,

SUM(BYTES / 1048576) size\_mb,

MIN(first\_time) oldest\_log,

MAX(first\_time) latest\_log

FROM v$flashback\_database\_logfile),

flashback\_usage AS

(SELECT file\_type,

ROUND(mb\_used, 2) mb\_used,

ROUND(mb\_reclaimable, 2) mb\_reclaimable,

DECODE(total\_mb, 0, 0, ROUND(mb\_used \* 100 / total\_mb, 2)) percent\_space\_used,

DECODE(total\_mb, 0, 0, ROUND(mb\_reclaimable \* 100 / total\_mb, 2)) percent\_space\_reclaimable,

number\_of\_files,

total\_mb db\_recovery\_file\_dest\_mb,

flashback\_retention\_target,

oldest\_record,

ROUND((sysdate - oldest\_record) \* 24 \* 60, 2) oldest\_record\_age\_sec

FROM (SELECT SUM(DECODE(NAME,

'db\_recovery\_file\_dest\_size',

VALUE / 1048576,

0)) total\_mb,

SUM(DECODE(NAME, 'db\_flashback\_retention\_target', VALUE, 0)) flashback\_retention\_target

FROM v$parameter

WHERE NAME IN ('db\_recovery\_file\_dest\_size',

'db\_flashback\_retention\_target')),

(SELECT 'FLASHBACKLOG' file\_type,

NVL(SUM(BYTES) / 1048576, 0) mb\_used,

sum(CASE

WHEN last\_time <= (sysdate - (tgt.value / 1440)) THEN

bytes / 1048576

ELSE

END) mb\_reclaimable,

COUNT(\*) number\_of\_files,

MIN(first\_time) oldest\_record

FROM (select bytes,

lead(first\_time) over(order by first\_time asc) last\_time,

first\_time

from v$flashback\_database\_logfile) fla\_log,

(SELECT value value

FROM v$parameter

WHERE name = 'db\_flashback\_retention\_target') tgt

UNION

SELECT 'BACKUPPIECE' file\_type,

NVL(SUM(BYTES / 1048576), 0) mb,

SUM(CASE

WHEN dl.rectype = 13 THEN

(BYTES / 1048576)

ELSE

END) reclaimable\_mb,

COUNT(\*) no\_of\_files,

MIN(start\_time) oldest\_record

FROM v$backup\_piece bp, x\\\\$kccagf dl

WHERE is\_recovery\_dest\_file = 'YES'

AND deleted = 'NO'

AND bp.recid = dl.recid(+)

AND dl.rectype(+) = 13

UNION

SELECT 'ARCHIVELOG' file\_type,

NVL(SUM(blocks \* block\_size) / 1048576, 0) mb,

SUM(CASE

WHEN dl.rectype = 11 THEN

(LOG.blocks \* LOG.block\_size / 1048576)

ELSE

END) reclaimable\_mb,

COUNT(\*) no\_of\_files,

MIN(first\_time) oldest\_record

FROM v$archived\_log log, x\\\\$kccagf dl

WHERE deleted = 'NO'

AND is\_recovery\_dest\_file = 'YES'

AND dl.recid(+) = log.recid

AND dl.rectype(+) = 11

UNION

SELECT 'ONLINELOG' file\_type,

SUM(BYTES / 1048576) mb,

0 reclaimable,

COUNT(\*) no\_of\_files,

MIN(first\_time) oldest\_record

FROM v$logfile lf,

(SELECT group\#, BYTES, first\_time

FROM v$standby\_log

UNION

SELECT group\#, BYTES, first\_time FROM v$log) l

WHERE l.group\# = lf.group\#

AND lf.is\_recovery\_dest\_file = 'YES'

UNION

SELECT 'IMAGECOPY',

NVL(SUM(blocks \* (block\_size / 1048576)), 0) mb,

0 reclaimable\_mb,

COUNT(\*) no\_of\_files,

MIN(creation\_time) oldest\_record

FROM v$datafile\_copy

WHERE deleted = 'NO'

AND is\_recovery\_dest\_file = 'YES'

UNION

SELECT 'CONTROLFILE',

NVL(SUM(block\_size \* file\_size\_blks) / 1048576, 0) mb,

0 reclaimable,

COUNT(\*) no\_of\_files,

NULL oldest\_record

FROM v$controlfile

WHERE is\_recovery\_dest\_file = 'YES'))

SELECT order\_, NAME, VALUE

FROM(

SELECT 0 order\_, NAME, VALUE

FROM v$parameter

WHERE NAME LIKE 'db\_recovery\_file%'

UNION

SELECT 3, 'oldest flashback log (minutes)', TO\_CHAR(ROUND(oldest\_log\_minutes, 2))

FROM flashback\_database\_log

UNION

SELECT 1, 'retention target (minutes)', TO\_CHAR(ROUND(retention\_target\_minutes, 2))

FROM flashback\_database\_log

UNION

SELECT 2, 'estimated size for flashback logs (MB)', TO\_CHAR(ROUND(estimated\_flashback\_size\_mb, 2))

FROM flashback\_database\_log

UNION

SELECT 2, 'Current flashback log count', TO\_CHAR(logs)

FROM flashback\_database\_logfile

UNION

SELECT 3, 'Most recent flashback log (minutes)', TO\_CHAR(ROUND((SYSDATE - latest\_log) \* 24 \* 60, 2))

FROM flashback\_database\_logfile

UNION

SELECT 4, 'Total size of all files in MB', TO\_CHAR(ROUND(SUM(mb\_used), 2))

FROM flashback\_usage

UNION

SELECT 5, 'Total size of reclaimable files in MB', TO\_CHAR(ROUND(SUM(mb\_reclaimable), 2))

FROM flashback\_usage

UNION

SELECT 6, 'unused space in MB', TO\_CHAR(ROUND(MIN(db\_recovery\_file\_dest\_mb) - SUM(mb\_used)))

FROM flashback\_usage)

ORDER BY order\_, NAME;

prompt \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  Recyblebin Information \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#

select value from gv$parameter where name='recyclebin';

prompt \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\# Supplemental Log Information \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#

select SUPPLEMENTAL\_LOG\_DATA\_MIN "MIN",

SUPPLEMENTAL\_LOG\_DATA\_PK "PK",

SUPPLEMENTAL\_LOG\_DATA\_UI "UI",

SUPPLEMENTAL\_LOG\_DATA\_FK "FK",

SUPPLEMENTAL\_LOG\_DATA\_ALL "ALL"

from v$database;

prompt \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\# Archive Log Information \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#

col name for a50

select

thread\#,sequence\# sequence\#,

FIRST\_CHANGE\# FIRST\_CHANGE\#,

FIRST\_TIME FIRST\_TIME,

NEXT\_CHANGE\# NEXT\_CHANGE\#,

NEXT\_TIME NEXT\_TIME,

name from (

select  rownum rn,a.\* from

(

select

sequence\#,thread\#,

FIRST\_CHANGE\#,

FIRST\_TIME,

NEXT\_CHANGE\#,

NEXT\_TIME,

name from v$archived\_log

where DELETED='NO'

order by NEXT\_TIME desc

) a

) where rn<10;

prompt \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\# Recover File Information \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#

select file\#,online\_status "STATUS",change\# "SCN",time"TIME" from v$recover\_file;

prompt \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\# Hot Backup Information \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#

select \* from v$backup;

prompt \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\# Recover Log Information \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#

select THREAD\#,SEQUENCE\# SEQUENCE\#,

TIME "TIME"

from v$recovery\_log;

prompt \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\# Rman Backup Information \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#

SELECT A.RECID "BACKUP SET",

A.SET\_STAMP,

DECODE (B.INCREMENTAL\_LEVEL,

'', DECODE (BACKUP\_TYPE, 'L', 'Archivelog', 'Full'),

1, 'Incr-1',

0, 'Incr-0',

B.INCREMENTAL\_LEVEL)

"Type LV",

B.CONTROLFILE\_INCLUDED "including CTL",

DECODE (A.STATUS,

'A', 'AVAILABLE',

'D', 'DELETED',

'X', 'EXPIRED',

'ERROR')

"STATUS",

A.DEVICE\_TYPE "Device Type",

A.START\_TIME "Start Time",

A.COMPLETION\_TIME "Completion Time",

A.ELAPSED\_SECONDS "Elapsed Seconds",

A.TAG "Tag",

A.HANDLE "Path"

FROM Gv$BACKUP\_PIECE A, Gv$BACKUP\_SET B

WHERE A.SET\_STAMP = B.SET\_STAMP AND A.DELETED = 'NO'

ORDER BY A.COMPLETION\_TIME DESC;

prompt \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\# Lock Information \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#

SELECT /\*+ RULE \*/ LS.OSUSER OS\_USER\_NAME, LS.USERNAME USER\_NAME,

DECODE(LS.TYPE, 'RW', 'Row wait enqueue lock', 'TM', 'DML enqueue lock', 'TX',

'Transaction enqueue lock', 'UL', 'User supplied lock') LOCK\_TYPE, O.OBJECT\_NAME

OBJECT, DECODE(LS.LMODE, 1, NULL, 2, 'Row Share', 3, 'Row Exclusive', 4,

'Share', 5, 'Share Row Exclusive', 6, 'Exclusive', NULL) LOCK\_MODE, O.OWNER,

LS.SID, LS.SERIAL\# SERIAL\_NUM, LS.ID1, LS.ID2 FROM SYS.DBA\_OBJECTS O, (SELECT

S.OSUSER, S.USERNAME, L.TYPE, L.LMODE, S.SID, S.SERIAL\#, L.ID1, L.ID2 FROM

v$SESSION S, v$LOCK L WHERE S.SID = L.SID) LS WHERE O.OBJECT\_ID = LS.ID1 AND

O.OWNER <> 'SYS' ORDER BY O.OWNER, O.OBJECT\_NAME;

select  /\*+no\_merge(a) no\_merge(b) \*/

(select username from v$session where sid=a.sid) blocker,

a.sid, 'is blocking',

(select username from v$session where sid=b.sid) blockee,

b.sid

from v$lock a,v$lock b

where a.block=1 and b.request>0

and a.id1=b.id1

and a.id2=b.id2

order by a.sid;

prompt \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\# Connect User Session Information \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#

select count(\*) from v$session;

select sid,serial\#,username,program,machine,status from v$session;

prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="\#336699"><b>Cursor Information \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#

prompt <hr align="left" width="300">

select sid, count(\*) cnt

from v$open\_cursor

group by  sid

having count(\*) >= 1000

order by cnt desc;

prompt \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\# Trigger Information \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#

select OWNER, TRIGGER\_NAME, TABLE\_NAME, STATUS

from dba\_triggers

where owner not in ('SYS','SYSTEM',

'SYSMAN',

'EXFSYS',

'WMSYS',

'OLAPSYS',

'OUTLN',

'DBSNMP',

'ORDSYS',

'ORDDATA',

'APPQOSSYS',

'APEX\_030200',

'OWBSYS',

'ORDPLUGINS',

'MDSYS',

'CTXSYS',

'AURORA$ORB$UNAUTHENTICATED',

'XDB',

'MGMT\_VIEW',

'ANONYMOUS',

'DMSYS',

'SI\_INFORMTN\_SCHEMA',

'MDDATA',

'DIP',

'ORACLE\_OCM',

'OWBSYS\_AUDIT',

'ANONYMOUS',

'XS$NULL',

'APEX\_PUBLIC\_USER',

'SPATIAL\_CSW\_ADMIN\_USR',

'SPATIAL\_WFS\_ADMIN\_USR',

'TSMSYS',

'FLOWS\_030000',

'FLOWS\_FILES');

prompt \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\# Which Table Not Build Index(Less Than 2G) Information \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#

select segment\_name, bytes/1024/1024/1024 "GB", blocks, tablespace\_name

from dba\_segments

where segment\_type = 'TABLE'

and owner not in ('SYS','SYSTEM',

'SYSMAN',

'EXFSYS',

'WMSYS',

'OLAPSYS',

'OUTLN',

'DBSNMP',

'ORDSYS',

'ORDDATA',

'APPQOSSYS',

'APEX\_030200',

'OWBSYS',

'ORDPLUGINS',

'MDSYS',

'CTXSYS',

'AURORA$ORB$UNAUTHENTICATED',

'XDB',

'MGMT\_VIEW',

'ANONYMOUS',

'DMSYS',

'SI\_INFORMTN\_SCHEMA',

'MDDATA',

'DIP',

'ORACLE\_OCM',

'OWBSYS\_AUDIT',

'ANONYMOUS',

'XS$NULL',

'APEX\_PUBLIC\_USER',

'SPATIAL\_CSW\_ADMIN\_USR',

'SPATIAL\_WFS\_ADMIN\_USR',

'TSMSYS',

'FLOWS\_030000',

'FLOWS\_FILES')

and segment\_name not in (select table\_name from dba\_indexes where owner not in ('SYS','SYSTEM',

'SYSMAN',

'EXFSYS',

'WMSYS',

'OLAPSYS',

'OUTLN',

'DBSNMP',

'ORDSYS',

'ORDDATA',

'APPQOSSYS',

'APEX\_030200',

'OWBSYS',

'ORDPLUGINS',

'MDSYS',

'CTXSYS',

'AURORA$ORB$UNAUTHENTICATED',

'XDB',

'MGMT\_VIEW',

'ANONYMOUS',

'DMSYS',

'SI\_INFORMTN\_SCHEMA',

'MDDATA',

'DIP',

'ORACLE\_OCM',

'OWBSYS\_AUDIT',

'ANONYMOUS',

'XS$NULL',

'APEX\_PUBLIC\_USER',

'SPATIAL\_CSW\_ADMIN\_USR',

'SPATIAL\_WFS\_ADMIN\_USR',

'TSMSYS',

'FLOWS\_030000',

'FLOWS\_FILES'))

and bytes / 1024 / 1024 / 1024 >= 2

order by GB desc;

prompt \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\# Which Partition Table Not Build Index(Less Than 2G) Information \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#

select segment\_name, sum(bytes)/1024/1024/1024 "GB", sum(blocks)

from dba\_segments

where segment\_type = 'TABLE PARTITION'

and owner not in ('SYS','SYSTEM',

'SYSMAN',

'EXFSYS',

'WMSYS',

'OLAPSYS',

'OUTLN',

'DBSNMP',

'ORDSYS',

'ORDDATA',

'APPQOSSYS',

'APEX\_030200',

'OWBSYS',

'ORDPLUGINS',

'MDSYS',

'CTXSYS',

'AURORA$ORB$UNAUTHENTICATED',

'XDB',

'MGMT\_VIEW',

'ANONYMOUS',

'DMSYS',

'SI\_INFORMTN\_SCHEMA',

'MDDATA',

'DIP',

'ORACLE\_OCM',

'OWBSYS\_AUDIT',

'ANONYMOUS',

'XS$NULL',

'APEX\_PUBLIC\_USER',

'SPATIAL\_CSW\_ADMIN\_USR',

'SPATIAL\_WFS\_ADMIN\_USR',

'TSMSYS',

'FLOWS\_030000',

'FLOWS\_FILES')

and segment\_name not in (select table\_name from dba\_indexes where owner not in ('SYS','SYSTEM',

'SYSMAN',

'EXFSYS',

'WMSYS',

'OLAPSYS',

'OUTLN',

'DBSNMP',

'ORDSYS',

'ORDDATA',

'APPQOSSYS',

'APEX\_030200',

'OWBSYS',

'ORDPLUGINS',

'MDSYS',

'CTXSYS',

'AURORA$ORB$UNAUTHENTICATED',

'XDB',

'MGMT\_VIEW',

'ANONYMOUS',

'DMSYS',

'SI\_INFORMTN\_SCHEMA',

'MDDATA',

'DIP',

'ORACLE\_OCM',

'OWBSYS\_AUDIT',

'ANONYMOUS',

'XS$NULL',

'APEX\_PUBLIC\_USER',

'SPATIAL\_CSW\_ADMIN\_USR',

'SPATIAL\_WFS\_ADMIN\_USR',

'TSMSYS',

'FLOWS\_030000',

'FLOWS\_FILES'))

group by segment\_name

having sum(bytes)/1024/1024/1024>=2

order by GB desc;

prompt \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\# Statistical Is Collected Information \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#

\--10g

select t.job\_name,t.program\_name,t.state,t.enabled

from dba\_scheduler\_jobs t

where job\_name = 'GATHER\_STATS\_JOB';

\--11g

select client\_name,status from dba\_autotask\_client;

select window\_next\_time,autotask\_status from DBA\_AUTOTASK\_WINDOW\_CLIENTS;

prompt \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\# Statistical Isn't Collected For A Long Time Information \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#

select owner, count(\*)

from dba\_tab\_statistics t

where (t.last\_analyzed is null or t.last\_analyzed < sysdate - 100)

and table\_name not like 'BIN$%'

and owner not in ('SYS','SYSTEM',

'SYSMAN',

'EXFSYS',

'WMSYS',

'OLAPSYS',

'OUTLN',

'DBSNMP',

'ORDSYS',

'ORDDATA',

'APPQOSSYS',

'APEX\_030200',

'OWBSYS',

'ORDPLUGINS',

'MDSYS',

'CTXSYS',

'AURORA$ORB$UNAUTHENTICATED',

'XDB',

'MGMT\_VIEW',

'ANONYMOUS',

'DMSYS',

'SI\_INFORMTN\_SCHEMA',

'MDDATA',

'DIP',

'ORACLE\_OCM',

'OWBSYS\_AUDIT',

'ANONYMOUS',

'XS$NULL',

'APEX\_PUBLIC\_USER',

'SPATIAL\_CSW\_ADMIN\_USR',

'SPATIAL\_WFS\_ADMIN\_USR',

'TSMSYS',

'FLOWS\_030000',

'FLOWS\_FILES')

group by owner

order by owner;

prompt \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\# Which Table Not Build Partition (Less Than 2G) Information \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#

select owner,

segment\_name,

segment\_type,

sum(bytes) / 1024 / 1024 / 1024 object\_size

from dba\_segments

where owner not in ('SYS','SYSTEM',

'SYSMAN',

'EXFSYS',

'WMSYS',

'OLAPSYS',

'OUTLN',

'DBSNMP',

'ORDSYS',

'ORDDATA',

'APPQOSSYS',

'APEX\_030200',

'OWBSYS',

'ORDPLUGINS',

'MDSYS',

'CTXSYS',

'AURORA$ORB$UNAUTHENTICATED',

'XDB',

'MGMT\_VIEW',

'ANONYMOUS',

'DMSYS',

'SI\_INFORMTN\_SCHEMA',

'MDDATA',

'DIP',

'ORACLE\_OCM',

'OWBSYS\_AUDIT',

'ANONYMOUS',

'XS$NULL',

'APEX\_PUBLIC\_USER',

'SPATIAL\_CSW\_ADMIN\_USR',

'SPATIAL\_WFS\_ADMIN\_USR',

'TSMSYS',

'FLOWS\_030000',

'FLOWS\_FILES')

and segment\_type = 'TABLE'

group by owner, segment\_name, segment\_type

having sum(bytes) / 1024 / 1024 / 1024 >= 10

order by object\_size desc;

prompt \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\# Foreign Key Not Build Index Information \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#

select table\_name,

constraint\_name,

cname1 || nvl2(cname2, ',' || cname2, null) ||

nvl2(cname3, ',' || cname3, null) ||

nvl2(cname4, ',' || cname4, null) ||

nvl2(cname5, ',' || cname5, null) ||

nvl2(cname6, ',' || cname6, null) ||

nvl2(cname7, ',' || cname7, null) ||

nvl2(cname8, ',' || cname8, null) columns

from (select b.table\_name,

b.constraint\_name,

max(decode(position, 1, column\_name, null)) cname1,

max(decode(position, 2, column\_name, null)) cname2,

max(decode(position, 3, column\_name, null)) cname3,

max(decode(position, 4, column\_name, null)) cname4,

max(decode(position, 5, column\_name, null)) cname5,

max(decode(position, 6, column\_name, null)) cname6,

max(decode(position, 7, column\_name, null)) cname7,

max(decode(position, 8, column\_name, null)) cname8,

count(\*) col\_cnt

from (select substr(table\_name, 1, 30) table\_name,

substr(constraint\_name, 1, 30) constraint\_name,

substr(column\_name, 1, 30) column\_name,

position

from dba\_cons\_columns where owner not in ('SYS','SYSTEM',

'SYSMAN',

'EXFSYS',

'WMSYS',

'OLAPSYS',

'OUTLN',

'DBSNMP',

'ORDSYS',

'ORDDATA',

'APPQOSSYS',

'APEX\_030200',

'OWBSYS',

'ORDPLUGINS',

'MDSYS',

'CTXSYS',

'AURORA$ORB$UNAUTHENTICATED',

'XDB',

'MGMT\_VIEW',

'ANONYMOUS',

'DMSYS',

'SI\_INFORMTN\_SCHEMA',

'MDDATA',

'DIP',

'ORACLE\_OCM',

'OWBSYS\_AUDIT',

'ANONYMOUS',

'XS$NULL',

'APEX\_PUBLIC\_USER',

'SPATIAL\_CSW\_ADMIN\_USR',

'SPATIAL\_WFS\_ADMIN\_USR',

'TSMSYS',

'FLOWS\_030000',

'FLOWS\_FILES')) a,

dba\_constraints b

where a.constraint\_name = b.constraint\_name

and b.constraint\_type = 'R'

and b.owner not in ('SYS','SYSTEM',

'SYSMAN',

'EXFSYS',

'WMSYS',

'OLAPSYS',

'OUTLN',

'DBSNMP',

'ORDSYS',

'ORDDATA',

'APPQOSSYS',

'APEX\_030200',

'OWBSYS',

'ORDPLUGINS',

'MDSYS',

'CTXSYS',

'AURORA$ORB$UNAUTHENTICATED',

'XDB',

'MGMT\_VIEW',

'ANONYMOUS',

'DMSYS',

'SI\_INFORMTN\_SCHEMA',

'MDDATA',

'DIP',

'ORACLE\_OCM',

'OWBSYS\_AUDIT',

'ANONYMOUS',

'XS$NULL',

'APEX\_PUBLIC\_USER',

'SPATIAL\_CSW\_ADMIN\_USR',

'SPATIAL\_WFS\_ADMIN\_USR',

'TSMSYS',

'FLOWS\_030000',

'FLOWS\_FILES')

group by b.table\_name, b.constraint\_name) cons

where col\_cnt > ALL

(select count(\*)

from dba\_ind\_columns i

where i.table\_name = cons.table\_name

and i.column\_name in (cname1, cname2, cname3, cname4, cname5,

cname6, cname7, cname8)

and i.column\_position <= cons.col\_cnt

and i.index\_owner not in ('SYS','SYSTEM',

'SYSMAN',

'EXFSYS',

'WMSYS',

'OLAPSYS',

'OUTLN',

'DBSNMP',

'ORDSYS',

'ORDDATA',

'APPQOSSYS',

'APEX\_030200',

'OWBSYS',

'ORDPLUGINS',

'MDSYS',

'CTXSYS',

'AURORA$ORB$UNAUTHENTICATED',

'XDB',

'MGMT\_VIEW',

'ANONYMOUS',

'DMSYS',

'SI\_INFORMTN\_SCHEMA',

'MDDATA',

'DIP',

'ORACLE\_OCM',

'OWBSYS\_AUDIT',

'ANONYMOUS',

'XS$NULL',

'APEX\_PUBLIC\_USER',

'SPATIAL\_CSW\_ADMIN\_USR',

'SPATIAL\_WFS\_ADMIN\_USR',

'TSMSYS',

'FLOWS\_030000',

'FLOWS\_FILES')

group by i.index\_name);

prompt \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\# Expansion Of The Exception Object Information \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#

select Segment\_Name, Segment\_Type, TableSpace\_Name,

(Extents/Max\_extents)\*100 Percent

From sys.DBA\_Segments

Where Max\_Extents != 0 and (Extents/Max\_extents)\*100>=95

order By Percent;

prompt \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\# System Tablespace Both System And Sys User Object Information \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#

select distinct(owner) from dba\_tables

where tablespace\_name='SYSTEM' and

owner!='SYS' and owner not in ('SYS','SYSTEM',

'SYSMAN',

'EXFSYS',

'WMSYS',

'OLAPSYS',

'OUTLN',

'DBSNMP',

'ORDSYS',

'ORDDATA',

'APPQOSSYS',

'APEX\_030200',

'OWBSYS',

'ORDPLUGINS',

'MDSYS',

'CTXSYS',

'AURORA$ORB$UNAUTHENTICATED',

'XDB',

'MGMT\_VIEW',

'ANONYMOUS',

'DMSYS',

'SI\_INFORMTN\_SCHEMA',

'MDDATA',

'DIP',

'ORACLE\_OCM',

'OWBSYS\_AUDIT',

'ANONYMOUS',

'XS$NULL',

'APEX\_PUBLIC\_USER',

'SPATIAL\_CSW\_ADMIN\_USR',

'SPATIAL\_WFS\_ADMIN\_USR',

'TSMSYS',

'FLOWS\_030000',

'FLOWS\_FILES')

union

select distinct(owner) from dba\_indexes

where tablespace\_name='SYSTEM' and

owner!='SYS' and owner not in ('SYS','SYSTEM',

'SYSMAN',

'EXFSYS',

'WMSYS',

'OLAPSYS',

'OUTLN',

'DBSNMP',

'ORDSYS',

'ORDDATA',

'APPQOSSYS',

'APEX\_030200',

'OWBSYS',

'ORDPLUGINS',

'MDSYS',

'CTXSYS',

'AURORA$ORB$UNAUTHENTICATED',

'XDB',

'MGMT\_VIEW',

'ANONYMOUS',

'DMSYS',

'SI\_INFORMTN\_SCHEMA',

'MDDATA',

'DIP',

'ORACLE\_OCM',

'OWBSYS\_AUDIT',

'ANONYMOUS',

'XS$NULL',

'APEX\_PUBLIC\_USER',

'SPATIAL\_CSW\_ADMIN\_USR',

'SPATIAL\_WFS\_ADMIN\_USR',

'TSMSYS',

'FLOWS\_030000',

'FLOWS\_FILES');

prompt \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\# Next Expansion Object Space In The Max Value Information \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#

select a.table\_name, a.next\_extent, a.tablespace\_name

from all\_tables a,

(select tablespace\_name, max(bytes) as big\_chunk

from dba\_free\_space

group by tablespace\_name ) f

where f.tablespace\_name = a.tablespace\_name

and a.next\_extent > f.big\_chunk

union

select a.index\_name, a.next\_extent, a.tablespace\_name

from all\_indexes a,

(select tablespace\_name, max(bytes) as big\_chunk

from dba\_free\_space

group by tablespace\_name ) f

where f.tablespace\_name = a.tablespace\_name

and a.next\_extent > f.big\_chunk;

prompt \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\# Job Information \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#

select

job                        jid,

log\_user                   subu,

priv\_user                  secd,

what                       proc,

to\_char(last\_date,'MM/DD') lsd,

substr(last\_sec,1,5)       lst,

to\_char(next\_date,'MM/DD') nrd,

substr(next\_sec,1,5)       nrt,

failures                   fail,

decode(broken,'Y','N','Y') ok

from sys.dba\_jobs;

prompt \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\# Unusable Job Information \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#

select job,what,last\_date,next\_date,failures,broken from dba\_jobs Where schema\_user='CAIKE';

prompt \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\# User Privilege and Default Tablespace Information \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#

select

username,

default\_tablespace    dts,

temporary\_tablespace  tts,

profile prof,

granted\_role || '-' ||

decode(admin\_option,'YES','A',' ') ||

decode(granted\_role,'YES','G',' ') role

from

dba\_users,

dba\_role\_privs

where

dba\_users.username = dba\_role\_privs.grantee and

username not in ('SYS','SYSTEM',

'SYSMAN',

'EXFSYS',

'WMSYS',

'OLAPSYS',

'OUTLN',

'DBSNMP',

'ORDSYS',

'ORDDATA',

'APPQOSSYS',

'APEX\_030200',

'OWBSYS',

'ORDPLUGINS',

'MDSYS',

'CTXSYS',

'AURORA$ORB$UNAUTHENTICATED',

'XDB',

'MGMT\_VIEW',

'ANONYMOUS',

'DMSYS',

'SI\_INFORMTN\_SCHEMA',

'MDDATA',

'DIP',

'ORACLE\_OCM',

'OWBSYS\_AUDIT',

'ANONYMOUS',

'XS$NULL',

'APEX\_PUBLIC\_USER',

'SPATIAL\_CSW\_ADMIN\_USR',

'SPATIAL\_WFS\_ADMIN\_USR',

'TSMSYS',

'FLOWS\_030000',

'FLOWS\_FILES')

order by

1,2,3,4 ;

prompt \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\# Unusable Index Information \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#

Select owner, index\_name, status

From dba\_indexes

where status = 'UNUSABLE'

and owner not in ('SYS','SYSTEM',

'SYSMAN',

'EXFSYS',

'WMSYS',

'OLAPSYS',

'OUTLN',

'DBSNMP',

'ORDSYS',

'ORDDATA',

'APPQOSSYS',

'APEX\_030200',

'OWBSYS',

'ORDPLUGINS',

'MDSYS',

'CTXSYS',

'AURORA$ORB$UNAUTHENTICATED',

'XDB',

'MGMT\_VIEW',

'ANONYMOUS',

'DMSYS',

'SI\_INFORMTN\_SCHEMA',

'MDDATA',

'DIP',

'ORACLE\_OCM',

'OWBSYS\_AUDIT',

'ANONYMOUS',

'XS$NULL',

'APEX\_PUBLIC\_USER',

'SPATIAL\_CSW\_ADMIN\_USR',

'SPATIAL\_WFS\_ADMIN\_USR',

'TSMSYS',

'FLOWS\_030000',

'FLOWS\_FILES')

order by 1, 2 ;

prompt \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\# Unusable Index Partition Information \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#

select index\_owner, index\_name, partition\_name

from dba\_ind\_partitions

where status ='UNUSABLE'

and index\_owner not in ('SYS','SYSTEM',

'SYSMAN',

'EXFSYS',

'WMSYS',

'OLAPSYS',

'OUTLN',

'DBSNMP',

'ORDSYS',

'ORDDATA',

'APPQOSSYS',

'APEX\_030200',

'OWBSYS',

'ORDPLUGINS',

'MDSYS',

'CTXSYS',

'AURORA$ORB$UNAUTHENTICATED',

'XDB',

'MGMT\_VIEW',

'ANONYMOUS',

'DMSYS',

'SI\_INFORMTN\_SCHEMA',

'MDDATA',

'DIP',

'ORACLE\_OCM',

'OWBSYS\_AUDIT',

'ANONYMOUS',

'XS$NULL',

'APEX\_PUBLIC\_USER',

'SPATIAL\_CSW\_ADMIN\_USR',

'SPATIAL\_WFS\_ADMIN\_USR',

'TSMSYS',

'FLOWS\_030000',

'FLOWS\_FILES') order by 1,2;

prompt \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\# Unusable Index Subpartition Information \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#

Select Index\_Owner, Index\_Name, partition\_name, SUBPARTITION\_NAME

From DBA\_IND\_SUBPARTITIONS

Where status = 'UNUSABLE'  and index\_owner not in ('SYS','SYSTEM',

'SYSMAN',

'EXFSYS',

'WMSYS',

'OLAPSYS',

'OUTLN',

'DBSNMP',

'ORDSYS',

'ORDDATA',

'APPQOSSYS',

'APEX\_030200',

'OWBSYS',

'ORDPLUGINS',

'MDSYS',

'CTXSYS',

'AURORA$ORB$UNAUTHENTICATED',

'XDB',

'MGMT\_VIEW',

'ANONYMOUS',

'DMSYS',

'SI\_INFORMTN\_SCHEMA',

'MDDATA',

'DIP',

'ORACLE\_OCM',

'OWBSYS\_AUDIT',

'ANONYMOUS',

'XS$NULL',

'APEX\_PUBLIC\_USER',

'SPATIAL\_CSW\_ADMIN\_USR',

'SPATIAL\_WFS\_ADMIN\_USR',

'TSMSYS',

'FLOWS\_030000',

'FLOWS\_FILES') order by 1, 2;

prompt \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\# Tables With No Primary Key Information \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#

SELECT owner, table\_name

FROM dba\_tables

WHERE 1 = 1

AND owner NOT IN

('SYS','SYSTEM',

'SYSMAN',

'EXFSYS',

'WMSYS',

'OLAPSYS',

'OUTLN',

'DBSNMP',

'ORDSYS',

'ORDDATA',

'APPQOSSYS',

'APEX\_030200',

'OWBSYS',

'ORDPLUGINS',

'MDSYS',

'CTXSYS',

'AURORA$ORB$UNAUTHENTICATED',

'XDB',

'MGMT\_VIEW',

'ANONYMOUS',

'DMSYS',

'SI\_INFORMTN\_SCHEMA',

'MDDATA',

'DIP',

'ORACLE\_OCM',

'OWBSYS\_AUDIT',

'ANONYMOUS',

'XS$NULL',

'APEX\_PUBLIC\_USER',

'SPATIAL\_CSW\_ADMIN\_USR',

'SPATIAL\_WFS\_ADMIN\_USR',

'TSMSYS',

'FLOWS\_030000',

'FLOWS\_FILES')

MINUS

SELECT owner, table\_name

FROM dba\_constraints

WHERE constraint\_type = 'P'

AND owner NOT IN

('SYS','SYSTEM',

'SYSMAN',

'EXFSYS',

'WMSYS',

'OLAPSYS',

'OUTLN',

'DBSNMP',

'ORDSYS',

'ORDDATA',

'APPQOSSYS',

'APEX\_030200',

'OWBSYS',

'ORDPLUGINS',

'MDSYS',

'CTXSYS',

'AURORA$ORB$UNAUTHENTICATED',

'XDB',

'MGMT\_VIEW',

'ANONYMOUS',

'DMSYS',

'SI\_INFORMTN\_SCHEMA',

'MDDATA',

'DIP',

'ORACLE\_OCM',

'OWBSYS\_AUDIT',

'ANONYMOUS',

'XS$NULL',

'APEX\_PUBLIC\_USER',

'SPATIAL\_CSW\_ADMIN\_USR',

'SPATIAL\_WFS\_ADMIN\_USR',

'TSMSYS',

'FLOWS\_030000',

'FLOWS\_FILES');

prompt \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\# Tables With No Unique Key Or Index Information \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#

SELECT owner, table\_name

FROM dba\_all\_tables

WHERE 1 = 1

AND owner NOT IN

('SYS','SYSTEM',

'SYSMAN',

'EXFSYS',

'WMSYS',

'OLAPSYS',

'OUTLN',

'DBSNMP',

'ORDSYS',

'ORDDATA',

'APPQOSSYS',

'APEX\_030200',

'OWBSYS',

'ORDPLUGINS',

'MDSYS',

'CTXSYS',

'AURORA$ORB$UNAUTHENTICATED',

'XDB',

'MGMT\_VIEW',

'ANONYMOUS',

'DMSYS',

'SI\_INFORMTN\_SCHEMA',

'MDDATA',

'DIP',

'ORACLE\_OCM',

'OWBSYS\_AUDIT',

'ANONYMOUS',

'XS$NULL',

'APEX\_PUBLIC\_USER',

'SPATIAL\_CSW\_ADMIN\_USR',

'SPATIAL\_WFS\_ADMIN\_USR',

'TSMSYS',

'FLOWS\_030000',

'FLOWS\_FILES')

MINUS

SELECT owner, table\_name

FROM dba\_constraints

WHERE constraint\_type = 'U'

AND owner NOT IN

('SYS','SYSTEM',

'SYSMAN',

'EXFSYS',

'WMSYS',

'OLAPSYS',

'OUTLN',

'DBSNMP',

'ORDSYS',

'ORDDATA',

'APPQOSSYS',

'APEX\_030200',

'OWBSYS',

'ORDPLUGINS',

'MDSYS',

'CTXSYS',

'AURORA$ORB$UNAUTHENTICATED',

'XDB',

'MGMT\_VIEW',

'ANONYMOUS',

'DMSYS',

'SI\_INFORMTN\_SCHEMA',

'MDDATA',

'DIP',

'ORACLE\_OCM',

'OWBSYS\_AUDIT',

'ANONYMOUS',

'XS$NULL',

'APEX\_PUBLIC\_USER',

'SPATIAL\_CSW\_ADMIN\_USR',

'SPATIAL\_WFS\_ADMIN\_USR',

'TSMSYS',

'FLOWS\_030000',

'FLOWS\_FILES')

MINUS

SELECT owner, table\_name

FROM dba\_indexes

WHERE uniqueness = 'UNIQUE'

AND owner NOT IN

('SYS','SYSTEM',

'SYSMAN',

'EXFSYS',

'WMSYS',

'OLAPSYS',

'OUTLN',

'DBSNMP',

'ORDSYS',

'ORDDATA',

'APPQOSSYS',

'APEX\_030200',

'OWBSYS',

'ORDPLUGINS',

'MDSYS',

'CTXSYS',

'AURORA$ORB$UNAUTHENTICATED',

'XDB',

'MGMT\_VIEW',

'ANONYMOUS',

'DMSYS',

'SI\_INFORMTN\_SCHEMA',

'MDDATA',

'DIP',

'ORACLE\_OCM',

'OWBSYS\_AUDIT',

'ANONYMOUS',

'XS$NULL',

'APEX\_PUBLIC\_USER',

'SPATIAL\_CSW\_ADMIN\_USR',

'SPATIAL\_WFS\_ADMIN\_USR',

'TSMSYS',

'FLOWS\_030000',

'FLOWS\_FILES');

prompt \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\# Invalid Object Information \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#

SELECT OWNER, OBJECT\_NAME, OBJECT\_TYPE, STATUS FROM DBA\_OBJECTS WHERE STATUS = 'INVALID' ORDER BY OWNER, OBJECT\_TYPE, OBJECT\_NAME;

prompt \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\# load profile \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#

set pagesize 99

set linesize 160

col snap\_date for a6

col "TIME" for a6

col "elapse(min)" for 999999

col "DB time(min)" for 999999

col redo for 999999

col "redo/s" for 999999

col logical for 999999

col "logical/s" for 999999

col physical for 999999

col "phy/s" for 999999

col execs for 999999

col "execs/s" for 999999

col parse for 999999

col "parse/s" for 999999

col hardparse for 999999

col "hardparse/s" for 999999

col trans for 999999

col "trans/s" for 999999

select s.snap\_date,

decode(s.redosize, null, '--shutdown or end--', s.currtime) "TIME",

to\_char(round(s.seconds/60,2)) "elapse(min)",

round(t.db\_time / 1000000 / 60, 2) "DB time(min)",

s.redosize redo,

round(s.redosize / s.seconds, 2) "redo/s",

s.logicalreads logical,

round(s.logicalreads / s.seconds, 2) "logical/s",

physicalreads physical,

round(s.physicalreads / s.seconds, 2) "phy/s",

s.executes execs,

round(s.executes / s.seconds, 2) "execs/s",

s.parse,

round(s.parse / s.seconds, 2) "parse/s",

s.hardparse,

round(s.hardparse / s.seconds, 2) "hardparse/s",

s.transactions trans,

round(s.transactions / s.seconds, 2) "trans/s"

from (select curr\_redo - last\_redo redosize,

curr\_logicalreads - last\_logicalreads logicalreads,

curr\_physicalreads - last\_physicalreads physicalreads,

curr\_executes - last\_executes executes,

curr\_parse - last\_parse parse,

curr\_hardparse - last\_hardparse hardparse,

curr\_transactions - last\_transactions transactions,

round(((currtime + 0) - (lasttime + 0)) \* 3600 \* 24, 0) seconds,

to\_char(currtime, 'yy/mm/dd') snap\_date,

to\_char(currtime, 'hh24:mi') currtime,

currsnap\_id endsnap\_id,

to\_char(startup\_time, 'yyyy-mm-dd hh24:mi:ss') startup\_time

from (select a.redo last\_redo,

a.logicalreads last\_logicalreads,

a.physicalreads last\_physicalreads,

a.executes last\_executes,

a.parse last\_parse,

a.hardparse last\_hardparse,

a.transactions last\_transactions,

lead(a.redo, 1, null) over(partition by b.startup\_time order by b.end\_interval\_time) curr\_redo,

lead(a.logicalreads, 1, null) over(partition by b.startup\_time order by b.end\_interval\_time) curr\_logicalreads,

lead(a.physicalreads, 1, null) over(partition by b.startup\_time order by b.end\_interval\_time) curr\_physicalreads,

lead(a.executes, 1, null) over(partition by b.startup\_time order by b.end\_interval\_time) curr\_executes,

lead(a.parse, 1, null) over(partition by b.startup\_time order by b.end\_interval\_time) curr\_parse,

lead(a.hardparse, 1, null) over(partition by b.startup\_time order by b.end\_interval\_time) curr\_hardparse,

lead(a.transactions, 1, null) over(partition by b.startup\_time order by b.end\_interval\_time) curr\_transactions,

b.end\_interval\_time lasttime,

lead(b.end\_interval\_time, 1, null) over(partition by b.startup\_time order by b.end\_interval\_time) currtime,

lead(b.snap\_id, 1, null) over(partition by b.startup\_time order by b.end\_interval\_time) currsnap\_id,

b.startup\_time

from (select snap\_id,

dbid,

instance\_number,

sum(decode(stat\_name, 'redo size', value, 0)) redo,

sum(decode(stat\_name,

'session logical reads',

value,

0)) logicalreads,

sum(decode(stat\_name,

'physical reads',

value,

0)) physicalreads,

sum(decode(stat\_name, 'execute count', value, 0)) executes,

sum(decode(stat\_name,

'parse count (total)',

value,

0)) parse,

sum(decode(stat\_name,

'parse count (hard)',

value,

0)) hardparse,

sum(decode(stat\_name,

'user rollbacks',

value,

'user commits',

value,

0)) transactions

from dba\_hist\_sysstat

where stat\_name in

('redo size',

'session logical reads',

'physical reads',

'execute count',

'user rollbacks',

'user commits',

'parse count (hard)',

'parse count (total)')

group by snap\_id, dbid, instance\_number) a,

dba\_hist\_snapshot b

where a.snap\_id = b.snap\_id

and a.dbid = b.dbid

and a.instance\_number = b.instance\_number

and a.dbid = (select dbid from v$database where rownum = 1 )

and a.instance\_number = (select instance\_number from v$instance where rownum = 1 )

order by end\_interval\_time)) s,

(select lead(a.value, 1, null) over(partition by b.startup\_time order by b.end\_interval\_time) - a.value db\_time,

lead(b.snap\_id, 1, null) over(partition by b.startup\_time order by b.end\_interval\_time) endsnap\_id

from dba\_hist\_sys\_time\_model a, dba\_hist\_snapshot b

where a.snap\_id = b.snap\_id

and a.dbid = b.dbid

and a.instance\_number = b.instance\_number

and a.stat\_name = 'DB time'

and a.dbid = (select dbid from v$database where rownum = 1 )

and a.instance\_number = (select instance\_number from v$instance where rownum = 1 )) t

where s.endsnap\_id = t.endsnap\_id

order by  s.snap\_date desc ,time asc;

prompt \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\# TOP 5 Event \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#

set linesize 120

col event for a30

col wclas for a16

SELECT event "Event", totwa "Waits", twsec "Time(s)", avgms "Avg(ms)",

ROUND(RATIO\_TO\_REPORT(twsec) OVER () \* 100,1) pctwa , wclas "Wait Class"

FROM

(SELECT

NVL(substr(event,1,30),' ')          event,

AVG(total\_waits)                      totwa,

ROUND(AVG(time\_waited\_micro)/1000000, 0)   twsec,

ROUND(SUM(time\_waited\_micro)/

SUM(total\_waits)/1000, 2)             avgms,

NVL(substr(wait\_class,1,13),' ')           wclas

FROM  Gv$SYSTEM\_EVENT

WHERE WAIT\_CLASS <> 'Idle'

GROUP BY event, wait\_class

HAVING SUM(total\_waits) > 0

UNION ALL

SELECT

'CPU time'                           event,

0                                    totwa,

AVG(icput)                           twsec,

0                                    avgms,

' '                                  wclas

FROM

(

select inst\_id, sum(VALUE)/100 icput

FROM   Gv$SYSSTAT

WHERE name in ('CPU used when call started',

'CPU used by this session')

group by inst\_id) ORDER BY twsec desc) WHERE ROWNUM <6;

spool off

exit;

转载于:https://www.cnblogs.com/liang545621/p/9410613.html

database.sql

发表评论取消回复

还没有评论，来说两句吧...

相关阅读