Script to compile all invalid objects
Compile all Invalid objects - compile_all.sql
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 same file once or twice more. This is to ensure that all the dependent objects are compiled.
Script to generate CREATE TABLE script
All tables script - cr_table.sql
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
Script to generate CREATE TRIGGER script
All triggers script - cr_trig.sql
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 VIEW script
All views script - cr_view.sql
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 source for various database objects
Generate source code - gensql.sqlGensql 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".
Script for listing all LOCKED objects in a database
List all locked objects - locked_objects.sql[Added on 26th April 2007]
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 getting size used by a table
Query to get size used by a table - table_size.sql[Added on 18th September 2007]
This script contains the query to get the size (size) used by a table in MB.
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 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 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.DBA_FREE_SPACE, SYS.V_$DATAFILE, SYS.V_$TABLESPACE views.
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 views.
Script to identify which tablespace are full
This script lists the tablespace which cannot extent causing tablespace full.
