Oracle Usefull Commands

ORACLE COMMANDS :

Backup Directory Creation
---------------------------------
Login  as sysdba

CREATE OR REPLACE DIRECTORY BACKUP_DIR AS 'D:\DB_Backup';
GRANT READ, WRITE ON DIRECTORY BACKUP_DIR TO database_name;


CREATE OR REPLACE DIRECTORY BACKUP_DIR AS 'D:/DB_Backup';



Export
--------
expdp username/password DIRECTORY=BACKUP_DIR DUMPFILE=backupfilename.dmp


Import
--------
impdp mi3ubs/mi3ubs directory=BACKUP_DIR dumpfile=mi3ubs.dmp logfile=mi3ubs.log


To make the username and password case insensitive : 
------------------------------------------------------------------------
ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = FALSE;


 To Know the version of oracle 
---------------------------------------
select * from v$version where banner like 'Oracle%'; 

Decode function
----------------------
select decode(sex, 'M','Male','F','Female','Unknown') from employees;


HOW TO SELECT ALL THE USERS AND STATUS IN ALL DATABASE:
1. select username,account_status from dba_users;

HOW TO CREATE NEW USER:
1. CREATE USER username IDENTIFIED BY apassword;
2. GRANT CONNECT TO username;

HOW TO CHANGE PASSWORD FOR LOGGEDIN USER :
1. password

HOW TO CHANGE PASSWORD FOR DIFFERENT USER:
1. ALTER USER username IDENTIFIED BY password;
2. ALTER USER username IDENTIFIED BY password ACCOUNT UNLOCK;


HOW TO CREATE NEW SID :
1. set ORACLE_SID=ORA10

HOW TO CREATE NEW SERVICE FOR AN SID :
1. oradim -new -sid %ORACLE_SID% -intpwd MYSECRETPASSWORD -startmode M

HOW TO LOGIN INTO ORACLE DATABASE WITHOUT USERNAME AND PASSWORD:
1. sqlplus /nolog


HOW TO GET ROLE FOR AN CURRENT USER :
1. select * from user_role_privs;

HOW TO CHANGE PRIVILEGES FOR AN TABLE TO USER :
1. GRANT ALL ON salary TO jfee;


When opening SqlDeveloper its asking for java.exe fullpath. If you are give java full path also sometime it will not work. follow below steps to resolve this issue.

1. Create JDK_HOME environmental variable path "ORACLE_HOME/jdk/bin"
2. create shortcut for sqldeveloper.exe.
3. right click on sqldeveloper shortcut and goto shortcut tab
4 click Advanced button and check "Run as Adiministrator" and click ok
5. double click sqldeveloper shortcut and give jdk/bin path as setted in environmental variable path.
6. thats it................... 


KEY INFORMATION'S ABOUT ORACLE :
1. Each SID's creation one password file will create in "ORACLE_HOME/database/PWDsidname.ORA" and new service will create.