【Oracle Database】 數據庫表空間管理

創建表空間
SQL>?create?tablespace?soe
datafile?'/u01/app/oracle/oradata/wallet/soe01.dbf'
size?1024M
extent?management?local
uniform?size?1M;

擴展表空間
方法一：在表空間中增加數據文件
SQL>?alter?tablespace?soe
add?datafile?'/u01/app/oracle/oradata/wallet/soe02.dbf'
size?2048M;

方法二：數據文件自動擴展
SQL>?alter?database?datafile?'/u01/app/oracle/oradata/wallet/soe01.dbf'?autoextend?on;

方法三：增加表空間中數據文件的大小
SQL>?alter?database?datafile?'/u01/app/oracle/oradata/wallet/soe01.dbf'?resize?2048M;

移動表空間數據文件
SQL>?alter?tablespace?soe?offline;?

SQL>?host?cp?/u01/app/oracle/oradata/wallet/soe02.dbf?/u02/app/oracle/oradata/wallet

SQL>?alter?tablespace?soe
rename?datafile?'/u01/app/oracle/oradata/wallet/soe02.dbf'
to?'/u02/app/oracle/oradata/wallet/soe02.dbf';

SQL>?alter?tablespace?soe?online;

SQL>?host?rm?-rf?/u01/app/oracle/oradata/wallet/soe02.dbf

刪除表空間
SQL>?drop?tablespace?soe?including?contents?and?datafiles;

創建臨時表空間
SQL>?create?temporary?tablespace?temp01
tempfile?'/u01/app/oracle/oradata/wallet/temp01.dbf'
size?1024M
extent?management?local
uniform?size?1M;

擴展臨時表空間
SQL>?alter?tablespace?temp01???????
add?tempfile?'/u01/app/oracle/oradata/wallet/temp02.dbf'
size?1024M;

查詢數據庫默認臨時表空間
SQL>?col?property_name?for?a40
SQL>?col?property_value?for?a40
SQL>?col?description?for?a40

SQL>?select?*?from?database_properties?where?property_name='DEFAULT_TEMP_TABLESPACE';
PROPERTY_NAME????????????????????????????PROPERTY_VALUE???????????????????????????DESCRIPTION
----------------------------------------?----------------------------------------?----------------------------------------
DEFAULT_TEMP_TABLESPACE??????????????????TEMP?????????????????????????????????????Name?of?default?temporary?tablespace

修改數據庫默認臨時表空間
SQL>?alter?database?default?temporary?tablespace?temp01;

SQL>?select?*?from?database_properties?where?property_name='DEFAULT_TEMP_TABLESPACE';
PROPERTY_NAME????????????????????????????PROPERTY_VALUE???????????????????????????DESCRIPTION
----------------------------------------?----------------------------------------?----------------------------------------
DEFAULT_TEMP_TABLESPACE??????????????????TEMP01???????????????????????????????????Name?of?default?temporary?tablespace

刪除臨時表空間
SQL>?drop?tablespace?temp?including?contents?and?datafiles;

創建UNDO表空間
SQL>?create?undo?tablespace?undotbs2
datafile?'/u01/app/oracle/oradata/wallet/undotbs02.dbf'
size?2048M;

查詢活動UNDO表空間
SQL>?show?parameter?undo_tablespace
NAME?????????????????????????????????TYPE??????????????????????????????VALUE
------------------------------------?---------------------------------?------------------------------
undo_tablespace??????????????????????string????????????????????????????UNDOTBS1

SQL>?select?count(*)?from?dba_undo_extents?where?status?=?'ACTIVE'?and?tablespace_name?=?'UNDOTBS1';
??COUNT(*)
----------
?????????6

修改活動UNDO表空間
SQL>?alter?system?set?undo_tablespace=undotbs2;

SQL>?show?parameter?undo_tablespace
NAME?????????????????????????????????TYPE??????????????????????????????VALUE
------------------------------------?---------------------------------?------------------------------
undo_tablespace??????????????????????string????????????????????????????UNDOTBS2?

刪除UNDO表空間
SQL>?select?count(*)?from?dba_undo_extents?where?status?=?'ACTIVE'?and?tablespace_name?=?'UNDOTBS1';
??COUNT(*)
----------
?????????0
?????????
SQL>?drop?tablespace?undotbs1?including?contents?and?datafiles;

SQL>?@dba_tablespaces.sql

+------------------------------------------------------------------------+
|?Report???:?Tablespaces?????????????????????????????????????????????????|
|?Instance?:?wallet??????????????????????????????????????????????????????|
+------------------------------------------------------------------------+

Tablespace?Name????????????????Status????TS?Type?????????Ext.?Mgt.??Seg.?Mgt.????????TS?Size?(MB)??????????Used?(MB)?Pct.?Used
------------------------------?---------?---------------?----------?----------?------------------?------------------?---------
SYSAUX?????????????????????????ONLINE????PERMANENT???????LOCAL??????AUTO????????????????????2,048????????????????482????????24
UNDOTBS1???????????????????????ONLINE????UNDO????????????LOCAL??????MANUAL??????????????????1,024????????????????114????????11
TEMP???????????????????????????ONLINE????TEMPORARY???????LOCAL??????MANUAL??????????????????1,024?????????????????28?????????3
SYSTEM?????????????????????????ONLINE????PERMANENT???????LOCAL??????MANUAL??????????????????2,048????????????????738????????36
SOE????????????????????????????ONLINE????PERMANENT???????LOCAL??????AUTO????????????????????4,096??????????????1,035????????25
USERS??????????????????????????ONLINE????PERMANENT???????LOCAL??????AUTO????????????????????1,024??????????????????1?????????0
???????????????????????????????????????????????????????????????????????????????------------------?------------------?---------
Average?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????16
Total??????????????????????????????????????????????????????????????????????????????????????11,264??????????????2,398

6?rows?selected.

SQL>?@dba_file_space_usage.sql

+------------------------------------------------------------------------+
|?Report???:?File?Usage??????????????????????????????????????????????????|
|?Instance?:?wallet??????????????????????????????????????????????????????|
+------------------------------------------------------------------------+

Tablespace?Name??????Filename??????????????????????????????????????????????FILE_ID?????File?Size?(MB)??????????Used?(MB)?Pct.?Used
--------------------?--------------------------------------------------?----------?------------------?------------------?---------
SOE??????????????????/u01/app/oracle/oradata/wallet/soe01.dbf????????????????????5??????????????2,048????????????????522????????25
SOE??????????????????/u01/app/oracle/oradata/wallet/soe02.dbf????????????????????6??????????????2,048????????????????513????????25
SYSAUX???????????????/u01/app/oracle/oradata/wallet/sysaux01.dbf?????????????????2??????????????2,048????????????????482????????23
SYSTEM???????????????/u01/app/oracle/oradata/wallet/system01.dbf?????????????????1??????????????2,048????????????????738????????36
TEMP?????????????????/u01/app/oracle/oradata/wallet/temp01.dbf???????????????????1??????????????1,024?????????????????28?????????2
UNDOTBS1?????????????/u01/app/oracle/oradata/wallet/undotbs01.dbf????????????????3??????????????1,024????????????????114????????11
USERS????????????????/u01/app/oracle/oradata/wallet/users01.dbf??????????????????4??????????????1,024??????????????????1?????????0
???????????????????????????????????????????????????????????????????????????????????------------------?------------------?---------
Average?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????17
Total??????????????????????????????????????????????????????????????????????????????????????????11,264??????????????2,398

7?rows?selected.