Oracle Cursor With Basic Loop.

Using oracle cursor with basic loop.

DECLARE
CURSOR C1 IS
SELECT EMPLOYEE_ID, LAST_NAME
FROM EMPLOYEES
WHERE DEPARTMENT_ID=90;

V_EMPID VARCHAR2(100);
V_NAME  VARCHAR2(300);

BEGIN

OPEN C1;

LOOP

FETCH C1 INTO V_EMPID, V_NAME;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(V_EMPID ||' '||V_NAME);

END LOOP;

END;

Oracle External Table

Oracle External Table:- Oracle External Table are use for the purpose of load data  from external sources such as spreadsheet data (csv format) , text file or any others. 

Limitations of  External Tables  
There are some limitations  to using external table

DML Operation are not support. Oracle external tables are read-only, but the base data can be edited in any text editor. Poor response for high-volume queries.

CREATE OR REPLACE DIRECTORY
DATA_FILE  AS
'D:\app\';


CREATE TABLE ext_table(
emp_id    VARCHAR2(20),
emp_name  VARCHAR2(150),
COMMENTS       VARCHAR2(300)
)
ORGANIZATION EXTERNAL (
TYPE oracle_loader
DEFAULT DIRECTORY DATA_FILE
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
LOGFILE 'data.log'
BADFILE 'data.txt'
FIELDS TERMINATED BY ','
MISSING FIELD VALUES ARE NULL
)
LOCATION('Data.csv')
)
REJECT LIMIT UNLIMITED;

Password Strength Check In Oracle APEX

Step 1: - Create a page and go to Header Text of the page and past the following ..

<style>
#passwordStrength
{
        height:10px;
        display:block;
        float:left;
}
.strength0
{
        width:190px;
        background:#cccccc;
}
.strength1
{
        width:45px;
        background:#ff0000;
}
.strength2
{
        width:90px;  
        background:#ff5f5f;
}
.strength3
{
        width:135px;
        background:#56e500;
}
.strength4
{
        background:#4dcd00;
        width:160px;
}
.strength5
{
        background:#399800;
        width:190px;
}
</style>

Step 2. Create an password item named (P2_PASSWORD)

Go to the HTML Form Element Attributes Of  P2_PASSWORD Item and past the following onkeyup="return passwordStrength();"

