Anuj Singh Oracle DBA

Thursday, 10 November 2011

Oracle Count the number of rows in tables in current SCHEMA

Oracle Row count in schema
Dynamic sql example
execute immediate sql example


with PARALLEL(16) 

set serveroutput on size 1000000
declare
  t_command       varchar2(500);
  t_cid           integer;
  t_total_records number(10);
  stat            integer;
  row_count       integer;
  t_limit         integer := 1; 
begin
  for i in (
    select owner, table_name from dba_tables 
    where owner IN ( 'ANUJ','XXXX'
         ) order by owner
  ) LOOP -- 
  
    t_command := 'SELECT /*+ PARALLEL(16) */ COUNT(0) FROM "'||i.owner||'"."'||i.table_name||'"';
    t_cid := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(t_cid, t_command, dbms_sql.native);
    DBMS_SQL.DEFINE_COLUMN(t_cid, 1, t_total_records);
    stat := DBMS_SQL.EXECUTE(t_cid);
    row_count := DBMS_SQL.FETCH_ROWS(t_cid);
    DBMS_SQL.COLUMN_VALUE(t_cid, 1, t_total_records);
    
   -- if t_total_records > t_limit then
       DBMS_OUTPUT.PUT_LINE(rpad(i.owner || '.' || i.table_name, 55, ' ')|| to_char(t_total_records, '999,999,999')||' record(s)');
  --  end if;
    
    DBMS_SQL.CLOSE_CURSOR(t_cid);
  end loop;
end;
/

set serveroutput on size 1000000

DECLARE
t_c1_tname      user_tables.table_name%TYPE;
t_command       varchar2(200);
t_cid           integer;
t_total_records number(10);
stat            integer;
row_count       integer;
t_limit         integer := 0;    -- <<<<<<<<<<<<<<<<<<Only show tables with more rows
cursor c1 is select table_name from user_tables order by table_name;
BEGIN
t_limit := 0;
open c1;
loop
        fetch c1 into t_c1_tname;
        exit when c1%NOTFOUND;
        t_command := 'SELECT COUNT(0) FROM '||t_c1_tname;
        t_cid := DBMS_SQL.OPEN_CURSOR;
        DBMS_SQL.PARSE(t_cid,t_command,dbms_sql.native);
        DBMS_SQL.DEFINE_COLUMN(t_cid,1,t_total_records);
        stat := DBMS_SQL.EXECUTE(t_cid);
        row_count := DBMS_SQL.FETCH_ROWS(t_cid);
        DBMS_SQL.COLUMN_VALUE(t_cid,1,t_total_records);
        if t_total_records > t_limit then
     DBMS_OUTPUT.PUT_LINE(rpad(t_c1_tname,55,' ')|| to_char(t_total_records,'99999999')||' record(s)');
        end if;
        DBMS_SQL.CLOSE_CURSOR(t_cid);
end loop;
close c1;
END;
/

declare

tbl1 varchar2(40);
sql1 varchar2(2000);
l_cnt    varchar2(30);

begin

for i in (select table_name from user_tables) loop

tbl1 := i.table_name ;
sql1 := 'select count(*) from ' || tbl1 ;
execute immediate sql1 into l_cnt ;

dbms_output.put_line(tbl1||': '|| l_cnt);

end loop;
end;
/

BONUS: 0
SALGRADE: 5
MYEMP: 2
EMP: 14
ANUJ_BIG_TABLE: 9999
ANUJ_BIG_TABLE1: 9999
AA: 14
TEST: 0
ANUJ10: 0
MYEMP_WORK: 0

set serveroutput on size 1000000

DECLARE
t_c1_tname user_tables.table_name%TYPE;
t_command varchar2(200);
t_cid integer;
t_total_records number(10);
stat integer;
row_count integer;
t_limit integer := 0; -- Only show tables with 0 rows
cursor c1 is select table_name from user_tables where tablespace_name = 'USERS';
BEGIN
t_limit := 0;
open c1;
loop
fetch c1 into t_c1_tname;
exit when c1%NOTFOUND;
t_command := 'SELECT COUNT(0) FROM '||t_c1_tname;
t_cid := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(t_cid,t_command,dbms_sql.native);
DBMS_SQL.DEFINE_COLUMN(t_cid,1,t_total_records);
stat := DBMS_SQL.EXECUTE(t_cid);
row_count := DBMS_SQL.FETCH_ROWS(t_cid);
DBMS_SQL.COLUMN_VALUE(t_cid,1,t_total_records);
if t_total_records = t_limit then
DBMS_OUTPUT.PUT_LINE(rpad(t_c1_tname,55,' ')||to_char(t_total_records,'99999999')||' record(s)');

