Modify output of a query

Kamal Agnihotri
Kamal Agnihotri used Ask the Experts™
on
-- The code below works.

declare
     v_row  integer := 0;
     v_rowcnt integer;
 begin
     for t in (select table_name from user_tables order by 1) loop
         v_row := v_row + 1;
         execute immediate 'select count(*) from ' || t.table_name into v_rowcnt;
         dbms_output.put_line (rpad(v_row, 5, ' ') || (rpad(t.table_name,40,'.') || v_rowcnt));
     end loop;    
 end;    
/
--#############################################################

I want to add the corresponding Tablespace_Name to the output for it to look like

1    ACCOUNT................................23..............Adv_Data                
2    AD......................................       50..............Adv_Data          
3    AD_AGE_STATED...................70.............Adv_Data
4    AD_BODY.................................60............ Bev_Data
5    AD_CATEGORY......................45..............Adv_Data          
6    AD_LOCATION_STATED.......25..............Bev_Data

What change to make in the code on top.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
Check the columns in the user_tables view.  Tablespace_name is one of them...


This is untested but you get the idea:
declare
      v_row  integer := 0; 
      v_rowcnt integer;
  begin
      for t in (select table_name, tablespace_name from user_tables order by 1) loop
          v_row := v_row + 1;
          execute immediate 'select count(*) from ' || t.table_name into v_rowcnt;
          dbms_output.put_line (rpad(v_row, 5, ' ') || (rpad(t.table_name,40,'.') || rpad(v_rowcnt,20,'.')  ||  t.tablespace_name));
      end loop;     
  end;    
 /

Open in new window

Kamal Agnihotri

Author

Commented:
Slightwv,

Thanks. That worked.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial