ORA-01008: not all variables bound

Cause :- In select statement when we using bind variable same name in more place. There is no problem in run time if we pass one values for this bind variable. But when we using sql statement with in pl/sql block with BULK COLLECT we need to pass value more time for bind variable. Like for 3 times using pass same value in 3 times.

SQL> CONN RND/rnd
Connected.
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE RND.DPR_BULK_DATA_TEST
  2  (P_EMP_ID NUMBER,
  3   P_DEPT_ID NUMBER
  4   )
  5  IS
  6  V_SQL VARCHAR2(3000);
  7  TYPE TYP_EMP_ALL IS TABLE OF EMP%ROWTYPE;
  8  REC_EMP_ALL  TYP_EMP_ALL;
  9  BEGIN
 10  V_SQL :='SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID
 11                FROM E
 12                WHERE EMPLOYEE_ID=:TEST AND DEPARTMENT_ID=:2 AND DEPARTMENT_ID=:2';
 13
 14        EXECUTE IMMEDIATE V_SQL  BULK COLLECT
 15        INTO REC_EMP_ALL
 16        USING  P_EMP_ID,     P_DEPT_ID;
 17
 18        FOR I IN REC_EMP_ALL.FIRST .. REC_EMP_ALL.LAST LOOP
 19
 20         INSERT INTO EMP(EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
 21         VALUES (REC_EMP_ALL(I).EMPLOYEE_ID, REC_EMP_ALL(I).FIRST_NAME, REC_EMP_ALL(I).LAST_NAME, REC_EMP_ALL(I).EMAIL, REC_EMP_ALL(I).PHONE_NUMBER, REC_EMP_ALL(I).HIRE_DATE, REC_EMP_ALL(I).JOB_ID,
 REC_EMP_ALL(I).SALARY, REC_EMP_ALL(I).COMMISSION_PCT, REC_EMP_ALL(I).MANAGER_ID, REC_EMP_ALL(I).DEPARTMENT_ID);
 22        END LOOP;
 23
 24
 25        FORALL J IN REC_EMP_ALL.FIRST .. REC_EMP_ALL.LAST
 26
 27       INSERT INTO EMP(EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
 28         VALUES (REC_EMP_ALL(J).EMPLOYEE_ID, REC_EMP_ALL(J).FIRST_NAME, REC_EMP_ALL(J).LAST_NAME, REC_EMP_ALL(J).EMAIL, REC_EMP_ALL(J).PHONE_NUMBER, REC_EMP_ALL(J).HIRE_DATE, REC_EMP_ALL(J).JOB_ID,
 REC_EMP_ALL(J).SALARY, REC_EMP_ALL(J).COMMISSION_PCT, REC_EMP_ALL(J).MANAGER_ID, REC_EMP_ALL(J).DEPARTMENT_ID);
 29
 30        COMMIT;
 31
 32  END;
 33  /

Procedure created.

SQL> BEGIN
  2  DPR_BULK_DATA_TEST(100,90);
  3  END;
  4  /
BEGIN
*
ERROR at line 1:
ORA-01008: not all variables bound
ORA-06512: at "RND.DPR_BULK_DATA_TEST", line 14
ORA-06512: at line 2


SQL> CREATE OR REPLACE PROCEDURE RND.DPR_BULK_DATA_TEST
  2  (P_EMP_ID NUMBER,
  3   P_DEPT_ID NUMBER
  4   )
  5  IS
  6  V_SQL VARCHAR2(3000);
  7  TYPE TYP_EMP_ALL IS TABLE OF EMP%ROWTYPE;
  8  REC_EMP_ALL  TYP_EMP_ALL;
  9  BEGIN
 10  V_SQL :='SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID
 11                FROM E
 12                WHERE EMPLOYEE_ID=:TEST AND DEPARTMENT_ID=:2 AND DEPARTMENT_ID=:2';
 13
 14        EXECUTE IMMEDIATE V_SQL  BULK COLLECT
 15        INTO REC_EMP_ALL
 16        USING  P_EMP_ID,     P_DEPT_ID, P_DEPT_ID;
 17
 18        FOR I IN REC_EMP_ALL.FIRST .. REC_EMP_ALL.LAST LOOP
 19
 20         INSERT INTO EMP(EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
 21         VALUES (REC_EMP_ALL(I).EMPLOYEE_ID, REC_EMP_ALL(I).FIRST_NAME, REC_EMP_ALL(I).LAST_NAME, REC_EMP_ALL(I).EMAIL, REC_EMP_ALL(I).PHONE_NUMBER, REC_EMP_ALL(I).HIRE_DATE, REC_EMP_ALL(I).JOB_ID,
 REC_EMP_ALL(I).SALARY, REC_EMP_ALL(I).COMMISSION_PCT, REC_EMP_ALL(I).MANAGER_ID, REC_EMP_ALL(I).DEPARTMENT_ID);
 22        END LOOP;
 23
 24
 25        FORALL J IN REC_EMP_ALL.FIRST .. REC_EMP_ALL.LAST
 26
 27       INSERT INTO EMP(EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
 28         VALUES (REC_EMP_ALL(J).EMPLOYEE_ID, REC_EMP_ALL(J).FIRST_NAME, REC_EMP_ALL(J).LAST_NAME, REC_EMP_ALL(J).EMAIL, REC_EMP_ALL(J).PHONE_NUMBER, REC_EMP_ALL(J).HIRE_DATE, REC_EMP_ALL(J).JOB_ID,
 REC_EMP_ALL(J).SALARY, REC_EMP_ALL(J).COMMISSION_PCT, REC_EMP_ALL(J).MANAGER_ID, REC_EMP_ALL(J).DEPARTMENT_ID);
 29
 30        COMMIT;
 31
 32  END;
 33  /

Procedure created.

SQL>  BEGIN
  2   DPR_BULK_DATA_TEST(100,90);
  3   END;
  4  /

PL/SQL procedure successfully completed.

SQL>

Flashback in table level using system change number (CURRENT_SCN) & BEFORE DROP.

SQL> create table flashback_test(id number(5), name varchar2(30));

Table created.

SQL> SELECT current_scn FROM v$database;

CURRENT_SCN                                                                    
-----------                                                                    
    6249647                                                                    

SQL> insert into flashback_test values(10,'Rajib');

1 row created.

SQL> ALTER TABLE flashback_test ENABLE ROW MOVEMENT;

Table altered.

SQL> insert into flashback_test values(10,'Rajib');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from flashback_test;

        ID NAME                                                                
---------- ------------------------------                                      
        10 Rajib                                                               
        10 Rajib                                                               

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> SELECT current_scn FROM v$database;

CURRENT_SCN                                                                    
-----------                                                                    
    6249734                                                                    

SQL> FLASHBACK TABLE flashback_table_test TO SCN 6249647;
FLASHBACK TABLE flashback_table_test TO SCN 6249647
                *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> FLASHBACK TABLE  flashback_test TO BEFORE DROP;

Flashback complete.

SQL> select * from  flashback_test;

        ID NAME                                                                
---------- ------------------------------                                      
        10 Rajib                                                               
        10 Rajib                                                               

SQL>  FLASHBACK TABLE flashback_test TO SCN 6249647;

Flashback complete.

SQL> select * from  flashback_test;

no rows selected

SQL> spool off;

ORA-01157 cannot identifylock data file 6 - see DBWR trace file

Cause:- This error occurred when data file are not present In data file directory. You can solve this problem in the following way ...

 1. If data in this file is not important that remove this file information from database then open your database.
2. If data in this file is important and  backup are available for this file and if your database is in archive log mode than you can recover the data in this file. (http://rajiboracle.blogspot.com/2014/09/ora-01157-cannot-identifylock-data-file.html)

Solution using 1.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: 'D:\ORACLE\TBACNTS.DBF'


SQL> select b.name as TABLESPACE_NAME ,status from v$datafile a ,v$tablespace b where a.ts#=b.ts#;

TABLESPACE_NAME                STATUS
------------------------------ -------
SYSTEM                         SYSTEM
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
USERS                          ONLINE
EXAMPLE                        ONLINE
TBACNTS                        ONLINE
TRBFES                         ONLINE
TBFES                          ONLINE
TBAML                          ONLINE
DATA                           ONLINE
TBSTRAN                        ONLINE

11 rows selected.

SQL>
SQL>
SQL> alter database datafile 'D:\ORACLE\TBACNTS.DBF' offline drop;

Database altered.

SQL> drop tablespace TRBFES including contents and datafiles;
drop tablespace TRBFES including contents and datafiles
*
ERROR at line 1:
ORA-01109: database not open


SQL> alter database datafile 'D:\ORACLE\TRBFES.DBF' offline drop;

Database altered.

SQL> alter database datafile 'D:\ORACLE\TBFES.DBF' offline drop;

Database altered.

SQL>
SQL>
SQL> alter database datafile 'D:\ORACLE\TBAML.DBF' offline drop;

Database altered.

SQL>
SQL> alter database datafile 'D:\ORACLE\DATA.DBF' offline drop;

Database altered.

SQL>
SQL> alter database datafile 'D:\ORACLE\TBstran.DBF' offline drop;
alter database datafile 'D:\ORACLE\TBstran.DBF' offline drop
*
ERROR at line 1:
ORA-01516: nonexistent log file, data file, or temporary file
"D:\ORACLE\TBstran.DBF"


SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 11 - see DBWR trace file
ORA-01110: data file 11: 'D:\ORACLE\TBSTRAN .DBF'


SQL>
SQL> alter database datafile 'D:\ORACLE\TBSTRAN .DBF' offline drop;

Database altered.

SQL> alter database open;

Database altered.

SQL> select status from v$instance;

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

SQL>

Using DBMS_PROFILER To Identify PL-SQL Performance.

Note : In this articular I would like to describe what is DBMS_PROFILER and how to use it.

DBMS_PROFILER:- The DBMS_PROFILER package are use to allow developers to profile the run-time behaviour of PL/SQL code. Using this package you can find out each line execution time.

How to use ?

This package use three tables to collect statistic of PL-SQL code.

PLSQL_PROFILER_DATA - information on profiler runs
PLSQL_PROFILER_RUNS - information on each line profiled
PLSQL_PROFILER_UNITS - profiler data for each line profiled

1. Go to location (D:\app\product\11.2.0\dbhome_1\RDBMS\ADMIN) to find two scripts.
     proftab.sql  -- For create profile tables.
     profload.sql -- For load package DBMS_PROFILER

2. Now connect sys user using sqlplus from  RDBMS\ADMIN Location.
  [D:\app\product\11.2.0\dbhome_1\RDBMS\ADMIN>sqlplus sys/sys as sysdba]

3. Now run this
 SQL>@profload.sql

4. Now connect another user to check pl/sql performance and execute proftab.sql file. I connect RND user in my database.
SQL> conn rnd/rnd;
Connected.
SQL> @proftab.sql

5. Now check the performance .....

SQL> CREATE TABLE E
  2  AS SELECT * FROM EMP;

Table created.


SQL> CREATE OR REPLACE PROCEDURE DPR_EMP_INSERT
  2  IS
  3  BEGIN
  4  FOR I IN 1 .. 100 LOOP
  5   FOR I IN (SELECT * FROM E) LOOP
  6    INSERT INTO Emp(EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
  7    VALUES (I.EMPLOYEE_ID, I.FIRST_NAME, I.LAST_NAME, I.EMAIL, I.PHONE_NUMBER, I.HIRE_DATE, I.JOB_ID, I.SALARY, I.COMMISSION_PCT, I.MANAGER_ID, I.DEPARTMENT_ID);
  8   END LOOP;
  9   END LOOP;
 10   COMMIT;
 11  END;
 12  /

Procedure created.

SQL>DECLARE
 2 v_result  BINARY_INTEGER;
 3 BEGIN
 4 v_result := DBMS_PROFILER.start_profiler;
 5 DPR_EMP_INSERT;
 6 v_result := DBMS_PROFILER.stop_profiler;
 7 END;

SQL>set line 2000
SQL> COLUMN RUN_OWNER FORMAT A10
SQL> COLUMN TEXT FORMAT A40
SQL> COLUMN LINE_TOTL_SECS FORMAT 99,99999999
SQL> COLUMN MIN_TIME FORMAT 99,99999999
SQL> COLUMN MAX_TIME FORMAT 99,99999999
SQL> COLUMN TOTL_RUN_SECS FORMAT 99,99999999
SQL>
SQL> SELECT C.LINE#, U.TEXT,C.TOTAL_OCCUR,
  2              TO_CHAR(C.TOTAL_TIME/1000000000,'99.999999999') LINE_TOTL_SECS,
  3              TO_CHAR(C.MIN_TIME/1000000000,'99.999999999') MIN_TIME,
  4              TO_CHAR(C.MAX_TIME/1000000000,'99.999999999') MAX_TIME,
  5              TO_CHAR(A.RUN_TOTAL_TIME/1000000000,'99.999999999') TOTL_RUN_SECS,
  6              A.RUN_OWNER
  7  FROM PLSQL_PROFILER_RUNS A,
  8            PLSQL_PROFILER_UNITS B,
  9            PLSQL_PROFILER_DATA C,
 10            USER_SOURCE U
 11  WHERE A.RUNID=B.RUNID
 12      AND A.RUNID=C.RUNID
 13      AND B.UNIT_NAME=U.NAME
 14      AND C.LINE#=U.LINE;

     LINE# TEXT                                     TOTAL_OCCUR LINE_TOTL_SEC MIN_TIME      MAX_TIME   TOTL_RUN_SECS RUN_OWNER
---------- ---------------------------------------- ----------- ------------- ------------- ------------- ------------- ----------
         1 PROCEDURE DPR_EMP_INSERT                           1    .000007488    .000007488    .000007488   1.937000000 TEST
         1 PROCEDURE DPR_EMP_INSERT                           0    .000000000    .000000000    .000000000   1.937000000 TEST
         2 IS                                                 0    .000000000    .000000000    .000000000   1.937000000 TEST
         3 BEGIN                                              1    .000000000    .000000000    .000000000   1.937000000 TEST
         3 BEGIN                                              2    .000022201    .000000599    .000021601   1.937000000 TEST
         4 FOR I IN 1 .. 100 LOOP                           101    .000034279    .000000137    .000000593   1.937000000 TEST
         4 FOR I IN 1 .. 100 LOOP                             1    .000003214    .000003214    .000003214   1.937000000 TEST
         5  FOR I IN (SELECT * FROM E) LOOP               10900    .425847635    .000000098    .000573357   1.937000000 TEST
         5  FOR I IN (SELECT * FROM E) LOOP                   0    .000000000    .000000000    .000000000   1.937000000 TEST
         6   INSERT INTO Emp(EMPLOYEE_ID, FIRST_NAM       10700   1.494573068    .000127229    .001236445   1.937000000 TEST
           E, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_

     LINE# TEXT                                     TOTAL_OCCUR LINE_TOTL_SEC MIN_TIME      MAX_TIME   TOTL_RUN_SECS RUN_OWNER
---------- ---------------------------------------- ----------- ------------- ------------- ------------- ------------- ----------
           DATE, JOB_ID, SALARY, COMMISSION_PCT, MA
           NAGER_ID, DEPARTMENT_ID)

         8  END LOOP;                                       100    .000000000    .000000000    .000000000   1.937000000 TEST
         9  END LOOP;                                         1    .000000000    .000000000    .000000000   1.937000000 TEST
        10  COMMIT;                                           1    .000343027    .000343027    .000343027   1.937000000 TEST
        11 END;                                               1    .000005140    .000005140    .000005140   1.937000000 TEST

14 rows selected.