Archive for the ‘Database’ Category

Oracle DataPump 4 – Export database using EXPDP command

Once the directory access is granted, the user scott can export his database objects with command arguments. Open command prompt (CMD) and enter the expdp command with parameters

EXPDP Command Format

expdp <username>/<password>@<database_name> directory=<directory_name> dumpfile=<dump_file_name> logfile=<log_file_name>

or

D:\> expdp scott/tiger@xe directory=TEST_DIR dumpfile=DB11G.dmp logfile=expdpDB11G.log

Example

Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

H:\>expdp scott/tiger@xe directory=TEST_DIR dumpfile=DB11G.dmp logfile=expdpDB11G.log

Export: Release 11.2.0.2.0 – Production on Mon Aug 5 13:49:36 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 – Productio
n
Starting "SCOTT"."SYS_EXPORT_SCHEMA_01":  scott/********@xe directory=TEST_DIR d
umpfile=DB11G.dmp logfile=expdpDB11G.log

Read more »

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 »