Posts Tagged ‘oracle’

DROP multiple TABLE / TRIGGER / FUNCTON in oracle using PL SQL

To drop more than one tables having a matching criteria then we should use a PL SQL statement, in oracle there is a table called USER_OBJECTS having name of all the tables for that particular user.

DROP TABLE is a DDL statement so that we need EXECUTE IMMEDIATE privilege for dropping table from a script, more easy way to drop table is to generate DROP TABLE command using a script and run that command.

The following PL SQL script generate drop table command for all tables having table name contains  word 'test'

SET SERVEROUTPUT ON;

DECLARE
  object_name varchar2(100);
  cursor cur1 is SELECT object_name FROM user_objects WHERE object_type='TABLE' AND object_name LIKE '%test%';
BEGIN

  open cur1;
  loop
        fetch cur1 into object_name;
        exit when cur1%notfound;
      dbms_output.put_line('DROP TABLE ' || object_name || ';' );
    end loop;
    close cur1;

END ;

Output

anonymous block completed

DROP TABLE USER_TEST;

Read more »

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 »

Last updated by at .