Continuity of tablespace and data file numbers in Oracle

The tablespace numbers and data file numbers in Oracle are continuous and do not stop.. If the tablespace in ts $ has a break number,

Oracle checks the data dictionary and control file at startup. When inconsistency is found, the database cannot be started normally.

When a tablespace is deleted, the table ts $ does not delete the relevant tablespace records. Set the tablespace status to 3, that is, the INVALID status.
If you create a tablespace with the same name again, oracle changes the status of the tablespace with the same name in Table TS $ from 3 to 1 and ONLINE. This
To ensure the continuity of tablespace numbers.

In ORACLE 10 Gb, SQL. bsq records the meaning of the online $ column of ts $ and the status $ column of file $:
Ts $
Online $/* 1 = ONLINE, 2 = OFFLINE, 3 = INVALID */

File $
Status $/* 1 = INVALID, 2 = AVAILABLE */

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as SYS

The UNDOTBS2, LIANG, and Liang tablespaces are deleted, but the related records are not deleted in ts $. Set the ONLINE $ status to 3 (INVALID)

SQL> SELECT ts #, name, online $ FROM ts $;
TS # name online $
---------------------
0 SYSTEM 1
1 SYSAUX 1
2 UNDOTBS1 1
3 TEMP 1
4 USERS 1
5 UNDOTBS2 3
6 EXAMPLE 1
7 TBS_REPORT 1
 8 LIANG 3
9 Liang 3
10 rows selected

-- The same data file number will not be deleted, and the deleted data file number will be set to 1 (INVALID status ).

SQL> SELECT file #, status $ FROM file $;
FILE # STATUS $
-----------
1 2
2 2
3 2
4 2
5 2
6 2
7 1
7 rows selected

SQL> SELECT d. file_id, d. tablespace_name, d. file_name FROM dba_data_files d
2 order by d. file_id;

FILE_ID TABLESPACE_NAME FILE_NAME
---------------------------------------------------------------------------
1 system d: \ ORACLE \ LIANGWEI \ ORADATA \ LIANGWEI \ SYSTEM01.DBF
2 sysaux d: \ ORACLE \ LIANGWEI \ ORADATA \ LIANGWEI \ SYSAUX01.DBF
3 UNDOTBS1 D: \ ORACLE \ LIANGWEI \ ORADATA \ LIANGWEI \ UNDOTBS01.DBF
4 users d: \ ORACLE \ LIANGWEI \ ORADATA \ LIANGWEI \ USERS01.DBF
5 example d: \ ORACLE \ LIANGWEI \ ORADATA \ LIANGWEI \ EXAMPLE01.DBF
6 TBS_REPORT D: \ ORACLE \ LIANGWEI \ ORADATA \ LIANGWEI \ TBS_REPORT1.DBF
6 rows selected

SQL> CREATE tablespace test DATAFILE 'd: \ ORACLE \ TEST02.DBF 'size 10 M;
Tablespace created

When you recreate a data file, if there is no data file in the INVALID state, oracle will assign a new data file number.
If there is a data file number in the status of 1 (INVALID,Whether or not the newly created data file is the same as the deleted data file.
Oracle will reuse the data file number set to 1 (INVALID). To ensure the continuity of data file numbers.

After a new data file is created, it is found that the number of the data file No. 7 is set to 2 (AVAILABLE)

SQL> SELECT file #, status $ FROM file $;
FILE # STATUS $
------------
1 2
2 2
3 2
4 2
5 2
6 2
7 2

7 rows selected

SQL> SELECT d. file_id, d. tablespace_name, d. file_name FROM dba_data_files d order by d. file_id;

FILE_ID TABLESPACE_NAME FILE_NAME
-----------------------------------------------------------------------------
1 system d: \ ORACLE \ LIANGWEI \ ORADATA \ LIANGWEI \ SYSTEM01.DBF
2 sysaux d: \ ORACLE \ LIANGWEI \ ORADATA \ LIANGWEI \ SYSAUX01.DBF
3 UNDOTBS1 D: \ ORACLE \ LIANGWEI \ ORADATA \ LIANGWEI \ UNDOTBS01.DBF
4 users d: \ ORACLE \ LIANGWEI \ ORADATA \ LIANGWEI \ USERS01.DBF
5 example d: \ ORACLE \ LIANGWEI \ ORADATA \ LIANGWEI \ EXAMPLE01.DBF
6 TBS_REPORT D: \ ORACLE \ LIANGWEI \ ORADATA \ LIANGWEI \ TBS_REPORT1.DBF
7 test d: \ ORACLE \ TEST02.DBF
7 rows selected

When creating a tablespace, if Table store $ does not have a tablespace with the same name and the status is 3. Oracle will reassign a sequential tablespace number.

SQL> SELECT ts #, name, online $ FROM ts $;

TS # name online $
--------------------
0 SYSTEM 1
1 SYSAUX 1
2 UNDOTBS1 1
3 TEMP 1
4 USERS 1
5 UNDOTBS2 3
6 EXAMPLE 1
7 TBS_REPORT 1
8 LIANG 3
9 Liang 3
10 TEST 1
11 rows selected

If a tablespace with the same name is created, oracle uses the tablespace in the original state of 3 and sets the state to 1.
SQL> CREATE tablespace LIANG DATAFILE 'd: \ ORACLE \ test01.dbf' SIZE 10 M;
Tablespace created

After the tablespace LIANG of the same name is created, the status of tablespace No. 8 changes from 3 to 1.
SQL> SELECT ts #, name, online $ FROM ts $;
TS # name online $
-----------------------
0 SYSTEM 1
1 SYSAUX 1
2 UNDOTBS1 1
3 TEMP 1
4 USERS 1
5 UNDOTBS2 3
6 EXAMPLE 1
7 TBS_REPORT 1
8 LIANG 1
9 Liang 3
10 TEST 1
11 rows selected

The new tablespace cannot have the same name as the existing tablespace in the database.

SQL> CREATE tablespace TEST DATAFILE 'd: \ ORACLE \ TEST02.DBF 'size 10 M;
CREATE tablespace test datafile 'd: \ ORACLE \ TEST02.DBF 'size 10 M
*
Row 3 has an error:
ORA-01543: The tablespace 'test' already exists.

The newly created data file cannot be the same as the existing data file in the database.
SQL> CREATE tablespace TEST2 DATAFILE 'd: \ ORACLE \ TEST02.DBF 'SIZE 10 M;
CREATE tablespace TEST2 DATAFILE 'd: \ ORACLE \ TEST02.DBF 'size 10 M
*
Row 3 has an error:
ORA-01537: Unable to add the file 'd: \ ORACLE \ TEST02.DBF '-the file is already part of the database