Tablespaces used by a user ?

Tablespaces used by a user ?

select    tablespace_name
,    ceil(sum(bytes) / 1024 / 1024) "MB"
from    dba_extents
where    owner like '&USERNAME_IN_CAPS'
group by tablespace_name
order by tablespace_name

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

Count No .Of Rows in table

 

Count No .Of Rows in tables under a schema (anonymous proc).

run as sys:



declare
--
-- Anonymous pl/sql code to count rows in tables of interest for v8.1+
--
-- basic logic
-- create list of target tables (cursor)
-- while more tables in list
-- dynamically generate select count
-- print or store results
-- ---------------------------------------------------------------------

v_ct number := 0 ;
v_sqlcode number := 0 ;
v_stmt varchar2(90) ;
-- modify cursor select for tables of interest, order by.
cursor c_tbl is
select owner, table_name
from sys.dba_tables
where owner = '&OWNER';
--
r_tbl c_tbl%rowtype;
--
begin
open c_tbl;
loop
fetch c_tbl into r_tbl;
exit when c_tbl%notfound;
v_stmt := 'select count(*) from '||r_tbl.owner||'.'||r_tbl.table_name;
execute immediate v_stmt into v_ct;
v_sqlcode := SQLCODE;
if v_sqlcode = 0
-- An insert into a row count history table should probably be here
then dbms_output.put_line('Table '||r_tbl.owner||'.'||
rpad(r_tbl.table_name,30)||
' count is '||to_char(v_ct,'999999999990')
);
else dbms_output.put_line('Bad return code'||v_sqlcode||
' on select of '||r_tbl.owner||
'.'||r_tbl.table_name
);
end if;
end loop;
close c_tbl;
end;

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

Schema info

How to get details of a schema including objects and their counts?

rum as the user:


select object_type,count(*)
from user_objects
group by object_type
order by object_Type desc;

How to check the size and total no. of objects owned by all schemas in a db?

run from sys:


set pages 999
col "size MB" format 999,999,999
col "size GB" format 999,99
col "Objects" format 999,999,999
select    obj.owner "Owner"
,    obj_cnt "Objects"
,    decode(seg_size_gb, NULL, 0, seg_size_gb) "size GB"
,decode(seg_size, NULL, 0, seg_size) "size MB"
from     (select owner, count(*) obj_cnt from dba_objects group by owner) obj
,    (select owner,ceil(sum(bytes)/1024/1024/1024) seg_size_GB, ceil(sum(bytes)/1024/1024) seg_size
    from dba_segments group by owner) seg
where     obj.owner  = seg.owner(+)
order    by 3 desc ,2 desc, 1;
/

 /****   To find size of a Table or table partition  ********/..

run as sys:



  select
  owner as "Schema"
  , segment_name as "Object Name"
  , segment_type as "Object Type"
  , round(bytes/1024/1024,2) as "Object Size (Mb)"
  , tablespace_name as "Tablespace"
from dba_segments
where owner like 'SCHEMA' 
and segment_type like '%TABLE%'
and segment_name like 'Name of the table'
order by segment_name

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