[20120915]关于表空间号.txt-阿里云开发者社区

开发者社区> 数据库> 正文
登录阅读全文

[20120915]关于表空间号.txt

简介: [20120915]关于表空间号.txt 前几天,开发问一个问题,就是建立表空间时表空间号有什么规律,我说:"我自己没有注意,应该是顺序增加吧" 他给看了测试环境,确实有一些不是规律增加,处于好奇研究看看.
[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.

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享: