How to Import / Export Data in Oracle

There are different Ways to Import / Export Database, Tablespace, Table, Schema in Oracle Database:  

To Import Table from one Database to another follow the following steps: 

1. First check Data Pump directory: 

select Directory_Name, Directory_Path from dba_directories;
2. If directory is not created or you would like to change the location of directory run this: 

CREATE OR REPLACE DIRECTORY DUMP_DIR AS '/disk1/folder1';"
3. Additionally you can grant read/ write access to the user. 

GRANT READ,WRITE ON DIRECTORY DUMP_DIR TO User;"
4. Export Table command (Run this command on Shell not on SQL Console)

 
expdp SYSTEM TABLES=Schema.Table DIRECTORY=DUMP_DIR DUMPFILE=TableDumpfileName.dmp LOGFILE=Table_dumplog.log
5. To Import the dump command (Run this command on Shell not on SQL Console). This command will import the Data in same Table. Table definition on destination must be same as it is in source: 

 
impdp SYSTEM TABLES=Schema.Table CONTENT=DATA_ONLY DIRECTORY=DUMP_DIR DUMPFILE=TableDumpfileName.dmp
Database Administrator by profession.

Comments

Post a Comment

Popular posts from this blog

How to Enable / Disable a user in MS SQL Server

Image
To Enable or Disable a User in MS SQL Server: There are two ways to Enable / Disable a user in MS SQL Server 1. Using TSQL To Enable a User:  use master go ALTER LOGIN [UserName] ENABLE go To Disable a User:  use master go ALTER LOGIN [UserName] DISABLE go 2. Using SQL Server Management Studio GUI: Open SQL Server Management Studio, go to object explorer and expend logins folder as in the below image:  Double click on desired user / login or right click and go to properties as in the below image: Click on Status and Enable / Disable user / login as in the below image:
Read more

How to Create a Database in MS SQL Server

Image
There are more than one ways to create a database in MS SQL Server. You can do it by using GUI from SQL Server management studio or you can use T-SQL. In this blog I will demonstrate all possible options to create a database: TSQL:  CREATE DATABASE [DBName] CONTAINMENT = NONE ON PRIMARY (NAME = N'DBName', FILENAME = N'Path\DBName.mdf' , SIZE = 20480KB , FILEGROWTH = 51200KB) LOG ON (NAME = N'DBName_log', FILENAME = N'Path\DBName_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10240KB) Using GUI:    Using GUI is a simple method to create a Database in MS SQL Server, Below method is simple and basic for advanced option you will have the option to choose the option you need:       
Read more