Routine Management of Oracle tablespace and data files

1. Create a tablespace
1. Create a common tablespace to specify the initial size, which is automatically expanded. Maximum Size:
(The maximum size of a single smallfile data file allowed by Oracle is 4194302 * blocksize, and the maximum size of a single bigfile data file is (32-128) TB)
(The Standard 8 K data block and smallfile tablespace are created by default)
Create tablespace t1 datafile '/u01/oracle/product/oradata/orcl/t1.dbf' size 10 m autoextend on next 5 m maxsize 100 m;
2. Create a tablespace with a non-standard block size:
Alter system set db_16k_cache_size = 100 m scope = spfile;
Shutdown immediate;
Startup;
Create tablespace t2 datafile '/u01/oracle/product/oradata/orcl/t2.dbf' size 10 m blocksize 16 k;
3. Create a large file tablespace (only one data file is allowed for a large file tablespace ):
Create bigfile tablespace t3 datafile '/u01/oracle/product/oradata/orcl/t3.dbf' size 10 m;

2. Modify the tablespace (if you modify the temporary tablespace, change the datafile in the statement to tempfile ):
1. Add a data file to the tablespace:
Alter tablespace t1 add datafile '/u01/oracle/product/oradata/orcl/t11.dbf' size 1 m;
2. delete data files in the tablespace:
Alter tablespace t1 drop datafile '/u01/oracle/product/oradata/orcl/t11.dbf ';
3. Modify the size of the specified data file
Alter database datafile '/u01/oracle/product/oradata/orcl/t1.dbf' resize 1 m;

3. Offline and online tablespaces and data files
1. offline and online tablespace
Alter tablespace t1 offline;
Alter tablespace t1 online;
2. offline and online data files (archive mode is required for offline and online data files. After offline data files are used, you must restore the media of the data files; otherwise, an error is reported)
Shutdown immediate;
Startup mount;
Alter database archivelog;
Alter database open;
Alter database datafile '/u01/oracle/product/oradata/orcl/t1.dbf' offline;
Recover datafile 5;
Alter database datafile '/u01/oracle/product/oradata/orcl/t1.dbf' online;

4. Tablespace name change
Alter tablespace t1 rename to t4;

5. tablespace Deletion
1. Normally Delete the tablespace without deleting the data file:
Drop tablespace t4;
2. Delete tablespaces and delete all related data files;
Drop tablespace t2 including contents and datafiles;

Sat. Create a tablespace using an existing but unused data file:
SQL> create tablespace t1 datafile '/u01/oracle/product/oradata/orcl/t1.dbf' size 10 m;
Create tablespace t1 datafile '/u01/oracle/product/oradata/orcl/t1.dbf' size 10 m
*
ERROR at line 1:
ORA-01119: error in creating database file
'/U01/oracle/product/oradata/orcl/t1.dbf'
ORA-27038: created file already exists
Additional information: 1
SQL> create tablespace t1 datafile '/u01/oracle/product/oradata/orcl/t1.dbf' reuse;

Tablespace created.