July 03, 2007
Tendean, Jakarta Selatan
Database Name : db1
Tablespace name : ts
Datafiles : D:\oracle\product\10.2.0\oradata\db1\ts01.dbf
Rename Tablespace :
SQL> ALTER TABLESPACE ts RENAME TO tspace;
Lihat Default Tablespace :
SQL> select property_name, property_value from database_properties
where lower(property_name) like ‘%PERMANENT%’;
Alter Default Tablespace :
SQL> alter database default tablespace tablespace_name;
Lihat Temporary Tablespace :
SQL> select property_name, property_value from database_properties
where property_name like ‘%TEMP%’;
Alter Temporary Tablespace :
SQL> alter database default temporary tablespace tablespace_name
Rename Datafiles :
1. offline kan tablespace secara normal : SQL> ALTER TABLESPACE tspace offline normal;
2. copy datafiles tersebut di tempat yang diinginkan, dan rename menjadi tspace01.dbf
3. rename datafile nya
SQL> ALTER TABLESPACE tspace RENAME DATAFILE
‘D:\oracle\product\10.2.0\oradata\db1\ts01.dbf’ TO ‘D:\oracle\product\10.2.0\oradata\db1\tspace01.dbf’
4. alter tablespace tspace online;
5. hapus datafile ‘D:\oracle\product\10.2.0\oradata\db1\ts01.dbf’ nya lewat OS level
Lihat datafile :
SQL> select name from v$datafile;
Lihat Datafiles dan tablespacenya :
SQL> select t.name, d.name from v$tablespace t, v$datafile d where t.ts#=d.ts#;
Resize datafile Pada Smallfile Tablespace :
SQL> alter database datafile ‘D:\oracle\product\10.2.0\oradata\db1\tspace01.dbf’ resize 50m;
Resize datafile Pada Bigfile Tablespace :
SQL> alter tablespace bigfile_tablespace_name resize 100m;
Add Datafile :
SQL> alter tablespace tspace add datafile ‘D:\oracle\product\10.2.0\oradata\db1\tspace02.dbf’ size 50m;
SMALLFILE TABLESPACE :
- Ukuran datafiles paling gede 32G;
BIGFILE TABLESPACE :
- datafile hanya satu
- ukuran bisa 8TB hingga 128 TB
Lihat Konfigurasi Tablespace :
SQL> select property_name, property_value from database_properties where property_name like ‘%TBS%’;
Ubah Ke Bigfile Tablespace :
SQL> alter database set default bigfile tablespace;
Ubah Ke Smallfile Tablespace :
SQL> alter database set default smallfile tablespace;
regards
abip
