SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS ------------------------------ --------- SYSTEM ONLINE SYSAUX ONLINE UNDOTBS1 ONLINE TEMP ONLINE USERS ONLINE EXAMPLE ONLINE EBANK_TEMP ONLINE ECIF_DATA ONLINE ECIF_INDEX ONLINE EIP_DATA ONLINE YANG ONLINE
TABLESPACE_NAME STATUS ------------------------------ --------- DUKE_TEMP ONLINE /*新创建的临时表空间*/ DUKE_DATA ONLINE /*新创建的数据表空间*/
已选择13行。
3.创建用户并指定表空间
1 2 3 4 5 6 7
CREATE USER duke /* 用户名 */ IDENTIFIED BY duke /* 密码 */ DEFAULT TABLESPACE duke_data /* 表空间 */ /*给用户指定默认表空间*/ TEMPORARY TABLESPACE duke_temp /* 临时表空间 */ /*默认临时表空间*/ ;
用户已创建。
4.修改用户默认和临时表空间
为了方便以后数据的备份和恢复，仅仅将有价值的数据做备份可以提高效率和节省空间。
修改用户默认表空间：
1 2
ALTER USER duke DEFAULT TABLESPACE USERS; /*duke 用户名，USERS 默认（DEFAULT）表空间*/ 用户已更改。
修改用户临时表空间：
1
ALTER USER duke temporary TABLESPACE TEMP; /*修改临时（temporary）表空间*/
注意：普通用户没有修改默认表空间的权限，但是可以通过授权来实现普通用户也能够修改默认表空间。
1 2 3 4 5 6
SQL> show user; USER 为 "SYS" SQL> select DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users where username='SYSTEM'; DEFAULT_TABLESPACE TEMPORARY_TABLESPACE ------------------------------ ------------------------------ SYSTEM TEMP
5.删除空间表
—-删除非空表空间，包含物理文件
1 2
DROP TABLESPACE duke_data INCLUDING CONTENTS AND DATAFILES; 表空间已经删除。
删除表空间后，原先指向该表空间的用户仍然默认的空间位置， 需要通过alter user 命令将用户的表空间指向一个有效的表空间。
删除表空间方法总结：
1 2 3 4 5 6 7 8 9 10 11 12 13
--删除空的表空间，但是不包含物理文件 drop tablespace duke_temp --删除非空表空间，但是不包含物理文件 drop tablespace duke_temp including contents; --删除空表空间，包含物理文件 drop tablespace duke_temp including datafiles; --删除非空表空间，包含物理文件 DROP TABLESPACE duke_data INCLUDING CONTENTS AND DATAFILES; --如果其他表空间中的表有外键等约束关联到了本表空间中的表的字段，就要加上CASCADE CONSTRAINTS drop tablespace duke_temp including contents and datafiles CASCADE CONSTRAINTS;
--说明： 删除了duke，只是删除了该duke下的schema objects，是不会删除相应的表空间（tablespace）的。 drop user duke cascade； --duke用户
SQL> select * from v$tablespace; /*包含从控制文件中获取的表空间名称和编号信息*/
TS# NAME INC BIG FLA ENC ---------- ------------------------------ --- --- --- --- 0 SYSTEM YES NO YES 1 SYSAUX YES NO YES 2 UNDOTBS1 YES NO YES 4 USERS YES NO YES 3 TEMP NO NO YES 6 EXAMPLE YES NO YES 8 EBANK_TEMP NO NO YES 9 ECIF_DATA YES NO YES 10 ECIF_INDEX YES NO YES 11 EIP_DATA YES NO YES 13 YANG YES NO YES
TS# NAME INC BIG FLA ENC ---------- ------------------------------ --- --- --- --- 14 DUKE_TEMP NO NO YES 15 DUKE_DATA YES NO YES
已选择13行。
10.查看表空间下所有用户
1 2 3 4 5 6 7
SQL> select distinct s.owner from dba_segments s where s.tablespace_name ='USERS'; /*USERS表空间下所有用户*/
OWNER ------------------------------ SCOTT SYSTEM OE
11.查看当前用户的表属于哪个表空间
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
SQL> select table_name,tablespace_name from user_all_tables; /*查看当前用户的表属于哪些表空间*/
TABLE_NAME TABLESPACE_NAME ------------------------------ ------------------------------ MLOG$ SYSTEM SLOG$ SYSTEM RGROUP$ SYSTEM RGCHILD$ SYSTEM SYS_IOT_OVER_5140 SYSAUX RULE_SET_PR$ SYS_IOT_OVER_5146 SYSAUX RULE_SET_IOT$ SYS_IOT_OVER_5150 SYSAUX RULE_SET_ROP$ SYS_IOT_OVER_5387 SYSAUX
12.查看用户的默认表空间和临时表空间
1 2 3 4 5 6
查看SYSTEM用户的默认表空间和临时表空间 SQL> select DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users where username='SYSTEM';
DEFAULT_TABLESPACE TEMPORARY_TABLESPACE ------------------------------ ------------------------------ SYSTEM TEMP
SQL> desc dba_data_files; /*数据字典dba_data_file用于存放数据文件的属性。*/ 名称 是否为空? 类型 ----------------------------------------- -------- ---------------------------- FILE_NAME VARCHAR2(513) FILE_ID NUMBER TABLESPACE_NAME VARCHAR2(30) BYTES NUMBER BLOCKS NUMBER STATUS VARCHAR2(9) RELATIVE_FNO NUMBER AUTOEXTENSIBLE VARCHAR2(3) MAXBYTES NUMBER MAXBLOCKS NUMBER INCREMENT_BY NUMBER USER_BYTES NUMBER USER_BLOCKS NUMBER ONLINE_STATUS VARCHAR2(7)
SQL> select file_name from dba_data_files where tablespace_name='DUKE_DATA';
drop tablespace DUKE_DATA including contents; /*删除表空间*/ 表空间已更改。 select file_name from dba_data_fileS where tablespace_name='DUKE_DATA'; /*查看表空间*/ 没有此DUKE_DATA表空间。
SQL> DESC DBA_TS_QUOTAS; 名称 是否为空? 类型 ----------------------------------------- -------- ---------------------------- TABLESPACE_NAME NOT NULL VARCHAR2(30) USERNAME NOT NULL VARCHAR2(30) BYTES NUMBER MAX_BYTES NUMBER BLOCKS NUMBER MAX_BLOCKS NUMBER DROPPED VARCHAR2(3)
注意：若MAX_BYTES=-1表示没有配额限制
三、oralce 数据库设置表空间的只读/读写模式
意义：主要是为了确保数据完整性。
如:数据备份与还原操作、历史数据的完整性保护。可以将表空间设置只读模式。
只读：是表空间中不能进行INSERT（插入）、UPDATE（修改）、DELETE（删除）等操作。
需要dba权限的用户登录才可以进行一下操作：
1.打开cmd输入,进入sqlplus和连接数据库及登录sys。
1
sqlplus sys/123456@ZhengJiaAo:1521/ORCL as sysdba
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
SQL> select tablespace_name,status from dba_tablespaces; //查看所有表空间状态，读写（ONLINE）
TABLESPACE_NAME STATUS ------------------------------ --------- SYSTEM ONLINE SYSAUX ONLINE UNDOTBS1 ONLINE TEMP ONLINE USERS ONLINE EXAMPLE ONLINE EBANK_TEMP ONLINE ECIF_DATA ONLINE ECIF_INDEX ONLINE EIP_DATA ONLINE YANG ONLINE
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS ------------------------------ --------- SYSTEM ONLINE SYSAUX ONLINE UNDOTBS1 ONLINE TEMP ONLINE USERS ONLINE EXAMPLE ONLINE EBANK_TEMP ONLINE ECIF_DATA ONLINE ECIF_INDEX ONLINE EIP_DATA ONLINE YANG ONLINE
已选择11行。
四、Schema和用户
Oracle的方案（Schema）和用户（User）的区别
Schema定义：
A schema is a collection of database objects (used by a user). Schema objects are the logical structures that directly refer to the database’s data. A user is a name defined in the database that can connect to and access objects。 Schemas and users help database administrators manage database security
访问一个表时，没有指明该表属于哪一个schema中的，系统就会自动给我们在表上加上缺省的sheman名. 访问scott用户下的emp表，通过select from emp; 其实，这sql语句的完整写法为select from scott.emp; 在数据库中一个对象的完整名称为schema.object，而不属user.object.
