Search This Blog

Thursday, December 26, 2013

Trying to get row counts for all tables at a time

You can use any of this
select
      t.owner,
      t.table_name,
      to_number(
        extractvalue(
          xmltype(dbms_xmlgen.getxml('select count(*) c from '||t.owner || '.' || t.table_name))
          ,'/ROWSET/ROW/C')
          )
          count
    from all_tables t,user_tab_privs p
    where t.owner = p.owner
      and t.table_name = p.table_name
      AND privilege = 'SELECT'
      
union all
select
      user,
      t.table_name,
      to_number(
        extractvalue(
          xmltype(dbms_xmlgen.getxml('select count(*) c from '||t.table_name))
          ,'/ROWSET/ROW/C')
          )
          row_count
    FROM user_tables t;
/

SELECT owner,table_name, num_rows 
from ALL_TABLES 
WHERE num_rows>0 
AND owner = 'OWNER_NAME'
ORDER BY num_rows DESC;

Monday, December 23, 2013

Oracle Certification Exam 1Z0-144 Question

Local procedure A calls remote procedure B. Procedure B was compiled at 8 A.M. Procedure A was modified and recompiled at 9 A.M. Remote procedure B was later modified and recompiled at 11 A.M. The dependency mode is set to TI MESTAMP. What happens when procedure A is invoked at 1 P.M?
A. There is no affect on procedure A and it runs successfully.
B. Procedure B is invalidated and recompiles when invoked.
C. Procedure A is invalidated and recompiles for the first time it is invoked.
D. Procedure A is invalidated and recompiles for the second time it is invoked.

Solution:
Answer: D
When the local procedure is invoked, at run time the Oracle server compares the two time stamps of the referenced remote procedure. If the time stamps are equal (indicating that the remote procedure has not recompiled), the Oracle server executes the local procedure. If the time stamps are not equal (indicating that the remote procedure had recompiled), the Oracle server invalidates the local procedure and returns a run-time error. If the local procedure, which is now tagged as invalid, is invoked a second time, the Oracle server re-compiles it before executing, in accordance with the automatic local dependency mechanism. So if a local procedure returns a run-time error the first time that it is invoked, indicating that the remote procedure's time stamp has changed, you should develop a strategy to re-invoke the local procedure.

Thursday, December 19, 2013

Oracle PL Number Evaluation Problem

Give me your feedback for this code mentioned below.
SET SERVEROUTPUT ON;
DECLARE
V_MYAGE NUMBER;
BEGIN
DBMS_OUTPUT.PUT_LINE('V_MYAGE : '||V_MYAGE);
IF V_MYAGE < 10 THEN
DBMS_OUTPUT.PUT_LINE('1-I am a child');
ELSE
DBMS_OUTPUT.PUT_LINE('1-I am NOT a child');
END IF;
IF V_MYAGE > 10 THEN
DBMS_OUTPUT.PUT_LINE('2-I am NOT a child');
ELSE
DBMS_OUTPUT.PUT_LINE('2-I am a child');
END IF;
END;