oracle users 表空間

邱東陽發表於2014-06-03
Oracle

Users表空間也就是預設使用者表空間

在建立一個使用者並沒有指定此使用者使用表空間時，次使用者所有資訊都會放入到users表空間中。

 

指定所有使用者預設表空間

 

檢視預設使用者表空間

SQL> select * from database_properties where property_name like '%DEF%';

 

PROPERTY_NAME

------------------------------

PROPERTY_VALUE

--------------------------------------------------------------------------------

DESCRIPTION

--------------------------------------------------------------------------------

DEFAULT_TEMP_TABLESPACE

TEMP

Name of default temporary tablespace

 

DEFAULT_PERMANENT_TABLESPACE

USERS

Name of default permanent tablespace

 

PROPERTY_NAME

------------------------------

PROPERTY_VALUE

--------------------------------------------------------------------------------

DESCRIPTION

--------------------------------------------------------------------------------

 

DEFAULT_TBS_TYPE

SMALLFILE

Default tablespace type

 

 

SQL>

建立一個表空間並指定為預設使用者表空間

SQL> create tablespace users1 datafile '+data/fengzi/datafile/users1.dbf' size 100m autoextend on;

Tablespace created.

 

 

SQL>

SQL> alter database default tablespace users1;

 

Database altered.

 

SQL>

再次查詢檢視

SQL> select * from database_properties where property_name like '%DEF%';

 

PROPERTY_NAME

------------------------------

PROPERTY_VALUE

--------------------------------------------------------------------------------

DESCRIPTION

--------------------------------------------------------------------------------

DEFAULT_TEMP_TABLESPACE

TEMP

Name of default temporary tablespace

 

DEFAULT_PERMANENT_TABLESPACE

USERS1

Name of default permanent tablespace

 

PROPERTY_NAME

------------------------------

PROPERTY_VALUE

--------------------------------------------------------------------------------

DESCRIPTION

--------------------------------------------------------------------------------

 

DEFAULT_TBS_TYPE

SMALLFILE

Default tablespace type

 

 

SQL>

 

指定單個使用者預設表空間

 

可以建立使用者時直接指定

SQL> create user g1 identified by g1 default tablespace users1;

 

或者

SQL>  create user g1 identified by g1;                         

SQL> alter user g1 default tablespace users1;

SQL>

 

檢視所有使用者的預設表空間

 

SQL> select username,default_tablespace from dba_users;

 

 

USERNAME                       DEFAULT_TABLESPACE

------------------------------ ------------------------------

MGMT_VIEW                      SYSTEM

SYS                               SYSTEM

SYSTEM                           SYSTEM

DBSNMP                          SYSAUX

SYSMAN                          SYSAUX

BJ1                               USERS

QIU                              USERS

G1                               USERS1

OUTLN                           SYSTEM

MDSYS                           SYSAUX

ORDSYS                          SYSAUX

 

USERNAME                       DEFAULT_TABLESPACE

------------------------------ ------------------------------

CTXSYS                           SYSAUX

ANONYMOUS                     SYSAUX

EXFSYS                           SYSAUX

DMSYS                           SYSAUX

WMSYS                           SYSAUX

XDB                              SYSAUX

ORDPLUGINS                      SYSAUX

SI_INFORMTN_SCHEMA             SYSAUX

OLAPSYS                          SYSAUX

MDDATA                          USERS

DIP                              USERS

 

USERNAME                       DEFAULT_TABLESPACE

------------------------------ ------------------------------

TSMSYS                         USERS

 

23 rows selected.

 

SQL>

 

Users表空間的備份與恢復

在歸檔模式下可以使用熱備與RMAN備

 

熱備

SQL> select tablespace_name,file_name from dba_data_files;

 

TABLESPACE_NAME                FILE_NAME                                   

------------------------------ ------------------------------------------------------------------------------------

SYSTEM                         +DATA/fengzi/datafile/system.277.842187103          

 

UNDOTBS1                       +DATA/fengzi/datafile/undotbs1.278.842187181      

 

SYSAUX                         +DATA/fengzi/datafile/sysaux.279.842187235          

 

USERS                          +DATA/fengzi/datafile/users.281.842187289    

開使備份

SQL> alter tablespace system begin backup;

 

Tablespace altered.

 

SQL>

建立備份存放目錄

SQL> ho mkdir /u01/app/oracle/bak

