Drop all tables in Oracle DB (Schema)
Imagine you have a list of tables in your Oracle DB and you want to drop them all using a client like SQLDeveloper. That’s easier said then done, as Read More…
Category: DB Scripts
Grant Select on all Tables Owned By Specific User
BEGIN FOR R IN (SELECT owner, table_name FROM all_tables WHERE owner=’TheOwner’) LOOP EXECUTE IMMEDIATE ‘grant select on ‘||R.owner||’.’||R.Read More…
How to find out total memory used by each database ?
The following query can be used to determine the memory usage rates in Oracle databases. If there is an increase in the memory values from timeRead More…
Script to Create a New Oracle Database
If you need to create a database in Oracle 10g but doesn¥t want to use the DBCA feature or are unable to use it, you could simply follow the followiRead More…
Script to Create a New Oracle Database
If you need to create a database in Oracle 10g but doesn¥t want to use the DBCA feature or are unable to use it, you could simply follow the followiRead More…
In SQL*Plus, how to change the prompt to show the connected user and database?
In SQL*Plus, how do I change the prompt to show the connected user and database? Connect as oracle user and add the following line at the end of tRead More…
Generating DDL of a schema
These functions can be used as part of a select statement to produce output for groups of objects, such as object creation scripts, like the one showRead More…
Database health checks in Oracle
Performing Database health checks, when there is an issue reported by Application users. Check the Database detailsMonitor the consumption of resoRead More…
DBA SCRIPTS
1) DROP USER OBJECTS: conn username/password —>>> MANDATORY to connect as a user declare
cursor fkc is select table_name,
constraint_nameRead More…
Script for getting Oracle table size
There is no oracle defined function for getting size of a table. After all if it is easy with one simple query who will require a function. Isn’t it?ARead More…