Oracle表空间的管理

表空间的管理

//查询数据文件
SQL> select name from v$datafile;

NAME
----------------------------------------------------------------------
D:/ORACLE/PRODUCT/10.1.0/ORADATA/DB3/SYSTEM01.DBF
D:/ORACLE/PRODUCT/10.1.0/ORADATA/DB3/UNDOTBS.DBF
D:/ORACLE/PRODUCT/10.1.0/ORADATA/DB3/SYSAUX01.DBF

 

//描述表空间文件
SQL> desc v$tablespace
 Name                                                              Null?    Type
 --------------------------------------------- -------- ------------------------
 TS#                                                                        NUMBER
 NAME                                                                      VARCHAR2(30)
 INCLUDED_IN_DATABASE_BACKUP                                 VARCHAR2(3)
 BIGFILE                                                                    VARCHAR2(3)
 FLASHBACK_ON                                                          VARCHAR2(3)

 

SQL> select *from v$tablespace;

     TS# NAME                                                                   INC BIG FLA
-------- ---------------------------------------------------- --- --- ---
         0 SYSTEM                                                                 YES  NO  YES
         1 UNDOTBS                                                               YES  NO  YES
         2 SYSAUX                                                                 YES  NO  YES
         3 TEMP                                                                     YES  NO  YES

//创建表空间data1，以本地管理方式

SQL> create tablespace data1
  2  datafile 'D:/ORACLE/PRODUCT/10.1.0/ORADATA/DB3/data1.DBF' size 2m
  3  extent management local uniform size 256k;

Tablespace created.

 

//查看表空间建立结果

SQL> select name,status,ts#,blocks from v$datafile;

NAME                                                                                              STATUS         TS#     BLOCKS
------------------------------------------------------------------------ ------- ---------- ----------
D:/ORACLE/PRODUCT/10.1.0/ORADATA/DB3/SYSTEM01.DBF                      SYSTEM           0      28160
D:/ORACLE/PRODUCT/10.1.0/ORADATA/DB3/UNDOTBS.DBF                         ONLINE           1       3840
D:/ORACLE/PRODUCT/10.1.0/ORADATA/DB3/SYSAUX01.DBF                        ONLINE           2       5760
D:/ORACLE/PRODUCT/10.1.0/ORADATA/DB3/DATA1.DBF                             ONLINE           4        256

//将表空间下线脱机
SQL> alter tablespace data1 offline;

Tablespace altered.

 

SQL> select name,status,ts#,blocks from v$datafile;

NAME                                                                                           STATUS         TS#     BLOCKS
---------------------------------------------------------------------- ------- ---------- ----------
D:/ORACLE/PRODUCT/10.1.0/ORADATA/DB3/SYSTEM01.DBF                    SYSTEM           0      28160
D:/ORACLE/PRODUCT/10.1.0/ORADATA/DB3/UNDOTBS.DBF                      ONLINE            1       3840
D:/ORACLE/PRODUCT/10.1.0/ORADATA/DB3/SYSAUX01.DBF                     ONLINE            2       5760
D:/ORACLE/PRODUCT/10.1.0/ORADATA/DB3/DATA1.DBF                          OFFLINE           4          0

 

//建立临时表空间

SQL> create temporary tablespace temp2
  2  tempfile 'D:/ORACLE/PRODUCT/10.1.0/ORADATA/DB3/temp2.DBF' size 30m
  3  extent management local uniform size 512k;

Tablespace created.

 

//查询临时文件
SQL> select name from v$tempfile;

NAME
----------------------------------------------------------------------
D:/ORACLE/PRODUCT/10.1.0/ORADATA/DB3/TEMP.DBF
D:/ORACLE/PRODUCT/10.1.0/ORADATA/DB3/TEMP2.DBF

 

SQL> desc database_properties

 Name                                                              Null?    Type
 --------------------------------------------- -------- ------------------------
 PROPERTY_NAME                                        NOT NULL VARCHAR2(30)
 PROPERTY_VALUE                                                     VARCHAR2(4000)
 DESCRIPTION                                                           VARCHAR2(4000)

 

 

SQL> column property_value format a50
SQL> column description format a50

SQL> select property_name,property_value from database_properties;

