oracle資料表空間與使用者建立

標籤：oracle   空間   使用者   product   

-------------------------建立資料表空間與使用者

建立暫存資料表空間

CREATE TEMPORARY TABLESPACE test_temp
650) this.width=650;" src="http://www.blogjava.net/Images/OutliningIndicators/None.gif" /> TEMPFILE ‘/u01/oracle/product/10.2.0/oradata/orcl/test_temp01.dbf ‘
650) this.width=650;" src="http://www.blogjava.net/Images/OutliningIndicators/None.gif" /> SIZE 32M
650) this.width=650;" src="http://www.blogjava.net/Images/OutliningIndicators/None.gif" /> AUTOEXTEND ON
650) this.width=650;" src="http://www.blogjava.net/Images/OutliningIndicators/None.gif" /> NEXT 32M MAXSIZE 2048M
650) this.width=650;" src="http://www.blogjava.net/Images/OutliningIndicators/None.gif" /> EXTENT MANAGEMENT LOCAL;

建立使用者資料表空間

CREATE TABLESPACE test_data
650) this.width=650;" src="http://www.blogjava.net/Images/OutliningIndicators/None.gif" /> LOGGING
650) this.width=650;" src="http://www.blogjava.net/Images/OutliningIndicators/None.gif" /> DATAFILE ‘/u01/oracle/product/10.2.0/oradata/orcl/my_data01.dbf ‘
650) this.width=650;" src="http://www.blogjava.net/Images/OutliningIndicators/None.gif" /> SIZE 32M
650) this.width=650;" src="http://www.blogjava.net/Images/OutliningIndicators/None.gif" /> AUTOEXTEND ON
650) this.width=650;" src="http://www.blogjava.net/Images/OutliningIndicators/None.gif" /> NEXT 32M MAXSIZE 2048M
650) this.width=650;" src="http://www.blogjava.net/Images/OutliningIndicators/None.gif" /> EXTENT MANAGEMENT LOCAL;

建立使用者並制定資料表空間

CREATE USER username IDENTIFIED BY password
DEFAULT TABLESPACE TEST_DATA
TEMPORARY TABLESPACE TEST_TEMP;

--------------------使用者賦權與查看使用者權限

給使用者授予許可權

GRANT
650) this.width=650;" src="http://www.blogjava.net/Images/OutliningIndicators/None.gif" /> 　　 CREATE SESSION, CREATE ANY TABLE , CREATE ANY VIEW , CREATE ANY INDEX , CREATE ANY PROCEDURE ,
650) this.width=650;" src="http://www.blogjava.net/Images/OutliningIndicators/None.gif" /> 　　 ALTER ANY TABLE , ALTER ANY PROCEDURE ,
650) this.width=650;" src="http://www.blogjava.net/Images/OutliningIndicators/None.gif" /> 　　 DROP ANY TABLE , DROP ANY VIEW , DROP ANY INDEX , DROP ANY PROCEDURE ,
650) this.width=650;" src="http://www.blogjava.net/Images/OutliningIndicators/None.gif" /> 　　 SELECT ANY TABLE , INSERT ANY TABLE , UPDATE ANY TABLE , DELETE ANY TABLE
650) this.width=650;" src="http://www.blogjava.net/Images/OutliningIndicators/None.gif" /> 　　 TO username;

將role這個角色授與username,也就是說，使username這個使用者可以管理和使用role所擁有的資源

650) this.width=650;" src="http://www.blogjava.net/Images/OutliningIndicators/None.gif" />GRANT role TO username;

查看所有使用者

SELECT * FROM DBA_USERS;
SELECT * FROM ALL_USERS;
SELECT * FROM USER_USERS;

查看使用者系統許可權

SELECT * FROM DBA_SYS_PRIVS;
SELECT * FROM USER_SYS_PRIVS;

查看使用者物件或角色許可權

SELECT * FROM DBA_TAB_PRIVS;
SELECT * FROM ALL_TAB_PRIVS;
SELECT * FROM USER_TAB_PRIVS;650) this.width=650;" src="http://www.blogjava.net/Images/OutliningIndicators/None.gif" />

查看所有角色

SELECT * FROM DBA_ROLES;

查看使用者或角色所擁有的角色

SELECT * FROM DBA_ROLE_PRIVS;
SELECT * FROM USER_ROLE_PRIVS;

本文出自 “努力奔向前方” 部落格，請務必保留此出處http://liucb.blog.51cto.com/3230681/1582387

oracle資料表空間與使用者建立