Создать директорию в oracle
Создайте объект DIRECTORY , соответствующий каталогу в файловой системе, где находится внешний источник данных.
Для создания объекта каталога для внешней таблицы используйте инструкцию CREATE DIRECTORY . Объект каталога задает псевдоним каталога в файловой системе сервера, где находится внешний источник данных. При ссылке на внешний источник данных можно использовать имена каталогов вместо жестко заданного в программе имени пути операционной системы с целью обеспечения большей гибкости управления файлами.
Для создания каталогов необходимо иметь системные привилегии CREATE ANY DIRECTORY . При создании каталога пользователю автоматически предоставляются объектные привилегии, и он может выдать привилегии READ и WRITE другим пользователям и ролям. Администратор базы данных также может предоставить эти привилегии другим пользователям и ролям.
Пользователю требуются привилегии READ на все каталоги, используемые во внешних таблицах, к которым происходит обращение, и привилегии WRITE для задействованных местоположений журнала, плохих и отвергнутых файлов.
Кроме того, привилегия WRITE необходима в случае, когда для выгрузки данных используется структура внешней таблицы.
Oracle предоставляет также тип ORACLE_DATAPUMP , с помощью которого можно выгрузить данные (т. е. прочитать данные из таблицы в базу данных и вставить их во внешнюю таблицу) и затем повторно загрузить их в базу данных Oracle. Это одноразовая операция, которая может быть выполнена при создании таблицы. После того как выполнено создание и заполнение начальными данными, какие либо строки невозможно обновить, вставить или удалить.
CREATE [ OR REPLACE ] DIRECTORY AS 'path_name';
OR REPLACE Укажите предложение OR REPLACE , чтобы повторно создать объект каталога базы данных, если он уже существует. Это предложение позволяет изменить определение существующего каталога без удаления, повторного создания и повторного предоставления объектных привилегий базы данных, которые были выданы ранее на каталог. Пользователи, получившие ранее привилегии на переопределенный каталог, могут продолжать обращаться к каталогу без необходимости повторного предоставления им привилегий.
directory Указывается имя создаваемого объекта каталога. Максимальная длина имени каталога равна 30 байтам. Объект каталога невозможно определить с помощью имени схемы.
'path_name' Указывается имя полного пути к каталогу операционной системы, к которому происходит обращение. Имя пути зависит от регистра.
Use the CREATE DIRECTORY statement to create a directory object. A directory object specifies an alias for a directory on the server file system where external binary file LOBs ( BFILE s) and external table data are located. You can use directory names when referring to BFILE s in your PL/SQL code and OCI calls, rather than hard coding the operating system path name, for management flexibility.
All directories are created in a single namespace and are not owned by an individual schema. You can secure access to the BFILE s stored within the directory structure by granting object privileges on the directories to specific users.
"Large Object (LOB) Data Types" for more information on BFILE objects
GRANT for more information on granting object privileges
You must have the CREATE ANY DIRECTORY system privilege to create directories.
When you create a directory, you are automatically granted the READ , WRITE , and EXECUTE object privileges on the directory, and you can grant these privileges to other users and roles. The DBA can also grant these privileges to other users and roles.
WRITE privileges on a directory are useful in connection with external tables. They let the grantee determine whether the external table agent can write a log file or a bad file to the directory.
For file storage, you must also create a corresponding operating system directory, an Oracle Automatic Storage Management (Oracle ASM) disk group, or a directory within an Oracle ASM disk group. Your system or database administrator must ensure that the operating system directory has the correct read and write permissions for Oracle Database processes.
Privileges granted for the directory are created independently of the permissions defined for the operating system directory, and the two may or may not correspond exactly. For example, an error occurs if sample user hr is granted READ privilege on the directory object but the corresponding operating system directory does not have READ permission defined for Oracle Database processes.
Specify OR REPLACE to re-create the directory database object if it already exists. You can use this clause to change the definition of an existing directory without dropping, re-creating, and regranting database object privileges previously granted on the directory.
Users who had previously been granted privileges on a redefined directory can still access the directory without being regranted the privileges.
DROP DIRECTORY for information on removing a directory from the database
Specify the name of the directory object to be created. The maximum length of directory is 30 bytes. You cannot qualify a directory object with a schema name.
Oracle Database does not verify that the directory you specify actually exists. Therefore, take care that you specify a valid directory in your operating system. In addition, if your operating system uses case-sensitive path names, then be sure you specify the directory in the correct format. You need not include a trailing slash at the end of the path name.
Do not refer to a parent directory in the directory name. For example, the following syntax is valid:
However, the following syntax is not valid:
Specify the full path name of the operating system directory of the server where the files are located. The single quotation marks are required, with the result that the path name is case sensitive.
Creating a Directory: Examples The following statement creates a directory database object that points to a directory on the server:
The following statement redefines directory database object bfile_dir to enable access to BFILE s stored in the operating system directory / usr/bin/bfile_dir :
Purpose
Use the CREATE DIRECTORY statement to create a directory object. A directory object specifies an alias for a directory on the server file system where external binary file LOBs ( BFILE s) and external table data are located. You can use directory names when referring to BFILE s in your PL/SQL code and OCI calls, rather than hard coding the operating system path name, for management flexibility.
All directories are created in a single namespace and are not owned by an individual schema. You can secure access to the BFILE s stored within the directory structure by granting object privileges on the directories to specific users.
See Also:
"Large Object (LOB) Datatypes " for more information on BFILE objects
GRANT for more information on granting object privileges
Prerequisites
You must have CREATE ANY DIRECTORY system privilege to create directories.
When you create a directory, you are automatically granted the READ and WRITE object privileges on the directory, and you can grant these privileges to other users and roles. The DBA can also grant these privileges to other users and roles.
WRITE privileges on a directory are useful in connection with external tables. They let the grantee determine whether the external table agent can write a log file or a bad file to the directory.
You must also create a corresponding operating system directory for file storage. Your system or database administrator must ensure that the operating system directory has the correct read and write permissions for Oracle Database processes.
Privileges granted for the directory are created independently of the permissions defined for the operating system directory, and the two may or may not correspond exactly. For example, an error occurs if sample user hr is granted READ privilege on the directory object but the corresponding operating system directory does not have READ permission defined for Oracle Database processes.
Syntax
create_directory::=
Semantics
OR REPLACE
Specify OR REPLACE to re-create the directory database object if it already exists. You can use this clause to change the definition of an existing directory without dropping, re-creating, and regranting database object privileges previously granted on the directory.
Users who had previously been granted privileges on a redefined directory can still access the directory without being regranted the privileges.
See Also:
Specify the name of the directory object to be created. The maximum length of directory is 30 bytes. You cannot qualify a directory object with a schema name.
Oracle Database does not verify that the directory you specify actually exists. Therefore, take care that you specify a valid directory in your operating system. In addition, if your operating system uses case-sensitive path names, be sure you specify the directory in the correct format. You need not include a trailing slash at the end of the path name.
Specify the full path name of the operating system directory of the server where the files are located. The single quotes are required, with the result that the path name is case sensitive.
Example
Creating a Directory: Examples
The following statement creates a directory database object that points to a directory on the server:
The following statement redefines directory database object bfile_dir to enable access to BFILE s stored in the operating system directory / private1/lob/files :
Use the CREATE DIRECTORY statement to create a directory object. A directory object specifies an alias for a directory on the server file system where external binary file LOBs ( BFILE s) and external table data are located. You can use directory names when referring to BFILE s in your PL/SQL code and OCI calls, rather than hard coding the operating system path name, for management flexibility.
All directories are created in a single namespace and are not owned by an individual schema. You can secure access to the BFILE s stored within the directory structure by granting object privileges on the directories to specific users.
"Large Object (LOB) Datatypes" for more information on BFILE objects
GRANT for more information on granting object privileges
You must have CREATE ANY DIRECTORY system privilege to create directories.
When you create a directory, you are automatically granted the READ and WRITE object privileges on the directory, and you can grant these privileges to other users and roles. The DBA can also grant these privileges to other users and roles.
WRITE privileges on a directory are useful in connection with external tables. They let the grantee determine whether the external table agent can write a log file or a bad file to the directory.
For file storage, you must also create a corresponding operating system directory, an ASM disk group, or a directory within an ASM disk group. Your system or database administrator must ensure that the operating system directory has the correct read and write permissions for Oracle Database processes.
Privileges granted for the directory are created independently of the permissions defined for the operating system directory, and the two may or may not correspond exactly. For example, an error occurs if sample user hr is granted READ privilege on the directory object but the corresponding operating system directory does not have READ permission defined for Oracle Database processes.
Specify OR REPLACE to re-create the directory database object if it already exists. You can use this clause to change the definition of an existing directory without dropping, re-creating, and regranting database object privileges previously granted on the directory.
Users who had previously been granted privileges on a redefined directory can still access the directory without being regranted the privileges.
DROP DIRECTORY for information on removing a directory from the database
Specify the name of the directory object to be created. The maximum length of directory is 30 bytes. You cannot qualify a directory object with a schema name.
Oracle Database does not verify that the directory you specify actually exists. Therefore, take care that you specify a valid directory in your operating system. In addition, if your operating system uses case-sensitive path names, be sure you specify the directory in the correct format. You need not include a trailing slash at the end of the path name.
Specify the full path name of the operating system directory of the server where the files are located. The single quotes are required, with the result that the path name is case sensitive.
Creating a Directory: Examples The following statement creates a directory database object that points to a directory on the server:
The following statement redefines directory database object bfile_dir to enable access to BFILE s stored in the operating system directory / usr/bin/ :
In Oracle, a directory object corresponds to a directory on the database server's filesystem. The CREATE DIRECTORY command is used to create a directory object in Oracle, and you must have a CREATE ANY DIRECTORY privilege. The following are the examples.
Create Directory Using Toad
Follow these steps to create a directory in Oracle using Toad
- Click on the menu Database > Create > Directory.
- Then Create Directory window will appear.
- Specify directory name in the Directory Name field.
- Specify the path in the Directory Path field, or you can browse directly there (if you are creating a directory on the server).
- Then click on OK to create the directory.
In Linux:
Please Share Share this content
In Windows:
Partially Rolling Back a Transaction in Oracle
Examples For Creating a Directory in Oracle
How to Import XML File in Oracle SQL Developer?
You Might Also Like
How to Validate Email Address Using PL/SQL?
October 9, 2018
Create Directory Using Oracle SQL Developer
Follow these steps to create a directory using Oracle SQL Developer
- Navigate to the Connections navigator, open the schema node.
- Locate Directories node and do the right click.
- Click on the Create Directory option in the shortcut menu.
- Then Create Directory window will appear.
- Specify the directory name in the Directory Name field.
- Specify the path in the Directory Path field.
- Then click on the Apply button to create the directory.
See also:
Vinish Kapoor
Читайте также: