My Hobby

For those who don't know, I have somewhat sort an arty side within me .. I can sew clothing, soft furnishings and even make jewlery, which I sell through ETSY.COM. Take a look and let mw know what you think...


The URL is http://www.etsy.com/shop/RBJohnson.

Delete all objects from Oracle database

Someitmes you wish to have a 'clean' database, or you may not wish to create your database from scratch, then in this case you can delete all the objects from your database as follows using SQLPlus:

You can use toad, using the results from the select statements between the spool lines below.

NOTE: USE WITH GREAT CAUTION!

1. Put the following in the file “DB_drop.sql”


set feedback off
set pagesize 0
spool AllObjectsDrop.sql
select 'drop view '||view_name||';' from user_views;
select distinct 'drop sequence '||sequence_name|| ';'from user_sequences;
select distinct 'drop table '||table_name|| ';'from user_tables;
select distinct 'drop procedure '||name|| ';'from user_source where type = 'procedure';
select distinct 'drop function '||name|| ';'from user_source where type = 'function';
select distinct 'drop package '||name|| ';'from user_source where type = 'package';
select 'drop synonym '||synonym_name||';' from user_synonyms where synonym_name not like 'sta%' and synonym_name like 's_%'
spool off


2. Log into the database as the user for the database you want to drop
3. Run the script (@DB_drop.sql). This creates the file “AllObjectsDrop.sql” which contains commands to drop all objects.
4. Run the created script (@AllObjectsDrop.sql)

Login to Oracle as sysdba in one command

Log into your database via sqlplus with sysdba priv’s with a single command. You may set up an alias if you wish.

1. Log into the server as the oracle user end ensure you have the effective group of the appropriate DBA group. typical unix installations have the OS user as ‘oracle’ and the dba group as ‘dba’ (not to be confused with the ‘oinstall’ group).

2. Check your account with the unix ‘id’ command. Make sure your environment variables are set to the appropriate database
$ id
uid=510(oracle) gid=500(dba)
$ env | grep ORA
ORACLE_SID=TESTDB
ORACLE_HOME=/opt/app/oracle/product/9.2.0

3. connect to the database$ sqlplus “/ as sysdba”
SQL>

4. that’s it! be *very* careful!

To make sure sure you in the correct instance enter:

SQL> select name from v$database;

Get extra records between two tables - Oracle

Here are examples of each when you wish to see matching data and data differences between two tables.

Example:
You have primary Person Table that has 100 records, unique identifier person_id,
then you have a another Table Person_Depts with only 63 records that has Person_id.

Table : Persons
person_id number,
person_name, etc...

Table : Person_Dept
person_id number,
dept_id number,
status char(1), etc...

When you wish to find the 63 matching records it's easy:

select distinct
from persons p,
person_dept pd
where p.person_id = pd.person_id

When you wish to return the 37 rows of the persons in person_dept and do not have a match in persons, you extract them using one of the followig two queries:

select distinct pd.person_id from person_dept pd
minus
select p.person_id from persons p

Or you could use the following lines on small sets of data, using this on long non-indexed tables

select tp.person_ID
from
person_dept tp
where (tp.person_ID ) NOT IN ( select p.person_id from persons p)