PROPERTY_NAME                            PROPERTY_VALUE
----------------------------------- --------------------------------------------------
DICT.BASE                                    2
DEFAULT_TEMP_TABLESPACE           TEMP    //默认临时表空间
DEFAULT_PERMANENT_TABLESPACE   SYSTEM
DBTIMEZONE                                 +08:00
DEFAULT_TBS_TYPE                       SMALLFILE
NLS_LANGUAGE                              AMERICAN
NLS_TERRITORY                             AMERICA
NLS_CURRENCY                              $
NLS_ISO_CURRENCY                        AMERICA
NLS_NUMERIC_CHARACTERS             .,
NLS_CHARACTERSET                       ZHS16GBK

 

PROPERTY_NAME                            PROPERTY_VALUE
----------------------------------- --------------------------------------------------
NLS_CALENDAR                              GREGORIAN
NLS_DATE_FORMAT                        DD-MON-RR
NLS_DATE_LANGUAGE                     AMERICAN
NLS_SORT                                    BINARY
NLS_TIME_FORMAT                        HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT               DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT                   HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT          DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY                     $
NLS_COMP                                   BINARY
NLS_LENGTH_SEMANTICS               BYTE

 

PROPERTY_NAME                          PROPERTY_VALUE
---------------------------------- --------------------------------------------------
NLS_NCHAR_CONV_EXCP                FALSE
NLS_NCHAR_CHARACTERSET           AL16UTF16
NLS_RDBMS_VERSION                    10.1.0.2.0
GLOBAL_DB_NAME                         DB3
EXPORT_VIEWS_VERSION               8

 

27 rows selected.

 

//删除当前默认临时表空间，此操作是不允许的
SQL> drop tablespace temp;

drop tablespace temp
*
ERROR at line 1:
ORA-12906: cannot drop default temporary tablespace

 

//将默认临时表空间更改为其他后
SQL> alter database default temporary tablespace temp2;

Database altered.

 

SQL> select property_name,property_value from database_properties;

 

PROPERTY_NAME                             PROPERTY_VALUE
------------------------------------ --------------------------------------------------
DICT.BASE                                     2
DEFAULT_TEMP_TABLESPACE            TEMP2   //默认临时表空间
DEFAULT_PERMANENT_TABLESPACE    SYSTEM

……
27 rows selected.

 

//删除临时表空间
SQL> drop tablespace temp;

Tablespace dropped.

 

//创建UNDO表空间
SQL> create undo tablespace undotbs2
  2  datafile 'D:/ORACLE/PRODUCT/10.1.0/ORADATA/DB3/undotbs2.DBF' size 30m
  3  autoextend on next 2m maxsize 100m;

Tablespace created.

 

//查看当前撤销表空间
SQL> show parameter undo_tablespace;

NAME                                 T                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace                                     string      undotbs

 

//更改撤销表空间为undotbs2
SQL> alter system set undo_tablespace=undotbs2;

System altered.

 

SQL> show parameter undo_tablespace;

NAME                                                    TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace                                     string      UNDOTBS2

 

//将撤销表空间OFFLINE
SQL> alter tablespace undotbs offline;

Tablespace altered.

 

//查看数据文件的信息
SQL> select name,status,ts#,blocks from v$datafile;

NAME                                                                                          STATUS         TS#     BLOCKS
---------------------------------------------------------------------- ------- ---------- ----------
D:/ORACLE/PRODUCT/10.1.0/ORADATA/DB3/SYSTEM01.DBF                      SYSTEM           0      28160
D:/ORACLE/PRODUCT/10.1.0/ORADATA/DB3/UNDOTBS.DBF                        OFFLINE          1          0
D:/ORACLE/PRODUCT/10.1.0/ORADATA/DB3/SYSAUX01.DBF                      ONLINE            2       5760
D:/ORACLE/PRODUCT/10.1.0/ORADATA/DB3/DATA1.DBF                            OFFLINE          4          0
D:/ORACLE/PRODUCT/10.1.0/ORADATA/DB3/UNDOTBS2.DBF                      ONLINE           6       3840

 

//删除撤销表空间
SQL> drop tablespace undotbs;

Tablespace dropped.

 

SQL> shutdown immediate

Database closed.
Database dismounted.
ORACLE instance shut down.

 

//重启失败，需进行修改PFile操作
SQL> startup

ORACLE instance started.

Total System Global Area  163577856 bytes
Fixed Size                   787948 bytes
Variable Size             111933972 bytes
Database Buffers           50331648 bytes
Redo Buffers                 524288 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced

 

//将PFile ——initDB3.ora 的undo_tablespace='undotbs2' 修改后重启数据库