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

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