Posts Tagged ‘oracle’

Oracle DataPump 3 – Create and grant privileges to user

Login as DBA

To create directory object, first you need to login as DBA (DataBase Adminstrator). Open command prompt (CMD) in windows and type the following

H:\>sqlplus

SQL*Plus: Release 11.2.0.2.0 Production on Mon Aug 5 13:30:54 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Enter user-name: CONN / AS SYSDBA
Enter password:

Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 – Production

SQL>


Create user to default table space

SQL> CREATE USER scott IDENTIFIED BY tiger DEFAULT TABLESPACE SYSAUX TEMPORARY TABLESPACE "TEMP";

User created.

SQL>


Grant privileges to the user

SQL> GRANT connect, create procedure, create session, create table, create type, create view, create synonym,create sequence, create trigger, resource TO scott;

Grant succeeded.

SQL>

Read more »

Oracle DataPump 2 – Creating Directory Objects

In order to use Data Pump, the database administrator must create a directory object and grant privileges to the user on that directory object. If a directory object is not specified, a default directory object called data_pump_dir is provided. The default data_pump_dir is available only to privileged users unless access is granted by the DBA.

Login as DBA

To create directory object, first you need to login as DBA (DataBase Adminstrator). Open command prompt (CMD) in windows and type the following

H:\>sqlplus

SQL*Plus: Release 11.2.0.2.0 Production on Mon Aug 5 13:30:54 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Enter user-name: CONN / AS SYSDBA
Enter password:

Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 – Production

SQL>


Default directory object

data_pump_dir= $ORACLE_BASE/admin/<database_name>/dpdump

Read more »

Oracle DataPump 1 – Introduction

Oracle Data Pump is a new and unique feature of Oracle Database 11g Release 2. A new public interface package, DBMS_DATAPUMP, provides a server-side infrastructure for fast data and metadata movement between Oracle databases. It is ideal for large databases and data warehousing environments, where high-performance data movement offers significant time savings to database administrators.

Key Features

Fast Performance

Operations performed with the new Data Pump Export and Import utilities are typically much faster than operations performed with the original Export and Import utilities. With Data Pump Export, when the direct path method of unloading is used, a single stream of data unload is about two times faster than original Export. This is because the direct path API has been modified to be even more efficient than before. Depending on the level of parallelism, the performance improvement can be even greater.


Improved Management Restart

Every Data Pump operation has a master table that is created in the schema of the user running a Data Pump job. The master table maintains information about all aspects of the job, such as the current state of every object exported or imported and its location in the dump file set. In the event of a planned or unplanned job stoppage, Data Pump knows which objects were currently being worked on and whether or not they completed successfully.

Read more »