"ORACLE" DML operations on Permanent tablespace (11g)

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)