首先看状况：
SQL> select file_id,file_name,tablespace_name,autoextensible from dba_data_files;
FILE_ID FILE_NAME TABLESPACE AUTOEXTENS
------- -------------------------------------------------- ---------- ----------
4 /u01/app/oracle/oradata/orcl/users01.dbf USERS YES
3 /u01/app/oracle/oradata/orcl/undotbs01.dbf UNDOTBS1 YES
2 /u01/app/oracle/oradata/orcl/sysaux01.dbf SYSAUX YES
1 /u01/app/oracle/oradata/orcl/system01.dbf SYSTEM YES
5 /u01/app/oracle/oradata/orcl/example01.dbf EXAMPLE YES
6 /u01/app/datafiles/gaodata1.dbf GAOTBS YES
6 rows selected.
SQL>
然后，再来扩展表空间 GAOTBS:
SQL> alter tablespace gaotbs add datafile '/u01/app/datafiles/gaodata2.dbf' size 500M;
Tablespace altered.
然后，再看：
SQL> select file_id,file_name,tablespace_name,autoextensible from dba_data_files;
FILE_ID FILE_NAME TABLESPACE AUTOEXTENS
------- -------------------------------------------------- ---------- ----------
4 /u01/app/oracle/oradata/orcl/users01.dbf USERS YES
3 /u01/app/oracle/oradata/orcl/undotbs01.dbf UNDOTBS1 YES
2 /u01/app/oracle/oradata/orcl/sysaux01.dbf SYSAUX YES
1 /u01/app/oracle/oradata/orcl/system01.dbf SYSTEM YES
5 /u01/app/oracle/oradata/orcl/example01.dbf EXAMPLE YES
6 /u01/app/datafiles/gaodata1.dbf GAOTBS YES
7 /u01/app/datafiles/gaodata2.dbf GAOTBS NO
7 rows selected.
SQL> select * from v$tablespace;
TS# NAME INC BIG FLA ENC
---- -------------------------------------------------- --- --- --- ---
0 SYSTEM YES NO YES
1 SYSAUX YES NO YES
2 UNDOTBS1 YES NO YES
4 USERS YES NO YES
3 TEMP NO NO YES
6 EXAMPLE YES NO YES
7 GAOTBS YES NO YES
7 rows selected.
SQL> desc v$tablespace;
Name Null? Type
----------------------------------------- -------- ----------------------------
TS# NUMBER
NAME VARCHAR2(30)
INCLUDED_IN_DATABASE_BACKUP VARCHAR2(3)
BIGFILE VARCHAR2(3)
FLASHBACK_ON VARCHAR2(3)
ENCRYPT_IN_BACKUP VARCHAR2(3)
SQL> select file_id,file_name,tablespace_name,autoextensible from dba_data_files;
FILE_ID FILE_NAME TABLESPACE AUTOEXTENS
------- -------------------------------------------------- ---------- ----------
4 /u01/app/oracle/oradata/orcl/users01.dbf USERS YES
3 /u01/app/oracle/oradata/orcl/undotbs01.dbf UNDOTBS1 YES
2 /u01/app/oracle/oradata/orcl/sysaux01.dbf SYSAUX YES
1 /u01/app/oracle/oradata/orcl/system01.dbf SYSTEM YES
5 /u01/app/oracle/oradata/orcl/example01.dbf EXAMPLE YES
6 /u01/app/datafiles/gaodata1.dbf GAOTBS YES
6 rows selected.
SQL>
然后，再来扩展表空间 GAOTBS:
SQL> alter tablespace gaotbs add datafile '/u01/app/datafiles/gaodata2.dbf' size 500M;
Tablespace altered.
然后，再看：
SQL> select file_id,file_name,tablespace_name,autoextensible from dba_data_files;
FILE_ID FILE_NAME TABLESPACE AUTOEXTENS
------- -------------------------------------------------- ---------- ----------
4 /u01/app/oracle/oradata/orcl/users01.dbf USERS YES
3 /u01/app/oracle/oradata/orcl/undotbs01.dbf UNDOTBS1 YES
2 /u01/app/oracle/oradata/orcl/sysaux01.dbf SYSAUX YES
1 /u01/app/oracle/oradata/orcl/system01.dbf SYSTEM YES
5 /u01/app/oracle/oradata/orcl/example01.dbf EXAMPLE YES
6 /u01/app/datafiles/gaodata1.dbf GAOTBS YES
7 /u01/app/datafiles/gaodata2.dbf GAOTBS NO
7 rows selected.
SQL> select * from v$tablespace;
TS# NAME INC BIG FLA ENC
---- -------------------------------------------------- --- --- --- ---
0 SYSTEM YES NO YES
1 SYSAUX YES NO YES
2 UNDOTBS1 YES NO YES
4 USERS YES NO YES
3 TEMP NO NO YES
6 EXAMPLE YES NO YES
7 GAOTBS YES NO YES
7 rows selected.
SQL> desc v$tablespace;
Name Null? Type
----------------------------------------- -------- ----------------------------
TS# NUMBER
NAME VARCHAR2(30)
INCLUDED_IN_DATABASE_BACKUP VARCHAR2(3)
BIGFILE VARCHAR2(3)
FLASHBACK_ON VARCHAR2(3)
ENCRYPT_IN_BACKUP VARCHAR2(3)
SQL>
本文转自健哥的数据花园博客园博客，原文链接：http://www.cnblogs.com/gaojian/p/3612900.html，如需转载请自行联系原作者
