分享程序员开发的那些事...
更新时间：2022-08-19 22:13:35
[20120915]关于表空间号.txt
前几天,开发问一个问题,就是建立表空间时表空间号有什么规律,我说:"我自己没有注意,应该是顺序增加吧"
他给看了测试环境,确实有一些不是规律增加,处于好奇研究看看.
1.测试环境:
SQL> select * from v$version ;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> select file#,rfile#,name,ts# from v$datafile ;
FILE# RFILE# NAME TS#
---------- ---------- -------------------------------------------------- ----------
1 1 /u01/app/oracle11g/oradata/test/system01.dbf 0
2 2 /u01/app/oracle11g/oradata/test/sysaux01.dbf 1
3 3 /u01/app/oracle11g/oradata/test/undotbs01.dbf 2
4 4 /u01/app/oracle11g/oradata/test/users01.dbf 4
5 5 /u01/app/oracle11g/oradata/test/example01.dbf 6
6 6 /u01/app/oracle11g/oradata/test/rman01.dbf 7
7 7 /u01/app/oracle11g/oradata/test/tools01.dbf 8
8 8 /u01/app/oracle11g/oradata/test/test01.dbf 9
9 9 /u01/app/oracle11g/oradata/test/test101.dbf 12
10 10 /u01/app/oracle11g/oradata/test/fratable01.dbf 13
10 rows selected.
SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
EXAMPLE
RMAN
TOOLS
TEST
TEST1
FRATABLE
11 rows selected.
2.建立1个新的表空间:
SQL> create tablespace testa datafile '/data/testtest/testa01.dbf' size 88k;
Tablespace created.
SQL> select file#,rfile#,name,ts# from v$datafile ;
FILE# RFILE# NAME TS#
---------- ---------- -------------------------------------------------- ----------
1 1 /u01/app/oracle11g/oradata/test/system01.dbf 0
2 2 /u01/app/oracle11g/oradata/test/sysaux01.dbf 1
3 3 /u01/app/oracle11g/oradata/test/undotbs01.dbf 2
4 4 /u01/app/oracle11g/oradata/test/users01.dbf 4
5 5 /u01/app/oracle11g/oradata/test/example01.dbf 6
6 6 /u01/app/oracle11g/oradata/test/rman01.dbf 7
7 7 /u01/app/oracle11g/oradata/test/tools01.dbf 8
8 8 /u01/app/oracle11g/oradata/test/test01.dbf 9
9 9 /u01/app/oracle11g/oradata/test/test101.dbf 12
10 10 /u01/app/oracle11g/oradata/test/fratable01.dbf 13
11 11 /data/testtest/testa01.dbf 21
11 rows selected.
--对应的TS# 竟然等于21.
3.做一个跟踪很容易发现,以前建立的表空间一直存在sys.ts$中,即使删除了表空间.
SQL> select ts#,name from sys.ts$;
TS# NAME
---------- --------------------------------------------------
0 SYSTEM
1 SYSAUX
2 UNDOTBS1
3 TEMP
4 USERS
5 UNDOTBS2
6 EXAMPLE
7 RMAN
8 TOOLS
9 TEST
10 _$deleted$10$0
11 TEST_8K
12 TEST1
13 FRATABLE
14 FY_REC_DATA
15 FY_RST_DATA
16 FY_REC_DATA1
17 TEST_R
18 TEST_Y
19 TESTTTS1
20 TEST_AAAZ
21 TESTA
22 rows selected.
--按照这个情况,很明显如果我建立表空间test_R TS#号一定等于17.测试看看.
SQL> create tablespace test_r datafile '/data/testtest/test_r01.dbf' size 88k;
Tablespace created.
SQL> select file#,rfile#,name,ts# from v$datafile ;
FILE# RFILE# NAME TS#
---------- ---------- -------------------------------------------------- ----------
1 1 /u01/app/oracle11g/oradata/test/system01.dbf 0
2 2 /u01/app/oracle11g/oradata/test/sysaux01.dbf 1
3 3 /u01/app/oracle11g/oradata/test/undotbs01.dbf 2
4 4 /u01/app/oracle11g/oradata/test/users01.dbf 4
5 5 /u01/app/oracle11g/oradata/test/example01.dbf 6
6 6 /u01/app/oracle11g/oradata/test/rman01.dbf 7
7 7 /u01/app/oracle11g/oradata/test/tools01.dbf 8
8 8 /u01/app/oracle11g/oradata/test/test01.dbf 9
9 9 /u01/app/oracle11g/oradata/test/test101.dbf 12
10 10 /u01/app/oracle11g/oradata/test/fratable01.dbf 13
11 11 /data/testtest/testa01.dbf 21
12 12 /data/testtest/test_r01.dbf 17
12 rows selected.
--确实是17.
4.改名表空间test_r为test_y看看.
SQL> alter tablespace test_r rename to test_y;
SQL> select file#,rfile#,name,ts# from v$datafile ;
FILE# RFILE# NAME TS#
---------- ---------- -------------------------------------------------- ----------
1 1 /u01/app/oracle11g/oradata/test/system01.dbf 0
2 2 /u01/app/oracle11g/oradata/test/sysaux01.dbf 1
3 3 /u01/app/oracle11g/oradata/test/undotbs01.dbf 2
4 4 /u01/app/oracle11g/oradata/test/users01.dbf 4
5 5 /u01/app/oracle11g/oradata/test/example01.dbf 6
6 6 /u01/app/oracle11g/oradata/test/rman01.dbf 7
7 7 /u01/app/oracle11g/oradata/test/tools01.dbf 8
8 8 /u01/app/oracle11g/oradata/test/test01.dbf 9
9 9 /u01/app/oracle11g/oradata/test/test101.dbf 12
10 10 /u01/app/oracle11g/oradata/test/fratable01.dbf 13
11 11 /data/testtest/testa01.dbf 21
12 12 /data/testtest/test_r01.dbf 17
12 rows selected.
--没有变化,依旧是17.
SQL> select ts#,name from sys.ts$;
TS# NAME
---------- --------------------------------------------------
0 SYSTEM
1 SYSAUX
2 UNDOTBS1
3 TEMP
4 USERS
5 UNDOTBS2
6 EXAMPLE
7 RMAN
8 TOOLS
9 TEST
10 _$deleted$10$0
11 TEST_8K
12 TEST1
13 FRATABLE
14 FY_REC_DATA
15 FY_RST_DATA
16 FY_REC_DATA1
17 TEST_Y
18 _$deleted$18$0
19 TESTTTS1
20 TEST_AAAZ
21 TESTA
22 rows selected.
--原来的ts#=18,name='TEST_Y',变成了'_$deleted$18$0'.
5.建立多个表空间看看:
SQL> show parameter db_files
NAME TYPE VALUE
--------- ----------- ------
db_files integer 2000
begin
for i in 1..1025 loop
execute immediate 'create tablespace testb'||i||' datafile ''/data/testtest/testb' || i ||''' size 88k';
end loop;
end;
/
PL/SQL procedure successfully completed.
--再删除它们.
SQL> create tablespace test_r datafile '/data/testtest/testr01.dbf' size 88k;
Tablespace created.
SQL> select file#,rfile#,name,ts# from v$datafile ;
FILE# RFILE# NAME TS#
---------- ---------- -------------------------------------------------- ----------
1 1 /u01/app/oracle11g/oradata/test/system01.dbf 0
2 2 /u01/app/oracle11g/oradata/test/sysaux01.dbf 1
3 3 /u01/app/oracle11g/oradata/test/undotbs01.dbf 2
4 4 /u01/app/oracle11g/oradata/test/users01.dbf 4
5 5 /u01/app/oracle11g/oradata/test/example01.dbf 6
6 6 /u01/app/oracle11g/oradata/test/rman01.dbf 7
7 7 /u01/app/oracle11g/oradata/test/tools01.dbf 8
8 8 /u01/app/oracle11g/oradata/test/test01.dbf 9
9 9 /u01/app/oracle11g/oradata/test/test101.dbf 12
10 10 /u01/app/oracle11g/oradata/test/fratable01.dbf 13
11 11 /data/testtest/testa01.dbf 21
12 12 /data/testtest/test_r01.dbf 17
13 13 /data/testtest/testr01.dbf 1047
13 rows selected.