end if;
DBMS_SQL.CLOSE_CURSOR(t_cid);
end loop;
close c1;
END;
/

TEST                                                           0 record(s)
ANUJ10                                                         0 record(s)
BONUS                                                          0 record(s)

set serveroutput on size 1000000
declare
t_c1_tname dba_tables.table_name%type;
t_c1_owner dba_tables.owner%type;
t_command varchar2(200);
t_cid integer;
t_total_records number(10);
stat integer;
row_count integer;
t_limit integer := 1; -- <<<<<<<<<<<<<<<<<<Only show tables with more then 1 rows
begin
for i in (select owner,table_name from dba_tables where owner='ANUJ') LOOP
t_limit := 1;
t_command := 'SELECT COUNT(0) FROM '||i.owner||'.'||i.TABLE_NAME;
t_cid := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(t_cid,t_command,dbms_sql.native);
DBMS_SQL.DEFINE_COLUMN(t_cid,1,t_total_records);
stat := DBMS_SQL.EXECUTE(t_cid);
row_count := DBMS_SQL.FETCH_ROWS(t_cid);
DBMS_SQL.COLUMN_VALUE(t_cid,1,t_total_records);
if t_total_records > t_limit then
DBMS_OUTPUT.PUT_LINE(rpad(i.TABLE_NAME,55,' ')|| to_char(t_total_records,'99999999')||' record(s)');
end if;
DBMS_SQL.CLOSE_CURSOR(t_cid);
end loop;

end;
/

TEST 10000 record(s)
ANUJ_BIG_TABLE 5119488 record(s)
DEPT 4 record(s)
T_CHILD 12 record(s)
T_PARENT 10 record(s)
TEST9 400000 record(s)
EMP_IND 3670016 record(s)
BV_DEMO 19999 record(s)
TEST_TAB 20000000 record(s)
CUST 900 record(s)
T1 10000 record(s)

    set serveroutput on size 100000 feedback off
     declare
       rs     integer;
       cur    integer;
       rp     integer;
       trs    integer;
       n      integer;
       un     varchar2(30);
     begin
        dbms_output.put_line(rpad('Table Name',40)||' Number of Rows      Created Date');
        dbms_output.put_line(rpad('-',73,'-'));
        cur:= dbms_sql.open_cursor;
          for t in (select object_name, created from user_objects where object_type='TABLE') loop
            dbms_sql.parse(cur,'select count(*) from ' || t.object_name, dbms_sql.v7);
            dbms_sql.define_column(cur, 1, rs);
            rp:= dbms_sql.execute(cur);
            n:=dbms_sql.fetch_rows(cur);
            dbms_sql.column_value(cur, 1, rs);
            dbms_output.put_line(rpad(t.object_name,48,'.')||rpad(rs,15,'.')||t.created);
        end loop;
        dbms_sql.close_cursor(cur);
        select count(*) into n from tab where tabtype='TABLE';
        select user into un from dual;
        dbms_output.put_line(rpad('-',73,'-'));
        dbms_output.put_line(un||' User contain '||n||' Table(s)');
     end;
     /
     set serveroutput off feedback on feedback 6

Table Name                               Number of Rows      Created Date
-------------------------------------------------------------------------
T2..............................................18.............17-NOV-11
T...............................................7..............18-NOV-11
SALGRADE........................................5..............22-SEP-11
ORDER_LINES.....................................0..............11-NOV-11
ORDERS..........................................0..............11-NOV-11
MYEMP_WORK......................................0..............22-SEP-11
MYEMP...........................................2..............22-SEP-11
EMP.............................................14.............22-SEP-11
DEPT............................................4..............22-SEP-11
CHAINED_ROWS....................................0..............23-NOV-11
BONUS...........................................0..............22-SEP-11
ANUJ_BIG_TABLE1.................................9999...........01-NOV-11
ANUJ_BIG_TABLE..................................9999...........31-OCT-11
ANUJ10..........................................0..............28-SEP-11
AA..............................................14.............04-NOV-11
-------------------------------------------------------------------------
SCOTT User contain 15 Table(s)

