【Oracle Database】 数据库表空间管理

创建表空间SQL> create tablespace soedatafile '/u01/app/oracle/oradata/wallet/soe01.dbf'size 1024Mextent management localuniform size 1M;扩展表空间方法一：在表空间中增加数据文件SQL> alter tablespace soeadd 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/walletSQL> alter tablespace soerename 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 temp01tempfile '/u01/app/oracle/oradata/wallet/temp01.dbf'size 1024Mextent management localuniform size 1M;扩展临时表空间SQL> alter tablespace temp01       add tempfile '/u01/app/oracle/oradata/wallet/temp02.dbf'size 1024M;查询数据库默认临时表空间SQL> col property_name for a40SQL> col property_value for a40SQL> col description for a40SQL> 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 undotbs2datafile '/u01/app/oracle/oradata/wallet/undotbs02.dbf'size 2048M;查询活动UNDO表空间SQL> show parameter undo_tablespaceNAME                                 TYPE                              VALUE------------------------------------ --------------------------------- ------------------------------undo_tablespace                      string                            UNDOTBS1SQL> 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_tablespaceNAME                                 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        24UNDOTBS1                       ONLINE    UNDO            LOCAL      MANUAL                  1,024                114        11TEMP                           ONLINE    TEMPORARY       LOCAL      MANUAL                  1,024                 28         3SYSTEM                         ONLINE    PERMANENT       LOCAL      MANUAL                  2,048                738        36SOE                            ONLINE    PERMANENT       LOCAL      AUTO                    4,096              1,035        25USERS                          ONLINE    PERMANENT       LOCAL      AUTO                    1,024                  1         0                                                                               ------------------ ------------------ ---------Average                                                                                                                     16Total                                                                                      11,264              2,3986 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        25SOE                  /u01/app/oracle/oradata/wallet/soe02.dbf                    6              2,048                513        25SYSAUX               /u01/app/oracle/oradata/wallet/sysaux01.dbf                 2              2,048                482        23SYSTEM               /u01/app/oracle/oradata/wallet/system01.dbf                 1              2,048                738        36TEMP                 /u01/app/oracle/oradata/wallet/temp01.dbf                   1              1,024                 28         2UNDOTBS1             /u01/app/oracle/oradata/wallet/undotbs01.dbf                3              1,024                114        11USERS                /u01/app/oracle/oradata/wallet/users01.dbf                  4              1,024                  1         0                                                                                   ------------------ ------------------ ---------Average                                                                                                                         17Total                                                                                          11,264              2,3987 rows selected.