Tuesday, November 15, 2016

Copy a table from one database to another database in Oracle


We can use any of following  method to copy one table from one database to another database.

1) using create table as select with help of DB_LINK between two databases

create table target_table_name as select * from source_table_name@dblink 

2) Using Legacy exp/imp utility

3) Using datapump expdp/impdp

4) Using SQLPlus copy command

5) Using other tools like SQL developer/Toad for Oracle etc.

Here I am giving only example of #4 (using SQLplus copy command). becuase this is looks like interesting to me.

SQL> COPY {FROM database | TO database | FROM database TO database} {APPEND|CREATE|INSERT|REPLACE} destination_table [(column, column, column, ...)]
USING query

example - (from any machine in your network)

SQL> copy from hr/hr@orcl to scott/tiger@orcl2 create employees_copy using select * from employees ;


Be carefull- SQLPlus COPY supports only the following datatypes:

    CHAR
    DATE
    LONG
    NUMBER
    VARCHAR2

Friday, August 26, 2016

How to Generate or Create ERD diagram with Oracle SQL Developer- Version 4.0.3.16


1)     Make an connection to your schema
2)      Then Go to tab View --> Data Modeler --> Browser  (double click) . it will open a window like below screen
               

3)      Then Right click on Relational  Models--> New relational models
 
4)      Then select your required tables from connection menu and drag these to New Relational model page.  There you go. Now you can decorate yourself .