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