Summary of Oracle commands

Listener & Connect:
Easy connect:

SQL> CONNECT hr/hr@host1.home.com:1521/dba10g

SQL> CONNECT username/passwd@10.10.10.10:1521/SERVICE_NAME
$ sqlplus xxxx/yyyy@10.l0.10.10:1521/SERVICE_NAME

$ sqlplus xxxx/yyyy@"(description=(address=(protocol=TCP) (host=10.10.10.10) (port=1521)) (connect_data=(SID=DEV)))"

Test Listener:

$ tnsping db.us.oracle.com:1521/dba10g

$ tnsping orcl

$ ps -ef | grep tns

$ netstat -lt | grep 1521

$ netstat -anp|grep 1521

$ watch -n 1 "lsnrctl services"

Register a new listener:

in tnsnames.ora add:
my_local_lsn= (ADDRESS = (PROTOCOL = TCP)(Host = x.x.x.x)(Port = 1521))

in sqlplus:
ALTER SYSTEM SET LOCAL_LISTENER='my_local_lsn';

OR:

without any changes in tnsnames.ora:
ALTER SYSTEM SET LOCAL_LISTENER='(ADDRESS = (PROTOCOL = TCP)(Host = x.x.x.x)(Port = 1521))';

 Oracle Enterprise Manager 10g Database Control:

# emca -deconfig dbcontrol db (to drop old config files)
# emca -config dbcontrol db (to create new config files)
# emca -config dbcontrol db -repos recreate

 Flashback:

• Row movement must be enabled on the table that you are performing the flashback operation on:

ALTER TABLE employees ENABLE ROW MOVEMENT;

• Flashback Query:

UPDATE employees SET salary =

(SELECT salary FROM employees
AS OF TIMESTAMP TO_TIMESTAMP
('2005-05-0411:00:00','yyyy-mm-ddhh24:mi:ss')
WHERE employee_id = 200)

WHERE employee_id = 200;

SELECT versions_xid, salary FROM employees
VERSIONS BETWEEN TIMESTAMP t1 and t2
WHERE employee_id = 200;