Skip to main content

Oracle Data Dump Import and Export

Login to server :
sqlplus / as sysdba


exp SYSTEM/password FULL=y FILE=dba.dmp LOG=dba.log CONSISTENT=y
or
exp SYSTEM/password PARFILE=param.dat

where param.dat contains the following information 

FILE=dba.dmp 
GRANTS=y 
FULL=y 
ROWS=y 
LOG=dba.log

To dump a single schema to disk.

exp / FILE=dump.dmp OWNER=production

Export Specific tables to disk

exp SYSTEM/password FILE=export.dmp TABLES=(user1.table,user2.table)

Export Data in one user 

exp / FILE=export.dmp TABLES=(table1,table2)


Using imp: 

To import the full database exported in the example above.
imp SYSTEM/password FULL=y FIlE=dba.dmp

To import just the dept and emp tables from the scott schema
imp SYSTEM/password FIlE=dba.dmp FROMUSER=scott TABLES=(dept,emp)

To import tables and change the owner 

imp SYSTEM/password FROMUSER=blake TOUSER=scott FILE=blake.dmp TABLES=(unit,manager)


To import just the scott schema exported in the example above
imp / FIlE=scott.dmp


Comments

Post a Comment

Popular posts from this blog

Split String which has space and replace spaces with "/"

Sample String "ABCD123 ETS, NM" UPDATE TABLE_NAME SET COLUMN_NAME = SUBSTR( 'ABCD123 ETS, NM' , 0 , 4 ) || REPLACE ((SUBSTR(( REPLACE ( 'ABCD123 ETS, NM' , ', ' , '/' )), 5 ,INSTR(( REPLACE ( 'ABCD123 ETS, NM' , ', ' , ',/' )), ',' , 1 ) - 4 )), ' ' , '/' ) || REPLACE (SUBSTR( 'ABCD123 ETS, NM' ,INSTR( 'ABCD123 ETS, NM' , ',' , 1 ) + 2 ), 'NM' , 'NEW MEXICO' ) WHERE CONDITIONS;
Post a Comment
Read more

Extracting Data in a Database Using Python

Extracting data in a database using python. Using Python to extract data in a MySQL table first we need to pip install pymysql Create table along with connection to the mysql server using python import pymysql connection=pymysql.connect(host='host',port=port,user='user',password='password',db='schema') #creating cursor cursor= connection.cursor() #query for create Table TABLES={} TABLES['employees'] = ( "CREATE TABLE employees (" "PersonID int," "LastName varchar(255)," "FirstName varchar(255)," "Address varchar(255)," "City varchar(255)" ")") for name, ddl in TABLES.items(): cursor.execute(ddl) connection.commit() connection.close() Insert Data into the MySQL Table using Python import pymysql # Data set to insert insert_people=[("8","Perera","L.D","123",...
1 comment
Read more