EXPDP/IMPDP example in oracle for database copy

Normally we need to have a database copy to export/import on our other database and separate schema. Here are the steps to do the schema copy from one database to another. You need to be admin user for performing some tasks.

$ mkdir datapump

$ chmod 777 datapump/

$ sqlplus adminuser1/adminpassword1@dbsid1

SQL> create directory datapump as '/path/to/folder/datapump';

SQL> grant read, write on directory datapump to public;

$ expdp user1/password1@dbsid1 dumpfile=datapump:myschema.dmp logfile=datapump:myschema_exp.log schemas=myschema

$ sqlplus adminuser2/adminpassword2@dbsid2

SQL> create directory datapump as '/same/path/to/folder/datapump';

SQL> grant read, write on directory datapump to public;

$ impdp user2/password2@dbsid2 dumpfile=datapump:myschema.dmp logfile=datapump:myschema_imp.log remap_schema=myschema:yourschema remap_tablespace=mytablespace:yourtablespace

You can remove remap_schema and remap_tablespace clauses in case your schema name and tablespace name are same on both the databases.

You also need to have same datapump location accessible from both the databases.

Sphere: Related Content

Change the permission of table space

To change the permission of table space to read write use following sql command

alter tablespace TFR_REP read write;

Sphere: Related Content

To change the oracle user tablespace

In case if you have assigned wrong tablespace while creating a user, you can change tablespace of that user by following command.

first connect with the system on the database where you have created this user.

alter user AHSAN_TFR default tablespace TFR_REP quota unlimited on TFR_REP;

must assign quota otherwise you cannot run any SQL statement.

---

Sphere: Related Content

To know tablespaces of oracle user

To know the tablespace assigned to a user run following query on sqlplus

select  USERNAME,
 CREATED,
 PROFILE,
 DEFAULT_TABLESPACE,
 TEMPORARY_TABLESPACE
from  dba_users
order  by USERNAME

Sphere: Related Content

Copying only schema definition to another schema

Here is the command to copy only the schema definition from one schema to another schema.

First export the schema definition from user tfr_rep to empty_schema.dmp file

exp tfr_rep/welcome@datagen1-ajaved:1521/tfrdb file=empty_schema.dmp log=export.log rows=n constraints=n owner=tfr_rep

Then copy the dump file to ahsan_tfr user from tfr_rep user as follows.

imp userid=ahsan_tfr/welcome@tfindiadbzone:1521/tfindia file=empty_schema.dmp log=import.log rows=n fromuser=tfr_rep touser=ahsan_tfr

NOTE: Names of tablespaces on both the schema should be same, otherwise import will fail indicating oracle error

Sphere: Related Content

Script to export/import oracle schema

Here is the bash script that does the job of export and import of an oracle schema to another schema.

Save this file as EXPORT_IMPORT.

For exporting the schema type
EXPORT_IMPORT EXP

This file generates the schema dump in exp_database.dmp file and it also makes log of export process in exp_database.log file.

For importing the same exported exp_database.dmp dump file to another schema type
EXPORT_IMPORT IMP

