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