以下的文章主要是通過相關的代碼來引出Oracle創建表空間,以及創建用戶的相關的賦權限,還有如何查看相關的權限與Oracle創建臨時表空間的相關內容的介紹,以下就是具體方案的描述,希望在你今後的學習中會有所幫助。
Sql代碼
- CREATE TEMPORARY TABLESPACE test_temp
- TEMPFILE 'C:\Oracle\product\10.1.0\oradata\orcl\test_temp01.dbf'
- SIZE 32M
- AUTOEXTEND ON
- NEXT 32M MAXSIZE 2048M
- EXTENT MANAGEMENT LOCAL;
- CREATE TEMPORARY TABLESPACE test_temp
- TEMPFILE 'C:\Oracle\product\10.1.0\oradata\orcl\test_temp01.dbf'
- SIZE 32M
- AUTOEXTEND ON
- NEXT 32M MAXSIZE 2048M
EXTENT MANAGEMENT LOCAL; Oracle創建用戶表空間
Sql代碼
- CREATE TABLESPACE test_data
- LOGGING
- DATAFILE 'C:\Oracle\PRODUCT\10.1.0\ORADATA\ORCL\TEST_DATA01.DBF'
- SIZE 32M
- AUTOEXTEND ON
- NEXT 32M MAXSIZE 2048M
- EXTENT MANAGEMENT LOCAL;
- CREATE TABLESPACE test_data
- LOGGING
- DATAFILE 'C:\Oracle\PRODUCT\10.1.0\ORADATA\ORCL\TEST_DATA01.DBF'
- SIZE 32M
- AUTOEXTEND ON
- NEXT 32M MAXSIZE 2048M
EXTENT MANAGEMENT LOCAL;刪除表空間(包括數據物理文件)
Sql代碼
- drop tablespace egov0 including contents and datafiles;
- drop tablespace egov0 including contents and datafiles;
Oracle創建用戶並定制空間
Sql代碼
- CREATE USER username IDENTIFIED BY passWord
- DEFAULT TABLESPACE TEST_DATA
- TEMPORARY TABLESPACE TEST_TEMP;
- CREATE USER username IDENTIFIED BY passWord
- DEFAULT TABLESPACE TEST_DATA
- TEMPORARY TABLESPACE TEST_TEMP;
給用戶賦權
Sql代碼
- GRANT
- CREATE SESSION, CREATE ANY TABLE, CREATE ANY VIEW ,CREATE ANY INDEX, CREATE ANY PROCEDURE,
- ALTER ANY TABLE, ALTER ANY PROCEDURE,
- DROP ANY TABLE, DROP ANY VIEW, DROP ANY INDEX, DROP ANY PROCEDURE,
- SELECT ANY TABLE, INSERT ANY TABLE, UPDATE ANY TABLE, DELETE ANY TABLE
- TO username;
- GRANT
- CREATE SESSION, CREATE ANY TABLE, CREATE ANY VIEW ,CREATE ANY INDEX, CREATE ANY PROCEDURE,
- ALTER ANY TABLE, ALTER ANY PROCEDURE,
- DROP ANY TABLE, DROP ANY VIEW, DROP ANY INDEX, DROP ANY PROCEDURE,
- SELECT ANY TABLE, INSERT ANY TABLE, UPDATE ANY TABLE, DELETE ANY TABLE
- TO username;
查看用戶權限
查看所有用戶
Sql代碼
- SELECT * FROM DBA_USERS;
- SELECT * FROM ALL_USERS;
- SELECT * FROM USER_USERS;
- SELECT * FROM DBA_USERS;
- SELECT * FROM ALL_USERS;
- SELECT * FROM USER_USERS;
查看用戶系統權限
Sql代碼
- SELECT * FROM DBA_SYS_PRIVS;
- SELECT * FROM USER_SYS_PRIVS;
- SELECT * FROM DBA_SYS_PRIVS;
- SELECT * FROM USER_SYS_PRIVS;
查看用戶對象或角色權限
Sql代碼
- SELECT * FROM DBA_TAB_PRIVS;
- SELECT * FROM ALL_TAB_PRIVS;
- SELECT * FROM USER_TAB_PRIVS;
- SELECT * FROM DBA_TAB_PRIVS;
- SELECT * FROM ALL_TAB_PRIVS;
SELECT * FROM USER_TAB_PRIVS;查看所有角色
Sql代碼
- SELECT * FROM DBA_ROLES;
SELECT * FROM DBA_ROLES; 查看用戶或角色所擁有的角色
Sql代碼
- SELECT * FROM DBA_ROLE_PRIVS;
- SELECT * FROM USER_ROLE_PRIVS;
- SELECT * FROM DBA_ROLE_PRIVS;
- SELECT * FROM USER_ROLE_PRIVS;
刪除表空間自建的所有表
Sql代碼
- declare
- vsql varchar2(200);
- cursor c1 is
- select 'drop table '||table_name||' cascade constraints' v_name
- from user_tables where tablespace_name='表空間名字';
- BEGIN
- for i in c1 loop
- vsql:=i.v_name;
- execute immediate vsql;
- end loop;
- end;