Scripts

  • Script to identify which tablespace are full This script lists the tablespace which cannot extent causing tablespace full.
    Posted May 27, 2010, 2:31 AM by Anantha Narayanan
  • Script to show used/free space datafile wise This script will list datafile wise Allocated size, Used Size and Free Size. For running this query you must have SELECT privileges to rem V$DATAFILE and DBA_FREE_SPACE ...
    Posted May 27, 2010, 2:30 AM by Anantha Narayanan
  • Script to show used/free space per tablespace This script will list tablespace wise free space and used space (also total size) as well as total space. For running this query you must have SELECT privileges to SYS ...
    Posted May 27, 2010, 2:30 AM by Anantha Narayanan
  • Script to find overall database size The biggest portion of a database's size comes from the datafiles. To find out how many megabytes are allocated to ALL datafiles: select sum(bytes)/1024/1024 "Meg" from ...
    Posted Jun 22, 2010, 10:44 PM by Anantha Narayanan
  • Script for getting size used by a table This script contains the query to get the size (size) used by a table in MB.
    Posted May 27, 2010, 2:27 AM by Anantha Narayanan
  • Script for listing all LOCKED objects in a database This script lists down the objects that have a lock. The output lists field USERNAME, OBJECT_OWNER, OBJECT_NAME, OBJECT_TYPE, OSUSER, SID, SERIAL#, STATUS, MODE_HELD. To kill the ...
    Posted May 27, 2010, 2:26 AM by Anantha Narayanan
  • Script for various database objects Gensql is a package comprising of various procedures to generate the source code to create objects in the database. The code is generated in a user (schema) level. This is ...
    Posted May 27, 2010, 2:25 AM by Anantha Narayanan
  • Script to generate CREATE VIEW script View creation script generated based on the view name passed in as paramter. Wild characters may be used (%) in the parameter list. Screen output saved at /tmp/crview.sql 1 ...
    Posted May 27, 2010, 2:22 AM by Anantha Narayanan
  • Script to generate CREATE TRIGGER script Trigger creation script generated based on the trigger name passed in as paramter. Wild characters may be used (%) in the parameter list. Screen output saved at /tmp/crtrig.sql1 ...
    Posted May 27, 2010, 2:16 AM by Anantha Narayanan
  • Script to generate CREATE TABLE script Table creation script generated based on the table name passed in as parameter. Wild characters may be used (%) in the parameter list. Screen output saved at /tmp/crtable.sql1 ...
    Posted May 27, 2010, 2:15 AM by Anantha Narayanan
  • Script to compile all invalid objects This script compiles all invalid objects in the schema. Login through SQL Plus and execute this file. In case all objects are not compiled even after the execution, execute the ...
    Posted May 27, 2010, 2:41 AM by Anantha Narayanan
Showing posts 1 - 11 of 11. View more »

Script to identify which tablespace are full

posted May 27, 2010, 2:31 AM by Anantha Narayanan

This script lists the tablespace which cannot extent causing tablespace full.

Script to show used/free space datafile wise

posted May 27, 2010, 2:30 AM by Anantha Narayanan

This script will list datafile wise Allocated size, Used Size and Free Size. For running this query you must have SELECT privileges to rem V$DATAFILE and DBA_FREE_SPACE views.

Script to show used/free space per tablespace

posted May 27, 2010, 2:29 AM by Anantha Narayanan

This script will list tablespace wise free space and used space (also total size) as well as total space. For running this query you must have SELECT privileges to SYS.DBA_FREE_SPACE, SYS.V_$DATAFILE, SYS.V_$TABLESPACE views.

Script to find overall database size

posted May 27, 2010, 2:28 AM by Anantha Narayanan   [ updated Jun 22, 2010, 10:44 PM ]

The biggest portion of a database's size comes from the datafiles. To find out how many megabytes are allocated to ALL datafiles: 

select sum(bytes)/1024/1024 "Meg" from dba_data_files; 

To get the size of all TEMP files: 

select nvl(sum(bytes),0)/1024/1024 "Meg" from dba_temp_files; 

To get the size of the on-line redo-logs: 

select sum(bytes)/1024/1024 "Meg" from sys.v_$log; 

Putting it all together into a single query: 

select a.data_size+b.temp_size+c.redo_size "total_size"
from ( select sum(bytes) data_size
from dba_data_files ) a,
( select nvl(sum(bytes),0) temp_size
from dba_temp_files ) b,
( select sum(bytes) redo_size
from sys.v_$log ) c


Script courtesy:
Orafaq

Script for getting size used by a table

posted May 27, 2010, 2:26 AM by Anantha Narayanan

This script contains the query to get the size (size) used by a table in MB.

Script for listing all LOCKED objects in a database

posted May 27, 2010, 2:25 AM by Anantha Narayanan

This script lists down the objects that have a lock. The output lists field USERNAME, OBJECT_OWNER, OBJECT_NAME, OBJECT_TYPE, OSUSER, SID, SERIAL#, STATUS, MODE_HELD. To kill the session for the retrieved set of records use the following SQL statement: 

ALTER SYSTEM KILL SESSION '&SID,&SERIAL#'; 

Pass the values for SID and SERIAL# obtained from the query in locked_objects.sql to the statement above.

Script for various database objects

posted May 27, 2010, 2:22 AM by Anantha Narayanan   [ updated May 27, 2010, 2:25 AM ]

Gensql is a package comprising of various procedures to generate the source code to create objects in the database. The code is generated in a user (schema) level. This is best suited for duplicating databases with a smaller size or with no data (export / import may not allow you to have control over the initial extent size!). The package is capable of generating source code for Profile, User, Role, Table, Foreign key, Primary/Unique key, Check constraint, Index, Comment, View, Synonym, Package/Procedure/Function, Trigger and Database link. The package comes with a procedure which can do all the above object code generations in one command. The online help provides the list of parameters and their default values in each procedure. 

The procedure uses the Oracle database 7.3 feature of UTL_FILE to write the output. Make sure you have UTL_FILE_DIRECTORY = /output_directory_path in your init.ora file and to include this path in the script as your output destination. This script assumes a value of "/tmp". 

For More Help on GENSQL click here

Script to generate CREATE VIEW script

posted May 27, 2010, 2:21 AM by Anantha Narayanan

View creation script generated based on the view name passed in as paramter. Wild characters may be used (%) in the parameter list. Screen output saved at /tmp/crview.sql

1. View Owner (Wild character % may be used)

2. View Name (Wild character % may be used)

Script Courtesy: http://www.bijoos.com/ora7/oracle_source.htm

Script to generate CREATE TRIGGER script

posted May 27, 2010, 2:16 AM by Anantha Narayanan

Trigger creation script generated based on the trigger name passed in as paramter. Wild characters may be used (%) in the parameter list. Screen output saved at /tmp/crtrig.sql

1. Trigger Owner (Wild character % may be used)

2. Trigger Name (Wild character % may be used)

Script Courtesy: http://www.bijoos.com/ora7/oracle_source.htm

Script to generate CREATE TABLE script

posted May 27, 2010, 2:14 AM by Anantha Narayanan

Table creation script generated based on the table name passed in as parameter. Wild characters may be used (%) in the parameter list. Screen output saved at /tmp/crtable.sql

1. Table Owner (Wild character % may be used)

2. Table Name (Wild character % may be used)

Script Courtesy: http://www.bijoos.com/ora7/oracle_source.htm

1-10 of 11