【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.