SQL> exec plato.audit('HTM');
General | Files | Storage | Rbs/Undo | Bckp/reco | Problems | Tables | Jobs/AQ | Memory | Security | Networking | Various | DBFeatures |
General Database Information | top |
|
|
Instance overview | ||||||||
---|---|---|---|---|---|---|---|---|
Id | Nr | Name | Host | Startup | Status | Par | Thread | Logins |
1 | 1 | TRU817 | trudba.iconos.be | 28-Sep 13:53:13 | OPEN | NO | 1 | ALLOWED |
File Information | top |
|
|
Tablespaces and datafiles | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
TsName | TS size | Used | AutoExt MaxSize | %AutoMax | TsStat | Filename | Tp | Filesize | Aut Ext | FileMax |
DICT_FULL | 5.0M |
3.8M (76%)
|
5.0M |
76
|
OFFL | /u03/oradata/TRU817/dict_full.dbf | D | 5.0M | NO | |
LMS_AUTO_FULL | 6.0M |
4.8M (81%)
|
6.0M |
81
|
ONL | /u03/oradata/TRU817/lms_auto_full.dbf | D | 6.0M | NO | |
LMS_FULL | 10.0M |
9.2M (93%)
|
10.0M |
93
|
ONL | /u03/oradata/TRU817/lms_full.dbf | D | 10.0M | NO | |
RBS | 145.0M |
12.0M (8%)
|
15.9G |
0
|
ONL | /u03/oradata/TRU817/rbs01.dbf | D | 145.0M | YES | 15.9G |
SYSTEM | 260.0M |
49.5M (19%)
|
15.9G |
0
|
ONL | /u03/oradata/TRU817/system01.dbf | D | 260.0M | YES | 15.9G |
TEMP | 15.0M |
256.0K (2%)
|
15.9G |
0
|
ONL | /u03/oradata/TRU817/temp01.dbf | D | 15.0M | YES | 15.9G |
TEMP2 | 6.0M |
6.0M (100%)
|
10.0M |
60
|
ONL | /u03/oradata/TRU817/temp201.dbf | T | 6.0M | YES | 10.0M |
TSOFFLINE | 100.0K |
4.0K (4%)
|
100.0K |
4
|
ONL | /u03/oradata/TRU817/tsoffline.dbf | D | 100.0K | NO | |
TSRECOVER | 200.0K |
8.0K (4%)
|
200.0K |
4
|
ONL | /u03/oradata/TRU817/tsrecover.dbf | D | 100.0K | NO | |
/u03/oradata/TRU817/tsrecover2.dbf | D | 100.0K | NO | |||||||
USERS | 150.0M |
107.5M (72%)
|
150.0M |
72
|
ONL | /u03/oradata/TRU817/users01.dbf | D | 150.0M | NO |
|
|
|
|
Storage parameters and file statistics | top |
Tablespace storage parameters | |||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|
TablespaceName | Init | Next | Min | Max | Pct | MinExtLen | Stat | Cont | Mgmt | AllocType | Log |
DICT_FULL | 256.0K | 256.0K | 1 | 249 | 0 | 0 | PERM | DICT | USER | LOG | |
LMS_AUTO_FULL | 64.0K | 1 | Unlim | 64.0K | ONL | PERM | LOCA | AUTO | LOG | ||
LMS_FULL | 256.0K | 256.0K | 1 | Unlim | 0 | 256.0K | ONL | PERM | LOCA | UNIFORM | LOG |
RBS | 512.0K | 512.0K | 8 | 4096 | 50 | 512.0K | ONL | PERM | DICT | USER | LOG |
SYSTEM | 64.0K | 64.0K | 1 | Unlim | 50 | 64.0K | ONL | PERM | DICT | USER | LOG |
TEMP | 64.0K | 64.0K | 1 | Unlim | 0 | 64.0K | ONL | TEMP | DICT | USER | LOG |
TEMP2 | 1.0M | 1.0M | 1 | Unlim | 0 | 1.0M | ONL | TEMP | LOCA | UNIFORM | NOL |
TSOFFLINE | 20.0K | 20.0K | 1 | 249 | 50 | 0 | ONL | PERM | DICT | USER | LOG |
TSRECOVER | 20.0K | 20.0K | 1 | 249 | 50 | 0 | ONL | PERM | DICT | USER | LOG |
USERS | 128.0K | 128.0K | 1 | 4096 | 0 | 4.0K | ONL | PERM | DICT | USER | LOG |
Datafile IO since startup (top 25) | ||||||
---|---|---|---|---|---|---|
Filename | PhyRds | PhyWrts | PhyBlkrd | PhyBlkWrt | Blks/Rd | R+W/sec |
/u03/oradata/TRU817/system01.dbf |
9229
|
4735
|
44153 | 4735 | 4.78 | .12 |
/u03/oradata/TRU817/rbs01.dbf |
46
|
4827
|
46 | 4827 | 1 | .04 |
/u03/oradata/TRU817/temp01.dbf |
89
|
249
|
251 | 249 | 2.82 | 0 |
/u03/oradata/TRU817/lms_full.dbf |
49
|
37
|
49 | 37 | 1 | 0 |
/u03/oradata/TRU817/lms_auto_full.dbf |
47
|
37
|
47 | 37 | 1 | 0 |
/u03/oradata/TRU817/users01.dbf |
39
|
37
|
39 | 37 | 1 | 0 |
/u03/oradata/TRU817/tsrecover.dbf |
39
|
37
|
39 | 37 | 1 | 0 |
/u03/oradata/TRU817/tsoffline.dbf |
39
|
37
|
39 | 37 | 1 | 0 |
/u03/oradata/TRU817/temp201.dbf |
4
|
0
|
4 | 0 | 1 | 0 |
/u03/oradata/TRU817/dict_full.dbf |
0
|
0
|
0 | 0 | 0 | 0 |
/u03/oradata/TRU817/tsrecover2.dbf |
0
|
0
|
0 | 0 | 0 | 0 |
Segment size by owner and tablespace | |||||||
---|---|---|---|---|---|---|---|
Owner\TsName | DICT_FULL | LMS_AUTO_FULL | LMS_FULL | RBS | SYSTEM | TEMP | USERS |
LOBOVERLINK | 0 | 0 | 0 | 0 | 0 | 0 | 128.0K |
NAGIOS | 0 | 0 | 0 | 0 | 0 | 0 | 640.0K |
ORAPROBE | 0 | 0 | 0 | 0 | 0 | 0 | 128.0K |
OUTLN | 0 | 0 | 0 | 0 | 60.0K | 0 | 0 |
PANDORA | 0 | 0 | 0 | 0 | 0 | 0 | 3.7M |
PERFSTAT | 0 | 0 | 0 | 0 | 0 | 0 | 90.8M |
SCOTT | 0 | 1.8M | 5.2M | 0 | 0 | 0 | 12.1M |
SYS | 3.8M | 3.0M | 3.7M | 12.0M | 46.3M | 256.0K | 0 |
SYSTEM | 0 | 0 | 0 | 0 | 3.1M | 0 | 0 |
Users's temporary tablespaces | |||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|
TsName | Size | Init | Next | Min Nr Ext | Min_ext | Max_ext | Pct_inc | Mgmt | AllocType | Max poss ext | Cont |
TEMP | 15.0M | 64.0K | 64.0K | 64.0K | 1 | Unlim | 0 | DICT | USER | 240 | TEMP |
Rollback segment information | top |
|
|
|
Backup and recovery | top |
|
|
Possible storage problems | top |
Tables where 4 next extents will not fit | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|
Owner | Segment | SegSize | Can allocate... | TsName | Allocation | Initial | Next | PctInc | Next1 | Next2 | Next3 | Next4 |
SYS | TAB_LMS_AUTO_FULL | 3.0M | no more extents!!! | LMS_AUTO_FULL | Local system | 1.0M | 1.0M | 1.0M | 1.0M | 1.0M | ||
SCOTT | TESTALLOC4 | 960.0K | 1 more extent | LMS_AUTO_FULL | Local system | 952.0K | 64.0K | 1.0M | 1.0M | 1.0M | ||
SCOTT | TESTALLOC6 | 896.0K | 2 more extents | LMS_AUTO_FULL | Local system | 852.0K | 64.0K | 64.0K | 1.0M | 1.0M | ||
SYS | TAB_LMS_FULL | 3.7M | 3 more extents | LMS_FULL | Local uniform | 3.7M | 256.0K | 0 | 256.0K | 256.0K | 256.0K | 256.0K |
SCOTT | TESTALLOC2 | 256.0K | 3 more extents | LMS_FULL | Local uniform | 100.0K | 256.0K | 0 | 256.0K | 256.0K | 256.0K | 256.0K |
SCOTT | TESTALLOC5 | 4.0M | 3 more extents | LMS_FULL | Local uniform | 4.0M | 256.0K | 0 | 256.0K | 256.0K | 256.0K | 256.0K |
SCOTT | TESTALLOC3 | 1.0M | 3 more extents | LMS_FULL | Local uniform | 952.0K | 256.0K | 0 | 256.0K | 256.0K | 256.0K | 256.0K |
Tables within 5 of their max extents | |||||||||
---|---|---|---|---|---|---|---|---|---|
Owner | Name | Type | TsName | Xtnts | MaxXtnts | Size | Init | Next | Pct |
SCOTT | VEEL_EXTENTS | TABLE | USERS | 1234 | 1236 | 5.4M | 8.0K | 4.0K | 0 |
Segments of SYS, top 10 by nr of extents | |||||||
---|---|---|---|---|---|---|---|
Name | Type | TsName | Xtnts | Size | Init | Next | Pct |
SOURCE$ | TABLE | SYSTEM | 63 | 15.4M | 12.0K | 200.0K | 0 |
IDL_UB1$ | TABLE | SYSTEM | 34 | 4.1M | 12.0K | 100.0K | 0 |
IDL_UB2$ | TABLE | SYSTEM | 27 | 3.1M | 12.0K | 100.0K | 0 |
TAB_DICT_FULL | TABLE | DICT_FULL | 15 | 3.8M | 256.0K | 256.0K | 0 |
SYSTEM | ROLLBACK | SYSTEM | 12 | 720.0K | 52.0K | 52.0K | 0 |
VIEW$ | TABLE | SYSTEM | 11 | 1.0M | 12.0K | 100.0K | 0 |
ARGUMENT$ | TABLE | SYSTEM | 10 | 1.1M | 12.0K | 100.0K | 0 |
C_OBJ# | CLUSTER | SYSTEM | 9 | 1.8M | 132.0K | 200.0K | 0 |
I_COL1 | INDEX | SYSTEM | 8 | 768.0K | 32.0K | 100.0K | 0 |
I_ARGUMENT1 | INDEX | SYSTEM | 8 | 880.0K | 12.0K | 100.0K | 0 |
Segments non-SYS, top 10 by nr of extents | |||||||||
---|---|---|---|---|---|---|---|---|---|
Owner | Name | Type | TsName | Xtnts | Size | Init | Next | MaxExt | Pct |
SCOTT | VEEL_EXTENTS | TABLE | USERS | 1234 | 5.4M | 8.0K | 4.0K | 1.2K | 0 |
PERFSTAT | STATS$SQL_SUMMARY_PK | INDEX | USERS | 162 | 21.1M | 1.0M | 128.0K | 4.0K | 0 |
PERFSTAT | STATS$SQL_SUMMARY | TABLE | USERS | 157 | 20.5M | 1.0M | 128.0K | 4.0K | 0 |
PERFSTAT | STATS$PARAMETER_PK | INDEX | USERS | 18 | 2.1M | 64.0K | 128.0K | 4.0K | 0 |
PERFSTAT | STATS$SYSSTAT_PK | INDEX | USERS | 16 | 2.8M | 1.0M | 128.0K | 4.0K | 0 |
PERFSTAT | STATS$LATCH_MISSES_SUMMARY_PK | INDEX | USERS | 13 | 1.5M | 64.0K | 128.0K | 4.0K | 0 |
PERFSTAT | STATS$SQLTEXT_PK | INDEX | USERS | 10 | 1.1M | 64.0K | 128.0K | 4.0K | 0 |
PERFSTAT | STATS$LATCH_PK | INDEX | USERS | 7 | 1.7M | 1.0M | 128.0K | 4.0K | 0 |
SCOTT | TESTALLOC12 | TABLE | USERS | 3 | 1.5M | 500.0K | 500.0K | 4.0K | 0 |
SCOTT | TESTALLOC11 | TABLE | USERS | 2 | 2.0M | 1.0M | 1.0M | 4.0K | 0 |
Invalid objects |
---|
Owner\ObjType |
Overview index types | ||
---|---|---|
Owner\IndType | LOB | NORMAL |
DBSNMP | 0 | 2 |
NAGIOS | 0 | 2 |
OUTLN | 0 | 3 |
PANDORA | 1 | 13 |
PERFSTAT | 0 | 31 |
SCOTT | 0 | 1 |
Init.ora | |
---|---|
Query rewrite enabled?: | FALSE |
Non-SYS objects in SYSTEM tablespace | |||
---|---|---|---|
Owner | Name | Type | Size |
SYS | R00 | ROLLBACK | 128.0K |
System tablespace fragmentation | |
---|---|
Start BlockNr | Size |
12687 | 210.4M |
Permanent objects in temporary tablespace | |||
---|---|---|---|
TsName | Owner | SegType | NrSeg |
Table properties | top |
|
|
Last 10 weeks only |
|
|
Analyzed tables with non-analyzed indexes | ||
---|---|---|
Owner | Tablename | Nr indexes |
More than 5% chained rows | |||||||
---|---|---|---|---|---|---|---|
Owner | Tablename | Num rows | Chained | Pct | LastAnalzd | AvgRowLen | Rows*Len |
SYS | IDL_UB1$ | 1052 | 214 | 20 | 23-jun-04 | 2386 | 2.3M |
SYS | IDL_UB2$ | 1103 | 145 | 13 | 23-jun-04 | 1809 | 1.9M |
|
|
Unused space | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
Owner | Table | TsName | EffData | TotAlloc | AboveHwm | Unused | %Unused | PctFree | Extnts | Analyzed |
SCOTT | EMP | USERS | 4.0K | 128.0K | 120.0K | 124.0K |
97
|
10 | 1 | 23-jun-04 |
SCOTT | DEPT | USERS | 4.0K | 128.0K | 120.0K | 124.0K |
97
|
10 | 1 | 23-jun-04 |
SCOTT | DUMMY | USERS | 20.0K | 128.0K | 120.0K | 108.0K |
84
|
10 | 1 | 23-jun-04 |
SYSTEM | AQ$_QUEUE_TABLES | SYSTEM | 4.0K | 64.0K | 56.0K | 60.0K |
94
|
10 | 1 | 23-jun-04 |
SYSTEM | AQ$_QUEUES | SYSTEM | 4.0K | 64.0K | 56.0K | 60.0K |
94
|
10 | 1 | 23-jun-04 |
Nr partitioned tables by owner and type |
---|
Type\Owner |
|
|
Index organized tables | ||||
---|---|---|---|---|
Owner | TableName | TsName | Type | IndexName |
Nested tables | |||||
---|---|---|---|---|---|
Owner | TableName | Type Owner | Type Name | Parent table | Parent column |
Materialized views | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
Owner | Name | Mode | Method | Built | Master link | Upd? | Rewr? | Last refr | Stale | State |
Jobs and AQ information | top |
DBMS Job information | |||||||
---|---|---|---|---|---|---|---|
Job | Schema | Last date | This date | Next date | Brkn | Fail | What |
1 | SCOTT | 20-07-2004 15:56:31 | N | begin null; end; |
Job queue parameters | |
---|---|
Security parameters | Value |
job_queue_processes: | 0 |
job_queue_interval: | 60 |
Advanced queues | |||
---|---|---|---|
Owner | Queue | QueueTable | Comment |
AQ parameters | |
---|---|
Parameter | Value |
aq_tm_processes: | 0 |
Memory information | top |
|
|
|
|
|
Parameter | Value |
---|---|
pga_aggregate_target: | Unexistant |
Security | top |
Overview users | ||||||||
---|---|---|---|---|---|---|---|---|
Username | DefTs | TempTs | Created | NrSeg | TotSize | OPS$ ? | Account | DBA ? |
SYS | SYSTEM | TEMP | 08-Dec-2003 | 400 | 69.1M | No | open | Yes |
SYSTEM | SYSTEM | TEMP | 08-Dec-2003 | 51 | 3.1M | No | open | Yes |
OUTLN | SYSTEM | SYSTEM | 08-Dec-2003 | 5 | 60.0K | No | open | No |
DBSNMP | SYSTEM | SYSTEM | 08-Dec-2003 | 0 | 0 | No | open | No |
TRACESVR | SYSTEM | SYSTEM | 08-Dec-2003 | 0 | 0 | No | open | No |
NAGIOS | USERS | SYSTEM | 20-Jan-2004 | 5 | 640.0K | No | open | No |
SCOTT | USERS | TEMP | 19-Apr-2004 | 19 | 19.1M | No | open | No |
ORACLE | SYSTEM | SYSTEM | 11-Jun-2004 | 0 | 0 | Yes | open | No |
PERFSTAT | USERS | TEMP | 12-Oct-2004 | 62 | 90.8M | No | open | No |
ORAPROBE | USERS | TEMP | 14-Nov-2004 | 1 | 128.0K | No | open | No |
PANDORA | USERS | TEMP | 25-Nov-2004 | 30 | 3.7M | No | open | No |
LOBOVERLINK | USERS | TEMP | 27-Jul-2005 | 1 | 128.0K | No | open | No |
Security parameters | |
---|---|
Security parameters | Value |
remote_login_passwordfile: | EXCLUSIVE |
remote_os_authent: | FALSE |
os_authent_prefix: |
Networking | top |
|
|
Dispatchers | |||||||
---|---|---|---|---|---|---|---|
Name | Network | Status | Accept | Messages | Bytes | Idle | Busy |
Shared servers | |||||
---|---|---|---|---|---|
Name | Status | Messages | Bytes | Idle | Busy |
Queues | ||||
---|---|---|---|---|
Name | Wait | TotalQ | Pct wait | Queued |
Various | top |
|
Featured init.ora parameters | ||
---|---|---|
Parameter | Dflt? | Value |
background_dump_dest | FALSE | /u03/admin/TRU817/bdump |
compatible | FALSE | 8.1.0 |
control_files | FALSE |
/u03/oradata/TRU817/control01.ctl, /u03/oradata/TRU817/control02.ctl, /u03/oradata/TRU817/control03.ctl |
core_dump_dest | FALSE | /u03/admin/TRU817/cdump |
db_block_buffers | FALSE | 2000 |
db_block_size | FALSE | 4096 |
db_name | FALSE | TRU817 |
instance_name | FALSE | TRU817 |
java_pool_size | FALSE | 32768 |
large_pool_size | FALSE | 614400 |
local_listener | FALSE | listener_TRU817.iconos.be |
log_archive_dest_1 | FALSE | location=/u03/oradata/TRU817/archive |
log_archive_format | FALSE | arc_%s.log |
log_archive_start | FALSE | TRUE |
log_buffer | FALSE | 163840 |
log_checkpoint_interval | FALSE | 10000 |
log_checkpoint_timeout | FALSE | 1800 |
max_enabled_roles | FALSE | 30 |
open_cursors | FALSE | 300 |
os_authent_prefix | FALSE | |
processes | FALSE | 150 |
remote_login_passwordfile | FALSE | EXCLUSIVE |
service_names | FALSE | TRU817 |
shared_pool_size | FALSE | 25217574 |
sort_area_retained_size | FALSE | 65536 |
sort_area_size | FALSE | 65536 |
sql_trace | TRUE | FALSE |
timed_os_statistics | TRUE | 0 |
timed_statistics | TRUE | FALSE |
user_dump_dest | FALSE | /u03/admin/TRU817/udump |
utl_file_dir | TRUE |
Hidden parameters | ||
---|---|---|
Name | Description | Instance value |
Database Features | top |
|
|
|
Script to recreate the controlfile | top |
CREATE CONTROLFILE script |
---|
CREATE CONTROLFILE REUSE DATABASE "TRU817" RESETLOGS ARCHIVELOG |
-- CREATE CONTROLFILE REUSE SET DATABASE "NEW_DB_NAME" RESETLOGS ARCHIVELOG |
MAXLOGFILES 32 |
MAXLOGMEMBERS 2 |
MAXDATAFILES 254 |
MAXINSTANCES 1 |
MAXLOGHISTORY 904 |
LOGFILE |
GROUP 1 ('/u03/oradata/TRU817/redo01.log') SIZE 1000K, |
GROUP 2 ('/u03/oradata/TRU817/redo02.log') SIZE 1000K, |
GROUP 3 ('/u03/oradata/TRU817/redo03.log') SIZE 1000K |
DATAFILE |
'/u03/oradata/TRU817/system01.dbf', |
'/u03/oradata/TRU817/rbs01.dbf', |
'/u03/oradata/TRU817/temp01.dbf', |
'/u03/oradata/TRU817/users01.dbf', |
'/u03/oradata/TRU817/lms_full.dbf', |
'/u03/oradata/TRU817/lms_auto_full.dbf', |
'/u03/oradata/TRU817/tsoffline.dbf', |
'/u03/oradata/TRU817/tsrecover.dbf', |
'/u03/oradata/TRU817/tsrecover2.dbf' |
CHARACTER SET WE8ISO8859P1 |
; |
-- Take files offline to match current control file: |
ALTER DATABASE DATAFILE '/u03/oradata/TRU817/tsrecover2.dbf' OFFLINE; |
-- Files in normal offline tablespaces are now named: |
ALTER DATABASE RENAME FILE 'MISSING00005' TO '/u03/oradata/TRU817/dict_full.dbf'; |
-- Commands to add tempfiles to temporary tablespaces: |
ALTER TABLESPACE TEMP2 ADD TEMPFILE '/u03/oradata/TRU817/temp201.dbf' |
SIZE 6M REUSE |
AUTOEXTEND ON NEXT 256 MAXSIZE 10M; |