col TXT for a60 heading "Table Row Count "
select rpad(table_name,30,' ') || ' ===> ' || extractvalue (xmltype (dbms_xmlgen.getxml ( 'select count(*) cnt from ' || owner ||'.'||table_name ) )
,'//CNT' ) txt from all_tables
where owner=upper('&Owner') ;

Table Row Count
------------------------------------------------------------
TEST11 ===> 2
ANUJ ===> 4
EMP8 ===> 3
DEPT ===> 4
EMP ===> 14
BONUS ===> 0
SALGRADE ===> 5

7 rows selected.

==========

define TABLE_OWNER='XXX'

define TABLE_NAME='AUDIT_LOG'

set serverout on size 1000000 verify off

declare

sql_stmt varchar2(1024);

row_count number;

cursor get_tab is

select table_name,partition_name from dba_tab_partitions where table_owner=upper('&TABLE_OWNER') and table_name='&TABLE_NAME'

--and rownum<20

;

begin

dbms_output.put_line('Record Counts for table_name..... ');

dbms_output.put_line('Log file to numrows_part_&TABLE_OWNER.lst ....');

dbms_output.put_line('....');

for get_tab_rec in get_tab loop

BEGIN

sql_stmt := 'select count(*) from &TABLE_OWNER..'||get_tab_rec.table_name||' partition ( '||get_tab_rec.partition_name||' )';

EXECUTE IMMEDIATE sql_stmt INTO row_count;

dbms_output.put_line('Table '||rpad(get_tab_rec.table_name||'('||get_tab_rec.partition_name||')',50)||' '||TO_CHAR(row_count)||' rows.');

exception when others then dbms_output.put_line ('Error counting rows for table '||get_tab_rec.table_name);

END;

end loop;

end;

set verify on

====

define table_owner='SCOTT'

set serverout on size 1000000

set verify off

declare

sql_stmt varchar2(1024);

row_count number;

v_table_name varchar2(40);

cursor get_tab is

select table_name

from dba_tables

where owner=upper('&&TABLE_OWNER');

begin

dbms_output.put_line('Checking Record Counts for table_name');

dbms_output.put_line('Log file to numrows_part_&&TABLE_OWNER..lst ....');

dbms_output.put_line('....');

for get_tab_rec in get_tab

loop

v_table_name := get_tab_rec.table_name;

sql_stmt := 'select count(*) from &&TABLE_OWNER..'||get_tab_rec.table_name;

dbms_output.put_line(sql_stmt);

EXECUTE IMMEDIATE sql_stmt INTO row_count;

dbms_output.put_line('Table '||rpad(get_tab_rec.table_name,50)||' '||TO_CHAR(row_count)||' rows.');

END loop;

EXCEPTION

when others then

dbms_output.put_line('Error counting rows for table '||v_table_name);

end;

define table_owner='SCOTT'

define TABLE_NAME='SALES_PART'

set serverout on size 1000000

set verify off

declare

sql_stmt varchar2(1024);

row_count number;

cursor get_tab is

select table_name,partition_name

from dba_tab_partitions

where table_owner=upper('&&TABLE_OWNER')

and table_name='&&TABLE_NAME'

;

begin

dbms_output.put_line('Checking Record Counts for table_name');

dbms_output.put_line('Log file to numrows_part_&&TABLE_OWNER.lst ....');

dbms_output.put_line('....');

for get_tab_rec in get_tab loop

BEGIN

sql_stmt := 'select count(*) from &&TABLE_OWNER..'||get_tab_rec.table_name||' partition ( '||get_tab_rec.partition_name||' )';

EXECUTE IMMEDIATE sql_stmt INTO row_count;

dbms_output.put_line('Table '||rpad(get_tab_rec.table_name||'('||get_tab_rec.partition_name||')',50)||' '||TO_CHAR(row_count)||' rows.');

exception when others then

dbms_output.put_line

('Error counting rows for table '||get_tab_rec.table_name);

END;

end loop;

end;

set verify on

=====

define 1 ='ANUJ';

set serveroutput on

declare

p_count number;

begin
for i in (select * from DBA_tab_partitions where table_name='&1') loop
execute immediate 'select count(*) from '||i.TABLE_OWNER||'.'||i.TABLE_NAME|| ' partition('||i.partition_name||')' into p_count;
dbms_output.put_line(i.partition_name||' '||to_char(p_count));
end loop;
end ;

