【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;
创新互联公司成立于2013年，是专业互联网技术服务公司，拥有项目
成都做网站、网站建设网站策划，项目实施与项目整合能力。我们以让每一个梦想脱颖而出为使命，1280元萨迦做网站,已为上家服务,为萨迦各地企业和个人服务,联系电话:18980820575创建临时表空间
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.
另外有需要云服务器可以了解下创新互联cdcxhl.cn，海内外云服务器15元起步，三天无理由+7*72小时售后在线，公司持有idc许可证，提供“云服务器、裸金属服务器、高防服务器、香港服务器、美国服务器、虚拟主机、免备案服务器”等云主机租用服务以及企业上云的综合解决方案，具有“安全稳定、简单易用、服务可用性高、性价比高”等特点与优势，专为企业上云打造定制，能够满足用户丰富、多元化的应用场景需求。
本文标题：【OracleDatabase】数据库表空间管理-创新互联
浏览路径：
http://jxruijie.cn/article/hdjjp.html
