sql> Create tablespace ts_users datafile
'/u01/app/oracle/oradata/enmo1/ts01.dbf ' size 10m autoextend on maxsize 20m,
'/u01/app/oracle/oradata/enmo1/ts01.bak.dbf ' size 10m autoextend on maxsize 20m;
Tablespace created.
Sql> select Tablespace_name,status from Dba_tablespaces;
tablespace_name STATUS
-------------------- -------------------
SYSTEM online
Sysaux on Line
UNDOTBS1 online
TEMP online
USERS &N Bsp online
EXAMPLE ONLINE
ts_users &NBSP ; ONLINE
7 rows selected.
Add data files
Sql> alter tablespace ts_users add datafile '/u01/app/oracle/oradata/enmo1/ts01.bak1.dbf ' size 10M;
Tablespace altered.
Sql> Select Tablespace_name,file_id,file_name from dba_data_files where tablespace_name = ' ts_users ';
Tablespace_name file_id file_name
----------------------- ---------- ----------------
Ts_users 6/u01/app/oracle/oradata/enmo1/ts01.dbf
Ts_users 7/u01/app/oracle/oradata/enmo1/ts01.bak.dbf
Ts_users 8/u01/app/oracle/oradata/enmo1/ts01.bak1.dbf
Modify data File Size
sql> ALTER DATABASE datafile 8 resize 20M;
Database altered.
Sql> Select file_id,bytes/1024/1024 | | ' M ' from dba_data_files where tablespace_name = ' ts_users ';
file_id bytes/1024/1024| | ' M
---------- -----------------------------------------
6 10M
7 10M
8 20M
Sql> Select File_id,file_name,autoextensible,maxbytes from dba_data_files where tablespace_name = ' TS_USERS ';
file_id file_name AUT MaxBytes
---------- ------------------------------------------------------- --------------- -------------
6/U01/APP/ORACLE/ORADATA/ENMO1/TS01.DBF YES 20971520
7/U01/APP/ORACLE/ORADATA/ENMO1/TS01.BAK.DBF YES 20971520
8/U01/APP/ORACLE/ORADATA/ENMO1/TS01.BAK1.DBF NO 0
sql> ALTER DATABASE DataFile 8 autoextend on next 5M MaxSize Unlimited;
Database altered.
Sql> Select File_id,file_name,autoextensible,maxbytes from dba_data_files where tablespace_name = ' TS_USERS ';
file_id file_name AUT MaxBytes
---------- ------------------------------ ------------------
6/u01/app/oracle/oradata/e YES 20971520
Nmo1/ts01.dbf
7/u01/app/oracle/oradata/e YES 20971520
Nmo1/ts01.bak.dbf
8/u01/app/oracle/oradata/e YES 3.4360E+10
Nmo1/ts01.bak1.dbf
Delete Table space
Sql> drop tablespace ts_users including contents and datafiles;
Tablespace dropped.
Sql> Select Tablespace_name,file_id,file_name
2 from dba_data_files where tablespace_name = ' ts_users ';
No rows selected
"ORACLE" DML operations on Permanent tablespace (11g)