Dec 10, 2010 | Databases, oracle pl/sql, sqlplus
Starting an Oracle Database and EM console (10g)
login/su as the user who installed the oracle software (i.e. oracle)
wins: [bash]set ORACLE_SID=sid_name #the name SID given when creating database[/bash]
linx: [bash]export ORACLE_SID=sid_name
sqlplus /nolog #just log onto the sqlplus console
connect user/passw as sysdba #log-in as a sysdba with user | default: system/manager
#output: Connected to an idle instance. (within SQL console)
startup #to startup the DB
exit #to commit, disconnect and log-off from db-console
lsnrctl status #to show the status of the listener ( may need ./lsnrctl)
lsnrctl start #to start the listener, based on the SID set above
#output: The command completed successfully
emctl start dbconsole #to start the Oracle Enterprise Manager console (web)
#output: ….started.[/bash]
Notes:
- listener.ora //the file that has the connection info for DBs
path: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
(or wherever the oracle DB product was installed)
Problems initializing:
- Shutdown and restart both database(sqlplus) and listener(linx shell)
- Try deleting and re-creating the database again (if this is a newly created db with no data, else BACK-IT-UP first!!!)
Sep 6, 2010 | oracle pl/sql
To enable output to console window in Oracle SQL Developer (as of 2.1.1.64)
- From the menu go to: View > Dbms Output
- From the new window (popped-up in the log area) click on the plus sign to enable output
- Enter the output you want i.e.:
[sql]DBMS_OUTPUT.PUT_LINE(‘CLA_CLA_ID: ‘ || V_ASY_CLA_ID2 || ‘, ‘ ||V_ASY_SURNAME2);[/sql]
- This should display when SQL code is run
Sep 3, 2010 | oracle pl/sql |
[sql]SELECT emp, COUNT(emp) AS NumOccurrences
FROM users GROUP BY emp
HAVING ( COUNT(emp) > 1 )[/sql]
also, find only one record:
[sql]SELECT emp
FROM users GROUP BY emp
HAVING ( COUNT(emp) = 1 )[/sql]
May 4, 2010 | Linux
To check environment vars in a linux setup:
[bash]set[/bash]
…this should return a list of the environment entries and can retrieve entries like:
[bash]HOME # the active user’s home dir
HOSTNAME # the server’s name
ORACLE_HOME # the default path of an oracle installation
ORACLE_SID # the service/name of a db instance[/bash]
May 4, 2010 | Linux
When checking if a listener is active or not in Oracle:
lsnrctl status
This command should return something similar to the following. Check-out the Services summary:
Services Summary…
Service “PLSExtProc” has 1 instance(s).
Instance “PLSExtProc”, status UNKNOWN, has 1 handler(s) for this service…
Service “domain.name.com” has 1 instance(s).
Instance “dmcl”, status READY, has 3 handler(s) for this service…
Comments