表空间的管理
//查询数据文件
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' 修改后重启数据库