Category: Script

Posted on

SQL Script to find the top 50 tables by size

Here is a SQL to identify the top 50 tables. When I say table, it could be a table, partitioned table or materialized view. Takes into consideration the indexes defined on the table and lob segments in the table, thus gives a complete picture of the table size. When you truncate the table, this much would be the space released…

select top50.owner, top50.table_name, meg, a.num_rows 
from dba_tables a, 
   (Select * from (
SELECT
   owner, table_name, TRUNC(sum(bytes)/1024/1024) Meg
FROM
(SELECT segment_name table_name, owner, bytes
 FROM dba_segments
 WHERE segment_type like 'TABLE%'
 UNION ALL
 SELECT i.table_name, i.owner, s.bytes
 FROM dba_indexes i, dba_segments s
 WHERE s.segment_name = i.index_name
 AND   s.owner = i.owner
 AND   s.segment_type like 'INDEX%'
 UNION ALL
 SELECT l.table_name, l.owner, s.bytes
 FROM dba_lobs l, dba_segments s
 WHERE s.segment_name = l.segment_name
 AND   s.owner = l.owner
 AND   s.segment_type = 'LOBSEGMENT'
 UNION ALL
 SELECT l.table_name, l.owner, s.bytes
 FROM dba_lobs l, dba_segments s
 WHERE s.segment_name = l.index_name
 AND   s.owner = l.owner
 AND   s.segment_type = 'LOBINDEX')
GROUP BY table_name, owner
HAVING SUM(bytes)/1024/1024 > 10  /* Ignore small tables */
ORDER BY SUM(bytes) desc
) where rownum < 51) top50
where top50.owner =a.owner
and top50.table_name = a.table_name
order by meg desc, num_rows desc;

Enjoy!

Posted on

Script to Grant Privileges and Create Synonyms

Extending the previous post Granting privileges on all or multiple objects to user/role , here is another script.

Often we have a requirement to create read only accounts for a schema. Typically these accounts are named _QUERY. The script when executed will prompt for the user name of the read only account, and the schema name where read privileges on the objects to be granted. It produces output to the screen and writes to file named tmpgrants.sql. Execute the tmpgrants.sql to grant SELECT privilege and to create a synonym under the read only account, so that the query account need not worry about qualifying the table with schema name.

First create the read only user account using similar syntax as below…

create user tdmaa_query identified by tdm123q default tablespace users;
grant create session to tdmaa_query;

Be sure to save all the below lines to a file and run the file in SQL*Plus… Executing this script will produce a script file named tmpgrants.sql. Run that script to grant privileges and create synonyms.

set pages 0 lines 200 trims on verify off feedback off


accept grants_to  prompt ‘Enter user to grant privileges: ‘
accept schema     prompt ‘Enter schema on which to grant: ‘


spool tmpgrants.sql


select ‘grant select on ‘||owner||’.’||table_name ||’ to &grants_to;’, chr(10),
       ‘create synonym &grants_to..’||table_name ||’ for ‘||owner||’.’||table_name||’;’, chr(10)
from  dba_tables
where owner = upper(‘&schema’)
union all
select ‘grant select on ‘||owner||’.’||view_name ||’ to &grants_to;’, chr(10),
       ‘create synonym &grants_to..’||view_name ||’ for ‘||owner||’.’||view_name||’;’, chr(10)
from  dba_views
where owner = upper(‘&schema’)
;


spool off


set pages 99 lines 80 verify on feedback on


prompt “Run tmpgrants.sql if you are satistified with the script…”

Enjoy!