Oracle SQL Command Handbook

Every developer works with database in some form or other. Either he directly gets involved with PL/SQL Programming where he writes huge procedures and functions or some times he ends up writing only select statements for some web service or user interface He gets exposure to multiple relational databases and starts comparing different database features and depending upon his needs he likes one over another. But what ever he do, he needs to keep few set of SQL commands handy so that he can use them when ever in needs. After all one can not remember every thing he learns, but keeping note of thing will definitely going to save a lot. In this post I will try to document all the commands that I kept note of 🙂

Creating Table Space of given size

CREATE TABLESPACE TBL_SPACE_NAME DATAFILE 'C:\Oracle\TBL_SPACE_NAME.DBF' SIZE 512M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

Alter Table Space

ALTER DATABASE DATAFILE 'C:\Oracle\ETPR2\TBL_SPACE_NAME.DBF' RESIZE 1024M;

Create Users

create user MYUSER PROFILE "DEFAULT" identified by MYPWD default tablespace TABLESPACE TBL_SPACE_NAME temporary tablespace temp;

Create or Replace writable directory for oracle

CREATE OR REPLACE DIRECTORY MYDIR AS 'C:\MYPUMPDIR';
GRANT READ, WRITE ON DIRECTORY MYDIR to MYUSER;

Import dump file from one schema to another

impdp MYUSER/MYPWD directory=MYPUMPDIR dumpfile=my_dmp_file.dmp schemas=myschema remap_schema=myschema:yourschema

Export schema to dump file

expdp MYUSER/MYPWD directory=MYPUMPDIR dumpfile=my_dmp_file.dmp SCHEMAS=myschema

Drop All from Schema

select 'drop '||object_type||' '|| object_name|| DECODE(OBJECT_TYPE,'TABLE',' CASCADE CONSTRAINTS;',';') from user_objects order by object_type

This will give you set of queries that you need to copy and execute on either sqlplus or sqldeveloper
Know tables space usage

select a.TABLESPACE_NAME,
a.BYTES bytes_used,
b.BYTES bytes_free,
b.largest,
round(((a.BYTES-b.BYTES)/a.BYTES)*100,2) percent_used
from 
(
select TABLESPACE_NAME,
sum(BYTES) BYTES 
from dba_data_files 
group by TABLESPACE_NAME
)
a,
(
select TABLESPACE_NAME,
sum(BYTES) BYTES ,
max(BYTES) largest 
from dba_free_space 
group by TABLESPACE_NAME
)
b
where a.TABLESPACE_NAME=b.TABLESPACE_NAME
order by ((a.BYTES-b.BYTES)/a.BYTES) desc;

Know tables space and data file loacations

select * from dba_data_files;

Get user grants

SELECT grantee , granted_role
FROM dba_role_privs
WHERE grantee = 'MYUSER'

Know session/system variable status

SELECT statistics_name,
session_status,
system_status,
activation_level,
session_settable
FROM   v$statistics_level
ORDER BY statistics_name;

Connect to remote db using sqlplus

sqlplus MYUSER/MYPWD@'(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521)))(CONNECT_DATA=(SID=MYSID)))'

List PUMP Directories

select * from dba_directories

I hope all the readers find this command compilation useful. Feel free and comment with any command you want to add in above list.