Oracle

Administration

1. Manual Startup

#xhost + //disables access control
#su - oracle //switch to user oracle (execute home folder)
#export ORACLE_SID=crm //for crm SID
#sqlplus / as sysdba //start sqlplus
SQL>startup //start it up
SQL>quit //
#lsnrctl start lsnrcrm //start listener
#emctl start dbconsole //Enterprise Manager

shutting down = shutdown immediate

2. Related (run as user oracle)

#dbca //db config assistant
db type: General Purpose
Global Db Name : eg dump.fsm
SID : eg dumpfsm

Option - > Generate DB Creation Scripts (Default location: /home/oracle/oracle/admin/dump/scripts)

#netca //configure listener

3. Enterprise Manager
Login:
User: sys
Pass: invent
Connect: SYSDBA
DB Login:
User: sys
Pass: invent
Connect: SYSDBA

Hosted(172.100.3.22):
User:sys
Pass:Imis2goke
Connect: SYSDBA

System Administration
1. Local http://localhost.localdomain:5500/em/
2. Hosted http://172.100.3.22:5501/em/

Manage (Create,Edit n Delete) Users, Roles, etc

Create User > Edit Roles > Priviledges >

Roles
All Except:
-DBA
-GLOBAL_AQ_USER_ROLE

Privileges
All Except:
-FREEZE_ANY_WORKSPACE


String Manipulation
Position Of Substring
INSTR(string, y [,start [, o]]) returns the location of substring y within string.
Optionally you can specify the start at a start position from the beginning of string and find the specific numbered occurance o of y.
For example INSTR(’abracadabraabraxas’, ’ra’, 4, 2) returns the location of the second occurance of the substring ’ra’ starting from location 4. It gives location 14 since the search ignores the first 3 characters (the first character is location 1).


Extract Substring
SUBSTR(string, start [, count]) returns a string by clipping out the subsection of string starting at location start and including count characters.
If count is omitted it will clip until the end of the string.

//get rid of leading and trailing characters
update channeltemp set channelplanname = substr(channelplanname,2, length(CHANNELPLANNAME)-2)

Execution Plan


Exception Handling

Trees - Connect By