Oracle Apps R12: More DBA Analysis Of TableSpaces, Log, And Control Files
Lots of tablespaces! select TABLESPACE_NAME from dba_tablespaces order by TABLESPACE_NAME TABLESPACE_NAME ------------------------------ APPS_CALCLIP APPS_OMO APPS_TS_ARCHIVE APPS_TS_DISCO APPS_TS_DISCO_OLAP APPS_TS_INTERFACE APPS_TS_MEDIA APPS_TS_NOLOGGING APPS_TS_QUEUES APPS_TS_SEED APPS_TS_SUMMARY APPS_TS_TX_DATA APPS_TS_TX_IDX B2B_DT B2B_IDX B2B_LOB B2B_RT BAM BIA_RTL CTXSYS CWMLITE DATA DCM DEMANTRA DISCO_PTM5_CACHE DISCO_PTM5_META DSGATEWAY_TAB DW_AGGREGATE_IDX DW_AGGREGATE_TBS DW_BASE_IDX DW_BASE_TBS DW_DERIVED_IDX DW_DERIVED_TBS DW_DM_TBS DW_LOOKUP_TBS DW_MVLOG_TBS DW_REFERENCE_IDX DW_REFERENCE_TBS GEOR_TBS HTMLDB IAS_META MTR OCATS ODM_DATA OLAP_BAAD OLTS_ATTRSTORE OLTS_BATTRSTORE OLTS_CT_STORE OLTS_DEFAULT OLTS_SVRMGSTORE OPMOR ORABPEL OWAPUB RBS_MIG RE SYNCSERVER SYSAUX SYSTEM TEMP1 TEMP2 TS_DP TS_SALES_DATA TS_SALES_DATA_ENGINE TS_SALES_DATA_ENGINE_X TS_SALES_DATA_X TS_SIM TS_SIM_X UDDISYS_TS UNDO_TBS WCRSYS_TS XDB 71 rows selected. ------------------------- Where are the files for the tablespaces? find /oapps/oracle/VIS/db/apps_st/data -type f| sort
/oapps/oracle/VIS/db/apps_st/data/apps_calclip1.dbf /oapps/oracle/VIS/db/apps_st/data/apps_calclip2.dbf /oapps/oracle/VIS/db/apps_st/data/apps_calclip3.dbf /oapps/oracle/VIS/db/apps_st/data/apps_calclip.dbf /oapps/oracle/VIS/db/apps_st/data/APPS_OMO02.dbf /oapps/oracle/VIS/db/apps_st/data/apps_ts_interface10.dbf /oapps/oracle/VIS/db/apps_st/data/apps_ts_interface11.dbf /oapps/oracle/VIS/db/apps_st/data/apps_ts_interface12.dbf /oapps/oracle/VIS/db/apps_st/data/apps_ts_interface13.dbf /oapps/oracle/VIS/db/apps_st/data/apps_ts_interface14.dbf ... /oapps/oracle/VIS/db/apps_st/data/ias_meta01.dbf /oapps/oracle/VIS/db/apps_st/data/IAS_META02.dbf /oapps/oracle/VIS/db/apps_st/data/log1.dbf /oapps/oracle/VIS/db/apps_st/data/log2.dbf /oapps/oracle/VIS/db/apps_st/data/log3.dbf /oapps/oracle/VIS/db/apps_st/data/media1.dbf /oapps/oracle/VIS/db/apps_st/data/media2.dbf /oapps/oracle/VIS/db/apps_st/data/media3.dbf /oapps/oracle/VIS/db/apps_st/data/media4.dbf /oapps/oracle/VIS/db/apps_st/data/media5.dbf ... /oapps/oracle/VIS/db/apps_st/data/sys9.dbf /oapps/oracle/VIS/db/apps_st/data/sysaux01.dbf /oapps/oracle/VIS/db/apps_st/data/SYSTEM01.dbf /oapps/oracle/VIS/db/apps_st/data/SYSTEM02.dbf /oapps/oracle/VIS/db/apps_st/data/temp1_01.dbf /oapps/oracle/VIS/db/apps_st/data/temp2_01.dbf /oapps/oracle/VIS/db/apps_st/data/ts_dp.dbf ... /oapps/oracle/VIS/db/apps_st/data/tx_idx8.dbf /oapps/oracle/VIS/db/apps_st/data/tx_idx9.dbf /oapps/oracle/VIS/db/apps_st/data/uddisys01.dbf /oapps/oracle/VIS/db/apps_st/data/undotbs_01.dbf /oapps/oracle/VIS/db/apps_st/data/undotbs_02.dbf /oapps/oracle/VIS/db/apps_st/data/wcrsys01.dbf 242 files. Includes: Log files Control files Files for Undo tablespaces Temp tablespaces -------------------- Total size of files: du -hs /oapps/oracle/VIS/db/apps_st/data 187G /oapps/oracle/VIS/db/apps_st/data -------------------- Log files: @loginfo Select * from v$log, v$logfile where v$log.group# = v$logfile.group# GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- FIRST_TIME GROUP# STATUS TYPE ----------------------------- ---------- ------- ------- MEMBER ---------------------------------------------------------------------------------------------------- IS_ --- 3 1 6 314572800 1 NO CURRENT 1.0132E+13 2010-NOV-21 17:43:42 3 ONLINE /oapps/oracle/VIS/db/apps_st/data/log1.dbf NO 2 1 5 314572800 1 NO INACTIVE 1.0132E+13 2010-APR-22 14:38:36 2 ONLINE /oapps/oracle/VIS/db/apps_st/data/log2.dbf NO 1 1 4 314572800 1 NO INACTIVE 1.0132E+13 2010-FEB-05 20:35:05 1 ONLINE /oapps/oracle/VIS/db/apps_st/data/log3.dbf NO -------------------- Control files: @ctlfile Select * from v$controlfile STATUS ------- NAME ---------------------------------------------------------------------------------------------------- IS_ BLOCK_SIZE FILE_SIZE_BLKS --- ---------- -------------- /oapps/oracle/VIS/db/apps_st/data/cntrl01.dbf NO 16384 1042 /oapps/oracle/VIS/db/apps_st/data/cntrl02.dbf NO 16384 1042 /oapps/oracle/VIS/db/apps_st/data/cntrl03.dbf NO 16384 1042 -------------------- Temp Tablespace: @ lttsinfo.sql select * from v$TEMPFILE FILE# CREATION_CHANGE# CREATION_TIME TS# RFILE# STATUS ENABLED ---------- ---------------- ----------------------------- ---------- ---------- ------- ---------- BYTES BLOCKS CREATE_BYTES BLOCK_SIZE ---------- ---------- ------------ ---------- NAME ---------------------------------------------------------------------------------------------------- 1 1.0132E+13 2010-JAN-28 00:14:33 75 1 ONLINE READ WRITE 2118123520 258560 1048576000 8192 /oapps/oracle/VIS/db/apps_st/data/temp1_01.dbf 2 1.0132E+13 2010-JAN-28 00:14:34 76 1 ONLINE READ WRITE 2118123520 258560 1048576000 8192 /oapps/oracle/VIS/db/apps_st/data/temp2_01.dbf ------------------------------ UNDO tablespace: select distinct ts.name from v$undostat u, v$tablespace ts where u.undotsn = ts.TS# - this script will only find UNDO tablespaces that are online, and have active undo extents NAME ------------------------------ UNDO_TBS -------------------- Which tablespaces have the most datafiles??? ltsu.sql: Select TABLESPACE_NAME , to_char ( (sum (BYTES ) / 1024 / 1024), '999,999,999.99' ) as Size_in_megs , count(*) Number_Of_Files from dba_data_files group by TABLESPACE_NAME order by 3, sum (BYTES ), 1 TABLESPACE_NAME SIZE_IN_MEGS NUMBER_OF_FILES ------------------------------ --------------- --------------- DISCO_PTM5_META 3.00 1 OCATS 3.00 1 OLTS_DEFAULT 3.00 1 OLTS_SVRMGSTORE 3.00 1 WCRSYS_TS 3.00 1 MTR 4.00 1 OPMOR 6.00 1 DSGATEWAY_TAB 7.00 1 BAM 8.00 1 OLTS_CT_STORE 8.00 1 RE 8.00 1 OWAPUB 10.00 1 B2B_LOB 12.00 1 ORABPEL 12.00 1 OLTS_ATTRSTORE 15.00 1 OLTS_BATTRSTORE 15.00 1 B2B_IDX 16.00 1 UDDISYS_TS 21.00 1 ODM_DATA 40.00 1 B2B_RT 42.00 1 RBS_MIG 43.00 1 TS_DP 50.00 1 TS_SALES_DATA 50.00 1 TS_SALES_DATA_ENGINE 50.00 1 TS_SALES_DATA_ENGINE_X 50.00 1 TS_SALES_DATA_X 50.00 1 TS_SIM 50.00 1 TS_SIM_X 50.00 1 SYNCSERVER 54.00 1 XDB 61.00 1 B2B_DT 68.00 1 CTXSYS 79.00 1 DW_AGGREGATE_IDX 100.00 1 DW_AGGREGATE_TBS 100.00 1 DW_BASE_IDX 100.00 1 DW_BASE_TBS 100.00 1 DW_DERIVED_IDX 100.00 1 DW_DERIVED_TBS 100.00 1 DW_DM_TBS 100.00 1 DW_LOOKUP_TBS 100.00 1 DW_MVLOG_TBS 100.00 1 DW_REFERENCE_IDX 100.00 1 DISCO_PTM5_CACHE 108.00 1 DW_REFERENCE_TBS 133.94 1 APPS_TS_DISCO 856.00 1 BIA_RTL 1,000.00 1 SYSAUX 1,327.00 1 APPS_TS_DISCO_OLAP 1,350.00 1 CWMLITE 23.00 2 DCM 198.00 2 IAS_META 221.00 2 HTMLDB 287.00 2 APPS_OMO 1,150.00 2 GEOR_TBS 2,136.00 2 UNDO_TBS 3,686.41 2 APPS_TS_NOLOGGING 575.00 3 APPS_TS_ARCHIVE 1,101.00 3 OLAP_BAAD 1,500.00 3 APPS_TS_SEED 3,803.00 3 APPS_TS_QUEUES 4,299.00 3 APPS_CALCLIP 4,770.00 4 DEMANTRA 5,400.00 4 DATA 7,200.00 5 APPS_TS_MEDIA 5,211.25 7 SYSTEM 19,297.00 12 APPS_TS_SUMMARY 22,369.00 15 APPS_TS_TX_IDX 32,471.00 26 APPS_TS_INTERFACE 2,404.00 27 APPS_TS_TX_DATA 62,692.25 57 69 rows selected. System tablespace is HUGE, compared to other installations I've done! 12 data files. Over 19 gigs! Tablespace APPS_TS_TX_DATA has 57 files, and is about 62 gigs in size. Wow! ------------------- How full are the tablespaces? Ltsu.sql TABLESPACE_NAME FREE_MEG USED_MEG TOTAL_ME PERCENT ------------------------------ -------- -------- -------- ------- TS_DP 50 0 50 .00 TS_SALES_DATA_ENGINE_X 50 0 50 .00 DW_REFERENCE_TBS 134 0 134 .00 DEMANTRA 5,400 0 5,400 .00 DW_REFERENCE_IDX 100 0 100 .00 BIA_RTL 1,000 0 1,000 .00 DW_LOOKUP_TBS 100 0 100 .00 DW_AGGREGATE_IDX 100 0 100 .00 DW_MVLOG_TBS 100 0 100 .00 OLAP_BAAD 1,500 0 1,500 .00 TS_SIM_X 50 0 50 .00 OWAPUB 10 0 10 .00 TS_SALES_DATA_X 50 0 50 .00 TS_SALES_DATA_ENGINE 50 0 50 .00 DW_BASE_IDX 100 0 100 .00 DW_DERIVED_TBS 100 0 100 .00 DW_DM_TBS 100 0 100 .00 TS_SALES_DATA 50 0 50 .00 DW_DERIVED_IDX 100 0 100 .00 TS_SIM 50 0 50 .00 DW_AGGREGATE_TBS 100 0 100 .00 DW_BASE_TBS 100 0 100 .00 DATA 7,193 7 7,200 .10 APPS_TS_DISCO_OLAP 1,334 16 1,350 1.19 OLTS_BATTRSTORE 15 0 15 1.67 OLTS_ATTRSTORE 15 0 15 1.67 UNDO_TBS 3,164 522 3,686 14.16 APPS_OMO 853 297 1,150 25.82 MTR 2 2 4 40.63 OLTS_DEFAULT 2 2 3 50.00 WCRSYS_TS 1 2 3 54.17 OCATS 1 2 3 56.25 APPS_CALCLIP 2,040 2,730 4,770 57.24 CTXSYS 30 49 79 62.34 DISCO_PTM5_META 1 2 3 62.50 APPS_TS_NOLOGGING 200 375 575 65.17 OLTS_SVRMGSTORE 1 2 3 70.83 ODM_DATA 10 30 40 75.94 OPMOR 1 5 6 76.04 DSGATEWAY_TAB 2 5 7 77.68 BAM 2 6 8 78.13 RE 2 7 8 81.25 APPS_TS_ARCHIVE 202 899 1,101 81.66 SYSAUX 205 1,122 1,327 84.52 APPS_TS_QUEUES 613 3,686 4,299 85.75 APPS_TS_TX_IDX 3,918 28,553 32,471 87.93 B2B_LOB 1 11 12 88.54 APPS_TS_SEED 433 3,370 3,803 88.62 APPS_TS_TX_DATA 7,061 55,631 62,692 88.74 B2B_IDX 2 14 16 89.06 OLTS_CT_STORE 1 7 8 89.06 B2B_DT 7 61 68 89.43 XDB 6 55 61 89.75 APPS_TS_DISCO 86 770 856 89.92 GEOR_TBS 215 1,922 2,136 89.96 DCM 20 178 198 89.96 HTMLDB 29 258 287 89.98 APPS_TS_SUMMARY 2,239 20,130 22,369 89.99 SYSTEM 1,927 17,370 19,297 90.01 IAS_META 22 199 221 90.05 APPS_TS_INTERFACE 236 2,168 2,404 90.18 SYNCSERVER 5 49 54 90.21 DISCO_PTM5_CACHE 11 97 108 90.22 B2B_RT 4 38 42 90.33 APPS_TS_MEDIA 497 4,715 5,211 90.47 RBS_MIG 4 39 43 90.70 UDDISYS_TS 2 19 21 90.77 CWMLITE 2 21 23 91.85 ORABPEL 1 11 12 95.31 69 rows selected. Elapsed: 00:24:10.53 (Does not include temp tablespaces.)