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$$';

DBA_SEGMENTS definition

http://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_4097.htm

DBA_TABLES definition

https://netfiles.uiuc.edu/jstrode/www/oradd/DBA_TABLES.html

Oracle Database Monitoring Tips

http://www.rocket99.com/techref/oracle8406.html