Oracle Directory（目錄）介紹 |
您所在的位置：网站首页 › 藻油评测 › Oracle Directory（目錄）介紹 |
Oracle Directory（目錄）介紹
|
Oracle Directory（目錄）介紹
Oracle Directory（目錄）可以讓使用者在Oracle資料庫中靈活地對檔案進行讀寫操作，極大地提高了Oracle的易用性和可擴充套件性。其語法如下所示： CREATE [OR REPLACE] DIRECTORY DIRECTORY AS 'PATHNAME'; 建立和刪除Directory的許可權為：CREATE ANY DIRECTORY、DROP ANY DIRECTORY。賦予普通使用者使用Directory的許可權包括READ、WRITE、EXECUTE，也可以直接寫ALL，如下所示： GRANT READ,WRITE,EXECUTE ON DIRECTORY EXP_DIR_LHR TO LHR; GRANT ALL ON DIRECTORY EXP_DIR_LHR TO LHR; 需要注意的是，在建立Directory資料庫物件時對應的路徑不存在也不會報錯，Oracle資料庫並不會到作業系統上檢驗路徑的存在性，只有在使用時才會校驗，因此在建立Directory時謹記對應的路徑的真實存在性，否則可能會報ORA-29913或ORA-29400的錯誤。 下面給出一個建立Oracle Directory的例子： [email protected]> CREATE OR REPLACE DIRECTORY EXP_DIR_LHR AS '/tmp'; Directory created. [email protected]> GRANT READ,WRITE ON DIRECTORY EXP_DIR_LHR TO LHR; Grant succeeded. [email protected]> COL DIRECTORY_PATH FORMAT A20 [email protected]> SELECT DIRECTORY_NAME ,DIRECTORY_PATH FROM DBA_DIRECTORIES WHERE DIRECTORY_NAME='EXP_DIR_LHR'; DIRECTORY_NAME DIRECTORY_PATH ------------------------------ -------------------- EXP_DIR_LHR /tmp MOS上對DIRECTORY的解釋(266875.1)：
(1)、基於服務端 vs 基於客戶端 DIRECTORY變數指出了expdp匯出資料泵或impdp匯入資料泵將dump檔案、log檔案以及SQL檔案(僅適用於impdp)寫到什麼路徑。 因為匯出資料泵和匯入資料泵都是基於服務端的，不是基於客戶端的，因此輸出檔案的路徑都是相對於服務端目錄的路徑。資料泵要求將目錄路徑作為一個目錄物件。一個目錄物件將檔案系統的一個目錄路徑對映為一個名稱。 (2)、如何建立一個目錄物件？ 為了建立目錄，必須具有DBA角色或者賦予了CREATE ANY DIRECTORY許可權。 示例： Window平臺 CONNECT system/manager CREATE OR REPLACE DIRECTORY my_dir as 'D:\DataPump'; CREATE OR REPLACE DIRECTORY my_logdir as 'E:\logs'; GRANT read, write ON DIRECTORY my_dir TO scott; GRANT read, write ON DIRECTORY my_logdir TO scott; Unix平臺 CONNECT system/manager GRANT CREATE ANY DIRECTORY TO scott; CONNECT scott/tiger CREATE OR REPLACE DIRECTORY my_dir as '/usr/DataPump'; CREATE OR REPLACE DIRECTORY my_logdir as '/usr/logs'; 如果普通使用者被賦予了CREATE ANY DIRECTORY許可權，那麼使用者就自動具備目錄的READ和WRITE許可權。 注意：CREATE DIRECTORY語句不會建立磁碟的真實目錄，如果目錄是無效的，資料泵作業會報錯： ORA-39002: invalid operation ORA-39070: Unable to open the log file. ORA-29283: invalid file operation ORA-06512: at "SYS.UTL_FILE", line 475 ORA-29283: invalid file operation (3)、如何查詢可用的目錄？ 可以使用如下SQL查詢具有READ和WRITE許可權的目錄： SET lines 80 COL grantee FORMAT a20 COL privilege FORMAT a10 SELECT directory_name, grantee, privilege FROM user_tab_privs t, all_directories d WHERE t.table_name(+)=d.directory_name ORDER BY 1,2,3; DIRECTORY_NAME GRANTEE PRIVILEGE ------------------------------ -------------------- ---------- DATA_PUMP_DIR EXP_FULL_DATABASE READ DATA_PUMP_DIR EXP_FULL_DATABASE WRITE DATA_PUMP_DIR IMP_FULL_DATABASE READ DATA_PUMP_DIR IMP_FULL_DATABASE WRITE MY_DIR SCOTT READ MY_DIR SCOTT WRITE MY_DIR SYSTEM READ MY_DIR SYSTEM WRITE MY_LOGDIR SCOTT READ MY_LOGDIR SCOTT WRITE MY_LOGDIR SYSTEM READ MY_LOGDIR SYSTEM WRITE ... (4)、需要的作業系統許可權。 對目錄物件的READ或WRITE許可權僅僅表示Oracle將會替你讀或寫這個檔案。你並沒有訪問Oracle以外檔案的許可權，除非你具備合適的作業系統許可權。 (5)、資料泵如何決定檔案的路徑 5.1 如果目錄物件是檔案標示符的一部分，那麼目錄物件指定的路徑就需要使用。在目錄MY_DIR建立dump檔案的示例： > expdp scott/tiger DUMPFILE=my_dir:expdp_s.dmp NOLOGFILE=Y 5.2 如果目錄物件不代表一個檔案，那麼就需要使用DIRECTORY變數命名的目錄物件。目錄MY_DIR中建立dump檔案，目錄MY_DIR_LOG中建立日誌檔案的示例： > expdp scott/tiger DIRECTORY=my_dir DUMPFILE=expdp_s.dmp \ LOGFILE=my_logdir:expdp_s.log 5.3 如果沒有明確目錄物件，也沒有以DIRECTORY變數命名的目錄物件，那麼環境變數DATA_PUMP_DIR將會使用。環境變數是在在執行匯出和匯入資料泵應用的客戶端系統中使用作業系統命令定義的，分配給基於客戶端環境變數的取值必須和基於服務端的目錄物件一致，且必須首先在伺服器端建立。 目錄MY_DIR中建立dump檔案和MY_DIR_LOG中建立日誌檔案的示例： 在使用expdp的客戶端機器上，設定環境變數： -- On windows, place all expdp parameters on one single line: C:\> set DATA_PUMP_DIR=MY_DIR C:\> expdp scott/[email protected]_db_alias DUMPFILE=expdp_s.dmp LOGFILE=my_logdir:expdp_s.log 注意環境變數DATA_DUMP_DIR對應的目錄名稱是大小寫敏感的。設定錯誤的DATA_PUMP_DIR環境變數會報錯，例如：DATA_PUMP_DIR=My_Dir： ORA-39002: invalid operation ORA-39070: Unable to open the log file. ORA-39087: directory name My_Dir is invalid 5.4 如果之前三種情況都沒有建立目錄物件，作為一個具有許可權的使用者(例如具有EXP_FULL_DATABASE或IMP_FULL_DATABASE角色)，那麼資料泵試圖使用預設的基於伺服器端的目錄物件，DATA_PUMP_DIR。理解資料泵不會建立DATA_PUMP_DIR目錄物件是非常重要的。僅當授權使用者未使用任何之前提到的機制建立的目錄物件時，才會嘗試使用DATA_PUMP_DIR。這個預設的目錄物件必須首先由DBA建立。不要將這個和同名的基於客戶端的環境變數相混淆。 首先，清空DATA_PUMP_DIR環境變數： C:\> set DATA_PUMP_DIR= 建立DATA_PUMP_DIR的目錄： CONNECT SYSTEM/MANAGER CREATE OR REPLACE DIRECTORY data_pump_dir AS 'D:\DataPump'; GRANT read, write ON DIRECTORY data_pump_dir TO scott; -- On windows, place all expdp parameters on one single line: C:\> expdp system/manage[email protected]_db_alias DUMPFILE=expdp_s.dmp LOGFILE=expdp_s.log SCHEMAS=scott 如果SCOTT使用者不是授權使用者，不能使用預設的DATA_PUMP_DIR。 ORA-39002: invalid operation ORA-39070: Unable to open the log file. ORA-39145: directory object parameter must be specified and non-null 使用者SCOTT的解決方法：如上面5.3，SCOTT可以設定環境變數DATA_PUMP_DIR為MY_DIR： -- On windows, place all expdp parameters on one single line: C:\> set DATA_PUMP_DIR=MY_DIR C:\> expdp scott/[email protected]_db_alias DUMPFILE=expdp_s.dmp LOGFILE=expdp_s.log SCHEMAS=scott 或者這種特定場景下，使用者SCOTT也可以有目錄DATA_PUMP_DIR的讀和寫許可權： -- On windows, place all expdp parameters on one single line: C:\> set DATA_PUMP_DIR=DATA_PUMP_DIR C:\> expdp scott/[email protected]_db_alias DUMPFILE=expdp_s.dmp LOGFILE=expdp_s.log SCHEMAS=scott
實驗： 建立目錄：CREATE DIRECTORY UTL_FILE_DIR AS '/oracle/backup'; 向用目錄物件標識的檔案寫內容： SQL> declare 2 fhandle utl_file.file_type; 3 begin 4 fhandle := utl_file.fopen('UTL_FILE_DIR', 'example.txt', 'w'); 5 utl_file.put_line(fhandle, 'test write one'); 6 utl_file.put_line(fhandle, 'test write two'); 7 utl_file.fclose(fhandle); 8 end; 9 / PL/SQL procedure successfully completed. SQL> ! [email protected]$ more /oracle/backup/example.txt test write one test write two 讀取使用目錄物件DIRECTORY標識的檔案內容： SQL> declare 2 fhandle utl_file.file_type; 3 fp_buffer varchar2(4000); 4 begin 5 fhandle := utl_file.fopen('UTL_FILE_DIR', 'example.txt', 'R'); 6 utl_file.get_line(fhandle, fp_buffer); 7 dbms_output.put_line(fp_buffer); 8 utl_file.get_line(fhandle, fp_buffer); 9 dbms_output.put_line(fp_buffer); 10 utl_file.fclose(fhandle); 11 end; 12 / PL/SQL procedure successfully completed. SQL> / PL/SQL procedure successfully completed. 此時沒有任何輸出，設定serveroutput： SQL> set serveroutput on SQL> / test write one test write two PL/SQL procedure successfully completed. 列印檔案內容。 DIRECTORY的目就在於可以讓我們在Oracle中靈活地對檔案系統中的檔案進行操作。 ORACLE DIRECTORY目錄 Create DIRECTORY讓我們可以在Oracle資料庫中靈活的對檔案進行讀寫操作，極大的提高了Oracle的易用性和可擴充套件性。
其語法為： CREATE [OR REPLACE] DIRECTORY directory AS 'pathname';
本案例具體建立如下: create or replace directory exp_dir as '/tmp';
目錄建立以後，就可以把讀寫許可權授予特定使用者，具體語法如下: GRANT READ[,WRITE] ON DIRECTORY directory TO username;
例如: grant read, write on directory exp_dir to eygle;
此時使用者eygle就擁有了對該目錄的讀寫許可權。 讓我們看一個簡單的測試:
SQL> create or replace directory UTL_FILE_DIR as '/opt/oracle/utl_file'; Directory created. SQL> declare 2 fhandle utl_file.file_type; 3 begin 4 fhandle := utl_file.fopen('UTL_FILE_DIR', 'example.txt', 'w'); 5 utl_file.put_line(fhandle , 'eygle test write one'); 6 utl_file.put_line(fhandle , 'eygle test write two'); 7 utl_file.fclose(fhandle); 8 end; 9 /
PL/SQL procedure successfully completed.
SQL> ! [[email protected] 9.2.0]$ more /opt/oracle/utl_file/example.txt eygle test write one eygle test write two [[email protected] 9.2.0]$
類似的我們可以通過utl_file來讀取檔案:
SQL> declare 2 fhandle utl_file.file_type; 3 fp_buffer varchar2(4000); 4 begin 5 fhandle := utl_file.fopen ('UTL_FILE_DIR','example.txt', 'R'); 6 7 utl_file.get_line (fhandle , fp_buffer ); 8 dbms_output.put_line(fp_buffer ); 9 utl_file.get_line (fhandle , fp_buffer ); 10 dbms_output.put_line(fp_buffer ); 11 utl_file.fclose(fhandle); 12 end; 13 / eygle test write one eygle test write two
PL/SQL procedure successfully completed.
可以查詢dba_directories檢視所有directory.
SQL> select * from dba_directories; OWNER DIRECTORY_NAME DIRECTORY_PATH ------------------------------ ------------------------------ ------------------------------ SYS UTL_FILE_DIR /opt/oracle/utl_file SYS BDUMP_DIR /opt/oracle/admin/conner/bdump SYS EXP_DIR /opt/oracle/utl_file
可以使用drop directory刪除這些路徑.
SQL> drop directory exp_dir; Directory dropped SQL> select * from dba_directories; OWNER DIRECTORY_NAME DIRECTORY_PATH ------------------------------ ------------------------------ ------------------------------ SYS UTL_FILE_DIR /opt/oracle/utl_file SYS BDUMP_DIR /opt/oracle/admin/conner/bdump
【DIRECTORY】普通使用者建立Oracle DIRECTORY資料庫物件的許可權需求及探索 本文以普通使用者建立Oracle DIRECTORY資料庫物件所需的許可權入手，給大家展示一下自我提示的方法，進而探索一下DIRECTORY資料庫物件的“小祕密”。 1.授予普通使用者建立DIRECTORY的許可權 為保證普通使用者具有建立Oracle資料庫的directory物件，需要確切知道需要授予使用者的許可權是什麼，我們這裡需要的許可權是“CREATE ANY DIRECTORY”許可權。 如果一時忘記需要的具體許可權，可以使用如下參考類SQL語句獲得提示。 [email protected]> select distinct privilege from dba_sys_privs where privilege like '%DIRECTORY%'; PRIVILEGE ---------------------------------------- DROP ANY DIRECTORY CREATE ANY DIRECTORY 對普通使用者sec使用者的授權語句如下。 [email protected]> grant CREATE ANY DIRECTORY to sec; Grant succeeded. 2.嘗試在普通使用者下建立DIRECTORY資料庫物件 [email protected]> create directory dir_test as '/home/oracle/secooler'; Directory created. OK，此時在sec使用者下建立DIRECTORY物件成功！ 3.建立DIRECTORY可能遇到的報錯資訊 如若在建立DIRECTORY之前普通使用者sec未獲得相應許可權，將會收到最為常見的“ORA-01031: insufficient privileges”錯誤。 模擬再現一下，供大家參考。 [email protected]> conn / as sysdba Connected. [email protected]> revoke CREATE ANY DIRECTORY from sec; Revoke succeeded. [email protected]> conn sec/sec Connected. [email protected]> create directory dir_test as '/home/oracle/secooler'; create directory dir_test as '/home/oracle/secooler' * ERROR at line 1: ORA-01031: insufficient privileges 4.獲得DIRECTORY建立結果資訊 用於查詢獲得DIRECTORY資訊的資料庫檢視“僅有”兩個，如下所示。 [email protected]> select TABLE_NAME from dict where table_name like '%DIRECTORIES'; TABLE_NAME ------------------------------ ALL_DIRECTORIES DBA_DIRECTORIES 這裡之所以強調“僅有”，是與其他資料庫物件檢視相比較而言的。 例如，用於獲得同名的資料庫檢視一般都會有三類，如下所示。 [email protected]> select TABLE_NAME from dict where table_name like '%SYNONYMS'; TABLE_NAME ------------------------------ DBA_SYNONYMS USER_SYNONYMS ALL_SYNONYMS 用於檢視DIRECTORY資料庫物件的檢視之所以沒有以USER開頭的是有其具體緣由的。 請繼續跟隨我進行探索。 我們使用ALL_DIRECTORIES檢視檢視剛剛建立成功的DIRECTORY物件。 [email protected]> col owner for a6 [email protected]> col DIRECTORY_NAME for a15 [email protected]> col DIRECTORY_PATH for a30 [email protected]> select * from all_directories where DIRECTORY_NAME = 'DIR_TEST'; OWNER DIRECTORY_NAME DIRECTORY_PATH ------ --------------- ------------------------------ SYS DIR_TEST /home/oracle/secooler 重點關注一下OWNER欄位的返回結果，這裡顯示的內容是“SYS”，並不是我們所期待的那個建立DIRECTORY的當前使用者“SEC”！ 這樣便可以順理成章的給出不存在以USER開頭的“USER_DIRECTORIES”檢視的合理解釋，因為DIRECTORY資料庫物件並不屬於某個具體使用者，都歸SYS使用者所有！ 5.小結 在對Oracle資料庫世界的探索過程中，不要放過任何蛛絲馬跡，每一個細節問題都充滿了樂趣，值得思考和總結。 在踐行的征程中我們追求的是一種甚解的態度！ 【DIRECTORY】確保DIRECTORY對應路徑書寫正確以避免遭遇ORA-29913和ORA-29400錯誤 這裡，提醒大家在建立DIRECTORY物件時，目錄路徑一定要書寫正確，不要出現不必要的空格或者回車，否則將會遭遇ORA-29913和ORA-29400錯誤。 1.故障再現及分析過程 1）調整為英文顯示環境 $ export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK 2）建立測試使用者SECOOLER並授權 $ sqlplus / as sysdba SQL*Plus: Release 11.1.0.6.0 - Production on Mon Mar 12 20:07:16 2012 Copyright (c) 1982, 2007, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [email protected]> drop user secooler cascade; User dropped. [email protected]> create user secooler identified by secooler; User created. [email protected]> grant dba to secooler; Grant succeeded. 2）建立DIRECTORY物件 注意這裡建立的是一個錯誤路徑，因為這裡在路徑的結尾處出現了一個多餘的回車換行。但是在查詢DIR_DUMP執行路徑是顯示的內容並沒有顯示出結尾處的回車。 [email protected]> create directory dir_dump as 'c:\ 2 '; Directory created. [email protected]> col OWNER for a5 [email protected]> col DIRECTORY_NAME for a15 [email protected]> col DIRECTORY_PATH for a15 [email protected]> select * from dba_directories where directory_name = 'DIR_DUMP'; OWNER DIRECTORY_NAME DIRECTORY_PATH ----- --------------- --------------- SYS DIR_DUMP c:\ 3）以外部表解除安裝資料方式測試DIRECTORY是否可用 [email protected]> create table t1 2 organization external 3 ( type oracle_datapump 4 default directory dir_dump 5 location ('t1_part1.dat','t1_part2.dat') 6 ) 7 PARALLEL 2 8 as 9 select owner,table_name,tablespace_name from all_tables where wner='SYSTEM'; create table t1 * ERROR at line 1: ORA-29913: error in executing ODCIEXTTABLEOPEN callout ORA-29400: data cartridge error error opening file c:\ \T1_6648_11956.log 可見，這裡的報錯與DIRECTORY建立時出現的那個回車換行有直接的關係（重點關注最後兩行提示資訊）。 4）與之對應的中文報錯內容如下 $ export NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS16GBK" $ sqlplus / as sysdba SQL*Plus: Release 11.1.0.6.0 - Production on 星期一 3月 12 20:20:44 2012 Copyright (c) 1982, 2007, Oracle. All rights reserved. 連線到: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [email protected]> create table t1 2 organization external 3 ( type oracle_datapump 4 default directory dir_dump 5 location ('t1_part1.dat','t1_part2.dat') 6 ) 7 PARALLEL 2 8 as 9 select owner,table_name,tablespace_name from all_tables where wner='SYSTEM'; create table t1 * 第 1 行出現錯誤: ORA-29913: 執行 ODCIEXTTABLEOPEN 調出時出錯 ORA-29400: 資料外掛錯誤error opening file c:\ \T1_6648_4104.log 5）測試由於多餘空格導致報錯過程 （1）重建DIRECTORY [email protected]> drop directory dir_dump; 目錄已刪除。 [email protected]> create directory dir_dump as 'c :\'; 目錄已建立。 （2）使用DIRECTORY解除安裝資料 [email protected]> create table t1 2 organization external 3 ( type oracle_datapump 4 default directory dir_dump 5 location ('t1_part1.dat','t1_part2.dat') 6 ) 7 PARALLEL 2 8 as 9 select owner,table_name,tablespace_name from all_tables where wner='SYSTEM'; create table t1 * 第 1 行出現錯誤: ORA-29913: 執行 ODCIEXTTABLEOPEN 調出時出錯 ORA-29400: 資料外掛錯誤error opening file c :\T1_6648_6560.log 報錯內容很顯然，c與後面的冒號之間的空格是導致錯誤的根本原因。 2.故障處理方法 問題的原因是由於建立DIRECTORY時指定的路徑中出現多餘的空格或者回車導致的，換一種說法便是，當我們指定的路徑不存在時就會報錯。 我們的處理方法便是給出DIRECTORY正確的路徑。 1）重建directory執行正確的路徑 [email protected]> drop directory dir_dump; 目錄已刪除。 [email protected]> create directory dir_dump as 'c:\'; 目錄已建立。 2）重新測試資料解除安裝 [email protected]> create table t1 2 organization external 3 ( type oracle_datapump 4 default directory dir_dump 5 location ('t1_part1.dat','t1_part2.dat') 6 ) 7 PARALLEL 2 8 as 9 select owner,table_name,tablespace_name from all_tables where wner='SYSTEM'; 表已建立。 建立成功，表明DIRECTORY此時已經正確可用。 3.小結 ORA-29913和ORA-29400錯誤背後的根本原因是在建立DIRECTORY資料庫物件時對應的路徑不存在。在建立DIRECTORY時Oracle資料庫並不會到作業系統上檢驗路徑的存在性，只有在使用時才會校驗，因此在建立DIRECTORY時謹記對應的路徑的真實存在性。 檢視使用者的目錄操作許可權 column grantee format a10 column grantor format a10 column dir_name format a20 column dir_path format a50 column privilege format a10 break on dir_name select d.directory_name dir_name, d.directory_path dir_path, p.privilege, p.grantee, p.grantor from dba_tab_privs p, dba_directories d where p.table_name = d.directory_name and p.grantee = upper('&user') order by d.directory_name, p.privilege /
About Me ............................................................................................................................................. ● 本文作者：小麥苗，部分內容整理自網路，若有侵權請聯絡小麥苗刪除 ● 本文在itpub（http://blog.itpub.net/26736162/abstract/1/）、部落格園（http://www.cnblogs.com/lhrbest）和個人微信公眾號（xiaomaimiaolhr）上有同步更新 ● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/ ● 本文部落格園地址：http://www.cnblogs.com/lhrbest ● 本文pdf版、個人簡介及小麥苗雲盤地址：http://blog.itpub.net/26736162/viewspace-1624453/ ● 資料庫筆試面試題庫及解答：http://blog.itpub.net/26736162/viewspace-2134706/ ● DBA寶典今日頭條號地址：http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826 ............................................................................................................................................. ● QQ群號：230161599（滿）、618766405 ● 微信群：可加我微信，我拉大家進群，非誠勿擾 ● 聯絡我請加QQ好友（646634621），註明新增緣由 ● 於 2017-12-01 09:00 ~ 2017-12-30 22:00 在魔都完成 ● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解 ● 版權所有，歡迎分享本文，轉載請保留出處 ............................................................................................................................................. ● 小麥苗的微店：https://weidian.com/s/793741433?wfr=c&ifr=shopdetail ● 小麥苗出版的資料庫類叢書：http://blog.itpub.net/26736162/viewspace-2142121/ ............................................................................................................................................. 使用微信客戶端掃描下面的二維碼來關注小麥苗的微信公眾號（xiaomaimiaolhr）及QQ群（DBA寶典），學習最實用的資料庫技術。
小麥苗的微信公眾號 小麥苗的DBA寶典QQ群2 《DBA筆試面寶典》讀者群 小麥苗的微店 .............................................................................................................................................
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26736162/viewspace-2148694/，如需轉載，請註明出處，否則將追究法律責任。 |
【本文地址】