oracle创建表空间用户及授权查看权限

Oracle 创建表空间、创建用户以及授

权、查看权限

一、创建临时表空间

# sqlplus / as sysdba;

sql> CREATE TEMPORARY TABLESPACE test_temp

TEMPFILE '/opt/oradata/test_temp.dbf'

SIZE 32M

AUTOEXTEND ON

NEXT 32M MAXSIZE 2048M

EXTENT MANAGEMENT LOCAL;

二、创建用户表空间

sql> CREATE TABLESPACE test_data

LOGGING

DATAFILE '/opt/oradata/test_data.dbf'

SIZE 32M

AUTOEXTEND ON

NEXT 32M MAXSIZE 2048M

EXTENT MANAGEMENT LOCAL;

sql> select

tablespace_name,block_size,extent_management,segment_space_management from dba_tablespaces

where tablespce_name like 'USER%';

三、还原表空间

sql> create undo tablespace user01_undo

datafile '/u01/oracle/app/oracle/user01_undo.dbf'

size 20M;

sql> select

tablespace_name,block_size,extent_management,segment_space_management from dba_tablespaces

from tablespace_name like 'user01_%';

sql> select tablespace_name,status,contents

from dba_tablespaces

from tablespace_name like 'user01_%'; 四、创建用户并制定表空间

sql> CREATE USER username IDENTIFIED BY password DEFAULT TABLESPACE TEST_DATA

TEMPORARY TABLESPACE TEST_TEMP;

sql> GRANT role TO username;

五、查看所有用户

sql> SELECT * FROM DBA_USERS;

sql> SELECT * FROM ALL_USERS;

sql> SELECT * FROM USER_USERS;

六、查看用户对象或角色权限

sql> SELECT * FROM DBA_TAB_PRIVS;

sql> SELECT * FROM ALL_TAB_PRIVS;

sql> SELECT * FROM USER_TAB_PRIVS;

七、查看所有角色

sql> SELECT * FROM DBA_ROLES;

八、查看用户或角色所拥有的角色sql> SELECT * FROM DBA_ROLE_PRIVS;

sql> SELECT * FROM USER_ROLE_PRIVS; 九、表空间脱机

sql> select tablespace_name,status,contents from dba_tablespaces

where tablespce_name like 'user_%'; sql> col name for a55

sql> select file#,name,status

from v$datafile

where file# >=8;

设置脱机状态

sql> alter tablespace user01 offline;

sql> select tablespace_name,status,contents from dba_tablespaces

where tablespce_name like 'user01_%'; sql> select file#,name,status

from v$datafile

where file# >= 8;

设置联机状态

sql> alter tablespace user01 online;

十一、只读表空间

设置只读表空间

sql> alter tablespace user01 read only;

sql> select tablespce_name,status,contents

from dba_tablespaces

where tablespace_name like 'user_%';

可读可写

sql> alter tablespace user01 read write;

sql> select tablespace_name,status,contents

from dba_tablespace

where tablespace_name like 'user%';

十二、改变表空间的存储设置

sql> alter tablespace user01 minimum extent 100K;

sql> alter tablespace user01

default storage ( initial 100 k next 100 k maxextents 200);

sql> set line 120

sql> col tablespace_name for a15

sql> select tablespace_name,initial_extent,next_extent,

max_extents,pct_increase,min_extents

from dba_tablespaces

where tablespace_name like 'user01%';