Posts Tagged ‘oracle’

Find table names and column names inside a database using oracle

Display all table names in a database

select * from tab;


Display number of tables in a database

select count(*) from tab;


Find table exits in a database

select * from tab where tname like '%STUDENT%';


Display all column names in a database

select * from USER_TAB_COLUMNS;


Display number of columns in a database

select count(*) from USER_TAB_COLUMNS;


Find column name exits in a database

select * from USER_TAB_COLUMNS where COLUMN_NAME like '%STUDENT_ID%';


Find column name exits on a table in a database

select * from USER_TAB_COLUMNS where TABLE_NAME like '%STUDENT%' and COLUMN_NAME like 'STUDENT_ID';


 

Read more »

Oracle DataPump 5 – Import database using IMPDP command

Create a user named jijo then grant directory and other privileges to user jijo. If you are not familiar with how to create and grant privileges then read the post –> Oracle DataPump 3 – Create and grant privileges to user

SQL> CREATE USER jijo IDENTIFIED BY kjose DEFAULT TABLESPACE SYSAUX TEMPORARY TBLESPACE "TEMP";

User created.

SQL> GRANT connect, create procedure, create session, create table, create type, create view, create synonym,create sequence, create trigger, resource TO jijo;

Grant succeeded.

SQL> GRANT READ, WRITE ON DIRECTORY TEST_DIR TO jijo;

Grant succeeded.


IMPDP Command Format

After creating the user we can import the database of  scott into jijo by using the following command.

impdp <username>/<password> DIRECTORY=<directory_name> DUMPFILE=<dump_file_name> REMAP_SCHEMA=<old_schema>:<new_schema>

or

impdp jijo/kjose DIRECTORY=TEST_DIR DUMPFILE=DB11G.dmp REMAP_SCHEMA=scott:jijo

Read more »

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 »