表空间管理(一)-CSDN博客

#查看表空间数据块大小

SQL> desc dba_tablespaces
Name Null? Type
----------------------------------------- -------- ----------------------------
TABLESPACE_NAME NOT NULL VARCHAR2(30)
BLOCK_SIZE NOT NULL NUMBER
INITIAL_EXTENT NUMBER
NEXT_EXTENT NUMBER
MIN_EXTENTS NOT NULL NUMBER
MAX_EXTENTS NUMBER
MAX_SIZE NUMBER
PCT_INCREASE NUMBER
MIN_EXTLEN NUMBER
STATUS VARCHAR2(9)
CONTENTS VARCHAR2(9)
LOGGING VARCHAR2(9)
FORCE_LOGGING VARCHAR2(3)
EXTENT_MANAGEMENT VARCHAR2(10)
ALLOCATION_TYPE VARCHAR2(9)
PLUGGED_IN VARCHAR2(3)
SEGMENT_SPACE_MANAGEMENT VARCHAR2(6)
DEF_TAB_COMPRESSION VARCHAR2(8)
RETENTION VARCHAR2(11)
BIGFILE VARCHAR2(3)
PREDICATE_EVALUATION VARCHAR2(7)
ENCRYPTED VARCHAR2(3)
COMPRESS_FOR VARCHAR2(12)

SQL> select TABLESPACE_NAME , BLOCK_SIZE , CONTENTS from dba_tablespaces;

TABLESPACE_NAME BLOCK_SIZE CONTENTS
------------------------------ ---------- ---------
SYSTEM 8192 PERMANENT
SYSAUX 8192 PERMANENT
UNDOTBS1 8192 UNDO
TEMP 8192 TEMPORARY
USERS 8192 PERMANENT
EXAMPLE 8192 PERMANENT
TEST 8192 PERMANENT

7 rows selected.

#查看表空间对应的数据文件

SQL> select FILE_NAME , TABLESPACE_NAME,STATUS from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME STATUS
------------------------------ ---------
/u01/app/oracle/oradata/ENMOEDU/system01.dbf
SYSTEM AVAILABLE

/u01/app/oracle/oradata/ENMOEDU/sysaux01.dbf
SYSAUX AVAILABLE

/u01/app/oracle/oradata/ENMOEDU/undotbs01.dbf
UNDOTBS1 AVAILABLE

FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME STATUS
------------------------------ ---------
/u01/app/oracle/oradata/ENMOEDU/users01.dbf
USERS AVAILABLE

/u01/app/oracle/oradata/ENMOEDU/example01.dbf
EXAMPLE AVAILABLE

/u01/app/oracle/oradata/ENMOEDU/test01.dbf
TEST AVAILABLE

FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME STATUS
------------------------------ ---------
/u01/app/oracle/oradata/ENMOEDU/test02.dbf
TEST AVAILABLE
selected.

#创建本地管理表空间并验证
SQL> create tablespace yang datafile '/u01/app/oracle/oradata/ENMOEDU/yang01.dbf' size 50m
2 extent management local;

Tablespace created.

SQL> select tablespace_name,extent_management,status from dba_tablespaces;

TABLESPACE_NAME EXTENT_MAN STATUS
------------------------------ ---------- ---------
SYSTEM LOCAL ONLINE
SYSAUX LOCAL ONLINE
UNDOTBS1 LOCAL ONLINE
TEMP LOCAL ONLINE
USERS LOCAL ONLINE
EXAMPLE LOCAL ONLINE
TEST LOCAL ONLINE
YANG LOCAL ONLINE

8 rows selected.

#创建undo表空间(当一个用户要修改某个属性值时,其他用户也要查看这个数据,因为当前用户还没有提交,为了保证读一致性oracle设计了还原段,在还原段中存放更改前的数据) 并验证

SQL> create undo tablespace user_undo datafile '/u01/app/oracle/oradata/ENMOEDU/user_undo.dbf'
2 size 5m;

Tablespace created.

SQL> select tablespace_name,status,contents,logging,extent_management
2 from dba_tablespaces;

TABLESPACE_NAME STATUS CONTENTS LOGGING EXTENT_MAN
------------------------------ --------- --------- --------- ----------
SYSTEM ONLINE PERMANENT LOGGING LOCAL
SYSAUX ONLINE PERMANENT LOGGING LOCAL
UNDOTBS1 ONLINE UNDO LOGGING LOCAL
TEMP ONLINE TEMPORARY NOLOGGING LOCAL
USERS ONLINE PERMANENT LOGGING LOCAL
EXAMPLE ONLINE PERMANENT LOGGING LOCAL
TEST ONLINE PERMANENT LOGGING LOCAL
YANG ONLINE PERMANENT LOGGING LOCAL
USER_UNDO ONLINE UNDO LOGGING LOCAL

9 rows selected.
需要补充说明的一点是logging参数为logging时说明说明该表空间的变化受重做日志的保护

#创建临时表空间
SQL> create temporary tablespace user_temp tempfile '/u01/app/oracle/oradata/ENMOEDU/user_temp.dbf' size 5m;

Tablespace created.

SQL> select tablespace_name,status,contents,logging,extent_management from dba_tablespaces where tablespace_name='USER_TEMP';

TABLESPACE_NAME STATUS CONTENTS LOGGING EXTENT_MAN
------------------------------ --------- --------- --------- ----------
USER_TEMP ONLINE TEMPORARY NOLOGGING LOCAL

1,其logging至于nologging状态是因为临时表空间存放临时数据不需要记录在重做日志文件中,
2,为了优化临时表空间排序操作的性能将uniform size的大小设置为sort_area_size的整数倍

#切换临时表空间
SQL> alter database default temporary tablespace user_temp;

Database altered.

SQL> select * from database_properties
2 where property_name like 'DEFAULT%';

PROPERTY_NAME PROPERTY_VALUE
------------------------------ --------------------
DESCRIPTION
----------------------------------------
DEFAULT_TEMP_TABLESPACE USER_TEMP
Name of default temporary tablespace

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29438041/viewspace-1141355/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29438041/viewspace-1141355/