Tablespace Free Space and Max Size

Earlier, provided a small script to find the maximum growable size of a tablespace... Here is little more comprehensive script that shows more tablespace space info. Current size, growable size based on the autoextensible data files, free space, biggest chunk...All size is in MB.


SELECT a.tablespace_name TSNAME, 
        ROUND(SUM(a.growable)/1048576) growable, 
        ROUND(SUM(a.tots)/1048576) Tot_Size,
        ROUND(SUM(a.sumb)/1048576) Tot_Free,
        ROUND(SUM(a.sumb)*100/sum(a.tots)) Pct_Free,
        ROUND(SUM(a.largest)/1048576) Large_Ext
FROM   (SELECt tablespace_name, 0 tots, SUM(bytes) sumb,
               MAX(bytes) largest, 0 growable
        FROM   dba_free_space a
        GROUP BY tablespace_name
        UNION
        SELECT tablespace_name, SUM(bytes) tots, 0, 0,  SUM (GREATEST (bytes, DECODE (autoextensible, 'YES', maxbytes, 0))) growable
        FROM   dba_data_files
        GROUP BY tablespace_name
        UNION
        SELECT tablespace_name, SUM(bytes) tots, 0, 0,  SUM (GREATEST (bytes, DECODE (autoextensible, 'YES', maxbytes, 0))) growable
        FROM   dba_temp_files
        GROUP BY tablespace_name) a, dba_tablespaces b
WHERE b.tablespace_name = a.tablespace_name
GROUP BY rollup(a.tablespace_name)
ORDER by a.tablespace_name

Enjoy!

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!

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!

Granting privileges on all or multiple objects to user/role

Granting privileges on all or multiple objects to user/role

Question:
I need to grant SELECT, UPDATE privilege on all tables owned by schema XXVMX to users MSUBBU, SMARTIN. Is there a command in Oracle to grant a privilege on all objects in schema to user?

Answer:
No such privilege in Oracle. You will have to write a script to grant the privilege on individual objects to the users.

If you have to repeat the same to more users, it may be better to create a role and grant the privileges to the role. Then assign the role to the users that need the privilege. Thus when new tables are created under XXVMX, the privilege need to be added to the role once only, do not have to do grant to all the individual users.

Create Role:

CREATE ROLE XXVMX_UPDATE;

Grant Role to Users:

GRANT XXVMX_UPDATE to MSUBBU, SMARTIN;

SQL Script:

set pages 0
set lines 300 trims on feedback on echo off


spool grants.sql


SELECT 'grant select, update on ' || owner ||'.'||table_name|| ' to XXVMX_UPDATE;'
FROM dba_tables
WHERE owner = 'XXVMX';


spool off


set pages 99 lines 80
set feedback on echo on

Execute the script file created to grant the privileges.

@grants.sql

You may replace the "dba_tables" in the query with "dba_objects" and change the WHERE clause appropriately to filter different sets of objects...

Script to Refresh Materialized Views

The following script can be used to refresh materialized views that are capable of FAST (incremental) refresh automatically. Can be used on EBS database as well if you un-comment the commented (REM) lines. The refresh criteria used is any fast refresh-able materialized view that has not been refreshed in the past 24 hours, but was refreshed in the last one month...

REM Uncomment below line if EBS database

REM alter session set current_schema=apps;

set serveroutput on size 9999

Declare

cursor mv2ref is select owner ||'.'||  mview_name mview

       from dba_mviews

       where last_refresh_date between sysdate -30 and sysdate -1

       and fast_refreshable = 'DML';

dummy pls_integer;

begin

REM Uncomment below line if EBS database

REM select MRP_AP_REFRESH_S.NEXTVAL into dummy from dual;

for rmv2ref in mv2ref loop

begin

    dbms_mview.refresh(rmv2ref.mview, 'F');

    dbms_output.put_line(rmv2ref.mview ||' Refreshed at '|| to_char(systimestamp));

exception

when others then

    dbms_output.put_line(rmv2ref.mview ||' Errored at '|| to_char(systimestamp));

    dbms_output.put_line('    Error: '|| sqlcode ||':'||sqlerrm);

end;

end loop;

end;

/

Enjoy!