Oracle 10g [ Tablespace Reports ]

Tablespace reports

basic knowledge, tips buat lihat semua tablespace didatabase, datafile, dan status memory berikut query nya..

SELECT dd.tablespace_name tablespace_name, dd.file_name file_name, dd.bytes/1024 TABLESPACE_KB, SUM(fs.bytes)/1024 KBYTES_FREE, MAX(fs.bytes)/1024 NEXT_FREE
FROM sys.dba_free_space fs, sys.dba_data_files dd
WHERE dd.tablespace_name = fs.tablespace_name GROUP BY dd.tablespace_name, dd.file_name, dd.bytes/1024 ORDER BY dd.tablespace_name, dd.file_name;

…dan buat lihat free bytes…

SELECT df.tablespace_name, SUM(df.bytes) TOTAL_SPACE, SUM(fs.bytes) FREE_SPACE, ROUND(((NVL(SUM(fs.bytes),0)/SUM(df.bytes))*100),2) PCT_FREE
FROM dba_free_space fs, dba_data_files df
WHERE df.tablespace_name = fs.tablespace_name (+)
GROUP BY df.tablespace_name
ORDER BY df.tablespace_name;

Extending a tablespace

Jika kita mau naikin maxsize dari datafile :

alter database datafile ‘/u01/oracle/oradata/scdb/questaims.dbf’ maxsize 2000M autoextend on;

atau tambah datafile:

alter tablespace add datafile ‘/u01/oracle/oradata/scdb/questaims.dbf’ size 100M maxsize 2000M autoextend on;

atau klo mau naikin size dari datafile yang ada:

alter database datafile ‘/u01/oracle/oradata/scdb/questaims.dbf’ resize 50M;