SQL> select distinct SEGMENT_TYPE from dba_segments;
Physically, tablespace consists of one or more datafiles. Information about the tablespace is in view v $ tablespace, dba_tablespaces, dba_data_files, dba_temp_files, etc..
Use the following command to see the types of tablespace
Use the following command to see the types of tablespace
SQL> select distinct CONTENTS from dba_tablespaces;
Based on the results of the query, the following three types of tablespaces:
- UNDO. To save the rollback (undo) segment
- TEMPORARY. To save the temporary segment
- PERMANENT. To save the segment in addition to the two above (eg, tables, indexes)
Examples made with the Undo Tablespace undotbs2 name, datafile / oradata/oracle/ts_bak/undotbs201.dbf, file size 10M. Do not forget to add an undo option after create.
SQL> create undo tablespace undotbs2 datafile '/oradata/oracle/ts_bak /undotbs201.dbf' size 10m; To add (increase size / space) can be done by increasing the size of the datafile or add datafile
SQL> alter database datafile '/oradata/oracle/ts_bak/undotbs201.dbf' resize 20m;SQL> alter tablespace undotbs2 add datafile '/oradata/oracle/ts_bak /undotbs202.dbf' size 10m; To see the size of the datafile and tablespace UNDOTBS2
SQL> select file_name,bytes from dba_data_files where tablespace_name='UNDOTBS2'; To see the free space of each datafile of the tablespace UNDOTBS2
SQL> select a.name, sum(b.bytes) from v$datafile a,
dba_free_space b where a.file#=b.file_id and
b.TABLESPACE_NAME='UNDOTBS2' group by a.name; Untuk melihat undo tablespace yang aktif saat ini gunakan
SQL> show parameter undo_tablespace Untuk mengubah undo_tablespace ke tablespace yang baru saja kita buat
SQL> alter system set undo_tablespace=UNDOTBS2; TEMPORARY TABLESPACE
SQL> create temporary tablespace temp2 tempfile '/oradata/oracle/ts/temp21.dbf' size 10m; To add (increase size / space) to do with the size of the tempfile elevates or add tempfile
SQL> alter database tempfile '/oradata/oracle/ts/temp21.dbf' resize 20m; SQL> alter tablespace temp2 add tempfile '/oradata/oracle/ts/temp22.dbf' size 10m;To see the temp files (files belonging TEMPORARY tablespace) and size. For example, suppose name is TEMP TEMPORARY tablespace:
SQL> select file_name,bytes from dba_temp_files where tablespace_name='TEMP';To view free spacenya
SQL> select a.name, sum(b.BYTES_FREE) from v$tempfile a,
V$TEMP_SPACE_HEADER b where a.file#=b.file_id and
b.TABLESPACE_NAME='TEMP' group by a.name;To view the temporary tablespace is used as the database is DEFAULT
SQL> select PROPERTY_VALUE from database_properties
where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE'; To change the default temporary tablespace to a tablespace we just created
SQL> alter database default temporary tablespace temp2; PERMANENT TABLESPACE
Example of making permanent tablespace with the name DATA datafile / oradata/oracle/ts_bak/data01.dbf, file size 10M.
SQL> create tablespace DATA datafile '/oradata/oracle/ts_bak/data01.dbf' size 10m;To add (increase size / space) to do with the size of the datafile elevates or add datafiles. How exactly the same as the UNDO tablespace
SQL> alter database datafile '/oradata/oracle/ts_bak/data01.dbf' resize 20m; SQL> alter tablespace DATA add datafile '/oradata/oracle/ts_bak/data02.dbf' size 10m;To view the datafile, size, and free size of PERMANENT tablespace; way as to UNDO tablespace, which use view dba_data_files, v $ datafile, and dba_free_space.
To view the permanent tablespace used as DEFAULT in the database is
SQL> select PROPERTY_VALUE from database_properties where PROPERTY_NAME='DEFAULT_PERMANENT_TABLESPACE'; To change the default permanent tablespace to a tablespace we just created
SQL> alter database default tablespace data; REDUCING THE SIZE OF TABLESPACE
Done by reducing the size of datafilenya. The command to reduce the size is the same as the command to add size, the point is to change the size (RESIZE). Do not forget, to use the temporary tablespace tempfile; for PERMANENT UNDO tablespace and the same, use the datafile.
SQL> alter database tempfile '/oradata/oracle/ts/temp21.dbf' resize 20m;
SQL> alter database datafile '/oradata/oracle/ts/undotbs1.dbf' resize 20m; important notes
Reduced size (resize) can not be done on the block below the high water mark. High water mark is the highest ever position of block used for extents. Sometime later I talk about this high water mark. Execution error when resizing will be done under the High water mark:
ORA-03297: file contains used data beyond requested RESIZE valueIts best practice, if for example datafile size 4G, and we want to lower the size of her, do it gradually (derived 100M - 100M) to find the size (high water mark) as appropriate.
Done by deleting temp files
For security reasons, the datafile can not be deleted. Remember, referred datafile is' files and UNDO tablespace PERMANENT.
SQL> alter database
datafile '/oradata/oracle/ts/test02.dbf' drop;
ERROR at line 1:
ORA-01916: keyword ONLINE, OFFLINE, RESIZE, AUTOEXTEND or END/DROP expected While the temp files get deleted (file belongs to tablespace TEMPORARY) because this file does not contain data. With the record, at least keep 1 tempfile.
SQL> alter database tempfile '/oradata/oracle/ts/temp02.dbf2' drop;Reference : http://rohmad.net/2008/06/04/administrasi-tablespace/
