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
|
1 |
CREATE TABLESPACE TBL_SPACE_NAME DATAFILE 'C:\Oracle\TBL_SPACE_NAME.DBF' SIZE 512M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO; |
Alter Table Space
|
1 |
ALTER DATABASE DATAFILE 'C:\Oracle\ETPR2\TBL_SPACE_NAME.DBF' RESIZE 1024M; |
Create Users
|
1 |
create user MYUSER PROFILE "DEFAULT" identified by MYPWD default tablespace TABLESPACE TBL_SPACE_NAME temporary tablespace temp; |
Create or Replace writable directory for oracle
|
1 2 |
CREATE OR REPLACE DIRECTORY MYDIR AS 'C:\MYPUMPDIR'; GRANT READ, WRITE ON DIRECTORY MYDIR to MYUSER; |
Import dump file from one schema to another
|
1 |
impdp MYUSER/MYPWD directory=MYPUMPDIR dumpfile=my_dmp_file.dmp schemas=myschema remap_schema=myschema:yourschema |
Export schema to dump file
|
1 |
expdp MYUSER/MYPWD directory=MYPUMPDIR dumpfile=my_dmp_file.dmp SCHEMAS=myschema |
Drop All from Schema
|
1 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
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
|
1 |
select * from dba_data_files; |
Get user grants
|
1 2 3 |
SELECT grantee , granted_role FROM dba_role_privs WHERE grantee = 'MYUSER' |
Know session/system variable status
|
1 2 3 4 5 6 7 |
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
|
1 |
sqlplus MYUSER/MYPWD@'(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521)))(CONNECT_DATA=(SID=MYSID)))' |
List PUMP Directories
|
1 |
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.
