[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%'