04 Nisan 2012

Oracle DB data size monitoring - tips for troubleshooting

To check Datafile size of a given tablespace;
select NAME,BYTES/1024/1024/1024 from v$datafile;
SELECT FILE_NAME,BYTES/1024/1024/1024 FROM DBA_DATA_FILES where TABLESPACE_NAME = 'USERS';
select sum(bytes)/1024/1024/1024 "Gig" from dba_data_files where TABLESPACE_NAME = 'TEST_OIM';
select * from DBA_FREE_SPACE where TABLESPACE_NAME = 'TEST_OIM';

Alter tablespace datafiles
ALTER DATABASE DATAFILE '+DATA/oimtest/datafile/test_oim.278.779629303' RESIZE 5000m;

To check size availability in Storage Disks(ASM)
select GROUP_NUMBER,NAME,TOTAL_MB,FREE_MB,HOT_USED_MB,COLD_USED_MB,USABLE_FILE_MB from v$asm_diskgroup;

To check actual sizes of objects in a schema:
select SEGMENT_NAME,SEGMENT_TYPE,BYTES/1024/1024 from dba_Segments where TABLESPACE_NAME = 'TEST_OIM' and BYTES/1024/1024> 1000 order by bytes desc;
select sum(BYTES/1024/1024) from dba_Segments where TABLESPACE_NAME = 'TEST_OIM';
select SEGMENT_NAME,SEGMENT_TYPE,BYTES/1024 from dba_Segments where TABLESPACE_NAME = 'TEST_OIM' and BYTES/1024/1024> 1000 order by bytes desc;

To check ownerships of LOB objects:
SELECT owner, table_name, column_name FROM dba_lobs WHERE segment_name = 'SYS_LOB0000095949C00007$$';

Hiç yorum yok: