oracle users 表空间

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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29532781/viewspace-1174669/