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)

update one table with data from another table

This blog post illustrates how to update more than one column in a table with values from columns in another table and explains how to do it in two RDBMS that I use.

Table Structures and values:
TableA has four columns: a, b, c, d (a is the primary key column)
TableB has five columns: a1, b1, c1, d1, e1 (a1 and b1 together constitute the primary key for this table)
The foreign key relationship between the two tables is based on A.a = B.a1
The data in these 2 tables is as follows:
I. TableA
a b c d
1 x y z
2 a b c
3 t x z

II. TableB
a1 b1 c1 d1 e1
1 x1 y1 z1 40
2 a1 b1 c1 50

The requirement is to write a SQL to update columns b, c and d in TableA from the columns b1, c1 and d1 from TableB where-ever the join condition satisfies and e1 > 40 in TABLEB.

Oracle:
UPDATE TABLEASET (b, c, d) = (SELECT b1, c1, d1 from TABLEB WHERE TABLEB.a1 = TABLEA.a and TABLEB.e1 > 40)WHERE EXISTS (SELECT 1 from TABLEB WHERE TABLEB.a1 = TABLEA.a and TABLEB.e1 > 40)
/

Results after the update:
a b c d
————————————
1 x y z
2 a1 b1 c1
3 t x z


SQL Server:
UPDATE TABLEA
SET b = TABLEB.b1,
c = TABLEB.c1,
d = TABLEB.d1
FROM TABLEA, TABLEB
WHERE TABLEA.a = TABLEB.a1
AND TABLEB.e1 > 40
GO

Note: This is an extension in SQL Server i.e. the FROM clause – it does make it simple to understand and is a nice feature.
Results after the update:
a b c d
————————————
1 x y z
2 a1 b1 c1
3 t x z



Happy writing SQL

Alter table column

This is something so simple, yet can cause you to pull your hair out. All you want to do is alter the length of a column in an Oracle table:

An example table:
Members
MbrID number(5)
FirstName varchar2(30)
Surname varchar2(30)

Now you wish to increase the length of the surname to 50 characters, as someone has a longer than usual surname.
Here is the syntax to do this:

alter table members
modify surname varchar2(50)

This has now been updated to :
Members
MbrID char(5)
FirstName varchar2(30)
Surname varchar2(50)


When you wish to add a column:
alter table members
add DateOfBirth date

When you wish to remove a column:
alter table members
drop column DateOfBirth

When you wish to rename a column
alter table customers
rename column surname to lastname

Find and replace text in MySQL and Oracle

Today someone told me that they have a blog with over 300 posts and just changed their URL, the problem is that the new blog still had reference to their previous blog's url. And all the images, and reference were to the previous URL which had a 401 re-direct to the new blog. The images, etc were already deleted from the previous blog and imported into the new URL.

He was actually going to update every single entry. I recommended that it the blog has all posts in it's SQL database and had to find all the fields which contained the text (old url address), with the new text (new url ).

Now I will show you and easy way to update this.

Go to your database SQL screen and type in as follows

UPDATE [your_table_name] SET [your_table_field] = REPLACE([your_table_field], '[string_to_find]' , '[string_to_be_replaced]');

example
UPDATE my_posts SET post_content = REPLACE(post_content, 'www.oldurl.com' , 'www.newurl.com');

So armed with only one query per column table combination, you can easily update your blog or database with the new text.

Setting preferred domain

When search engines find your website, they may come across, http://yourdomain.com or http://www.yourdomain.com, which is read as two domains, causing duplicate listings. To fix this you have to decide which web domain will be the preferred domain.

To set your domain in google, go to the www.google.com/webmasters/tools/ website and set your preferred domain.

After you set the preferred domain, you should force everyone to use this domain with a 301 redirect. You do this by editing your root .htaccess file, if hosted on a Linux server. If you do not have a .htaccess file you will need to create one first as follows:

1) Create a text file and name it .htaccess
2) Modify it with the preferred domain code (as details below)
3) Upload it to the root of your server (public www)

If you want people to redirect from non-WWW to WWW:
(for example, if someone types in http://yourdomain.com, it will forward to http://www.yourdomain.com)

RewriteEngine On
RewriteCond %{HTTP_HOST} !^(.*)\.yourdomain\.com$ [NC]
RewriteRule ^(.*)$ http://www.yourdomain.com/$1 [R=301,L]

If you want people to redirect from WWW to non-WWW:
(for example, if someone types in http://www.yourdomain.com, it will forward to http://yourdomain.com)

RewriteEngine On
RewriteCond %{HTTP_HOST} ^www\.yourdomain\.com$ [NC]
RewriteRule ^.*$ http://yourdomain.com%{REQUEST_URI} [R=301,L]

Don't forget to replace “yourdomain” with the URL of your domain, otherwise the code above won't work!

Therefore by setting a preferred domain you are ensuring that Google and all the major search engines will only index your url web links from one domain. This will help your PageRank in search engine rankings.

So don't put it off, set your preferred domain, if you have not done so already! You will see your PageRank rise!

Combine text using Concatenation in Oracle

When wanting to combine text you can use the concatenation operator

Examples:
To place comma after every row returned use as follows:
SELECT table_name ',' FROM user_tables;

You may have a date that you wish to place in a CSV file, you can concentate as follows:
SELECT 'the date today is: ' to_char(sysdate, 'MM-DD-YYYY') ',' FROM sys.dual;

Reverse function in Oracle

The following shows how to use the reverse function to reverse a string or number in Oracle.
SQL> select reverse('string') from dual;
REVERS
------
gnirts

However you may not have the function installed and can use the following procedure:
SQL>
SQL> create or replace procedure reverse(x in varchar2) as
2 a char(1);
3 i number;
4 begin
5 for i in 1..length(x) loop
6 select substr(x,length(x)-i+1,1) into c from dual;
7 dbms_output.put(a);
8 end loop;
9 dbms_output.put_line(' ');
10 end;
11 /


Procedure created.

SQL> set serverout on
SQL> exec reverse('Oracle')
elcarO

SQL> exec reverse('string')
gnirts
PL/SQL procedure successfully completed.

Get last 2 financial years quarters

The 'solution' below is to retrieve the start and end dates of annual quarters of the last 2 years, using the @end_date as your input parameter.

You can replace the '--- YOUR QUERY USING the @qtr_start and @qtr_end parameters' with your query, with the @qtr_start and @qtr_end as your search criteria.


DECLARE
@qtr_start DATETIME,
@qtr_end DATETIME,
@end_date DATETIME

-- your date parameter
SET @end_date = '2009-06-30'

-- initialise dates
SET @qtr_start = DATEADD(YYYY, -2, DATEADD(D,1, @end_date))
SET @qtr_end = DATEADD(DD, -1,DATEADD(M,3,@qtr_start))


WHILE @qtr_end <= @end_date
BEGIN
SELECT 'Quarter: '+convert(VARCHAR, @qtr_start, 3)+'-'+convert(VARCHAR, @qtr_end, 3),
@qtr_start,
@qtr_end

--- YOUR QUERY USING the @qtr_start and @qtr_end parameters

-- get rest of the quarters until end date
SET @qtr_start = DATEADD(DD, 1,@qtr_end)
SET @qtr_end = DATEADD(DD, -1,DATEADD(M,3,@qtr_start))
END