[Oracle] Some Useful Commands

--Drop User
drop user <user-name> CASCADE;

--Create User
CREATE USER <user-name> PROFILE "DEFAULT" IDENTIFIED BY <password> TEMPORARY TABLESPACE "TEMP" ACCOUNT UNLOCK ;
GRANT "RESOURCE", "CONNECT", "DBA" TO <user-name>;

--Database character set
SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER='NLS_CHARACTERSET';

-- User constraints
SELECT * FROM user_constraints WHERE 
 table_name = '<table-name>' AND constraint_type = 'U'
 and constraint_name = '<constraint-name>';

-- Indexes
SELECT * FROM   user_indexes WHERE  index_name = '<index-name>'

-- See User session
select sid,serial#,status,osuser,machine, program, logon_time, username , 'ALTER SYSTEM DISCONNECT SESSION '''||SID||','||SERIAL#||''' IMMEDIATE;' Kill_Command
from v$session where username in( '<user1>','<user2>') And program not in ('SQL Developer','sqlplus.exe');

-- See locked resources/objects
select object_name, s.sid, s.serial#, p.spid, 'ALTER SYSTEM DISCONNECT SESSION '''||s.sid||','||s.serial#||''' IMMEDIATE;' Kill_Command
from v$locked_object l, dba_objects o, v$session s, v$process p
where l.object_id = o.object_id and l.session_id = s.sid and s.paddr = p.addr;

-- See Version
SELECT * FROM V$VERSION

-- BLOB to Text Conversion
select utl_raw.cast_to_varchar2(dbms_lob.substr(value)) from assumption where assumptionsetid = 84;

-- Search table by column name
select distinct owner, table_name 
from all_tab_columns 
where column_name in ('lic%');

SELECT * FROM COLS WHERE COLUMN_NAME = 'lic%'

[Oracle] Import Database Dumps using "impdp" command

Below are few commands to import database dump in Oracle 11g.

--1. Drop existing user, if already exists:
DROP USER <dest_db> CASCADE;

--2. Create user and schema and grant rights:
CREATE USER <dest_db> PROFILE "DEFAULT" IDENTIFIED BY <password> 
     TEMPORARY TABLESPACE "TEMP" ACCOUNT UNLOCK ;
GRANT "RESOURCE", "CONNECT", "DBA" TO <dest_db>;

--3. test if impdp is support, from command prompt, just write:
impdp

/*it should say:
Import: Release 11.2.0.1.0 - Production on Sat Feb 16 21:13:27 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
*/

--4. To import through dmp file, from command prompt, just write:
impdp <Dest_db>/<password> remap_schema=<source_db>:<dest_db> directory=DATA_PUMP_DIR EXCLUDE=STATISTICS dumpfile=<source_db_dumpfile>.dmp

--where, DATA_PUMP_DIR is the default Oracle directory. To verify write:
select * from ALL_DIRECTORIES

--5. If it says tablespace <tablespace> doesnot exists, then use:
impdp <dest_db>/<password> remap_schema=<source_db>:<dest_db> remap_tablespace=<dest_tablespace>:<source_tablespace> directory=DATA_PUMP_DIR EXCLUDE=STATISTICS dumpfile=<source_db_dumpfile>.dmp

-- where USERS is the table space of destination user, to verify:
select default_tablespace from dba_users where username='<dest_db>';

--6. If it says "only one COMPRESS or NOCOMPRESS clause may be specified", then use following command:
impdp <dest_db>/<password> remap_schema=<source_db>:<dest_db> remap_tablespace=<dest_tablespace>:<source_tablespace> directory=DATA_PUMP_DIR EXCLUDE=STATISTICS dumpfile=<source_db_dumpfile>.dmp transform=segment_attributes:n 

--7. Verify if import is successful by getting the object count both valid/invalid.
select count(*) from user_objects;
select count(*) from user_objects where status='INVALID';

Enjoy!