The value assigned to this client-based environment variable must be the name of a server-based directory object, which must first be created on the server system by a DBA. This environment variable is defined using operating system commands on the client system where the Data Pump Export and Import utilities are run.
If a directory object is not specified as part of the file specification, and if no directory object is named by the DIRECTORY parameter, then the value of the environment variable, DATA_PUMP_DIR, is used. If a directory object is not specified as part of the file specification, then the directory object named by the DIRECTORY parameter is used. (The directory object must be separated from the file name by a colon.) If a directory object is specified as part of the file specification, then the location specified by that directory object is used. Similarly, the Oracle database requires permission from the operating system to read and write files in the directories.ĭata Pump Export and Import use the following order of precedence to determine a file's location: You are not given direct access to those files outside of the Oracle database unless you have the appropriate operating system privileges. Note that READ or WRITE permission to a directory object only means that the Oracle database can read or write files in the corresponding directory on your behalf. For example, to allow the Oracle database to read and write files on behalf of user hr in the directory named by dpump_dir1, the DBA must execute the following command: SQL> GRANT READ, WRITE ON DIRECTORY dpump_dir1 TO hr If you are not a privileged user, then before you can run Data Pump Export or Data Pump Import, a directory object must be created by a database administrator (DBA) or by any user with the CREATE ANY DIRECTORY privilege.Īfter a directory is created, the user creating the directory object must grant READ or WRITE permission on the directory to other users. (The user SYSTEM has read and write access to the DATA_PUMP_DIR directory, by default.) By default, it is available only to privileged users.
Oracle sql developer dump windows#
On UNIX and Windows operating systems, a default directory object, DATA_PUMP_DIR, is created at database creation or whenever the database dictionary is upgraded. If you were allowed to specify a directory path location for an output file, then the server might overwrite a file that you might not normally have privileges to delete. If you were allowed to specify a directory path location for an input file, then you might be able to read data that the server has access to, but to which you should not. The reason that a directory object is required is to ensure data security and integrity. SQL> CREATE DIRECTORY dpump_dir1 AS '/usr/apps/datafiles' The following example shows a SQL statement that creates a directory object named dpump_dir1 that is mapped to a directory located at /usr/apps/datafiles. DBAs must ensure that only approved users are allowed access to the directory object associated with the directory path. A directory object maps a name to a directory path on the file system. Data Pump requires that directory paths be specified as directory objects. If you don’t want to touch the statements, you can change your SQL*Plus session to match:Īlter session set NLS_NUMERIC_CHARACTERS = '.Because Data Pump is server-based rather than client-based, dump files, log files, and SQL files are accessed relative to server-based directory paths. In SQL Developer that is a comma in SQL*Plus it appears to be a period. Your to_date() format model uses X for the ‘local radix character’. Your SQL Developer and SQL*Plus sessions are set up differently probably different locales, but the relevant part here is the NLS_NUMERIC_CHARACTERS setting in each environment, which is usually inherited form the territory. I know there is DBA view in Oracle SQL Developer but it says I don’t have DBA privileges therefore I am looking to work with options that I have. Is there way to make export that works with SQLPlus? ORA-01830: date format picture ends before converting entire input string These run fine when I run them using Oracle SQL Developer. Oracle SQL Developer generates me insert statements where time is formatted like this: to_timestamp(' 12:30:22,593000000','DD.MM.RRRR HH24:MI:SSXFF'). I then try to import it using SQLPlus v12.2. I export data in Oracle SQL Developer v17.2 using Tools > Database Export ….
0 kommentar(er)