===

with creation date !!!

define 1 ='XXXXXX';

set serveroutput on

declare

p_count number;

begin

for i in (select owner,object_name,subobject_name,created from dba_objects where object_name='&1' and owner= 'RRRR' and object_type='TABLE PARTITION' order by created) loop

execute immediate 'select /*+ parallel(112) */ count(*) from '||i.OWNER||'.'||i.object_name||' partition('||i.subobject_name||')' into p_count;

dbms_output.put_line(to_char(i.created,'dd-mm-yyyy hh24:mi:ss')||' ' ||i.subobject_name||' '||to_char(p_count));

end loop;

end ;

===

interval '1' month

INTERVAL '1' YEAR

INTERVAL '10' day

define 1 ='XXXX';

set serveroutput on

declare

p_count number;

begin

for i in (select owner,object_name,subobject_name,created from dba_objects where object_name='&1'

and owner= 'XXX'

and object_type='TABLE PARTITION' and created > TRUNC(SYSDATE+1) - INTERVAL '1' YEAR

order by created) loop

execute immediate 'select /*+ parallel(112) */ count(*) from '||i.OWNER||'.'||i.object_name||' partition('||i.subobject_name||')' into p_count;

dbms_output.put_line(to_char(i.created,'dd-mm-yyyy hh24:mi:ss')||' ' ||i.subobject_name||' '||to_char(p_count));

end loop;

end ;

set pagesize 0

select 'prompt PARTITION_NAME-- '||PARTITION_NAME , 'SELECT count(*),MIN("time") min , max("time") MAX from reports.reports partition ('||PARTITION_NAME ||');'from dba_tab_partitions

where 1=1 and TABLE_NAME='XXX'

set pagesize 0

select 'prompt PARTITION_NAME-- '||PARTITION_NAME , 'SELECT count(*),to_char(MIN("time"),''dd-mm-yyyy hh24:mi:ss'') min ,

to_char(max("time"),''dd-mm-yyyy hh24:mi:ss'') MAX from Anuj.test partition ('||PARTITION_NAME ||');'

from dba_tab_partitions where 1=1

and TABLE_NAME='XXX'

==================================

with subpartition count !!!!

alter session set container=XXX ;

nohup sqlplus '/ as sysdba' @pcount.sql > pcount.sql_`date '+%Y%m%d_%H%M%S'`.log 2>&1 &

-- pcount.sql

--set serverout on size 1000000 verify off

ALTER SESSION SET DB_FILE_MULTIBLOCK_READ_COUNT=128;

ALTER SESSION SET SORT_AREA_SIZE=500000000;

ALTER SESSION SET SORT_AREA_RETAINED_SIZE=500000000;

set serveroutput on size unlimited

define TABLE_OWNER='XXXX'

define TABLE_NAME='PRINTED'

declare

sql_stmt varchar2(1024);

sql_stmt1 varchar2(1024);

row_count number;

row_count1 number;

v_table_name varchar2(50);

cursor get_tab is

select table_name,partition_name

from dba_tab_partitions

where table_owner=upper('&&TABLE_OWNER') and table_name='&&TABLE_NAME';

cursor get_tab1 is

select table_name,SUBPARTITION_NAME

from dba_tab_subpartitions

where table_owner=upper('&&TABLE_OWNER') and table_name='&&TABLE_NAME';

begin

dbms_output.put_line('Checking Record Counts for table_name');

dbms_output.put_line('Log file to numrows_part_&&TABLE_OWNER.lst ....');

dbms_output.put_line('....');

for get_tab_rec in get_tab

loop

for get_tab_rec1 in get_tab1

loop

v_table_name := get_tab_rec.table_name;

sql_stmt := 'select count(*) from &&TABLE_OWNER..'||get_tab_rec.table_name||' partition ( '||get_tab_rec.partition_name||' )';

sql_stmt1 := 'select count(*) from &&TABLE_OWNER..'||get_tab_rec1.table_name||' subpartition ( '||get_tab_rec1.SUBPARTITION_NAME||' )';

EXECUTE IMMEDIATE sql_stmt INTO row_count;

EXECUTE IMMEDIATE sql_stmt1 INTO row_count1;

