Monthly Archives: January 2013

Create Oracle Script To Count All Rows In Each Table

Create Script to count rows in each table.  

The Script Looks as follows:


set lines 200
set pages 200
select 'Select count(*) from ' ||owner|| '.' ||table_name|| ';' from dba_all_tables order by owner, table_name; 



This will produce a script that looks like:


Select count(*) from XDBPM.REVISED_CHOICE_MODEL;
Select count(*) from XDBPM.REVISED_COLL_TYPES;
Select count(*) from XDBPM.REVISED_TYPES;
Select count(*) from XDBPM.REVISED_TYPE_ATTRS;
Select count(*) from XDBPM.REVISED_TYPE_SUMMARY;
Select count(*) from XDBPM.STORAGE_MODEL_CACHE;
Select count(*) from XDBPM.TYPE_SUMMARY;
Select count(*) from XDBPM.XDBPM_INDEX_DDL_CACHE;

Display All The Oracle Tables In The Database


Display All Oracle Tables in the database.  Shows all tables(less the system tables.   Change for your databases.  With where statement like  "where owner in 'YOUROWNERNAME'"



set lines 200
set pages 200
select owner, table_name, table_type, tablespace_name 
from dba_all_tables 
where owner not in ('SYSTEM','DBSNMP','ORDSYS','OUTLN','SYS') 
order by owner, table_name, tablespace_name



Display All The Oracle Indexes In The Database


Display All Oracle Indexes in the database.  Shows all indexes.   Customize for your database.

set lines 200
set pages 200
Select owner, index_name, table_type, tablespace_name 
From dba_indexes 
where owner not in ('SYSTEM','DBSNMP','ORDSYS','OUTLN','SYS') 
order by owner, index_name, tablespace_name

Upgrade Oracle Database from Oracle Standard Edition to Oracle Enterprise Edition

How to Convert/Upgrade an Oracle Database from Oracle Standard to Oracle Enterprise Edition

 The following steps should be done to convert your Oracle Standard Edition database to Oracle Enterprise Edition: 

  1. Backup the database
  2. UnInstall or De-install the Oracle Standard Edition software 
  3. Install the Oracle Enterprise Edition software  
  4. If you have an existing database, point your ORACLE_SID to this pre-existing database
  5. Startup the database
  6. Run the "catalog.sql" and "catproc.sql" scripts (This make sures all objects for Oracle Enterprise Edition are up to date)

That is pretty much it Oracle Enterprise Edition and ready to pay thoose yearly mainteance fees!