文档介绍：Oracle 创建表空间、创建用户以及授权、查看权限一、创建临时表空间# sqlplus / as sysdba; sql> CREATE TEMPORARY TABLESPACE test_temp TEMPFILE '/opt/oradata/' SIZE 32M AUTOEXTEND ON NEXT 32M MAXSIZE 2048M EXTENT MANAGEMENT LOCAL; 二、创建用户表空间 sql> CREATE TABLESPACE test_data LOGGING DATAFILE '/opt/oradata/' 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/' 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