维护与删除oracle表空间

1、重命名表空间

SQL> alter tablespace user_commerce02 rename to user_commerce02_new;

Tablespace altered.

2、设置表空间为读写状态

SQL> alter tablespace user_commerce02_new read write;

Tablespace altered.

3、设置表空间为临时脱机状态

SQL> alter tablespace user_commerce02_new offline temporary;

Tablespace altered.

4、设置默认表空间，将临时表空间设置为默认的临时表空间

SQL> alter database  default temporary tablespace user_commerce_temp;

Database altered.

5、将表空间及其数据文件一并删除

SQL> drop tablespace user_commerce02_new including contents and datafiles;

Tablespace dropped.

6、将表空间对应的数据文件修改大小为20M

SQL> alter database datafile '/u01/app/oracle/oradata/orcl/user_commerce01.dbf' resize 20M;

Database altered.

7、修改表空间的自动扩展性，增量大小为10M，文件大小限制为7M

SQL> alter database datafile '/u01/app/oracle/oradata/orcl/user_commerce01.dbf' autoextend off;

Database altered.

SQL> alter database datafile '/u01/app/oracle/oradata/orcl/user_commerce01.dbf' autoextend on next 10M maxsize 7M;

Database altered.

8、增加表空间数据文件

SQL> alter tablespace user_commerce01 add datafile '/u01/app/oracle/oradata/orcl/user_commerce03.dbf'size 5M autoextend on next 5M maxsize 7M;

Tablespace altered.

9、移动表空间数据文件

SQL> alter tablespace user_commerce01 offline;

Tablespace altered.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[oracle@cnbugs1 ~]$ mkdir /u01/app/oracle/oradata/demo
[oracle@cnbugs1 ~]$ mv /u01/app/oracle/oradata/orcl/user_commerce01.dbf /u01/app/oracle/oradata/demo/
[oracle@cnbugs1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Wed Dec 16 11:27:23 2020

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> alter tablespace user_commerce01 rename datafile '/u01/app/oracle/oradata/orcl/user_commerce01.dbf' to '/u01/app/oracle/oradata/demo/user_commerce01.dbf';

Tablespace altered.

SQL> alter tablespace user_commerce01 online;

Tablespace altered.


SQL> select tablespace_name,file_name from dba_data_files where tablespace_name='USER_COMMERCE01';

TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
USER_COMMERCE01
/u01/app/oracle/oradata/demo/user_commerce01.dbf

USER_COMMERCE01
/u01/app/oracle/oradata/orcl/user_commerce03.dbf

10、删除表空间数据文件

SQL> alter tablespace user_commerce01 drop datafile '/u01/app/oracle/oradata/orcl/user_commerce03.dbf';

Tablespace altered.

SQL> select tablespace_name,status,allocation_type,bigfile from dba_tablespaces;

TABLESPACE_NAME                STATUS    ALLOCATIO BIG
------------------------------ --------- --------- ---
SYSTEM                         ONLINE    SYSTEM    NO
SYSAUX                         ONLINE    SYSTEM    NO
UNDOTBS1                       ONLINE    SYSTEM    NO
TEMP                           ONLINE    UNIFORM   NO
USERS                          ONLINE    SYSTEM    NO
CNBUGSDB                       ONLINE    SYSTEM    NO
USER_COMMERCE01                ONLINE    SYSTEM    NO
USER_COMMERCE_TEMP             ONLINE    UNIFORM   NO
USER_COMMERCE_BIG              ONLINE    SYSTEM    NO
USER_COMMERCE_UNDO             ONLINE    SYSTEM    NO

10 rows selected.

11、查看当前实例orcl表空间名称、状态、盘区大小分配方式和表空间类型

SQL> select tablespace_name,bytes,blocks from dba_free_space where tablespace_name='USER_COMMERCE01';

TABLESPACE_NAME                     BYTES     BLOCKS
------------------------------ ---------- ----------
USER_COMMERCE01                  19922944       2432

SQL> select tablespace_name,bytes,blocks,user_bytes,user_blocks  from dba_data_files where tablespace_name='USER_COMMERCE01';

TABLESPACE_NAME                     BYTES     BLOCKS USER_BYTES USER_BLOCKS
------------------------------ ---------- ---------- ---------- -----------
USER_COMMERCE01                  20971520       2560   19922944        2432

查看用户表空间和临时表空间名称

SQL> select tablespace_name,status,allocation_type,bigfile from dba_tablespaces;

TABLESPACE_NAME                STATUS    ALLOCATIO BIG
------------------------------ --------- --------- ---
SYSTEM                         ONLINE    SYSTEM    NO
SYSAUX                         ONLINE    SYSTEM    NO
UNDOTBS1                       ONLINE    SYSTEM    NO
TEMP                           ONLINE    UNIFORM   NO
USERS                          ONLINE    SYSTEM    NO
CNBUGSDB                       ONLINE    SYSTEM    NO
USER_COMMERCE01                ONLINE    SYSTEM    NO
USER_COMMERCE_TEMP             ONLINE    UNIFORM   NO
USER_COMMERCE_BIG              ONLINE    SYSTEM    NO
USER_COMMERCE_UNDO             ONLINE    SYSTEM    NO

10 rows selected.

13、查看表空间的空闲空间信息

SQL> select tablespace_name,bytes,blocks from dba_free_space where tablespace_name='USER_COMMERCE01';

TABLESPACE_NAME                     BYTES     BLOCKS
------------------------------ ---------- ----------
USER_COMMERCE01                  19922944       2432

SQL> select tablespace_name,bytes,blocks,user_bytes,user_blocks  from dba_data_files where tablespace_name='USER_COMMERCE01';

TABLESPACE_NAME                     BYTES     BLOCKS USER_BYTES USER_BLOCKS
------------------------------ ---------- ---------- ---------- -----------
USER_COMMERCE01                  20971520       2560   19922944        2432

14、查看表空间的数据文件信息

SQL> select tablespace_name,file_name from dba_data_files where tablespace_name='USER_COMMERCE01';

TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
USER_COMMERCE01
/u01/app/oracle/oradata/demo/user_commerce01.dbf

USER_COMMERCE01
/u01/app/oracle/oradata/orcl/user_commerce03.dbf

15、查看当前数据库默认的表空间类型

SQL> select property_name,property_value from database_properties where property_name like 'DEFAULT%';

PROPERTY_NAME           PROPERTY_VALUE
----------------------- --------------
DEFAULT_TBS_TYPE        SMALLFILE
DEFAULT_EDITION         ORA$BASE
DEFAULT_PERMANENT_TABLE USERS
SPACE

DEFAULT_TEMP_TABLESPACE USER_COMMERCE_
                        TEMP

修改字段显示列宽大小

SQL> column property_name format A23;
SQL> column property_value format A14;

Mr丶点心永久会员

打赏 收藏 海报 链接