Am I Oracle DBA
col "Am I Oracle DBA ?" format a15
select USERENV('ISDBA') "Am I Oracle DBA ?" from dual ;
col "Am I Oracle DBA ?" format a15
select USERENV('ISDBA') "Am I Oracle DBA ?" from dual ;
COLUMN curruser FORMAT A8 HEADING "Current|User"
COLUMN currschema FORMAT A8 HEADING "Current|Schema"
col "Am I Oracle DBA ?" format a15
select SYS_CONTEXT('USERENV', 'ISDBA') "Am I Oracle DBA ?" ,SYS_CONTEXT('USERENV', 'CURRENT_USER', 8) curruser,SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA', 8) currschema from dual
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
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.
Dynamic sql example
execute immediate sql example
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 ;
Subscribe to:
Posts (Atom)
