【OracleDatabase】数据库表空间管理
创建表空间 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.
文章标题：【OracleDatabase】数据库表空间管理
本文URL：http://cdiso.cn/article/pojsid.html
