表的条带化_oracle 数据表条带化-优快云博客

本文链接：https://blog.youkuaiyun.com/2301_79009758/article/details/138487549

本文详细展示了如何在Oracle数据库中使用SQL命令创建、修改和删除表空间，添加数据文件，以及调整表的extent分配，包括ALTERTABLESPACE,CREATETABLE,DROPTABLE和EXTENTS管理操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

SQL> conn / as sysdba
Connected.
SQL> select name from v$datafile where rownum=1;

NAME
------------------------------------------
F:\ORACLE\ORADATA\ORA9\SYSTEM01.DBF

SQL> create tablespace st datafile 'F:\ORACLE\ORADATA\ORA9\st1.dbf' size 2m;

Tablespace created.

SQL> alter tablespace st add datafile 'F:\ORACLE\ORADATA\ORA9\st2.dbf' size 2m;

Tablespace altered.

SQL> alter tablespace st add datafile 'F:\ORACLE\ORADATA\ORA9\st3.dbf' size 2m;

Tablespace altered.

SQL> drop table scott.t1;
drop table scott.t1
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> create table scott.t1 tablespace st as select * from scott.emp;

Table created.

SQL> select tablespace_name from dba_tables where table_name='T1' AND owner='SCOTT';

TABLESPACE_NAME
---------------
ST

SQL> DESC DBA_EXTENTS
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
OWNER VARCHAR2(30)
SEGMENT_NAME VARCHAR2(81)
PARTITION_NAME VARCHAR2(30)
SEGMENT_TYPE VARCHAR2(18)
TABLESPACE_NAME VARCHAR2(30)
EXTENT_ID NUMBER
FILE_ID NUMBER
BLOCK_ID NUMBER
BYTES NUMBER
BLOCKS NUMBER
RELATIVE_FNO NUMBER

SQL> SELECT EXTENT_ID,FILE_ID,BLOCK_ID,BLOCKS FROM DBA_EXTENTS
2 where SEGMENT_NAME='T1' AND owner='SCOTT';

EXTENT_ID FILE_ID BLOCK_ID BLOCKS
---------- ---------- ---------- ----------
0 12 9 8

SQL> SELECT FILE_ID,FILE_NAME FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='ST';

FILE_ID FILE_NAME
---------- ----------------------------------------
11 F:\ORACLE\ORADATA\ORA9\ST1.DBF
12 F:\ORACLE\ORADATA\ORA9\ST2.DBF
13 F:\ORACLE\ORADATA\ORA9\ST3.DBF

SQL> ALTER TABLE SCOTT.T1 ALLOCATE EXTENT(DATAFILE 'F:\ORACLE\ORADATA\ORA9\ST1.DBF');

Table altered.

SQL> SELECT EXTENT_ID,FILE_ID,BLOCK_ID,BLOCKS FROM DBA_EXTENTS
2 where SEGMENT_NAME='T1' AND owner='SCOTT';

EXTENT_ID FILE_ID BLOCK_ID BLOCKS
---------- ---------- ---------- ----------
0 12 9 8
1 11 9 8

SQL> ALTER TABLE SCOTT.T1 ALLOCATE EXTENT(DATAFILE 'F:\ORACLE\ORADATA\ORA9\ST3.DBF');

Table altered.

SQL> SELECT EXTENT_ID,FILE_ID,BLOCK_ID,BLOCKS FROM DBA_EXTENTS
2 where SEGMENT_NAME='T1' AND owner='SCOTT';

EXTENT_ID FILE_ID BLOCK_ID BLOCKS
---------- ---------- ---------- ----------
0 12 9 8
1 11 9 8
2 13 9 8

SQL> SPOOL OFF