www.javadevhome.com

Google
   

ORACLE

www.oracle.com
Documentation

Basic Commands

SELECT * FROM Persons
install:
create_user_oracle
./runInstaller

start:
linux:
su - oracle
$ORACLE_HOME/bin/dbstart # Start DB's
$ORACLE_HOME/bin/lsnrctl start # Start listener
$ORACLE_HOME/bin/namesctl start # Start OraNames (optional)
windows: (type it DON'T copy - paste)
Control panel -> Services
Command prompt:
C:\oracle9i\bin\oradim -startup -sid ORCL92 –usrpwd manager -starttype SRVC,INST -pfile C:\oracle9i\admin\ORCL92\pfile\init.ora
C:\oracle\10.2.0\db_1\BIN\oradim -STARTUP -SID MYDBNAME –SYSPWD manager -STARTTYPE SRVC,INST

stop:
$ORACLE_HOME/bin/dbshut # Stop DB's
C:\oracle9i\bin\oradim -shutdown -sid ORCL92 -shuttype SRVC,INST –shutmode A
C:\oracle\10.2.0\db_1\BIN\oradim -SHUTDOWN -SID MYDBNAME -SHUTMODE immediate

Default passwords:
SYS change_on_install
SYSTEM manager
DBSNMP dbsnmp
XDB change_on_install

To connect see: oracle/db/network/admin/listener.ora & tnsnames.ora

listener.ora :

# listener.ora Network Configuration File: C:\oracle\product\10.2.0\db_1\network\admin\listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = MYHOSTNAME)(PORT = 1521))
)
)

tnsnames.ora :

# tnsnames.ora Network Configuration File: C:\oracle\product\10.2.0\db_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
MYDBNAME =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = MYHOSTNAME)(PORT = 1521))
)
(CONNECT_DATA =
(SID = MYDBNAME)
)
)

listener:
lsnrctl start
lsnrctl stop
export ORACLE_SID=MYDBNAME
sqlplus '/ as sysdba'

connect_DB:
export ORACLE_SID=MYDBNAME
sqlplus /nolog
connect mySchema/myPassword as sysdba
# Note that : userName = schemaName (in general)
connect sys/change_on_install as sysdba

see runnig DB:
#ps -fu oracle
the process list ends with DBname

shutdownDB:
connect_DB
shutdown abort
shutdown immediate
shutdown normal
shutdown transactional

startupDB:
conect_DB
startup
startup mount
startup restrict
startup force
startup open recover

call sql script:
SQL> @scriptFile.sql

SQL> create user alfredo identified by passw0rd;
SQL> grant dba to alfredo;

list all databases:
ls oracle/oradata
find / -name *.dbf *.ctl

list all schemas:
select username FROM all_users;

list tables:
SELECT table_name FROM user_tables;
SELECT table_name FROM all_tables;

call a oracle function;
select supplier_seq.nextval from dual; // calls th function returns nextval

incrementing index:
CREATE SEQUENCE supplier_seq MINVALUE 1 START WITH 1 INCREMENT BY 1 CACHE 20;
INSERT INTO suppliers
(supplier_id, supplier_name)
VALUES
(supplier_seq.nextval, 'Kraft Foods');

counter:
select supplier_seq.nextval from dual;
INSERT INTO gcscadmin.CDR (COUNTER,MSISDN) VALUES (gcscadmin.cdr_seq.nextval,'mobileMSISDN')

Adding single quote in a String:
'John''s car' : John's car
'''''' : ''
String concatenation
'ali '||'veli'
'SMRO_070417113125_'||gcscadmin.cdr_seq.currval

export data:
exp scott/tiger file=emp.dmp log=emp.log tables=emp rows=yes indexes=no
exp scott/tiger file=emp.dmp tables=(emp,dept)

exp userid=scott/tiger@orcl parfile=export.txt
... where export.txt contains:
BUFFER=100000
FILE=account.dmp
FULL=n
OWNER=scott
GRANTS=y
COMPRESS=y

import data:
imp scott/tiger file=emp.dmp full=yes
imp scott/tiger file=emp.dmp fromuser=scott touser=scott tables=dept

triggers:
select trigger_name from user_triggers;

date format : to_date('2006/04/05', 'yyyy/mm/dd')


Oracle XML DB Ports conflict with Tomcat 8080:
1.Check that the Oracle listener is running:
Make sure that the Oracle TNS Listener service (for example, OracleOraDb10g_home1TNSListener) is set to Started.
lsnrctl start
lsnrctl stop
2.Log into SQL*Plus or iSQL*Plus as SYS or XDB using the SYSDBA role.
# sqlplus "sys/welcome as SYSDBA"
3.Run the catxdbdbca.sql script.
For example, to use 2200 for the FTP port and 8200 for the HTTP port
SQL> @$ORACLE_HOME/rdbms/admin/catxdbdbca.sql 2200 8200
4.Exit SQL*Plus or iSQL*Plus.

Convert to raw data
UTL_I18N.STRING_TO_RAW (data, 'AL32UTF8')