Resolving ORA-01940 error

This post shows how to get around ORA-01940 error. You typically get this error when the schema you are trying to drop is connected to something (presumably another application).

The following script will allow you to kill the sessions and drop the schema.

set serveroutput on
DECLARE
  cursor c1 is select SID, SERIAL# from v$session where username = '<schema_name';
BEGIN
FOR rec IN c1 loop
EXECUTE IMMEDIATE 'alter system kill session '''||rec.SID||','||rec.SERIAL#||'''';
end loop;
DROP USER <SCHEMA_NAME> CASCADE;
END;

Useful Oracle SQL commands

This blog entry will be updated as I come across more common useful SQL queries.

View current schema:

select sys_context('userenv','current_schema') from dual;




Change Schema: alter session set current_schema=name_of_your_schema;



Grant access to tablespace to user (before a user created is allowed to create a table). grant all on all_tables to sys;



List of users who have privilege to create database linkSELECT * FROM dba_sys_privs WHERE privilege=’CREATE DATABASE LINK’
and admin_option=’YES’;


Grant permission to create database link privilegeGRANT CREATE DATABASE LINK TO sys WITH ADMIN OPTIONS



Unlock a schema or user accountALTER USER schema_name account unlock; 

Importing Database from a DMP file

imp schema_name/schema_password IGNORE=Y FILE=EXP_SCHEMA_NAME_SCHEMA.DMP LOG=IMPORT_LOG_FILE.log