if [ $# -lt 1 ]; then
echo "First argument to this file should be either EXP or IMP."
exit
fi
if [ "$1" = "EXP" ]; then
if [ $# != 2 ]; then
echo "No of arguments to this file should be two."
echo "USAGE: EXPORT_IMPORT EXP "
exit
fi
exp "$2" FILE=exp_database.dmp COMPRESS=N CONSISTENT=N CONSTRAINTS=Y GRANTS=Y INDEXES=Y RECORD=Y ROWS=Y Feedback=1000 LOG=exp_database.log
fi
if [ "$1" = "IMP" ]; then
if [ $# != 3 ]; then
echo "No of arguments to this file should be three."
echo "USAGE: EXPORT_IMPORT IMP "
exit
fi
from_user=`echo "$2" | cut -f1 -d'/'`
to_user=`echo "$3" | cut -f1 -d'/'`
imp userid="$3" fromuser="$from_user" touser="$to_user" file=exp_database.dmp buffer=64000 commit=y
fi

Sphere: Related Content

Know all users on an Oracle database

To know all users on Oracle database, issue following command on sqlplus window

select * from all_users

Sphere: Related Content

Create a new schema in Oracle

Here we will first create a tablespace and then we will create a user for that tablespace.

create tablespace ahsan
add datafile 'e:\ahsan.dbf' size 100 m;

create user ahsan identified by welcome
default tablespace ahsan temporary tablespace temp;

grant create session, connect, resource to ahsan;

Hence now you are ready with the new tablespace and a new user that belongs to the ahsan tablespace;

Sphere: Related Content

Installing oracle 10g database on parallels desktop

Recently, I switched to Mac from Windows machine. I had a challenge to make Mac development environment as similar to Windows development environment. Being a java developer I usually use Oracle database. So I tried to install it first on my mac, then I found somehow that Oracle does not support my mac version. Then I had to install Parallels Desktop to make it work.
I proceeded this way

1. Install Parallels Desktop, You can find how to do it on parallels support center.

2. Install Oracle 10g database on Parallels Desktop, It is smooth, so no problem.

3. Steps to install InstantClient and Sqlplus package on mac to access Parallel's oracle DB.

4. Download InstantClient and SqlPlus packages from Oracle download site.

5. Extract these two packages to a single directory like I chose /Applications/OracleClient

6. Copy tnsnames.ora file from parallels oracle installation to the Mac's /Applications/OracleClient directory you chose for installing InstantClient and SqlPlus.

7. Modify your .bash_profile to indicate these entries

PATH=/Applications/OracleClient:${PATH}
export PATH
ORACLE_HOME=/Applications/OracleClient
export ORACLE_HOME
DYLD_LIBRARY_PATH=/Applications/OracleClient
export DYLD_LIBRARY_PATH
TNS_ADMIN=/Applications/OracleClient
export TNS_ADMIN

8. Check for the IP of the Parallel's Desktop machine and note it.

9. Check for the IP of the Mac machine and note it.

10. open vi editor and edit /etc/hosts file for Mac machine to reflect the IP obtained from parallels desktop machine(Step 7)

192.168.128.82 ahsan-javed.mydomain.com
(Note:ahsan-javed.mydomain.com is the machine name that was given in my office network.)

11. open notepad and edit C:/Windows/System32/drivers/etc/hosts file for Parallels Desktop to reflect the IP obtained from Mac machine (step 8)

192.168.128.132 ahsanjaved
(Note: ahsanjaved is the machine name I assigned to parallels desktop while installing Windows XP on it.)

12. Go to Windows Control Center and disable firewall/enable 1521,1158 and 5506 ports for communication between Parallels Oracle and Mac machine.

13. Now go to shell and type sqlplus. it should ask you for username and password. Here you go connected.

Sphere: Related Content

Oracle cursors parameters

To view open cursors on oracle

No of Open cursors on Oracle(Admin permission is required)
select count(*) from v$open_cursor;

To see open_cursors parameter on oracle db.
show parameter open_cursors;

To set open_cursors parameter on oracle db.

alter system set open_cursors=700 scope=memory;

To see the sql query associated with cursor

select sid, sql_text from v$Open_cursor order by sid, sql_text;

To select number of open cursors, machine name, operating system and user name executing the query, run the following

SELECT v.value as numopencursors ,s.machine ,s.osuser,s.username
FROM V$SESSTAT v, V$SESSION s
WHERE v.statistic# = 3 and v.sid = s.sid ;

Sphere: Related Content

Rebuild unusable index on a column of a table in Oracle schema

To rebuild unusable indexes on Oracle schema we can use this simple script.
This script first checks for all unusable indexes in the entire schema and then rebuild them to VALID state.

set serverout on
set head on
set echo on
declare
str varchar2(500);
begin for i in (select index_name,status from user_indexes where Status='UNUSABLE')
loop
str:='alter index ' || i.index_name || ' rebuild unrecoverable' ;
execute immediate str;
dbms_output.put_line('rebuilt index ' || i.index_name);
end loop;
end;
/

Sphere: Related Content