dbms_output.put_line('Table partition_name'||rpad(get_tab_rec.table_name||'('||get_tab_rec.partition_name||')',50)||' '||TO_CHAR(row_count)||' rows.');

dbms_output.put_line('Table sub_name'||rpad(get_tab_rec.table_name||'('||get_tab_rec1.SUBPARTITION_NAME||')',50)||' '||TO_CHAR(row_count1)||' rows.');

END loop;

EXCEPTION when others then

dbms_output.put_line ('Error counting rows for table '||v_table_name);

end;

Tuesday, 8 November 2011

Oracle Sql formatting tool

Oracle tool

http://www.wangz.net/easyscript.php

http://www.wangz.net/scripts.php

Monday, 7 November 2011

ARCHIVE LOG START is DEPRECATED in Oracle 11g

ARCHIVE LOG START is not valid in Oracle 11g
ARCHIVE LOG START

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /opt/app/oracle/admin/arch
Oldest online log sequence 79
Next log sequence to archive 81
Current log sequence 81

SQL>
SQL> !mkdir -p /opt/app/oracle/admin/arch1

SQL> !ls -ltr /opt/app/oracle/admin/arch1
total 0

SQL> ARCHIVE LOG START '/opt/app/oracle/admin/arch1' ; ----- will not do any thing in Oracle 11g
Statement processed.

SQL> !ls -ltr /opt/app/oracle/admin/arch1
total 0

SQL> ALTER SYSTEM SET log_archive_dest ='/opt/app/oracle/admin/arch1' ;

System altered.

SQL> !ls -ltr /opt/app/oracle/admin/arch1
total 4
-rw-r----- 1 oracle oinstall 2560 2011-11-07 14:19 1_83_757353423.dbf

Oracle Table detail info

Table detail info

set echo off term on
accept 2 prompt "Enter the name of the Table: "
accept 1 prompt "Enter table owner: "
set heading on newpage 0
ttitle 'Table Description - Space Definition'

btitle off
column Nline newline
set pagesize 54
set linesize 78
set heading off embedded off verify off

-- set term off

set embedded on heading on

column Ts format a20
column Ta format a20
column Clu format a20
column Pcf format 99999999999990
column Pcu format 99999999999990
column Int format 99,999,999,990
column Mat format 99,999,999,990
column Inx format 99,999,999,990
column Nxt format 99,999,999,990
column Mix format 99,999,999,990
column Max format 99,999,999,990
column Pci format 99999999999990
column Num format 99,999,999,990
column Blo format 99,999,999,990
column Emp format 99,999,999,990
column Avg format 99,999,999,990
column Cha format 99,999,999,990
column Rln format 99,999,999,990
column Hdg format a30 newline
set heading off
select 'Table Name' Hdg, Table_Name Ta,
'Tablespace Name' Hdg, Tablespace_Name Ts,
'Cluster Name' Hdg, Cluster_Name Clu,
'% Free' Hdg, Pct_Free Pcf,
'% Used' Hdg, Pct_Used Pcu,
'Ini Trans' Hdg, Ini_Trans Int,
'Max Trans' Hdg, Max_Trans Mat,
'Initial Extent (K)' Hdg, Initial_Extent/1024 Inx,
'Next Extent (K)' Hdg, Next_Extent/1024 Nxt,
'Min Extents' Hdg, Min_Extents Mix,
'Max Extents' Hdg, Max_Extents Max,
'% Increase' Hdg, Pct_Increase Pci,
'Number of Rows' Hdg, Num_Rows Num,
'Number of Blocks' Hdg, Blocks Blo,
'Number of Empty Blocks' Hdg, Empty_Blocks Emp,
'Average Space' Hdg, Avg_Space Avg,
'Chain Count' Hdg, Chain_Cnt Cha,
'Average Row Length' Hdg, Avg_Row_len Rln,
'Last Analyzed' Hdg, Last_Analyzed La
from DBA_TABLES
where Table_Name=UPPER('&&2')
and Owner=UPPER('&&1')
/
set heading on
set embedded off
column Cn format a30 heading 'Column Name'
column Fo format a15 heading 'Type'
column Nu format a8 heading 'Null'
-- column Nds format 99,999,999 heading 'No Distinct'
-- column Dfl format 99 heading 'Dflt Len'
column Dfv format a12 heading 'Default Val'
ttitle 'Table Description - Column Definition'
select Column_Name Cn,
Data_Type ||
DECODE(Data_Type,
'NUMBER',
'('||TO_CHAR(Data_Precision)||
DECODE(Data_Scale,0,'',','||
TO_CHAR(Data_Scale))||')',
'VARCHAR2',
'('||TO_CHAR(Data_Length)||')',
'CHAR',
'('||TO_CHAR(Data_Length)||')',
'DATE','',
'LONG','',
'Error') Fo,
DECODE(Nullable,'Y','','NOT NULL') Nu,
Data_Default Dfv
from DBA_TAB_COLUMNS
where Table_Name=UPPER('&&2')
and Owner=UPPER('&&1')
order by Column_ID
/