Go to the Pre Element Text Of  P2_PASSWORD Item and past the following

    <script language="javascript">
    function passwordStrength(){
    var pwd = document.getElementById('P2_PASSWORD');
    var password = document.getElementById("P2_PASSWORD").value;
    var desc = new Array();
            desc[0] = "Very Weak";
            desc[1] = "Weak";
            desc[2] = "Better";
            desc[3] = "Medium";
            desc[4] = "Strong";
            desc[5] = "Strongest";
            var score   = 0;
            if (password.length > 6) score++;
            if ( ( password.match(/[a-z]/) ) && ( password.match(/[A-Z]/) ) ) score++;
            if (password.match(/\d+/)) score++;
            if ( password.match(/.[!,@,#,$,%,^,&,*,?,_,~,-,(,)]/) ) score++;
            if (password.length > 12) score++;
            document.getElementById("passwordDescription").innerHTML = desc[score];
            if (password.length==0) {
                 document.getElementById("passwordDescription").innerHTML  = 'Password not entered';
           }

          document.getElementById("passwordStrength").className = "strength" + score;

    }
    </script>

Go to the Post Element Text Of  P2_PASSWORD Item and past the following 

    <div id="passwordDescription">Password not entered</div>

    <div id="passwordStrength" class="strength0">
    </div>

Now run your page and check

ORA-02429: cannot drop index used for enforcement

Cause : It is an unique index that enforces unique constraint. It can not drop

DROP INDEX SY_CURRENT_USERCOMP_UNIQUE;

Action : You need to drop unique constraint BY

ALTER TABLE TABLE_NAME DROP CONSTRAINT CONSTRAINT_NAME

ALTER TABLE SY_CURRENT_USER DROP CONSTRAINT SY_CURRENT_USERCOMP_UNIQUE;

ORA-01489: result of string concatenation is too long

Select ('data:image/png;base64,')||IMAGE Value
From(
WITH xml AS
     (SELECT DBMS_XMLGEN.getxmltype
                        ('select cust_img from MEMIMG.WEB_IMAGE_TEMP1')
                                                                       AS xml
        FROM DUAL)
SELECT EXTRACTVALUE (xs.object_value, '/ROW/CUST_IMG') AS image
  FROM xml x
    , TABLE (XMLSEQUENCE (EXTRACT (x.xml, '/ROWSET/ROW'))) xs)
                                                                                  *
ERROR at line 1:
ORA-01489: result of string concatenation is too long

Cause : This problem occurred when it can try to return data which are grater then 4000 characters. Varchar2 can return maximum 4000 characters.

This problem can also occurred when we try to CONCAT a VARCHAR2 with CLOB.


Solution: You can use TO_CLOB For Converting character data to clob values.


Select '<img src="'||Value||'"/>' Image
From (
Select TO_CLOB('data:image/png;base64,')||IMAGE Value
From(
WITH xml AS
     (SELECT DBMS_XMLGEN.getxmltype
                        ('select cust_img from MEMIMG.WEB_IMAGE_TEMP1')
                                                                       AS xml
        FROM DUAL)
SELECT EXTRACTVALUE (xs.object_value, '/ROW/CUST_IMG') AS image
  FROM xml x
    , TABLE (XMLSEQUENCE (EXTRACT (x.xml, '/ROWSET/ROW'))) xs)
)

Show Image In Form Form Row Data

1. Creating Table 
CREATE TABLE WEB_IMAGE_TEMP1
(
  SRLNO      NUMBER,
  CUST_ID    NUMBER,
  CUST_IMG   LONG,
  MIME_TYPE  VARCHAR2(2000 BYTE)
)

2. Create Query To Show Image In Forms (Specially In Oracle Apex. )

Select '<img src="'||Value||'"/>' Image
From (
Select TO_CLOB('data:image/png;base64,')||IMAGE Value
From(
WITH xml AS
     (SELECT DBMS_XMLGEN.getxmltype
                        ('select cust_img from MEMIMG.WEB_IMAGE_TEMP1')
                                                                       AS xml
        FROM DUAL)
SELECT EXTRACTVALUE (xs.object_value, '/ROW/CUST_IMG') AS image
  FROM xml x
    , TABLE (XMLSEQUENCE (EXTRACT (x.xml, '/ROWSET/ROW'))) xs)
)

ORA-02437 cannot validate

Cause :- This error occur when tries to create a unique index for the unique or primary key index but failed to create unique index.

Solution :-
1. Create an unique index on field.
2. Create NOVALIDATE Primary key.
3. Enable NOVALIDATE Primary Key.
 
CREATE INDEX IND_SY_MENU_USER ON SY_MENU_USER(USER_CODE, CHAILD_ID)

ALTER TABLE SY_MENU_USER
ADD CONSTRAINT PK_SY_MENU_USER_COMP PRIMARY KEY(USER_CODE, CHAILD_ID) NOVALIDATE ;

ALTER TABLE SY_MENU_USER ENABLE NOVALIDATE PRIMARY KEY

Export Import Oracle Dump File Using expde/impdp With Scheduler

Export Import Dump File 

Step 1:- Create Shell File Name exportimport.sh In (/u01/Script )
Step 2:- chmod u+x /u01/Script/exportimport.sh
Step 3:-


# 1. Set Your Environment Veritable.

export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
export ORACLE_SID=micrdb
export PATH=$PATH:$ORACLE_HOME/bin

# 2. Declare and initialize value or date time.

SET_DATE=`date +%d%m%Y`
export CDATE

DMP_FILE=backup_$SET_DATE.dmp
LOG_FILE=backup_$SET_DATE.log

export DMP_FILE LOG_FILE

# 3. Export Starting



'/u01/app/oracle/product/11.2.0/db_1/bin/expdp' system/system@micrdb schemas=micr,beftn directory=dmp_bkp dumpfile=$DMP_FILE logfile=EXP$LOG_FILE

# 4. Moving File To Backup Folder.

mv /u01/DMP/$DMP_FILE /u01/DUMP_BACKUP/$DMP_FILE

mv /u01/DMP/$LOG_FILE /u01/DUMP_BACKUP/$LOG_FILE

# 5. Connect To Backup Server Drop, Create User And Given Permission.

su - oracle<<EOO

export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
export ORACLE_SID=bkpdb
export PATH=$PATH:$ORACLE_HOME/bin

sqlplus /nolog << EOF
CONNECT system/system@bkpdb
SPOOL /u01/emp.lst
SET LINESIZE 100
SET PAGESIZE 50

DROP USER MICR CASCADE;

DROP USER BEFTN CASCADE;

CREATE USER MICR IDENTIFIED BY ERA123MICR
DEFAULT TABLESPACE MICR
TEMPORARY TABLESPACE TEMP
QUOTA UNLIMITED ON CHQIMG
QUOTA UNLIMITED ON MICR
QUOTA UNLIMITED ON MICRACK
QUOTA UNLIMITED ON MICRDFT
QUOTA UNLIMITED ON MICRIMG
QUOTA UNLIMITED ON MICRIND
QUOTA UNLIMITED ON MICRINW
QUOTA UNLIMITED ON MICRLOG
QUOTA UNLIMITED ON MICROUT
QUOTA UNLIMITED ON MICRSTP
QUOTA UNLIMITED ON MICRXML;

CREATE USER BEFTN IDENTIFIED BY ERA123BEFTN
DEFAULT TABLESPACE BEFTN
TEMPORARY TABLESPACE TEMP
QUOTA UNLIMITED ON BEFTN;

GRANT CONNECT TO MICR, BEFTN;

GRANT DBA TO MICR, BEFTN;

GRANT RESOURCE TO MICR, BEFTN;

GRANT SELECT ON SYS.V_$SESSION TO MICR, BEFTN;

GRANT EXECUTE ON SYS.DBMS_CRYPTO TO MICR,BEFTN;

GRANT SELECT ON SYS.DBA_JOBS TO MICR;

GRANT SELECT ON SYS.DBA_JOBS_RUNNING TO MICR;

GRANT READ, WRITE ON DIRECTORY MY_IMAGE_ICE TO MICR;
GRANT READ, WRITE ON DIRECTORY EFT_XML TO BEFTN;
GRANT READ, WRITE ON DIRECTORY EFT_ORE TO BEFTN;
GRANT READ, WRITE ON DIRECTORY EFT_NOC TO BEFTN;
GRANT READ, WRITE ON DIRECTORY EFT_ICE TO BEFTN;
GRANT READ, WRITE ON DIRECTORY MY_IMAGE_DIR TO MICR;
GRANT READ, WRITE ON DIRECTORY MY_DATA_DIR TO MICR;
GRANT READ, WRITE ON DIRECTORY MY_XML TO MICR;
GRANT READ, WRITE ON DIRECTORY MY_ORE TO MICR;
GRANT READ, WRITE ON DIRECTORY MY_OCE TO MICR;
GRANT READ, WRITE ON DIRECTORY BB_IRE TO MICR;
GRANT READ, WRITE ON DIRECTORY BB_ICE TO MICR;
GRANT READ, WRITE ON DIRECTORY BB_ACK TO MICR;

EXEC DBMS_JAVA.grant_permission('MICR', 'java.io.FilePermission', '<<ALLFILES>>', 'read ,write, execute, delete');
EXEC DBMS_JAVA.grant_permission('MICR','SYS:java.lang.RuntimePermission', 'writeFileDescriptor', '');
EXEC DBMS_JAVA.grant_permission('MICR','SYS:java.lang.RuntimePermission', 'readFileDescriptor', '');

SPOOL OFF
EXIT;

EOF

EOO

# 6. Set Your Environment Veritable.

export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
export ORACLE_SID=bkpdb
export PATH=$PATH:$ORACLE_HOME/bin

# 7. Import Backup To Backup Server.


'/u01/app/oracle/product/11.2.0/db_1/bin/impdp' system/system@bkpdb DIRECTORY=DMP_BKP TABLE_EXISTS_ACTION=REPLACE DUMPFILE=$DMP_FILE LOGFILE=IMP$LOG_FILE  SCHEMAS=MICR,BEFTN;

Using Ref Cursors To Return Recordsets.

CREATE TABLE STHOLTAB
(
  BRANCD  VARCHAR2(5 BYTE)                      NOT NULL,
  HOLDTE  DATE                                  NOT NULL,
  HOLDES  VARCHAR2(500 BYTE)                    NOT NULL
)

CREATE TABLE TEST_CURSOR
(
  BRANCD  VARCHAR2(5 BYTE)                      NOT NULL,
  HOLDTE  DATE                                  NOT NULL,
  HOLDES  VARCHAR2(500 BYTE)                    NOT NULL
)

CREATE OR REPLACE PROCEDURE DPR_CURSOR_RETURN(p_BRANCD    IN  VARCHAR2,
                                              p_recordset OUT SYS_REFCURSOR) AS
BEGIN
  OPEN p_recordset FOR
    SELECT BRANCD, HOLDTE, HOLDES
    FROM   STHOLTAB
    WHERE BRANCD=P_BRANCD
    ORDER BY BRANCD;
END DPR_CURSOR_RETURN;

DECLARE
C_RECORDSET SYS_REFCURSOR;
V_BRANCD VARCHAR2(100);
V_HOLDTE DATE;
V_HOLDES VARCHAR2(1000);
BEGIN
DPR_CURSOR_RETURN('003',C_RECORDSET);

LOOP
FETCH C_RECORDSET  INTO  V_BRANCD, V_HOLDTE, V_HOLDES;

    INSERT INTO TEST_CURSOR VALUES(V_BRANCD, V_HOLDTE, V_HOLDES);
   
    COMMIT;
    EXIT WHEN C_RECORDSET%NOTFOUND;
END LOOP;

END;

Steps to configure Oracle 11g EM DBConsole manually.

Step 1. Drop the existing configuration if you already present sysman user.

emca -deconfig dbcontrol db -repos drop

STARTED EMCA at Sep 22, 2013 4:07:56 PM
EM Configuration Assistant, Version 11.2.0.0.2 Production
Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Enter the following information:
Database SID: micrdb
Listener port number: 1521
Password for SYS user:
Password for SYSMAN user:

Do you wish to continue? [yes(Y)/no(N)]: Y
Sep 22, 2013 4:08:16 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /u01/app/oracle/cfgtoollogs/emca/micrdb/                                                                                        emca_2013_09_22_16_07_56.log.
Sep 22, 2013 4:08:16 PM oracle.sysman.emcp.util.DBControlUtil stopOMS
INFO: Stopping Database Control (this may take a while) ...
Sep 22, 2013 4:08:19 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Dropping the EM repository (this may take a while) ...
Sep 22, 2013 4:11:14 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully dropped
Enterprise Manager configuration completed successfully
FINISHED EMCA at Sep 22, 2013 4:11:19 PM


Step 2. Create the OEM GRID repository

emca -repos create

STARTED EMCA at Sep 22, 2013 4:12:06 PM
EM Configuration Assistant, Version 11.2.0.0.2 Production
Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Enter the following information:
Database SID: micrdb
Listener port number: 1521
Password for SYS user:
Password for SYSMAN user:

Do you wish to continue? [yes(Y)/no(N)]: Y
Sep 22, 2013 4:12:19 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /u01/app/oracle/cfgtoollogs/emca/micrdb/emca_2013_09_22_16_12_06.log.
Sep 22, 2013 4:12:19 PM oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) ...
Sep 22, 2013 4:18:57 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully created
Enterprise Manager configuration completed successfully
FINISHED EMCA at Sep 22, 2013 4:18:57 PM
-bash-4.1$ emca -config dbcontrol db

STARTED EMCA at Sep 22, 2013 4:19:42 PM
EM Configuration Assistant, Version 11.2.0.0.2 Production
Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Enter the following information:
Database SID: micrdb
Listener port number: 1521
Listener ORACLE_HOME [ /u01/app/oracle/product/11.2.0/db_1 ]:
Password for SYS user:
Password for DBSNMP user:
Password for SYSMAN user:
Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):
-----------------------------------------------------------------

You have specified the following settings

Database ORACLE_HOME ................ /u01/app/oracle/product/11.2.0/db_1

Local hostname ................ micrsvr.localdomain
Listener ORACLE_HOME ................ /u01/app/oracle/product/11.2.0/db_1
Listener port number ................ 1521
Database SID ................ micrdb
Email address for notifications ...............
Outgoing Mail (SMTP) server for notifications ...............

-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: Y
Sep 22, 2013 4:20:18 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /u01/app/oracle/cfgtoollogs/emca/micrdb/emca_2013_09_22_16_19_42.log.
Sep 22, 2013 4:20:24 PM oracle.sysman.emcp.EMReposConfig uploadConfigDataToRepository
INFO: Uploading configuration data to EM repository (this may take a while) ...
Sep 22, 2013 4:21:14 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Uploaded configuration data successfully
Sep 22, 2013 4:21:17 PM oracle.sysman.emcp.util.DBControlUtil configureSoftwareLib
INFO: Software library configured successfully.
Sep 22, 2013 4:21:17 PM oracle.sysman.emcp.EMDBPostConfig configureSoftwareLibrary
INFO: Deploying Provisioning archives ...
Sep 22, 2013 4:21:42 PM oracle.sysman.emcp.EMDBPostConfig configureSoftwareLibrary
INFO: Provisioning archives deployed successfully.
Sep 22, 2013 4:21:42 PM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Securing Database Control (this may take a while) ...
Sep 22, 2013 4:22:14 PM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Database Control secured successfully.
Sep 22, 2013 4:22:14 PM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
Sep 22, 2013 4:23:31 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
Sep 22, 2013 4:23:31 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>> The Database Control URL is https://micrsvr.localdomain:1158/em <<<<<<<<<<<
Sep 22, 2013 4:23:35 PM oracle.sysman.emcp.EMDBPostConfig invoke
WARNING:
************************  WARNING  ************************

Management Repository has been placed in secure mode wherein Enterprise Manager data will be encrypted.  The encryption key has been placed in the file: /u01/app/oracle/product/11.2.0/db_1/micrsvr.localdomain_micrdb/sysman/config/emkey.ora.   Please ensure this file is backed up as the encrypted data will become unusable if this file is lost.

***********************************************************
Enterprise Manager configuration completed successfully
FINISHED EMCA at Sep 22, 2013 4:23:35 PM

Step 4. Configure EM Grid control

emctl status dbconsole

Oracle Enterprise Manager 11g Database Control Release 11.2.0.1.0
Copyright (c) 1996, 2009 Oracle Corporation.  All rights reserved.
https://micrsvr.localdomain:1158/em/console/aboutApplication
Oracle Enterprise Manager 11g is running.
------------------------------------------------------------------
Logs are generated in directory /u01/app/oracle/product/11.2.0/db_1/micrsvr.localdomain_micrdb/sysman/log
 

Step 5. Use EM With URL(https://10.11.201.204:1158/em/)

  Check Your EM Status

emctl status dbconsole

Start Your EM  

emctl start dbconsole

Stop Your EM

emctl stop dbconsole 

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Cause: The listener received a request to establish a connection to a database or other service. The connect descriptor received by the listener specified a service name for a service (usually a database service) that either has not yet dynamically registered with the listener or has not been statically configured for the listener. This may be a temporary condition such as after the listener has started, but before the database instance has registered with the listener.

Action: 
- Check your database service are started.
- Wait a moment and try to connect a second time.
- Check which services are currently known by the listener.
- Check that the SERVICE_NAME parameter in the connect descriptor of the net service name.

ORA-12518 has been known to occur in Oracle Net Services version.

The reason ORA-12518 is being throw may be because of DEDICATED connections because Oracle 10g has a value of PROCESSES is defaulted at 150 which can be lower than necessary in a production system.

ORA-12518 may be thrown in SHARED SERVER because the dispatcher may have reached the maximum connection value, so it denies all other.   

 ORA-12518, you would need to try increasing the PROCESSES parameter so that it can handle the needed number of processes.  You can ensure that you have the needed value by monitoring the listener log for ORA-12518.  Also, note that because the PROCESSES parameter is static, the database will need to be bounced.  

In shared server mode
 
SQL> alter system set dispatchers='(PROTOCOL=TCP)' ;

System altered.

In Dedicated server mode

SQL> conn /as sysdba
Connected.
SQL> alter system set processes=300 scope=spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  452984832 bytes
Fixed Size                  1291120 bytes
Variable Size             167775376 bytes
Database Buffers          276824064 bytes
Redo Buffers                7094272 bytes
Database mounted.
Database opened.
SQL>

Calculation Method Of Database Session, Processes, Transaction.

processes=x
sessions=x*1.1+5
transactions=sessions*1.1

E.g.
processes=500
sessions=555
transactions=610

sql> alter system set processes=500 scope=both sid='*';
sql> alter system set sessions=555 scope=both sid='*';
sql> alter system set transactions=610 scope=both sid='*';

Blocking Oracle user access by IP Address.

We are able to block clients based on their IP address or host name using oracle sqlnet.ora file which exist in  D:\IN\Oracle\product\10.2.0\db_1\network\ADMIN this path.

1. Go to D:\IN\Oracle\product\10.2.0\db_1\network\ADMIN and open  sqlnet.ora file.

2. Insert the following lines.

# authentication, they will not be able to connect to the database on NT.

SQLNET.AUTHENTICATION_SERVICES = (NTS)

tcp.validnode_checking = yes             
                          
#This turns on the hostname/IP checking for your listeners. After this you can select list of host/IP # which you are allow to database connection.
 
tcp.invited_nodes = (localhost, 10.11.201.150, 10.11.201.200)

# This the list of host name or IP Address.

3. Now restart your listener.


Now you can try to connect your database from another IP Like 10.11.201.204 you can get error message.

Flashback DROP Table In Oracle 10g. (Recover Your Droped Table )

In Oracle database 10g default DROP option to move table in the recycle bin (or rename it). The DROP TABLE ... PURGE option can be used to permanently drop a table. If you drop table without  using DROP TABLE RAJIB PURGE you can recover your table using Oracle flashback Technology.


SQL> CREATE TABLE FLASHBACK_TEST(ID NUMBER, NAME VARCHAR2(20));

Table created.

SQL> INSERT INTO FLASHBACK_TEST(ID, NAME) VALUES(10,'RAJIB');

1 row created.

SQL> INSERT INTO FLASHBACK_TEST(ID, NAME) VALUES(20,'KING');

1 row created.

SQL> INSERT INTO FLASHBACK_TEST(ID, NAME) VALUES(30,'MORGAN');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM FLASHBACK_TEST;

        ID NAME
---------- --------------------
        10 RAJIB
        20 KING
        30 MORGAN

SQL> SELECT TO_TIMESTAMP(SYSDATE) FROM DUAL;

TO_TIMESTAMP(SYSDATE)
---------------------------------------------------------------------------
27-JUL-13 12.00.00 AM

SQL> SELECT CURRENT_SCN FROM V$DATABASE;

CURRENT_SCN
-----------
    2047418

SQL> SELECT * FROM FLASHBACK_TEST;

        ID NAME
---------- --------------------
        10 RAJIB
        20 KING
        30 MORGAN

SQL> DROP TABLE  FLASHBACK_TEST;

Table dropped.

SQL> SELECT * FROM FLASHBACK_TEST;
SELECT * FROM FLASHBACK_TEST
              *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> SHOW RECYCLEBIN;
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
FLASHBACK_TEST   BIN$JTA6PK8nRqeAuj79hlI9dQ==$0 TABLE        2013-07-27:12:40:36

SQL> FLASHBACK TABLE  FLASHBACK_TEST TO BEFOURE DROP;
FLASHBACK TABLE  FLASHBACK_TEST TO BEFOURE DROP
                                   *
ERROR at line 1:
ORA-00905: missing keyword


SQL> FLASHBACK TABLE  FLASHBACK_TEST TO BEFORE DROP;

Flashback complete.

SQL> SELECT * FROM FLASHBACK_TEST;

        ID NAME
---------- --------------------
        10 RAJIB
        20 KING
        30 MORGAN

SQL>

ORA-01033: ORACLE initialization or shutdown in progress

Cause : Your database are not in open stage. May be your database in startup/mount stage.

Action : Open database the following way.

SQL> CONN MICR/MICR;
ERROR:
ORA-01033: ORACLE initialization or shutdown in progress


Warning: You are no longer connected to ORACLE.
SQL> ALTER DATABASE OPEN;
SP2-0640: Not connected
SQL> CONN /AS SYSDBA
Connected.
SQL> ALTER DATABASE OPEN;

Database altered.

SQL> SELECT STATUS FROM V$INSTANCE;

STATUS
------------
OPEN

SQL> CONN MICR/MICR;
Connected.
SQL>

ORA-38706: Cannot turn on FLASHBACK DATABASE logging.

Cause : Your media recovery in not enable (Your database in not in archive log mode). In the case of enable Oracle database flashback on you can get this error message if your database is not in archive log mode.

Action : To raise then error set alter your database in archive log mode.

SQL> ALTER DATABASE FLASHBACK ON;
ALTER DATABASE FLASHBACK ON
*
ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38707: Media recovery is not enabled.


SQL> ALTER DATABASE ARCHIVELOG;

Database altered.

SQL> ALTER DATABASE FLASHBACK ON;

Database altered.

Oracle Flashback Technology.

Oracle Flashback Technology is a group of Oracle Database features that let you view past states of database objects or to return database objects to a previous state without using point-in-time media recovery.
Using flashback features, we can do the following:

    1. Perform queries that return past data.
    2. Perform queries that return metadata that shows a detailed history of changes to the database.
    3. Recover tables or rows to a previous point in time.
    4. Automatically track and archive transactional data changes.
    5. Roll back a transaction and its dependent transactions while the database remains online.

Enable Flashback Database:  To enable Flashback Database, set the DB_FLASHBACK_RETENTION_TARGET initialization parameter and issue the ALTER DATABASE FLASHBACK ON statement.

DB_FLASHBACK_RETENTION_TARGET specifies the upper limit (in minutes) on how far back in time the database may be flashed back.

To Enable Flashback Database log in your oracle database as sysdba. And ensure your database in mounting stage.

SQL> SELECT FLASHBACK_ON FROM V$DATABASE;

FLASHBACK_ON
------------------
NO

SQL> SHOW RECYCLEBIN;
SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP MOUNT;
ORACLE instance started.

Total System Global Area  717225984 bytes
Fixed Size                  1292684 bytes
Variable Size             293602932 bytes
Database Buffers          415236096 bytes
Redo Buffers                7094272 bytes
Database mounted.
SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=1440;   # 24*60=
1440 (For 1 Day)
System altered.

SQL> ALTER DATABASE FLASHBACK ON;
ALTER DATABASE FLASHBACK ON
*
ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38707: Media recovery is not enabled.


SQL> ALTER DATABASE ARCHIVELOG;

Database altered.

SQL> ALTER DATABASE FLASHBACK ON;

Database altered.

SQL>
 

Generat XML File From Oracle database.

1. Create table for insert data which can use to generate XML File. 

CREATE TABLE XML_DATA
(
  C1  VARCHAR2(10 BYTE),
  C2  VARCHAR2(100 BYTE)
)

2. Inserting data in table.

Insert into XML_DATA
   (C1, C2)
 Values
   ('100', 'KING');
Insert into XML_DATA
   (C1, C2)
 Values
   ('500', 'RAJIB');
Insert into XML_DATA
   (C1, C2)
 Values
   ('200', 'MORGAN');
COMMIT;

3. Create an directory for create an XML File

CREATE OR REPLACE DIRECTORY
BB_ICE AS
'/u01/XML/ICE/';


GRANT READ, WRITE ON DIRECTORY BB_ICE TO BEFTN;

4. Create an procedure for generate XML File.

CREATE OR REPLACE PROCEDURE DPR_GENERAT_XML_FILE
(P_DIRECTORY VARCHAR2)
IS
    V_FILE UTL_FILE.FILE_TYPE;
    V_DATA   dbms_xmlgen.ctxtype;
    v_xml       CLOB;
BEGIN
       V_FILE := utl_file.fopen (P_DIRECTORY, 'TESAT.XML', 'w');
     
    FOR I IN (SELECT C1 FROM XML_DATA) LOOP 
         V_DATA :=dbms_xmlgen.newcontext
               ('SELECT C1, C2 FROM XML_DATA WHERE C1 = '||I.C1);
         --dbms_xmlgen.setrowsettag (V_DATA, ABC);
         dbms_xmlgen.setnullhandling (V_DATA, dbms_xmlgen.EMPTY_TAG);
         dbms_xmlgen.setrowtag (V_DATA, NULL);
         v_xml := dbms_xmlgen.getxml (V_DATA);
         utl_file.put_line (V_FILE,v_xml);
         dbms_xmlgen.closecontext (V_DATA);
    END LOOP;
       utl_file.fclose   (V_FILE);  
END;

ORA-39726: unsupported add/drop column operation on compressed tables

Cause : In this error occurred because the table your trying to drop columns from are compressed. Table compression is usually applied to partitioned tables, to minimized spaced based on columns with repeating values


SQL> ALTER TABLE OUTWDMST COMPRESS;

Table altered.

SQL> ALTER TABLE OUTWDMST DROP COLUMN CCU_CODE;
ALTER TABLE OUTWDMST DROP COLUMN CCU_CODE
                                 *
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables


SQL> ALTER TABLE OUTWDMST SET UNUSED COLUMN CCU_CODE;

Table altered.

SQL> ALTER TABLE OUTWDMST DROP UNUSED COLUMNS;

Table altered.

SQL> SELECT CCU_CODE FROM OUTWDMST;
SELECT CCU_CODE FROM OUTWDMST
       *
ERROR at line 1:
ORA-00904: "CCU_CODE": invalid identifier


SQL>

ORA-12547: TNS:lost contact

Cause : This error occur when oracle database resource owner in not oracle user.

Action : Connect your server as root user and perform the following.

[root@micrsvr db_1]# chown -R oracle:oinstall /u01
[root@micrsvr db_1]# chmod -R 775 /u01
[root@micrsvr db_1]# su oracle

[oracle@micrsvr ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jul 18 13:18:20 2013

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

SQL> conn /as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area  935317504 bytes
Fixed Size                  2218912 bytes
Variable Size             692061280 bytes
Database Buffers          234881024 bytes
Redo Buffers                6156288 bytes
Database mounted.
Database opened.
SQL>

Mounting Linux Path With Another Server/Host Shared Locatation

[root@micrsvr ~]# mount //10.11.201.200/Share/ /u01/MICR/ -o username=Rajib,password=Admin

Monitoring OS Resources In Red hat Linux.

[root@micrsvr ~]# top
top - 12:21:15 up 13 days, 15:53,  5 users,  load average: 0.24, 0.25, 0.19
Tasks: 262 total,   1 running, 261 sleeping,   0 stopped,   0 zombie
Cpu(s):  3.8%us,  1.4%sy,  0.0%ni, 91.7%id,  3.0%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:   2043324k total,  1972360k used,    70964k free,   101808k buffers
Swap:  4192248k total,    31944k used,  4160304k free,  1275172k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND           
 8739 root      20   0  288m  12m 9496 S  1.0  0.6   0:00.35 gnome-terminal    
   12 root      20   0     0    0    0 S  0.3  0.0   5:40.41 events/1          
 6875 root      20   0 98860 4712 2908 S  0.3  0.2   0:01.95 sshd              
 8933 root      20   0 15220 1360  944 R  0.3  0.1   0:00.02 top               
    1 root      20   0 19396 1292 1080 S  0.0  0.1   0:09.82 init              
    2 root      20   0     0    0    0 S  0.0  0.0   0:00.00 kthreadd          
    3 root      RT   0     0    0    0 S  0.0  0.0   0:00.04 migration/0       
    4 root      20   0     0    0    0 S  0.0  0.0   0:00.01 ksoftirqd/0       
    5 root      RT   0     0    0    0 S  0.0  0.0   0:00.00 migration/0       
    6 root      RT   0     0    0    0 S  0.0  0.0   0:00.00 watchdog/0        
    7 root      RT   0     0    0    0 S  0.0  0.0   0:00.05 migration/1       
    8 root      RT   0     0    0    0 S  0.0  0.0   0:00.00 migration/1       
    9 root      20   0     0    0    0 S  0.0  0.0   0:00.29 ksoftirqd/1       
   10 root      RT   0     0    0    0 S  0.0  0.0   0:00.00 watchdog/1        
   11 root      20   0     0    0    0 S  0.0  0.0   0:00.03 events/0          
   13 root      20   0     0    0    0 S  0.0  0.0   0:00.00 cpuset            
   14 root      20   0     0    0    0 S  0.0  0.0   0:00.00 khelper           

Sort top Output command
=============================================
We can sort top output command by pressing O while it is running.

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND           
 8973 oracle    20   0 1126m  23m  21m R  4.3  1.2   0:00.13 oracle            
 6875 root      20   0 98860 4712 2908 S  1.0  0.2   0:02.01 sshd              
 8739 root      20   0  288m  12m 9496 S  0.7  0.6   0:00.42 gnome-terminal    
   12 root      20   0     0    0    0 S  0.3  0.0   5:40.44 events/1          
 2859 oracle    20   0 1110m  16m  14m S  0.3  0.8   2:25.11 oracle            
 6921 root      20   0  239m  10m 7564 S  0.3  0.5   0:00.26 metacity          
 8950 root      20   0 15220 1356  944 R  0.3  0.1   0:00.10 top               
    1 root      20   0 19396 1288 1076 S  0.0  0.1   0:09.82 init              
    2 root      20   0     0    0    0 S  0.0  0.0   0:00.00 kthreadd          
    3 root      RT   0     0    0    0 S  0.0  0.0   0:00.04 migration/0       
    4 root      20   0     0    0    0 S  0.0  0.0   0:00.01 ksoftirqd/0       
    5 root      RT   0     0    0    0 S  0.0  0.0   0:00.00 migration/0       
    6 root      RT   0     0    0    0 S  0.0  0.0   0:00.00 watchdog/0        
    7 root      RT   0     0    0    0 S  0.0  0.0   0:00.05 migration/1       
    8 root      RT   0     0    0    0 S  0.0  0.0   0:00.00 migration/1       
    9 root      20   0     0    0    0 S  0.0  0.0   0:00.30 ksoftirqd/1       
   10 root      RT   0     0    0    0 S  0.0  0.0   0:00.00 watchdog/1        
Current Sort Field:  A  for window 1:Def

[root@micrsvr ~]# top
Select sort field via field letter, type any other key to return

* A: PID        = Process Id              v: nDRT       = Dirty Pages count
  b: PPID       = Parent Process Pid      w: S          = Process Status
  c: RUSER      = Real user name          x: COMMAND    = Command name/line
  d: UID        = User Id                 y: WCHAN      = Sleeping in Function
  e: USER       = User Name               z: Flags      = Task Flags <sched.h>
  f: GROUP      = Group Name
  g: TTY        = Controlling Tty       Note1:
  h: PR         = Priority                If a selected sort field can't be
  i: NI         = Nice value              shown due to screen width or your
  j: P          = Last used cpu (SMP)     field order, the '<' and '>' keys
  k: %CPU       = CPU usage               will be unavailable until a field
  l: TIME       = CPU Time                within viewable range is chosen.
  m: TIME+      = CPU Time, hundredths
  n: %MEM       = Memory usage (RES)    Note2:
  o: VIRT       = Virtual Image (kb)      Field sorting uses internal values,
  p: SWAP       = Swapped size (kb)       not those in column display.  Thus,
  q: RES        = Resident size (kb)      the TTY & WCHAN fields will violate
  r: CODE       = Code size (kb)          strict ASCII collating sequence.
  s: DATA       = Data+Stack size (kb)    (shame on you if WCHAN is chosen)
  t: SHR        = Shared Mem size (kb)
  u: nFLT       = Page Fault count
top - 12:22:37 up 13 days, 15:54,  5 users,  load average: 0.33, 0.28, 0.20
Tasks: 263 total,   2 running, 261 sleeping,   0 stopped,   0 zombie
Cpu(s):  1.0%us,  0.5%sy,  0.0%ni, 98.3%id,  0.2%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:   2043324k total,  1972384k used,    70940k free,    97596k buffers
Swap:  4192248k total,    31944k used,  4160304k free,  1278756k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND           
16672 root      18  -2 11292  696  264 S  0.0  0.0   0:00.00 udevd             
16663 root      18  -2 12480 2352  544 S  0.0  0.1   0:00.02 udevd             
11415 oracle    20   0 90320 2424 1964 S  0.0  0.1   0:00.00 gconf-helper      
11412 oracle     9 -11  429m 3848 2496 S  0.0  0.2   0:00.11 pulseaudio        
11159 oracle    20   0  207m 5960 2688 S  0.0  0.3   0:24.42 tnslsnr           
 8986 oracle    20   0 1109m  12m  10m R  1.7  0.6   0:00.05 oracle            
 8950 root      20   0 15220 1360  948 R  0.3  0.1   0:00.15 top               
 8741 root      20   0  105m 1804 1444 S  0.0  0.1   0:00.01 bash              
 8740 root      20   0  8280  692  596 S  0.0  0.0   0:00.00 gnome-pty-helpe   
 8739 root      20   0  288m  12m 9496 S  0.0  0.6   0:00.44 gnome-terminal    
 7412 root      20   0  280m  11m 8552 S  0.0  0.6   0:00.07 notification-da   
 7331 root      20   0  211m 2868 2416 S  0.0  0.1   0:00.00 gvfsd-computer    
 7325 root      20   0  129m 1768 1460 S  0.0  0.1   0:00.00 gvfsd-metadata    
 7322 root      20   0  131m 2176 1868 S  0.0  0.1   0:00.00 gvfsd-burn        
 7320 root      20   0  225m 5820 4656 S  0.0  0.3   0:00.00 ibus-x11          
 7318 root      20   0  335m  23m  11m S  0.0  1.2   0:00.29 python            
 7316 root      20   0  150m 3164 2648 S  0.0  0.2   0:00.00 ibus-gconf 


Kill a process.
==================================================
If you want to kill a process then first note down the PID and then while running top press "k" which will ask you for the process id. Type the process ID.You can see killed successfully.
[root@micrsvr ~]# top
top - 12:27:56 up 13 days, 15:59,  5 users,  load average: 0.05, 0.11, 0.14
Tasks: 262 total,   1 running, 261 sleeping,   0 stopped,   0 zombie
Cpu(s):  0.2%us,  0.1%sy,  0.0%ni, 99.4%id,  0.3%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:   2043324k total,  1976272k used,    67052k free,    96232k buffers
Swap:  4192248k total,    31944k used,  4160304k free,  1288384k cached
PID to kill: 2589
  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND           
 2589 oracle    20   0 1109m  15m  13m S  2.0  0.8   0:40.34 oracle            
 9157 root      20   0 15216 1232  832 R  2.0  0.1   0:00.01 top               
    1 root      20   0 19396 1276 1064 S  0.0  0.1   0:09.84 init              
    2 root      20   0     0    0    0 S  0.0  0.0   0:00.00 kthreadd          
    3 root      RT   0     0    0    0 S  0.0  0.0   0:00.04 migration/0       
    4 root      20   0     0    0    0 S  0.0  0.0   0:00.01 ksoftirqd/0       
    5 root      RT   0     0    0    0 S  0.0  0.0   0:00.00 migration/0       
    6 root      RT   0     0    0    0 S  0.0  0.0   0:00.00 watchdog/0        
    7 root      RT   0     0    0    0 S  0.0  0.0   0:00.05 migration/1       
    8 root      RT   0     0    0    0 S  0.0  0.0   0:00.00 migration/1       
    9 root      20   0     0    0    0 S  0.0  0.0   0:00.30 ksoftirqd/1       
   10 root      RT   0     0    0    0 S  0.0  0.0   0:00.00 watchdog/1        
   11 root      20   0     0    0    0 S  0.0  0.0   0:00.03 events/0          
   12 root      20   0     0    0    0 S  0.0  0.0   5:40.60 events/1          
   13 root      20   0     0    0    0 S  0.0  0.0   0:00.00 cpuset            
   14 root      20   0     0    0    0 S  0.0  0.0   0:00.00 khelper           
   15 root      20   0     0    0    0 S  0.0  0.0   0:00.00 netns