2 .TABLESPACE MANAGEMENT
1. Create a NEW tablespace:
Create tablespace DATA datafile '/u01/dbaclass/oradata/data01.dbf' size 5G autoextend on next 500M;
Here, 500M of extent will be added to the datafile automatically, when the space is required.
2.For creating tablespace on ASM diskgroup:
- With OMF file system:
Create tablespace DATA datafile '+DATAG' size 5G autoextend on next 500M;
--- Else we can give the complete path also:
Create tablespace DATA datafile '+DATAG/oradata/datafile/data01.dbf' size 5G autoextend on next 500M;
3. Adding a datafile:
alter tablespace DATA add datafile '/u01/dbaclass/oradata/data02.dbf' size 2G;
4. Resize a datafile: ( To increase or decrease the datafile)
alter database datafile '/u01/dbaclass/oradata/data02.dbf' resize 3G;
5. Create a big tablespace;
CREATE BIGFILE TABLESPACE BIGTS datafile '/u01/dbaclass/oradata/bigts01.dbf' size 100G autoextend on NEXT 1G;
6.Change default tablespace:
Every database will have one default tablespace. If any user is created without mentioning any tablespace_name, then that schema objects will be created under default tablespace.- Get the current default tablespace:
SELECT PROPERTY_VALUE
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME = 'DEFAULT_PERMANENT_TABLESPACE';
ALTER DATABASE DEFAULT TABLESPACE DATATBS;
7 . Rename a tablespace:
set pagesize 200
set lines 200
col file_name for a57
SQL❯ select file_id,file_name,tablespace_name from dba_data_files where file_id=37;
FILE_ID FILE_NAME TABLESPACE_NAME
---------- -------------------------------------------------------- ------------------------------
37 /home/oracle/app/oracle/oradata/cdb1/testin1.dbf TESTING
--- Rename the tablespace_name from TESTING to PRODUCING;
SQL❯ alter tablespace TESTING rename to PRODUCING;
Tablespace altered.
SQL❯ select file_id,file_name,tablespace_name from dba_data_files where file_id=37;
FILE_ID FILE_NAME TABLESPACE_NAME
---------- -------------------------------------------------------- ------------------------------
37 /home/oracle/app/oracle/oradata/cdb1/testin1.dbf PRODUCING
NOTE: SYSTEM and SYSAUX tablespace cannot be renamed.
8. Drop a tablespace:
-- Drop a tablespace without removing the physical database files.
SQL❯ drop tablespace TESTING;
Tablespace dropped.
SQL❯ select file_name from dba_data_files where tablespace_name='TESTING';
no rows selected
-- Drop tablespace including the physical datafiles.
SQL❯ drop tablespace TESTING including contents and datafiles;
Tablespace dropped.
9. Make tablespace Read only:
alter tablespace DATA2 READ ONLY;alter tablespace DATA2 read write;
10. Take tablespace offline:
DBAs usually make the tablespace offline, while restoring or recovering any underlying corrupt datafile.
alter tablespace DATA2 READ ONLY;
alter tablespace DATA2 read write;
