connect to the database without TNS configure

1. with TNS In replace of Service name

sqlplus> conn scott/tiger@'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP) (HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(service_name=orcl)))'

--need to write in one line

2. Easy connection Method

In sqlnet.ora

NAMES.DIRECTORY_PATH=(TNSNAMES, EZCONNECT)

Then

sqlplus> conn scott/tiger@//localhost:1521/halim

---here halim is sid

ORA-02429: cannot drop index used for enforcement of unique/primary key

ORA-02429: cannot drop index used for enforcement of unique/primary key
========================================================================

---when I execute

SQL> drop index IND_ON_HALIM_TEST ; ----its reply

ORA-02429: cannot drop index used for enforcement of unique/primary key


If it is an unique index that enforces unique constraint
you cannot simply drop it



You have to drop unique constraint BY

alter table tablename drop constraint constraintname;



=================================================
Hands on Example
=================================================

SQL*Plus: Release 11.1.0.6.0 - Production on Wed Jun 30 16:21:01 2010

Copyright (c) 1982, 2007, Oracle. All rights reserved.

SQL> CONN STLBAS/STLBAS@JUNCLS
Connected.
SQL>
SQL>
SQL> DROP TABLE HALIM_TEST ;

Table dropped.

SQL>
SQL> CREATE TABLE halim_test (id number,name varchar2(30)) ;

Table created.

SQL>
SQL>
SQL> INSERT INTO halim_test VALUES (100,'Mr. Halim');

1 row created.

SQL>
SQL>
SQL> INSERT INTO halim_test VALUES (100,'Mr Cadot');

1 row created.

SQL>
SQL>
SQL> INSERT INTO halim_test VALUES (200,'Mr. Arif');

1 row created.

SQL>
SQL>
SQL> INSERT INTO halim_test VALUES (300,'Mr. Akash');

1 row created.

SQL>
SQL> COMMIT;

Commit complete.

SQL>
SQL>
SQL> create index IND_ON_HALIM_TEST on halim_test(ID);

Index created.

SQL> ALTER TABLE halim_test ADD PRIMARY KEY (ID) NOVALIDATE ;

Table altered.

SQL>
SQL>
SQL>
SQL> DROP index IND_ON_HALIM_TEST ;
DROP index IND_ON_HALIM_TEST
*
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key

SQL>
SQL>
SQL>
SQL>
SQL> SELECT owner, constraint_name, constraint_type, table_name
2 FROM user_constraints
3 WHERE table_name = 'HALIM_TEST';

OWNER CONSTRAINT_NAME C TABLE_NAME
------------------------------ ------------------------------ - ----------------

STLBAS SYS_C0084077 P HALIM_TEST

SQL>
SQL>
SQL> alter table halim_test drop constraint SYS_C0084077 ;

Table altered.

SQL>
SQL>
SQL>
SQL>
SQL> SELECT index_name, index_type, table_owner, table_name, table_type,
2 uniqueness
3 FROM user_indexes
4 WHERE table_name = 'HALIM_TEST';

INDEX_NAME INDEX_TYPE TABLE_OWNER
TABLE_NAME TABLE_TYPE UNIQUENES
------------------------------ --------------------------- ---------------------
--------- ------------------------------ ----------- ---------
IND_ON_HALIM_TEST NORMAL STLBAS
HALIM_TEST TABLE NONUNIQUE

SQL>
SQL>
SQL> drop index IND_ON_HALIM_TEST;

Index dropped.

SQL>
SQL>
SQL>
SQL>

ORA-02437: cannot validate - primary key violated

ORA-02437: cannot validate - primary key violated
====================================================



SQL*Plus: Release 11.1.0.6.0 - Production on Wed Jun 30 16:21:01 2010

Copyright (c) 1982, 2007, Oracle. All rights reserved.

SQL> CONN STLBAS/STLBAS@JUNCLS
Connected.
SQL>
SQL>
SQL> DROP TABLE HALIM_TEST ;

Table dropped.

SQL>
SQL> CREATE TABLE halim_test (id number,name varchar2(30)) ;

Table created.

SQL>
SQL>
SQL> INSERT INTO halim_test VALUES (100,'Mr. Halim');

1 row created.

SQL>
SQL>
SQL> INSERT INTO halim_test VALUES (100,'Mr Cadot');

1 row created.

SQL>
SQL>
SQL> INSERT INTO halim_test VALUES (200,'Mr. Arif');

1 row created.

SQL>
SQL>
SQL> INSERT INTO halim_test VALUES (300,'Mr. Akash');

1 row created.

