04 Nisan 2012
To shrink datafiles as much as possible
select 'alter database datafile '''||file_name||''' resize ' || ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) || 'm;' cmd from dba_data_files a, ( select file_id, max(block_id+blocks-1) hwm from dba_extents group by file_id ) b where a.TABLESPACE_NAME='TEST_OIM' and a.file_id = b.file_id(+) and ceil( blocks*&&blksize/1024/1024) - ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0;
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$$';
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$$';
Kaydol:
Kayıtlar (Atom)
