SQL> select * from dba_roles where role='DBA'; ROLE PASSWORD_REQUIRED ------------------------------ ----------------- DBA NOdba_roles是查詢所有系統角色,從結果上看DBA是系統角色,不是權限,同樣dba_roles中沒有sysdba和sysoper說明這兩個不是角色。
SQL> select * from dba_role_privs where granted_role='DBA' and (grantee='SYS' or grantee='SYSTEM'); GRANTEE GRANTED_ROLE ADMIN_OPTION DEFAULT_ROLE ------------------------------ ------------------------------ ------------ ------------ SYS DBA YES YES SYSTEM DBA YES YESDBA_ROLE_PRIVS是查詢所有用戶或角色對應的角色,從結果上看sys和system都有DBA的角色。
SQL> select * from v$pwfile_users; USERNAME SYSDBA SYSOPER ------------------------------ ------ ------- SYS TRUE TRUE
SQL> select * from system_privilege_map; PRIVILEGE NAME PROPERTY ---------- ---------------------------------------- ---------- -3 ALTER SYSTEM 0 -4 AUDIT SYSTEM 0 -5 CREATE SESSION 0 -6 ALTER SESSION 0 -7 RESTRICTED SESSION 0 -10 CREATE TABLESPACE 0 -11 ALTER TABLESPACE 0 -12 MANAGE TABLESPACE 0 -13 DROP TABLESPACE 0 -15 UNLIMITED TABLESPACE 0 -20 CREATE USER 0 -21 BECOME USER 0 -22 ALTER USER 0 -23 DROP USER 0 -30 CREATE ROLLBACK SEGMENT 0 -31 ALTER ROLLBACK SEGMENT 0 -32 DROP ROLLBACK SEGMENT 0 -40 CREATE TABLE 0 -41 CREATE ANY TABLE 0 -42 ALTER ANY TABLE 0 PRIVILEGE NAME PROPERTY ---------- ---------------------------------------- ---------- -43 BACKUP ANY TABLE 0 -44 DROP ANY TABLE 0 -45 LOCK ANY TABLE 0 -46 COMMENT ANY TABLE 0 -47 SELECT ANY TABLE 0 -48 INSERT ANY TABLE 0 -49 UPDATE ANY TABLE 0 -50 DELETE ANY TABLE 0 -60 CREATE CLUSTER 0 -61 CREATE ANY CLUSTER 0 -62 ALTER ANY CLUSTER 0 -63 DROP ANY CLUSTER 0 -71 CREATE ANY INDEX 0 -72 ALTER ANY INDEX 0 -73 DROP ANY INDEX 0 -80 CREATE SYNONYM 0 -81 CREATE ANY SYNONYM 0 -82 DROP ANY SYNONYM 0 -83 SYSDBA 0 -84 SYSOPER 0 -85 CREATE PUBLIC SYNONYM 0 PRIVILEGE NAME PROPERTY ---------- ---------------------------------------- ---------- -86 DROP PUBLIC SYNONYM 0 -90 CREATE VIEW 0 -91 CREATE ANY VIEW 0 -92 DROP ANY VIEW 0 -105 CREATE SEQUENCE 0 -106 CREATE ANY SEQUENCE 0 -107 ALTER ANY SEQUENCE 0 -108 DROP ANY SEQUENCE 0 -109 SELECT ANY SEQUENCE 0 -115 CREATE DATABASE LINK 0 -120 CREATE PUBLIC DATABASE LINK 0 -121 DROP PUBLIC DATABASE LINK 0 -125 CREATE ROLE 0 -126 DROP ANY ROLE 0 -127 GRANT ANY ROLE 0 -128 ALTER ANY ROLE 0 -130 AUDIT ANY 0 -135 ALTER DATABASE 0 -138 FORCE TRANSACTION 0 -139 FORCE ANY TRANSACTION 0 -140 CREATE PROCEDURE 0 PRIVILEGE NAME PROPERTY ---------- ---------------------------------------- ---------- -141 CREATE ANY PROCEDURE 0 -142 ALTER ANY PROCEDURE 0 -143 DROP ANY PROCEDURE 0 -144 EXECUTE ANY PROCEDURE 0 -151 CREATE TRIGGER 0 -152 CREATE ANY TRIGGER 0 -153 ALTER ANY TRIGGER 0 -154 DROP ANY TRIGGER 0 -160 CREATE PROFILE 0 -161 ALTER PROFILE 0 -162 DROP PROFILE 0 -163 ALTER RESOURCE COST 0 -165 ANALYZE ANY 0 -167 GRANT ANY PRIVILEGE 0 -172 CREATE MATERIALIZED VIEW 0 -173 CREATE ANY MATERIALIZED VIEW 0 -174 ALTER ANY MATERIALIZED VIEW 0 -175 DROP ANY MATERIALIZED VIEW 0 -177 CREATE ANY DIRECTORY 0 -178 DROP ANY DIRECTORY 0 -180 CREATE TYPE 0 PRIVILEGE NAME PROPERTY ---------- ---------------------------------------- ---------- -181 CREATE ANY TYPE 0 -182 ALTER ANY TYPE 0 -183 DROP ANY TYPE 0 -184 EXECUTE ANY TYPE 0 -186 UNDER ANY TYPE 0 -188 CREATE LIBRARY 0 -189 CREATE ANY LIBRARY 0 -190 ALTER ANY LIBRARY 0 -191 DROP ANY LIBRARY 0 -192 EXECUTE ANY LIBRARY 0 -200 CREATE OPERATOR 0 -201 CREATE ANY OPERATOR 0 -202 ALTER ANY OPERATOR 0 -203 DROP ANY OPERATOR 0 -204 EXECUTE ANY OPERATOR 0 -205 CREATE INDEXTYPE 0 -206 CREATE ANY INDEXTYPE 0 -207 ALTER ANY INDEXTYPE 0 -208 DROP ANY INDEXTYPE 0 -209 UNDER ANY VIEW 0 -210 QUERY REWRITE 0 PRIVILEGE NAME PROPERTY ---------- ---------------------------------------- ---------- -211 GLOBAL QUERY REWRITE 0 -212 EXECUTE ANY INDEXTYPE 0 -213 UNDER ANY TABLE 0 -214 CREATE DIMENSION 0 -215 CREATE ANY DIMENSION 0 -216 ALTER ANY DIMENSION 0 -217 DROP ANY DIMENSION 0 -218 MANAGE ANY QUEUE 1 -219 ENQUEUE ANY QUEUE 1 -220 DEQUEUE ANY QUEUE 1 -222 CREATE ANY CONTEXT 0 -223 DROP ANY CONTEXT 0 -224 CREATE ANY OUTLINE 0 -225 ALTER ANY OUTLINE 0 -226 DROP ANY OUTLINE 0 -227 ADMINISTER RESOURCE MANAGER 1 -228 ADMINISTER DATABASE TRIGGER 0 -233 MERGE ANY VIEW 0 -234 ON COMMIT REFRESH 0 -235 EXEMPT ACCESS POLICY 0 -236 RESUMABLE 0 PRIVILEGE NAME PROPERTY ---------- ---------------------------------------- ---------- -237 SELECT ANY DICTIONARY 0 -238 DEBUG CONNECT SESSION 0 -241 DEBUG ANY PROCEDURE 0 -243 FLASHBACK ANY TABLE 0 -244 GRANT ANY OBJECT PRIVILEGE 0 -245 CREATE EVALUATION CONTEXT 1 -246 CREATE ANY EVALUATION CONTEXT 1 -247 ALTER ANY EVALUATION CONTEXT 1 -248 DROP ANY EVALUATION CONTEXT 1 -249 EXECUTE ANY EVALUATION CONTEXT 1 -250 CREATE RULE SET 1 -251 CREATE ANY RULE SET 1 -252 ALTER ANY RULE SET 1 -253 DROP ANY RULE SET 1 -254 EXECUTE ANY RULE SET 1 -255 EXPORT FULL DATABASE 0 -256 IMPORT FULL DATABASE 0 -257 CREATE RULE 1 -258 CREATE ANY RULE 1 -259 ALTER ANY RULE 1 -260 DROP ANY RULE 1 PRIVILEGE NAME PROPERTY ---------- ---------------------------------------- ---------- -261 EXECUTE ANY RULE 1 -262 ANALYZE ANY DICTIONARY 0 -263 ADVISOR 0 -264 CREATE JOB 0 -265 CREATE ANY JOB 0 -266 EXECUTE ANY PROGRAM 0 -267 EXECUTE ANY CLASS 0 -268 MANAGE SCHEDULER 0 -269 SELECT ANY TRANSACTION 0 -270 DROP ANY SQL PROFILE 0 -271 ALTER ANY SQL PROFILE 0 -272 ADMINISTER SQL TUNING SET 0 -273 ADMINISTER ANY SQL TUNING SET 0 -274 CREATE ANY SQL PROFILE 0 -275 EXEMPT IDENTITY POLICY 0 -276 MANAGE FILE GROUP 1 -277 MANAGE ANY FILE GROUP 1 -278 READ ANY FILE GROUP 1 -279 CHANGE NOTIFICATION 0 -280 CREATE EXTERNAL JOB 0 166 rows selectedsystem_privilege_map是查詢所有系統權限,總共有166個系統權限,但沒有sysdba和sysoper。V$PWFILE_USERS是查詢所有擁有sysdba和sysoper權限的用戶,系統默認只有sys有sysdba和sysoper權限。結合可以看出sysdba和sysoper數據庫管理的特殊權限是不存在數據庫中的,接下來再看看sysdba和sysoper權限的區別(網上有很多我只粘來一張圖片)。
導出具體分為:導出表、導出方案、導出數據庫三中方式(可以在cmd下導出也可以在oracle的bin目錄下exp.exe下導出)。導出使用exp命令來完成,學用的選項有: userid:指定執行導出操作的用戶名,口令,連接字符串。 table:指定導出操作的表。 owner:指定執行導出操作的方案。 full=y:指定執行導出操作的數據庫。 rows:指定執行導出操作是否導出表中的數據。 inctype:指定執行導出操作的增量類型。 file:指定導出文件名。 實例:(導出表)exp userid=scott/scott@orcl tables=(emp,dept) file=d:\data.dmp; (導出方案)exp userid=scott/scott@orcl owner=(scott) file=d:\data.dmp; (導出數據庫)exp userid=system/orclt@orcl full=y inctype=complete file=d:\data.dmp;
(2)導入
導入就是使用工具import將文件中的對象和數據導入到數據庫中,但導入要使用的文件必須是exp所導出的文件。
imp常用的選項有
userid:指定執行導出操作的用戶名,口令,連接字符串。 table:指定導出操作的表。 fromuser:指定源用戶。 touser:指定目標用戶。 file:指定導入文件名。 full=y:指定執行導入整個文件。 rows:指定執行導出操作是否導出表中的數據。 inctype:指定執行導出操作的增量類型。 ignore:如果表存在,則只導入數據。