连接 Oracle 数据库
[root@localhost ~]
[oracle@dsjtest3 ~]$ sqlplus / as sysdba
SQL> SELECT tablespace_name, file_id, file_name, round(bytes / (1024 * 1024), 0) FROM dba_data_files ;
表空间创建
SQL> CREATE TABLESPACE test_create_table_by_select DATAFILE '/home/oracle/oradata/orcl/test_create_table_by_select.dbf' SIZE 50M AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL;
SQL> CREATE TEMPORARY TABLESPACE test_del_dbf TEMPFILE '/home/oracle/oradata/orcl/test_del_dbf.dbf' SIZE 50M AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL;
SQL> CREATE TABLESPACE TEST DATAFILE '/home/oracle/oradata/orcl/test.dbf' size 10M AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED default compress;
SQL> SELECT tablespace_name,def_tab_compression FROM user_tablespaces WHERE tablespace_name = 'TEST';
TABLESPACE_NAME DEF_TAB_
TEST ENABLED
用户创建 && 赋权
SQL> CREATE USER lsr IDENTIFIED BY lsr DEFAULT TABLESPACE TEST ;
User created.
SQL> GRANT CONNECT, RESOURCE, DBA TO lsr ;
Grant succeeded.
SQL> ALTER USER jhmktest QUOTA UNLIMITED ON TEST1 ;
SQL> GRANT SELECT, UPDATE, INSERT, DELETE ON TEST1.TEST TO lsr ;
用户权限配置
SQL> ALTER USER lsr IDENTIFIED BY '123456' ;
SQL> DROP USER lsr ;
SQL> GRANT CONNECT, RESOURCE, DBA TO lsr ;
SQL> REVOKE CONNECT, RESOURCE, DBA FROM lsr ;
SQL> SELECT * FROM dba_role_privs WHERE GRANTED_ROLE= 'DBA' ;
SQL> SELECT username FROM dba_users WHERE account_status='OPEN';
SQL> SELECT * FROM dba_role_privs WHERE GRANTEE= 'LSR';
SQL> SELECT * FROM dba_sys_privs WHERE GRANTEE='LSR';
参考
Oracle数据库中,当前用户操作其他表空间的table
Oracle数据库DBA权限回收操作参考
Oracle 用户、角色、权限的创建、删除、更改
Oracle 数据库 DBA 权限回收操作参考
Oracle 压缩表、压缩表空间操作