oracle Posts

[Oracle] How to Truncate and Clean All Your Database’s Tables

I worked with Oracle for this project, and sometime i need to truncate and clean all my oracle database’s content. So instead of truncate the tables one by one, i found a simple query to generate a simple truncate query.

select 'truncate table ' || table_name || ';' from user_tables

Hope it helped others :)

Google+

How to Count Number of Tables and Views from Oracle and DB2

Several days ago i had a project that made me had to do data migration from DB2 to Oracle.
For validity check, i had to count number of tables and views from DB2 before migrated and number of tables and views on Oracle after ive migrate it.

Okay enough chit-chat, this is my queries.
Count DB2’s number of tables

Select Count(*) from syscat.tables
where tabschema = 'YOUR_SCHEMA_NAME' 
AND type = 'T'

And this is how to count DB2’s View numbers

Select Count(*) from syscat.tables
where tabschema = 'YOUR_SCHEMA_NAME' 
AND type = 'V'

This is how to count number of tables from Oracle

select owner, count(*) from dba_tables
where owner = 'YOUR_OWNER_NAME'
group by owner

And this is how to count number of views from Oracle

select owner, count(*) from dba_views
where owner = 'YOUR_OWNER_NAME'
group by owner

Hope it will help others,
cheers (D)

Google+