SQL>
SQL> COMMIT;

Commit complete.

SQL>
SQL> ALTER TABLE halim_test ADD PRIMARY KEY (ID) NOVALIDATE ;
ALTER TABLE halim_test ADD PRIMARY KEY (ID) NOVALIDATE
*
ERROR at line 1:
ORA-02437: cannot validate (STLBAS.SYS_C0084074) - primary key violated


SQL>
SQL>
SQL> ALTER TABLE halim_test ADD PRIMARY KEY (ID) DISABLE ;

Table altered.

SQL>
SQL> ALTER TABLE halim_test ENABLE NOVALIDATE PRIMARY KEY;
ALTER TABLE halim_test ENABLE NOVALIDATE PRIMARY KEY
*
ERROR at line 1:
ORA-02437: cannot validate (STLBAS.SYS_C0084075) - primary key violated

SQL>
SQL> ALTER TABLE halim_test ENABLE NOVALIDATE PRIMARY KEY;
ALTER TABLE halim_test ENABLE NOVALIDATE PRIMARY KEY
*
ERROR at line 1:
ORA-02437: cannot validate (STLBAS.SYS_C0084076) - primary key violated



======================
CAUSE & sOLUTION:
======================

This error occurs because it tries to create a unique index for the unique or primary
key index. The uniqueness is checked during the creation of the unique index.


Solution
=============
------- First, create a NON-UNIQUE index on the same fields that you want to
--------include in the constraint.
SQL>
SQL>
SQL> alter table HALIM_TEST drop constraint SYS_C0084076 ;

Table altered.

SQL>
SQL>
SQL> create index IND_ON_HALIM_TEST on halim_test(ID);

Index created.


-------- Then add the unique or primary key constraint.

SQL>
SQL> ALTER TABLE halim_test ADD PRIMARY KEY (ID) NOVALIDATE ;

Table altered.

oR

SQL>
SQL>
SQL> ALTER TABLE HALIM_TEST ENABLE NOVALIDATE PRIMARY KEY;

Table altered.

SQL>
SQL>

Oracle Flashback Version Query and Oracle Flashback Transaction Query

Oracle Flashback Version Query and Oracle Flashback Transaction Query
====================================================================
No need to db archive mode , recyclebin on


You are informed that the record empno=7499 is missing from
the scott.EMP table. You need to identify the following:

delete from emp where empno=7499;
commit ;

* The transaction identifier of the transaction that deleted the empno record
* The SQL statements necessary to undo the delete
* The user who executed the transaction

Which would you use?


A. Oracle Flashback Drop only
B. Oracle Flashback Version Query only
C. Oracle Flashback Version Query and Oracle Flashback Transaction Query
D. RMAN REPORT command only

Ans : C

Practical example :
-----------------

1. --SHOW all rows of emp table

select * from emp

2. --Delete one row

delete from emp where empno=7499

3. --commit the change

commit

4.----Oracle Flashback Version Query to find out the identified (history) of the transaction.

SELECT versions_xid, versions_operation
FROM emp
VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE
WHERE empno=7499;


5.---Oracle Flashback Transaction Query to see the all information (audit) as well as undo sql


SELECT XID, COMMIT_TIMESTAMP, LOGON_USER,OPERATION, TABLE_NAME, TABLE_OWNER, UNDO_SQL
FROM FLASHBACK_TRANSACTION_QUERY
WHERE xid=HEXTORAW('09002700CE180000');

Oracle Database Security Checklist

Oracle Database Security Checklist
==================================
For a production Database, must need to check the following points for
better security.



1. Protecting the database environment.............................................................
2. Install only what is required..........................................................................
3. Lock and expire default user accounts...........................................................
4. Changing default user passwords...................................................................
5. Change passwords for administrative accounts.............................................
6. Change default passwords for all users...........................................................
7. Enforce password management......................................................................
8. Secure batch jobs............................................................................................
9. Manage access to SYSDBA and SYSOPER roles..........................................
10. Enable Oracle data dictionary protection......................................................
11. Follow the principle of least privilege.............................................................
12. Public privileges..............................................................................................
13. Restrict permissions on run-time facilities......................................................
14. Authenticate clients........................................................................................
15. Restrict operating system access.....................................................................
16. Secure the Oracle listener..............................................................................
17. Secure external procedures.............................................................................
18. Prevent runtime changes to listener................................................................
19. Checking network IP addresses......................................................................
20. Harden the operating system.........................................................................
21. Encrypt network traffic..................................................................................
22. Apply all security patches...............................................................................
23. Report security issues to Oracle....................................................................