第九章： 表空间和数据文件（2）
临时表空间：
temporary tablespace 用于排序，可以建立多个临时表空间，但默认的临时表空间也只能有一个，default temporary tablespace不能offline和drop。如果未指定默认的临时表空间
oracle 将会使用system作为临时表空间。
09:00:53 SQL> alter tablespace temp add tempfile '/u01/app/oracle/oradata/lx02/temp01.dbf' size 100m reuse;
Tablespace altered.
09:01:14 SQL> select file_id,file_name,tablespace_name from dba_temp_files;
FILE_ID FILE_NAME TABLESPACE_NAME
---------- -------------------------------------------------- ------------------------------
1 /u01/app/oracle/oradata/lx02/temp01.dbf TEMP
09:01:17 SQL> select file#,name ,bytes/1024/1024 from v$tempfile;
FILE# NAME BYTES/1024/1024
---------- -------------------------------------------------- ---------------
1 /u01/app/oracle/oradata/lx02/temp01.dbf 100
09:01:22 SQL>
----建立临时表空间
09:04:18 SQL> create temporary tablespace tmp01
09:05:42 2 tempfile '/u01/app/oracle/oradata/lx02/tmp01.dbf' size 10m
09:06:03 3 extent management local uniform size 128k;
Tablespace created.
09:06:17 SQL> select file#,name from v$tempfile;
FILE# NAME
---------- --------------------------------------------------
1 /u01/app/oracle/oradata/lx02/temp01.dbf
2 /u01/app/oracle/oradata/lx02/tmp01.dbf
09:06:32 SQL>
------------查看默认的临时表空间
09:06:52 SQL> col PROPERTY_VALUE for a30
09:06:59 SQL> col description for a50
09:07:04 SQL> r
1* select * from database_properties
PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
------------------------------ ------------------------------ --------------------------------------------------
DICT.BASE 2 dictionary base tables version #
DEFAULT_TEMP_TABLESPACE TEMP Name of default temporary tablespace
DEFAULT_PERMANENT_TABLESPACE USERS Name of default permanent tablespace
DBTIMEZONE -04:00 DB time zone
DEFAULT_TBS_TYPE SMALLFILE Default tablespace type
NLS_LANGUAGE AMERICAN Language
NLS_TERRITORY AMERICA Territory
NLS_CURRENCY $ Local currency
NLS_ISO_CURRENCY AMERICA ISO currency
NLS_NUMERIC_CHARACTERS ., Numeric characters
NLS_CHARACTERSET ZHS16GBK Character set
NLS_CALENDAR GREGORIAN Calendar system
NLS_DATE_FORMAT DD-MON-RR Date format
NLS_DATE_LANGUAGE AMERICAN Date language
NLS_SORT BINARY Linguistic definition
NLS_TIME_FORMAT HH.MI.SSXFF AM Time format
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM Time stamp format
PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
------------------------------ ------------------------------ --------------------------------------------------
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR Time with timezone format
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR Timestamp with timezone format
NLS_DUAL_CURRENCY $ Dual currency symbol
NLS_COMP BINARY NLS comparison
NLS_LENGTH_SEMANTICS BYTE NLS length semantics
NLS_NCHAR_CONV_EXCP FALSE NLS conversion exception
NLS_NCHAR_CHARACTERSET AL16UTF16 NCHAR Character set
NLS_RDBMS_VERSION 10.2.0.1.0 RDBMS version for NLS parameters
GLOBAL_DB_NAME LX02 Global database name
EXPORT_VIEWS_VERSION 8 Export views revision #
27 rows selected.
-----------用户指定临时表空间
20:55:00 SQL> alter user scott temporary tablespace tmp01;
User altered.
-----切换默认的临时表空间
09:07:05 SQL> alter database default temporary tablespace tmp01;
Database altered.
09:07:34 SQL> select * from database_properties
09:07:39 2 ;
PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
------------------------------ ------------------------------ --------------------------------------------------
DICT.BASE 2 dictionary base tables version #
DEFAULT_TEMP_TABLESPACE TMP01 Name of default temporary tablespace
DEFAULT_PERMANENT_TABLESPACE USERS Name of default permanent tablespace
DBTIMEZONE -04:00 DB time zone
DEFAULT_TBS_TYPE SMALLFILE Default tablespace type
NLS_LANGUAGE AMERICAN Language
NLS_TERRITORY AMERICA Territory
NLS_CURRENCY $ Local currency
NLS_ISO_CURRENCY AMERICA ISO currency
NLS_NUMERIC_CHARACTERS ., Numeric characters
NLS_CHARACTERSET ZHS16GBK Character set
NLS_CALENDAR GREGORIAN Calendar system
NLS_DATE_FORMAT DD-MON-RR Date format
NLS_DATE_LANGUAGE AMERICAN Date language
NLS_SORT BINARY Linguistic definition
NLS_TIME_FORMAT HH.MI.SSXFF AM Time format
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM Time stamp format
PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
------------------------------ ------------------------------ --------------------------------------------------
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR Time with timezone format
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR Timestamp with timezone format
NLS_DUAL_CURRENCY $ Dual currency symbol
NLS_COMP BINARY NLS comparison
NLS_LENGTH_SEMANTICS BYTE NLS length semantics
NLS_NCHAR_CONV_EXCP FALSE NLS conversion exception
NLS_NCHAR_CHARACTERSET AL16UTF16 NCHAR Character set
NLS_RDBMS_VERSION 10.2.0.1.0 RDBMS version for NLS parameters
GLOBAL_DB_NAME LX02 Global database name
EXPORT_VIEWS_VERSION 8 Export views revision #
27 rows selected.
09:07:41 SQL>
--------------建立临时表空间组
09:07:41 SQL> alter tablespace temp tablespace group tmpgrp;
Tablespace altered.
09:09:33 SQL> alter tablespace tmp01 tablespace group tmpgrp;
Tablespace altered.
09:09:38 SQL> select * from dba_tablespace_groups;
GROUP_NAME TABLESPACE_NAME
------------------------------ ------------------------------
TMPGRP TEMP
TMPGRP TMP01
------------将临时表空间组射程默认临时表空间，可以实现负载均衡
09:09:52 SQL> alter database default temporary tablespace tmpgrp;
Database altered.
09:10:10 SQL> select * from database_properties;
PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
------------------------------ ------------------------------ --------------------------------------------------
DICT.BASE 2 dictionary base tables version #
DEFAULT_TEMP_TABLESPACE TMPGRP Name of default temporary tablespace
DEFAULT_PERMANENT_TABLESPACE USERS Name of default permanent tablespace
DBTIMEZONE -04:00 DB time zone
DEFAULT_TBS_TYPE SMALLFILE Default tablespace type
NLS_LANGUAGE AMERICAN Language
NLS_TERRITORY AMERICA Territory
NLS_CURRENCY $ Local currency
NLS_ISO_CURRENCY AMERICA ISO currency
NLS_NUMERIC_CHARACTERS ., Numeric characters
NLS_CHARACTERSET ZHS16GBK Character set
NLS_CALENDAR GREGORIAN Calendar system
NLS_DATE_FORMAT DD-MON-RR Date format
NLS_DATE_LANGUAGE AMERICAN Date language
NLS_SORT BINARY Linguistic definition
NLS_TIME_FORMAT HH.MI.SSXFF AM Time format
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM Time stamp format
PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
------------------------------ ------------------------------ --------------------------------------------------
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR Time with timezone format
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR Timestamp with timezone format
NLS_DUAL_CURRENCY $ Dual currency symbol
NLS_COMP BINARY NLS comparison
NLS_LENGTH_SEMANTICS BYTE NLS length semantics
NLS_NCHAR_CONV_EXCP FALSE NLS conversion exception
NLS_NCHAR_CHARACTERSET AL16UTF16 NCHAR Character set
NLS_RDBMS_VERSION 10.2.0.1.0 RDBMS version for NLS parameters
GLOBAL_DB_NAME LX02 Global database name
EXPORT_VIEWS_VERSION 8 Export views revision #
27 rows selected.
09:10:17 SQL>
------------查看临时表空间信息
21:03:08 SQL> col name for a50
21:03:10 SQL> r
1* select file#,name,bytes from v$tempfile
FILE# NAME BYTES
---------- -------------------------------------------------- ----------
1 /u01/app/oracle/oradata/prod/temp01.dbf 20971520
2 /u01/app/oracle/oradata/prod/tmp01.dbf 10485760
3 /u01/app/oracle/oradata/prod/tmp02.dbf 10485760
21:03:11 SQL> desc dba_temp_files;
Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
FILE_NAME VARCHAR2(513)
FILE_ID NUMBER
TABLESPACE_NAME NOT NULL VARCHAR2(30)
BYTES NUMBER
BLOCKS NUMBER
STATUS CHAR(9)
RELATIVE_FNO NUMBER
AUTOEXTENSIBLE VARCHAR2(3)
MAXBYTES NUMBER
MAXBLOCKS NUMBER
INCREMENT_BY NUMBER
USER_BYTES NUMBER
USER_BLOCKS NUMBER
表空间处于read only 状态，不可以做dml 操作，但可以删除对象。
09:20:53 SQL> create table t01 (id int) tablespace cuug;
Table created.
09:25:41 SQL> alter tablesapce cuug read only;
alter tablesapce cuug read only
*
ERROR at line 1:
ORA-00940: invalid ALTER command
09:25:46 SQL> alter tablespace cuug read only;
Tablespace altered.
09:25:53 SQL> insert into t01 values (1);
insert into t01 values (1)
*
ERROR at line 1:
ORA-00372: file 9 cannot be modified at this time
ORA-01110: data file 9: '/u01/app/oracle/oradata/lx02/cuug01.dbf'
09:26:03 SQL> drop table t01 purge;
Table dropped.
09:26:09 SQL>
09:26:09 SQL> alter tablespace cuug read write;
Tablespace altered.
09:26:34 SQL>
表空间脱机offline（在表空间被破坏，需要recovery）
1）system表空间不能offline ，正在使用的undo 不能offline，默认temporary tablespace不能offline
2）表空间脱机时会在tablespace上生成检查点，下次online 不需要做恢复操作。
09:26:34 SQL> alter tablespace system offline;
alter tablespace system offline
*
ERROR at line 1:
ORA-01541: system tablespace cannot be brought offline; shut down if necessary
9:29:06 SQL> alter tablespace undo_tbs01 offline;
alter tablespace undo_tbs01 offline
*
ERROR at line 1:
ORA-30042: Cannot offline the undo tablespace
-----------datafile offline 不生成检查点，在online 需要做recover
09:29:15 SQL> alter tablespace test offline;
Tablespace altered.
09:29:20 SQL> alter tablespace test online;
Tablespace altered.
09:29:25 SQL>
21:13:36 SQL> alter database datafile 2 offline;
Database altered.
21:15:02 SQL> select file#,name ,checkpoint_change# from v$datafile_header;
FILE# NAME CHECKPOINT_CHANGE#
---------- -------------------------------------------------- ------------------
1 /u01/app/oracle/oradata/prod/system01.dbf 1624678
2 /u01/app/oracle/oradata/prod/users01.dbf 1624804
3 /u01/app/oracle/oradata/prod/sysaux01.dbf 1624678
4 /u01/app/oracle/oradata/prod/index01.dbf 1624678
5 /u01/app/oracle/oradata/prod/example01.dbf 1624678
6 /u01/app/oracle/oradata/prod/test01.dbf 1624678
7 /u01/app/oracle/oradata/prod/undo_tbs01.dbf 1624678
8 /u01/app/oracle/oradata/prod/test02.dbf 1624678
9 /u01/app/oracle/oradata/prod/cuug01.dbf 1624678
10 /u01/app/oracle/oradata/prod/lx01.dbf 1624678
11 /u01/app/oracle/oradata/prod/perfstat01.dbf 1624678
12 /u01/app/oracle/oradata/prod/lx02.dbf 1624678
13 /u01/app/oracle/oradata/prod/lx03.dbf 1624678
14 /u01/app/oracle/oradata/prod/rtbs01.dbf 1624678
14 rows selected.
21:15:07 SQL> alter database datafile 2 online;
alter database datafile 2 recover datafile 2; online
*
ERROR at line 1:
ORA-01113: file 2 needs media recovery
ORA-01110: data file 2: '/u01/app/oracle/oradata/prod/users01.dbf'
21:15:24 SQL>
Media recovery complete.
21:15:42 SQL> alter database datafile 2 online;
Database altered.
----------offline immediate 立刻offline 不生成检查点，下次online需要recover
21:15:47 SQL> alter tablespace tests offline immediate;
Tablespace altered.
21:17:50 SQL> select file#,name ,checkpoint_change# from v$datafile_header;
FILE# NAME CHECKPOINT_CHANGE#
---------- -------------------------------------------------- ------------------
1 /u01/app/oracle/oradata/prod/system01.dbf 1624678
2 /u01/app/oracle/oradata/prod/users01.dbf 1624887
3 /u01/app/oracle/oradata/prod/sysaux01.dbf 1624678
4 /u01/app/oracle/oradata/prod/index01.dbf 1624678
5 /u01/app/oracle/oradata/prod/example01.dbf 1624678
6 /u01/app/oracle/oradata/prod/test01.dbf 1624678
7 /u01/app/oracle/oradata/prod/undo_tbs01.dbf 1624678
8 /u01/app/oracle/oradata/prod/test02.dbf 1624678
9 /u01/app/oracle/oradata/prod/cuug01.dbf 1624678
10 /u01/app/oracle/oradata/prod/lx01.dbf 1624678
11 /u01/app/oracle/oradata/prod/perfstat01.dbf 1624678
12 /u01/app/oracle/oradata/prod/lx02.dbf 1624678
13 /u01/app/oracle/oradata/prod/lx03.dbf 1624678
14 /u01/app/oracle/oradata/prod/rtbs01.dbf 1624678
14 rows selected.
21:17:54 SQL> alter tablespace tests online;
alter tablespace tests online
*
ERROR at line 1:
ORA-01113: file 6 needs media recovery
ORA-01110: data file 6: '/u01/app/oracle/oradata/prod/test01.dbf'
21:18:19 SQL> recover tablespace tests;
Media recovery complete.
21:18:27 SQL> alter tablespace tests online;
Tablespace altered.
21:18:32 SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /disk1/arch/prod
Oldest online log sequence 6
Next log sequence to archive 8
Current log sequence 8
21:18:46 SQL>
-------对于非归档模式脱机：alter tablespace xxx offline drop；
调整表空间的尺寸（表空间的大小和它的数据文件对应）
1）自动 autoextend 扩展
2）resize datafile 大小
3）增加表空间数据文件
1）自动扩展配置
09:33:17 SQL> select file_id,file_name,tablespace_name,AUTOEXTENSIBLE from dba_data_files;
FILE_ID FILE_NAME TABLESPACE_NAME AUT
---------- -------------------------------------------------- ------------------------------ ---
1 /u01/app/oracle/oradata/lx02/system01.dbf SYSTEM NO
9 /u01/app/oracle/oradata/lx02/cuug01.dbf CUUG NO
11 /u01/app/oracle/oradata/lx02/lx02.dbf LX02 NO
7 /u01/app/oracle/oradata/lx02/OLTP01.DBF OLTP NO
6 /u01/app/oracle/oradata/lx02/indx01.dbf INDX NO
5 /u01/app/oracle/oradata/lx02/example01.dbf EXAMPLE YES
4 /u01/app/oracle/oradata/lx02/user01.dbf USERS NO
3 /u01/app/oracle/oradata/lx02/sysaux01.dbf SYSAUX NO
2 /u01/app/oracle/oradata/lx02/rtbs01.dbf RTBS NO
8 /u01/app/oracle/oradata/lx02/test01.dbf TEST NO
10 /u01/app/oracle/oradata/lx02/lx01.dbf LX01 NO
12 /u01/app/oracle/oradata/lx02/undotbs01.dbf UNDO_TBS01 NO
12 rows selected.
----------自动扩展
09:33:20 SQL> alter database datafile '/u01/app/oracle/oradata/lx02/test01.dbf'
09:34:47 2 autoextend on next 10m maxsize 500m;
Database altered.
----------resize
09:34:57 SQL> alter database datafile '/u01/app/oracle/oradata/lx02/test01.dbf' resize 50m;
Database altered.
-----------增加datafile
09:35:35 SQL> alter tablespace test add
09:35:45 2 datafile '/u01/app/oracle/oradata/lx02/test02.dbf' size 10m;
Tablespace altered.
09:36:03 SQL> select file_id,file_name,tablespace_name,AUTOEXTENSIBLE ,bytes/1024/1024 "size" from dba_data_files;
FILE_ID FILE_NAME TABLESPACE_NAME AUT size
---------- -------------------------------------------------- ------------------------------ --- ----------
1 /u01/app/oracle/oradata/lx02/system01.dbf SYSTEM NO 300
9 /u01/app/oracle/oradata/lx02/cuug01.dbf CUUG NO 10
11 /u01/app/oracle/oradata/lx02/lx02.dbf LX02 NO 10
7 /u01/app/oracle/oradata/lx02/OLTP01.DBF OLTP NO 48
6 /u01/app/oracle/oradata/lx02/indx01.dbf INDX NO 40
5 /u01/app/oracle/oradata/lx02/example01.dbf EXAMPLE YES 400
4 /u01/app/oracle/oradata/lx02/user01.dbf USERS NO 48
3 /u01/app/oracle/oradata/lx02/sysaux01.dbf SYSAUX NO 100
2 /u01/app/oracle/oradata/lx02/rtbs01.dbf RTBS NO 100
8 /u01/app/oracle/oradata/lx02/test01.dbf TEST YES 50
10 /u01/app/oracle/oradata/lx02/lx01.dbf LX01 NO 10
12 /u01/app/oracle/oradata/lx02/undotbs01.dbf UNDO_TBS01 NO 10
13 /u01/app/oracle/oradata/lx02/test02.dbf TEST NO 10
13 rows selected.
09:36:21 SQL>
迁移表空间数据文件：
1） open 状态
09:36:21 SQL> alter tablespace test offline;
Tablespace altered.
09:40:07 SQL> !
[oracle@oracle ~]$ cp /u01/app/oracle/oradata/lx02/test02.dbf /disk1/lx02/oradata
[oracle@oracle ~]$ exit
exit
09:40:24 SQL> alter tablespace test rename
09:40:34 2 datafile '/u01/app/oracle/oradata/lx02/test02.dbf' to '/disk1/lx02/oradata/test02.dbf' ;
Tablespace altered.
09:40:48 SQL> alter tablespace test online;
Tablespace altered.
09:40:57 SQL> select file_id,file_name,tablespace_name,AUTOEXTENSIBLE ,bytes/1024/1024 "size" from dba_data_files;
FILE_ID FILE_NAME TABLESPACE_NAME AUT size
---------- -------------------------------------------------- ------------------------------ --- ----------
1 /u01/app/oracle/oradata/lx02/system01.dbf SYSTEM NO 300
9 /u01/app/oracle/oradata/lx02/cuug01.dbf CUUG NO 10
11 /u01/app/oracle/oradata/lx02/lx02.dbf LX02 NO 10
7 /u01/app/oracle/oradata/lx02/OLTP01.DBF OLTP NO 48
6 /u01/app/oracle/oradata/lx02/indx01.dbf INDX NO 40
5 /u01/app/oracle/oradata/lx02/example01.dbf EXAMPLE YES 400
4 /u01/app/oracle/oradata/lx02/user01.dbf USERS NO 48
3 /u01/app/oracle/oradata/lx02/sysaux01.dbf SYSAUX NO 100
2 /u01/app/oracle/oradata/lx02/rtbs01.dbf RTBS NO 100
8 /u01/app/oracle/oradata/lx02/test01.dbf TEST YES 50
10 /u01/app/oracle/oradata/lx02/lx01.dbf LX01 NO 10
12 /u01/app/oracle/oradata/lx02/undotbs01.dbf UNDO_TBS01 NO 10
13 /disk1/lx02/oradata/test02.dbf TEST NO 10
13 rows selected.
2）mount 状态
09:41:01 SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
09:42:15 SQL> startup mount;
ORACLE instance started.
Total System Global Area 251658240 bytes
Fixed Size 1218820 bytes
Variable Size 125830908 bytes
Database Buffers 121634816 bytes
Redo Buffers 2973696 bytes
Database mounted.
09:42:23 SQL> !
[oracle@oracle ~]$ cp /disk1/lx02/oradata/test02.dbf /u01/app/oracle/oradata/lx02
[oracle@oracle ~]$ exit
exit
09:42:36 SQL> alter database rename file
09:42:40 2 '/disk1/lx02/oradata/test02.dbf' to '/u01/app/oracle/oradata/lx02/test02.dbf' ;
Database altered.
09:42:56 SQL> alter database open;
Database altered.
09:43:03 SQL> select file_id,file_name,tablespace_name,AUTOEXTENSIBLE ,bytes/1024/1024 "size" from dba_data_files;
FILE_ID FILE_NAME TABLESPACE_NAME AUT size
---------- -------------------------------------------------- ------------------------------ --- ----------
1 /u01/app/oracle/oradata/lx02/system01.dbf SYSTEM NO 300
9 /u01/app/oracle/oradata/lx02/cuug01.dbf CUUG NO 10
11 /u01/app/oracle/oradata/lx02/lx02.dbf LX02 NO 10
7 /u01/app/oracle/oradata/lx02/OLTP01.DBF OLTP NO 48
6 /u01/app/oracle/oradata/lx02/indx01.dbf INDX NO 40
5 /u01/app/oracle/oradata/lx02/example01.dbf EXAMPLE YES 400
4 /u01/app/oracle/oradata/lx02/user01.dbf USERS NO 48
3 /u01/app/oracle/oradata/lx02/sysaux01.dbf SYSAUX NO 100
2 /u01/app/oracle/oradata/lx02/rtbs01.dbf RTBS NO 100
8 /u01/app/oracle/oradata/lx02/test01.dbf TEST YES 50
10 /u01/app/oracle/oradata/lx02/lx01.dbf LX01 NO 10
12 /u01/app/oracle/oradata/lx02/undotbs01.dbf UNDO_TBS01 NO 10
13 /u01/app/oracle/oradata/lx02/test02.dbf TEST NO 10
13 rows selected.
09:43:11 SQL>
删除表空间
can not drop ：1）system 2）active undo tablespace 3） default temporary tablespace 4）默认的永久表空间
09:43:11 SQL> DROP TABLESPACE SYSTEM;
DROP TABLESPACE SYSTEM
*
ERROR at line 1:
ORA-01550: cannot drop system tablespace
09:44:38 SQL> drop tablespace cuug including contents and datafiles;
Tablespace dropped.
09:44:59 SQL> select file_id,file_name,tablespace_name,AUTOEXTENSIBLE ,bytes/1024/1024 "size" from dba_data_files;
FILE_ID FILE_NAME TABLESPACE_NAME AUT size
---------- -------------------------------------------------- ------------------------------ --- ----------
1 /u01/app/oracle/oradata/lx02/system01.dbf SYSTEM NO 300
11 /u01/app/oracle/oradata/lx02/lx02.dbf LX02 NO 10
7 /u01/app/oracle/oradata/lx02/OLTP01.DBF OLTP NO 48
6 /u01/app/oracle/oradata/lx02/indx01.dbf INDX NO 40
5 /u01/app/oracle/oradata/lx02/example01.dbf EXAMPLE YES 400
4 /u01/app/oracle/oradata/lx02/user01.dbf USERS NO 48
3 /u01/app/oracle/oradata/lx02/sysaux01.dbf SYSAUX NO 100
2 /u01/app/oracle/oradata/lx02/rtbs01.dbf RTBS NO 100
8 /u01/app/oracle/oradata/lx02/test01.dbf TEST YES 50
10 /u01/app/oracle/oradata/lx02/lx01.dbf LX01 NO 10
12 /u01/app/oracle/oradata/lx02/undotbs01.dbf UNDO_TBS01 NO 10
13 /u01/app/oracle/oradata/lx02/test02.dbf TEST NO 10
12 rows selected.
09:45:02 SQL>
查看表空间空闲大小
09:47:04 SQL> select TABLESPACE_NAME,sum(bytes)/1024/1024 from dba_free_space
09:47:33 2 group by tablespace_name;
TABLESPACE_NAME SUM(BYTES)/1024/1024
------------------------------ --------------------
SYSAUX 51.0625
LX01 9
UNDO_TBS01 8.6875
LX02 9.9921875
RTBS 18.6875
USERS 16
OLTP 46
TEST 59.875
SYSTEM 131.890625
EXAMPLE 399
INDX 39.9375
建立非标准块表空间
09:50:46 SQL> alter system set db_16k_cache_size=12m;
System altered.
09:50:49 SQL> create tablespace tbs_16k
09:51:02 2 datafile '/u01/app/oracle/oradata/tbs16k01.dbf' size 10m
09:51:21 3 blocksize 16k;
Tablespace created.
09:51:29 SQL> select TABLESPACE_NAME,block_size from dba_tablespaces;
TABLESPACE_NAME BLOCK_SIZE
------------------------------ ----------
SYSTEM 8192
RTBS 8192
SYSAUX 8192
TEMP 8192
USERS 8192
EXAMPLE 8192
INDX 8192
OLTP 8192
TEST 8192
LX01 8192
LX02 8192
UNDO_TBS01 8192
TMP01 8192
TBS_16K 16384
14 rows selected.
大文件（bigfile）表空间（默认small file）
1）small file，在一个表空间可以建立多个数据文件
2）bigfile ：在一个表空间只能建立一个数据文件 （8k的block ，datafile maxsize 可以 32T），可以简化对数据文件管理
09:54:49 SQL> create bigfile tablespace big_tbs
09:54:52 2 datafile '/u01/app/oracle/oradata/bigtbs01.dbf' size 100m;
Tablespace created.
09:55:01 SQL> alter tablespace big_tbs
09:55:10 2 add datafile '/u01/app/oracle/oradata/bigtbs02.dbf' size 100m;
alter tablespace big_tbs
*
ERROR at line 1:
ORA-32771: cannot add file to bigfile tablespace
09:55:20 SQL>
09:55:46 SQL> select name,bigfile from v$tablespace;
NAME BIG
-------------------------------------------------- ---
SYSTEM NO
RTBS NO
SYSAUX NO
USERS NO
EXAMPLE NO
INDX NO
OLTP NO
TEMP NO
TEST NO
TBS_16K NO
LX01 NO
LX02 NO
UNDO_TBS01 NO
TMP01 NO
BIG_TBS YES
15 rows selected.
