Time Flies!

Just thought that I have not updated the blog for sometime, but did not realize it is almost 10 months since the last update… Time really flies… Oracle Openworld is here, though this is my sixth or seventh OOW at SFO, 2012 is the first OOW where I am part of the event outside of participation… Will … Read more

Sequences Nearing Limit

Happy New Year 2012!  During the holidays we hit a production issue on the EBS instance. It was one of the Sequences reaching the maximum limit, but took a lot of time for our admins and Oracle support to figure out where the issue was… If we had the following SQL as part of the … Read more

Oracle EBS Housekeeping Jobs

For a long time, I searched for a comprehensive list of housekeeping jobs [Standard EBS jobs provided by Oracle] that should be scheduled periodically on the EBS instance. Since I could not find a single source, came up with my own after a lot of reading… There may be more, but these should be scheduled on the … Read more

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,      … Read more

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 … Read more

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 more

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 … Read more

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 … Read more