ttitle off
prompt
prompt TABLE CONSTRAINTS
prompt
set heading on
column Cn format a20 heading 'Primary Constraint'
column Cln format a20 heading 'Table.Column Name'
column Ct format a7 heading 'Type'
column St format a7 heading 'Status'
column Ro format a20 heading 'Ref Owner|Constraint Name'
column Se format a70 heading 'Criteria ' newline
break on Cn on St
set embedded on
prompt Primary Key
prompt
select CNS.Constraint_Name Cn,
CNS.Table_Name||'.'||CLS.Column_Name Cln,
INITCAP(CNS.Status) St
from DBA_CONSTRAINTS CNS,
DBA_CONS_COLUMNS CLS
where CNS.Table_Name=UPPER('&&2')
and CNS.Owner=UPPER('&&1')
and CNS.Constraint_Type='P'
and CNS.Constraint_Name=CLS.Constraint_Name
order by CLS.Position
/
prompt Unique Key
prompt
column Cn format a30 heading 'Unique Key'
select CNS.Constraint_Name Cn,
CNS.Table_Name||'.'||CLS.Column_Name Cln,
INITCAP(CNS.Status) St
from DBA_CONSTRAINTS CNS,
DBA_CONS_COLUMNS CLS
where CNS.Table_Name=UPPER('&&2')
and CNS.Owner=UPPER('&&1')
and CNS.Constraint_Type='U'
and CNS.Constraint_Name=CLS.Constraint_Name
order by CLS.Position
/
prompt Foreign Keys
prompt
column Cln format a18 heading 'Foreign Key' newline
column Clfn format a18 heading 'Parent Key'
column Cn format a40 heading 'Foreign Constraint'
break on Cn on St skip 1
select CNS.Constraint_Name Cn,
INITCAP(CNS.Status) St,
CLS.Table_Name||'.'||CLS.Column_Name Cln,
CLF.Owner||'.'||CLF.Table_Name||'.'||CLF.Column_Name Clfn
from DBA_CONSTRAINTS CNS,
DBA_CONS_COLUMNS CLF,
DBA_CONS_COLUMNS CLS
where CNS.Table_Name=UPPER('&&2')
and CNS.Owner=UPPER('&&1')
and CNS.Constraint_Type='R'
and CNS.Constraint_Name=CLS.Constraint_Name
and CLF.Constraint_Name = CNS.R_Constraint_Name
and CLF.Owner = CNS.Owner
and CLF.Position = CLS.Position
order by CNS.Constraint_Name, CLS.Position
/
prompt Check Constraints
prompt
column Cn format a40 heading 'Check Constraint'
column Se format a75 heading 'Criteria'
set arraysize 1
set long 32000
select Constraint_Name Cn,
INITCAP(Status) St,
Search_Condition Se
from DBA_CONSTRAINTS
where Table_Name=UPPER('&&2')
and Owner=UPPER('&&1')
and Constraint_Type='C'
/
prompt View Constraints
column Cn format a40 heading 'View Constraint'
select Constraint_Name Cn,
INITCAP(Status) St,
Search_Condition Se
from DBA_CONSTRAINTS
where Table_Name=UPPER('&&2')
and Owner=UPPER('&&1')
and Constraint_Type='V'
/
set arraysize 30

SQL> @tab1
Enter the name of the Table: EMP
Enter table owner: SCOTT
Table Name EMP
Tablespace Name USERS
Cluster Name
% Free 10
% Used
Ini Trans 1
Max Trans 255
Initial Extent (K) 64
Next Extent (K) 1,024
Min Extents 1
Max Extents 2,147,483,645
% Increase
Number of Rows 14
Number of Blocks 4
Number of Empty Blocks 0
Average Space 0
Chain Count 0
Average Row Length 38
Last Analyzed 15-OCT-11