直接CP 資料檔案

檔案系統

SQL> ho cp /u01/app/oracle/oradata/fengzi/users01.dbf  /u01/app/oracle/bak

ASM

[oracle@dongyang /]$ rman target /

 

Recovery Manager: Release 10.2.0.1.0 - Production on Tue Apr 1 11:24:25 2014

 

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

 

connected to target database: FENGZI (DBID=1573521836)

 

RMAN> copy datafile 4 to '/u01/app/oracle/bak/bkupusers.dbf';

 

 

結束備份

SQL> alter tablespace system end backup; 

 

恢復：

SQL>alter database datafile 4 offline;

 

檔案系統

 SQL> ho cp /u01/app/oracle/bak/users01.dbf     /u01/app/oracle/oradata/fengzi/users01.dbf

Asm

 RMAN> copy datafile '/u01/app/oracle/bak/bkupusers.dbf'  to   '+DATA1/fengzi/datafile/ users.281.842187289’;

 

   SQL>alter  tablespace users online;

   SQL>recover tablespace users;

SQL>alter  tablespace users online;

 

RMAN備份

[oracle@dongyang /]$ rman target /

 

Recovery Manager: Release 10.2.0.1.0 - Production on Tue Apr 1 11:24:25 2014

 

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

 

connected to target database: FENGZI (DBID=1573521836)

 

RMAN> backup tablespace users; 

恢復：

SQL>alter database datafile 4 offline;

RMAN>restore tablespace users;

  RMAN>recover tablespace users;

  SQL>alter tablespace users online;

 

對於後建的表空間有時不需要備份也是可以恢復的

 

QL> select username,default_tablespace from dba_users;

 

USERNAME                       DEFAULT_TABLESPACE

------------------------------ ------------------------------

MGMT_VIEW                      SYSTEM

SYS                            SYSTEM

SYSTEM                         SYSTEM

DBSNMP                         SYSAUX

SYSMAN                         SYSAUX

BJ1                            USERS

QIU                            USERS

G1                             USERS1

OUTLN                          SYSTEM

MDSYS                          SYSAUX

ORDSYS                         SYSAUX

 

USERNAME                       DEFAULT_TABLESPACE

------------------------------ ------------------------------

CTXSYS                         SYSAUX

ANONYMOUS                      SYSAUX

EXFSYS                         SYSAUX

DMSYS                          SYSAUX

WMSYS                          SYSAUX

XDB                            SYSAUX

ORDPLUGINS                     SYSAUX

SI_INFORMTN_SCHEMA             SYSAUX

OLAPSYS                        SYSAUX

MDDATA                         USERS

DIP                            USERS

 

USERNAME                       DEFAULT_TABLESPACE

------------------------------ ------------------------------

TSMSYS                         USERS

 

23 rows selected.

在g1使用者下建立表插入資料

SQL> conn g1/g1

SQL> create table yangzai (id number);

SQL> insert into yangzai values(1);

SQL> commit;

將users1離線刪除users1表空間資料檔案

SQL> alter tablespace users1 offline;

ASMCMD> rm users1.dbf

聯機

SQL> alter tablespace users1 online;

alter tablespace users1 online

*

ERROR at line 1:

ORA-01157: cannot identify/lock data file 6 - see DBWR trace file

ORA-01110: data file 6: '+DATA/fengzi/datafile/users1.dbf'

 

SQL> alter database datafile 6 offline;

 

Database altered.

 

SQL> select * from g1.yangzai; （現在查詢沒有資料，因為資料檔案已刪除）

select * from g1.yangzai

                 *

ERROR at line 1:

ORA-00376: file 6 cannot be read at this time

ORA-01110: data file 6: '+DATA/fengzi/datafile/users1.dbf'

 

 

SQL>

這種方法必須在歸檔模式下，取決於歸檔日誌完整。如果時間太長可能就恢復不了（模板無法使用這種方法）

SQL> alter database create datafile 6 as '+DATA/fengzi/datafile/users1.dbf';  （根據原資料檔案地址）

 

Database altered.

 

SQL> recover tablespace users1; 

Media recovery complete.

SQL>

SQL> alter tablespace users1 online;

 

Tablespace altered.

 

SQL> select * from g1.yangzai;

 

        ID

----------

         1

 

SQL>

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29532781/viewspace-1174669/，如需轉載，請註明出處，否則將追究法律責任。

相關文章