[20250505]建表指定特定的数据文件是否可行.txt

[20250505]建表指定特定的数据文件是否可行.txt

--//测试需要，是否可以实现建表指定特定的数据文件，建立表只能指定表空间，无法指定数据文件，测试看看。

1.环境：
SCOTT@book01p> @ ver2
==============================
PORT_STRING                   : x86_64/Linux 2.4.xx
VERSION                       : 21.0.0.0.0
BANNER                        : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
BANNER_FULL                   : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
BANNER_LEGACY                 : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
CON_ID                        : 0
PL/SQL procedure successfully completed.

2.思路：
--//先在原来的表空间上建立新的数据文件，通过类似如下方式测试看看：
ALTER TABLESPACE USERS
  ADD DATAFILE '/u01/oradata/BOOK/book01p/users02.dbf'
  SIZE 100M
  AUTOEXTEND ON
  NEXT 4M
  MAXSIZE UNLIMITED;

SCOTT@book01p> select * from dba_data_files where TABLESPACE_NAME='USERS';
FILE_NAME                                FILE_ID TABLESPACE_NAME      BYTES     BLOCKS STATUS    RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ONLINE_ LOST_WR
------------------------------------- ---------- --------------- ---------- ---------- --------- ------------ --- ---------- ---------- ------------ ---------- ----------- ------- -------
/u01/oradata/BOOK/book01p/users01.dbf         12 USERS            267386880      32640 AVAILABLE           12 YES 3.4360E+10    4194302          160  266338304       32512 ONLINE  OFF
/u01/oradata/BOOK/book01p/users02.dbf         15 USERS            104857600      12800 AVAILABLE           15 YES 3.4360E+10    4194302          512  103809024       12672 ONLINE  OFF

create table t2 as select * from all_objects where 0=1;
alter  table t2 allocate extent ( datafile '/u01/oradata/BOOK/book01p/users02.dbf' size 64K)

3.测试：
--//增加数据文件脚本如上，不再贴出。

SCOTT@book01p> create table t2 as select * from all_objects where 0=1;
Table created.

SCOTT@book01p> column PARTITION_NAME noprint
SCOTT@book01p> select * from dba_extents where segment_name='T2';
no rows selected
--//11g新特性SEGMENT CREATION DEFERRED，这样建立表不存在段的分配。

SCOTT@book01p> alter  table t2 allocate extent ( datafile '/u01/oradata/BOOK/book01p/users02.dbf' size 64K);
Table altered.

SCOTT@book01p> select * from dba_extents where segment_name='T2';
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME  EXTENT_ID    FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO
----- ------------ ------------ --------------- ---------- ---------- ---------- ---------- ---------- ------------
SCOTT T2           TABLE        USERS                    0         12        176      65536          8           12
SCOTT T2           TABLE        USERS                    1         15        128      65536          8           15
--//可以发现这种方法不行，会同时建立2个段，分别在2个数据文件上。

SCOTT@book01p> @ seg2 t2 ''
SCOTT@book01p> @ pr
==============================
SEG_MB                        : 0
SEG_OWNER                     : SCOTT
SEG_SEGMENT_NAME              : T2
SEG_PARTITION_NAME            :
SEG_SEGMENT_TYPE              : TABLE
SEG_TABLESPACE_NAME           : USERS
BLOCKS                        : 16
HDRFIL                        : 12
HDRBLK                        : 178
PL/SQL procedure successfully completed.
--//dba_segments视图仅仅记录1条记录。

4.换一个思路：
--//如果其他数据文件offline，然后在建立表是否可行呢？测试看看。

SCOTT@book01p> drop table t2 purge;
Table dropped.

SCOTT@book01p> alter database datafile '/u01/oradata/BOOK/book01p/users01.dbf' offline ;
alter database datafile '/u01/oradata/BOOK/book01p/users01.dbf' offline
*
ERROR at line 1:
ORA-01145: offline immediate disallowed unless media recovery enabled

SCOTT@book01p> alter database datafile '/u01/oradata/BOOK/book01p/users01.dbf' offline drop ;
Database altered.
--//注：因为我的测试环境没有打开归档，导致直接offline报错，加入drop没有问题，注意并非真正意思删除。

SCOTT@book01p> recover datafile 12;
Media recovery complete.
--//顺手先1次recover datafile 12;以后可以直接online,这样即使日志覆盖了，online也没有问题。

SCOTT@book01p> create table t2 SEGMENT CREATION IMMEDIATE as select * from all_objects where 0=1;
Table created.

SCOTT@book01p> select * from dba_extents where segment_name='T2';
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME  EXTENT_ID    FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO
----- ------------ ------------ --------------- ---------- ---------- ---------- ---------- ---------- ------------
SCOTT T2           TABLE        USERS                    0         15        128      65536          8           15

SCOTT@book01p> @ seg2 T2 ''
SEG_MB OWNER SEGMENT_NAME SEGMENT_TYPE SEG_TABLESPACE_NAME     BLOCKS     HDRFIL     HDRBLK
------ ----- ------------ ------------ ------------------- ---------- ---------- ----------
     0 SCOTT T2           TABLE        USERS                        8         15        130

SCOTT@book01p> alter database datafile '/u01/oradata/BOOK/book01p/users01.dbf' online ;
Database altered.
--//说明这样的方式可行.

5.收尾：
SCOTT@book01p> drop table t2 purge;
Table dropped.

--//删除建立的数据文件/u01/oradata/BOOK/book01p/users02.dbf，这时该数据文件没有分配任何段，可以删除。

alter tablespace "USERS" drop datafile '/u01/oradata/BOOK/book01p/users02.dbf';