1 row selected.

Mon Nov 07 page 1
Table Description - Column Definition

Column Name Type Null Default Val
------------------------------ --------------- -------- ------------
EMPNO NUMBER(4) NOT NULL
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)

8 rows selected.

TABLE CONSTRAINTS

Primary Key

Primary Constraint
--------------------
Table.Column Name Status
-------------------- -------
SYS_C0022543
EMP.EMPNO Enabled

1 row selected.

Unique Key

no rows selected

Foreign Keys

no rows selected

Check Constraints

no rows selected

View Constraints

no rows selected

Oracle re Create table with max length

dynamic plsql example

set serverout on
set feedback off
set verify off

accept v_table prompt 'enter table name : '

declare

type r_c is ref cursor;
c_ref r_c;
v_cname col.cname%type;
t_table varchar2(30);
v_table varchar2(30);
t_command varchar2(200);
t_cid integer;
t_val number(10);
stat integer;
row_count integer;
tot_rows number;
tot_col number;

begin

t_table := '&v_table';
open c_ref for select cname from col where tname=t_table ;
select count(*) into tot_col from col where tname=t_table ;
dbms_output.put_line(' . ');
dbms_output.put_line(' . ');
dbms_output.put_line(' . ');
dbms_output.put_line(' ');

dbms_output.put('select ');

loop
fetch c_ref into v_cname;
exit when c_ref%NOTFOUND;
tot_rows:= c_ref%ROWCOUNT;

t_command := 'select max(length("'||v_cname|| '")) from &v_table';
t_cid := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(t_cid,t_command,dbms_sql.native);
DBMS_SQL.DEFINE_COLUMN(t_cid,1,t_val);
stat := DBMS_SQL.EXECUTE(t_cid);
row_count := DBMS_SQL.FETCH_ROWS(t_cid);
DBMS_SQL.COLUMN_VALUE(t_cid,1,t_val);

if t_val is null then
t_val := 1;
end if;

dbms_output.put('substr('||v_cname||',1,'||t_val||') ' ||v_cname );
if tot_rows < tot_col then
dbms_output.put_line(' ,' );
else
dbms_output.put_line(' from "'||t_table||'"' );
end if;

end loop;
close c_ref;

dbms_output.put_line(' . ');
dbms_output.put_line(' . ');
dbms_output.put_line(' . ');
end;
/
undefine t
set feedback on
set verify on

SQL> @tab
enter table name : EMP
.
.
.
select substr(EMPNO,1,4) EMPNO ,
substr(ENAME,1,6) ENAME ,
substr(JOB,1,9) JOB ,
substr(MGR,1,4) MGR ,
substr(HIREDATE,1,9) HIREDATE ,
substr(SAL,1,4) SAL ,
substr(COMM,1,4) COMM ,
substr(DEPTNO,1,2) DEPTNO from "EMP"

Saturday, 5 November 2011

Oracle grant select from One schema to other schema

set heading off pagesize 0 feedback off echo off verify off linesize 200

ACCEPT schema_name CHAR prompt 'SCHEMA_NAME refers to the schema to duplicate privileges from :'
ACCEPT grantee_name CHAR prompt 'GRANTEE_NAME refers to the SCHEMA/role to grant privileges to :'

spool new_&&grantee_name&&schema_name..sql
spool &&grantee_name&&schema_name..sql

select (CASE
WHEN
object_type in ('VIEW','MATERIALIZED VIEW') THEN 'GRANT SELECT on '
WHEN
OBJECT_TYPE IN ('SEQUENCE') THEN 'GRANT SELECT ON '
WHEN
OBJECT_TYPE LIKE 'DIRECTORY' THEN 'GRANT READ ON DIRECTORY '
ELSE 'NOT AN APPROPRIATE OBJECT'END ) ||OWNER||'."'||OBJECT_NAME||'" TO &&grantee_name ;'
from dba_objects a
where owner='&&schema_name'
AND OBJECT_TYPE IN ('DIRECTORY','VIEW','MATERIALIZED VIEW','SEQUENCE')
and not exists (select 1 from dba_tab_privs b where a.object_name=b.table_name
and b.owner=a.owner and b.grantee='&&grantee_name');

-- Now we do tables...we did not do tables from all objects because we need to

-- limit it to tables and not external tables or nested tables

select 'grant select on '||a.owner||'."'||a.table_name||'" to &&grantee_name ;' from dba_tables a
where owner='&&schema_name' and a.table_name not in
(select axt.table_name from dba_external_tables axt
where axt.owner=a.owner)
and a.table_name not in
(select nst.table_name from dba_nested_tables nst
where nst.owner=a.owner)
and not exists (select 1 from dba_tab_privs b where
a.table_name=b.table_name
and b.owner=a.owner
and b.grantee='&&grantee_name');

-- Now we do the external tables

select 'grant select on '||owner||'."'||table_name||'" to &&grantee_name ;' from dba_external_tables a
where owner='&&schema_name'
and not exists (select 1 from dba_tab_privs b where a.table_name=b.table_name
and b.owner=a.owner and b.grantee='&&grantee_name');

-- Now we tackle objects not owned by the schema_name
-- exclude directories because they require a DIRECTORY key word in grant

select 'grant select on ' || owner||'."'||table_name||'" to &&grantee_name ;'
from dba_tab_privs a
where grantee='&&schema_name' and not exists (select 1 from dba_tab_privs b
where a.privilege=b.privilege and a.privilege='SELECT'
and a.table_name=b.table_name and b.grantee='&&grantee_name')
and not exists (select 1 from dba_directories c
where c.directory_name=a.table_name
and a.owner=c.owner)
;

spool off;
set heading on verify on feedback on echo on
prompt check script &&grantee_name&&schema_name..sql
undef schema_name
undef grantee_name

Oracle Plsql REF CURSOR

Working with REF CURSOR
ref cursor example

ref cursor are used only for select

from scott

set serveroutput on
declare

type r_c is ref cursor;
c_ref r_c;
c_ref2 r_c;
v_tabname user_tables.table_name%TYPE;
v_cnt number(10);

begin

open c_ref for select table_name from user_tables ;

dbms_output.put_line('Table_name Row_Counts ');

dbms_output.put_line('========== ========= ');

loop

fetch c_ref into v_tabname;

open c_ref2 for 'select count(1) from "'||v_tabname||'"';

fetch c_ref2 into v_cnt;

close c_ref2;

dbms_output.put_line(rpad(v_tabname,30,' ')||v_cnt);

exit when c_ref%NOTFOUND;

end loop;

close c_ref;

dbms_output.put_line('. ');
dbms_output.put_line('============end================== ');

end;
/

Table_name Row_Counts
========== =========
DEPT 4
BONUS 0
SALGRADE 5
MYEMP 2
EMP 14
ANUJ_BIG_TABLE 9999
ANUJ_BIG_TABLE1 9999
AA 14
ANUJ10 0
MYEMP_WORK 0
MYEMP_WORK 0
.
============end==================

********************************************************************************

from sys

set serveroutput on
declare

type r_c is ref cursor;
c_ref r_c;
c_ref2 r_c;
v_tabname dba_tables.table_name%TYPE;
v_owner dba_tables.table_name%TYPE;
v_cnt number(10);

begin

open c_ref for select owner,table_name from dba_tables where owner='SCOTT' ;

dbms_output.put_line('Table_name Row_Counts ');

dbms_output.put_line('========== ========= ');

loop

fetch c_ref into v_owner,v_tabname;

open c_ref2 for 'select count(1) from "'||v_owner||'"."'||v_tabname||'"';

fetch c_ref2 into v_cnt;

close c_ref2;

dbms_output.put_line(rpad(v_tabname,30,' ')||v_cnt);

exit when c_ref%NOTFOUND;

end loop;

close c_ref;

dbms_output.put_line('. ');
dbms_output.put_line('============end================== ');

end;
/

===================================

for testing

begin
dbms_output.put_line('select count(1) from "'||'v_owner'||'"."'||'v_tabname'||'"') ;
end;
/

Anuj Singh Oracle DBA

Search This Blog

Total Pageviews

Thursday, 10 November 2011

Oracle Count the number of rows in tables in current SCHEMA

Tuesday, 8 November 2011

Oracle Sql formatting tool

Monday, 7 November 2011

ARCHIVE LOG START is DEPRECATED in Oracle 11g

Oracle Table detail info

Oracle re Create table with max length

Saturday, 5 November 2011

Oracle grant select from One schema to other schema

Oracle Plsql REF CURSOR

Oracle DBA

anuj blog Archive