One Stop Shopping for Date/Timestamp Queries

Assumptions

[1] Under this work, ALL_OBJECTS view will be used, as it usually has many rows.
[2] New queries will be added to address new requirements.This post will grow with time.
[3] Reader should have basic knowledge of built-in SQL functions.

[4] Following are not the only ways to solve the questions.

Queries

1) Given a date, Display n subsequent days including days name ?

[Solution]

Given date='28-AUG-01' and n=8

SQL> select to_char(SUB_DATE,'Day') Day,SUB_DATE SUBSEQUENT_DATE from
(select to_date('28-AUG-01') + rownum-1 SUB_DATE
from ALL_OBJECTS where rownum<=8)


DAY SUBSEQUENT_DATE
--------- ---------
Tuesday 28-AUG-01
Wednesday 29-AUG-01
Thursday 30-AUG-01
Friday 31-AUG-01
Saturday 01-SEP-01
Sunday 02-SEP-01
Monday 03-SEP-01
Tuesday 04-SEP-01

8 rows selected.

SQL>

2) Given two dates, Provide days between them including days name ?

[Solution]

Given dates='28-JAN-01','05-FEB-01'

SQL> select
to_char(BETWEEN_DATES,'Day') Day, BETWEEN_DATES DATES from
(
select to_date('28-JAN-01')+rownum-1 BETWEEN_DATES from ALL_OBJECTS
where rownum<=(to_date('05-FEB-01')-to_date('28-JAN-01')+1)
)
/

DAY DATES
--------- ---------
Sunday 28-JAN-01
Monday 29-JAN-01
Tuesday 30-JAN-01
Wednesday 31-JAN-01
Thursday 01-FEB-01
Friday 02-FEB-01
Saturday 03-FEB-01
Sunday 04-FEB-01
Monday 05-FEB-01

9 rows selected.

SQL>

3) How many sundays came in the month of december 2006?

[Solution]

SQL> select count(*) sundays from
(
select to_char(to_date('01-DEC-06')+rownum-1,'Day') MONTH_DAYS
from ALL_OBJECTS
where rownum<=(last_day(to_date('01-DEC-06'))-to_date('01-DEC-06')+1)
)
where trim(MONTH_DAYS)='Sunday'
/

SUNDAYS
----------
5

SQL>

4) Fetch the data from a table for the current month and the next two months.

[Solution]

SQL> desc xxx;
Name Null? Type
----------------------------------------- -------- ----------------------------

DATE1 DATE

SQL> select * from xxx;
DATE1
---------
15-MAR-07
13-MAR-07
13-APR-07
29-MAY-07
29-JUN-07
29-JAN-07

SQL> select sysdate from dual;

SYSDATE
----------
27-MAR-07

SQL> select * from xxx
where date1
between trunc(sysdate,'mm') and
trunc(add_months(sysdate,3),'mm')-1;

DATE1
---------
15-MAR-07
13-MAR-07
13-APR-07
29-MAY-07

SQL>

New queries will come very soon...

A practical approach to understand Oracle number datatype

Oracle Database Version: 9iR2

Oracle number datatype houses fixed and floating point numbers. A number datatype can be decked by specifying p=precision (total number of digits) and s=scale(number of digits to the right of the decimal point).

If specifying, precision can go up to 38 digits.


SQL> create table table1(col1 number(39,2));
create table table1(col1 number(39,2))
*
ERROR at line 1:
ORA-01727: numeric precision specifier is out of range (1 to 38)



If precision is not specified, Number data type can house a value up to 9.99E125.

Max Value

SQL> create table table1(col1 number);

Table created.

SQL> insert into table1 values(9999999999999999999999999999
9999999999999999999999999999999999999999999999999999
999999999999999999999999999999999999999999999);

1 row created.

SQL> select length('999999999999999999999999999999999999
9999999999999999999999999999999999999999999999999999
9999999999999999999999999999999999999') Length from dual;

LENGTH
----------
125


Beyond Max Value

Wrote file afiedt.buf

1* insert into table1 values(999999999999999999999999999999
9999999999999999999999999999999999999999999999999999
99999999999999999999999999999999999999999991)
SQL> /
insert into table1 values(99999999999999999999999999999999
999999999999999999999999999999999999999999999999999
9999999999999999999999999999999999999999991)
*
ERROR at line 1:
ORA-01426: numeric overflow



1* select length('99999999999999999999999999999999999999
999999999999999999999999999999999999999999999999999
9999999999999999999999999999999999991') Beyond Length from
dual
SQL> /

BEYOND LENGTH
----------
126

SQL>


If specifying, scale can be in the range (-84 to 127).


SQL> create table table1(col1 number(7,-88));
create table table1(col1 number(7,-88))
*
ERROR at line 1:
ORA-01728: numeric scale specifier is out of range (-84 to 127)


SQL> create table table1(col1 number(7,127));

Table created.

SQL> create table table1(col1 number(7,128));
create table table1(col1 number(7,128))
*
ERROR at line 1:
ORA-01728: numeric scale specifier is out of range (-84 to 127)



Let us take p=3 and s=1. Out of 3 digits 1 digit can come to the left of decimal point. Thus only 2 digits can come to the right of decimal point.

SQL> create table table1(col1 number(3,1));

Table created.

If you specify a value .26 on right side, then Oracle will round it to .3 . You can insert a two digit positive value. Oracle takes (p-s)=(3-1) = 2 digits on right side of decimal point. You cannot store 126.55555 value in this case.

SQL> insert into table1 values(12);

1 row created.

SQL> insert into table1 values(12.1);

1 row created.

SQL> insert into table1 values(12.1555); // .1555 will be rounded to .2

1 row created.

SQL> select * from table1;

COL1
----------
12
12.1
12.2

SQL> insert into table1 values(126.1555);
insert into table1 values(126.1555)
*
ERROR at line 1:
ORA-01438: value larger than specified precision allows for this column

SQL> insert into table1 values(100); // Only two digits allowed before decimal
insert into table1 values(100)
*
ERROR at line 1:
ORA-01438: value larger than specified precision allows for this column

Let us take now p=7 and s=-9. If negative scale value is specified then digits on right side of the decimal point will be rounded. Anything after the decimal will be ignored.

SQL> create table table1(col1 number(7,-9));

Table created.

SQL> insert into table1 values(1234567.12); // 7 digits on left

1 row created.

SQL> select * from table1;

COL1
----------
0

SQL> insert into table1 values(12345678.12); // 8 digits on left

1 row created.

SQL> select * from table1;

COL1
----------
0
0

SQL> insert into table1 values(123456781.12); // 9 digits

1 row created.

SQL> select * from table1;

COL1
----------
0
0
0

SQL> insert into table1 values(1234567812.12); // 10 digits on left

1 row created.

SQL> select * from table1;

COL1
----------
0
0
0
0
1000000000

SQL> insert into table1 values(1734567812.12); // 10 digits on left, should round to 2000000000

1 row created.

SQL> select * from table1;

COL1
----------
0
0
0
0
1000000000
2000000000

6 rows selected.

SQL>


How many digits can be there in this case ? (p-s) = (7-(-9))=16 digits can be stored on right side.


SQL> insert into table1 values(17345678125.12);

1 row created.

SQL> insert into table1 values(173456781255555.12);

1 row created.

SQL> insert into table1 values(1734567812555555.12);

1 row created.

SQL> select length('1734567812555555') from dual;

LENGTH('1734567812555555')
--------------------------
16

SQL>

Increasing one more digit on left side will lead to a complain as follows.

SQL> insert into table1 values(17345678125555555.12);
insert into table1 values(17345678125555555.12)
*
ERROR at line 1:
ORA-01438: value larger than specified precision allows for this column


As an another example, consider p=3 and s=-1.

SQL> create table table1(col1 number(3,-1));

Table created.

SQL> insert into table1 values(1.2);

1 row created.

SQL> insert into table1 values(99.2);

1 row created.

SQL> select * from table1;

COL1
----------
0
100

SQL> insert into table1 values(999.2);

1 row created.

SQL> select * from table1;

COL1
----------
0
100
1000

Can it house a value of 9999.2 ? No, because as scale is negative, it will try to round 9999 value. After rounding result will be 10000, which is a five digit number. It cannot be housed, because total digits can be p-s=3-(-1)=4.

SQL> insert into table1 values(9999.2);
insert into table1 values(9999.2)
*
ERROR at line 1:
ORA-01438: value larger than specified precision allows for this column


SQL> insert into table1 values(9999);
insert into table1 values(9999)
*
ERROR at line 1:
ORA-01438: value larger than specified precision allows for this column


SQL> insert into table1 values(9998);
insert into table1 values(9998)
*
ERROR at line 1:
ORA-01438: value larger than specified precision allows for this column


Can it house a value of 9498 ? Yes as follows.


SQL> insert into table1 values(9498); //As value is below 9500 no need to round

1 row created.

Conclusion: Various examples have been taken into consideration for understanding number data type. This practice will give more insight for beginners as they always confuse with precision and scale attributes of number data types. I welcome any comment on this.

Restoring Oracle database on different Windows Platforms

Purpose of this post is to answer the question of restoring oracle database on different windows platform. Three oracle databases have been created on Windows 2000 Advanced Server (Oracle SID=Win2000),Windows 2003 Enterprise Server (Oracle SID=Win2003) and Windows XP (Oracle SID=WinXP). On all the boxes Oracle 10gR2 have been installed.

Steps of restoring the database from Windows 2000 (Source Server) to Windows 2003 (Destination Server) are enumerated as follows...

1) Take the cold backup of the database present in source server. Also backup spfile.

2) Shift these files to the destination server. In source server the database files are present in c:\oracle\product\10.2.0\oradata\Win2000. In this experiment, these files will be restored in in D:\oracle\product\10.2.0\oradata\Win2000. So renaming of files is required here.

3) Create the default folders bdump,cdump,adump,dpdump and pfile in d:\oracle\product\10.2.0\admin\Win2000 folder.

4) Create the windows service for oracle. Specifying startmode as manual, will not start the instance.

D:\Documents and Settings\Administrator>oradim -new -sid Win2000 -startmode manual

5) CONTROL_FILES parameter is not modifiable through ALTER SYSTEM SET command. Create pfile from this spfile.

D:\Documents and Settings\Administrator>sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Mar 6 19:20:59 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to an idle instance.
SQL> create pfile='d:\oracle\product\10.2.0\db_1\database\initWin2000.ora' from spf
ile;

File created.

6) Change the values of CONTROL_FILES,DB_RECOVERY_FILE_DEST,BACKGROUND_DUMP_DEST and other location specific parameters, pointing to D drive.

7) Again connect to SQL and create the spfile.

D:\Documents and Settings\Administrator>sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Mar 6 19:20:59 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to an idle instance.

SQL> create spfile from pfile;

File created.

8) Execute startup command (instance will come throught spfile) in nomount stage and check
the value of CONTROL_FILES parameter.

SQL> startup nomount

ORACLE instance started.

Total System Global Area 289406976 bytes
Fixed Size 1248600 bytes
Variable Size 104858280 bytes
Database Buffers 180355072 bytes
Redo Buffers 2945024 bytes
SQL> show parameter control_files

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string D:\ORACLE\PRODUCT\10.2.0\ORADA
TA\WIN2000\CONTROL01.CTL, D:\ORAC
LE\PRODUCT\10.2.0\ORADATA\WIN2000
\CONTROL02.CTL, D:\ORACLE\PROD
UCT\10.2.0\ORADATA\WIN2000\CONTRO
L03.CTL

9) Mount the database.

SQL> alter database mount;

Database altered.

10) Rename the datafiles and online redo log files. One example shown below. If restoring in the same location then this step can be skipped.

SQL> select name from v$datafile;

NAME
----------------------------------------------------------------------------

C:\ORACLE\PRODUCT\10.2.0\ORADATA\Win2000\SYSTEM01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\Win2000\UNDOTBS01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\Win2000\SYSAUX01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\Win2000\USERS01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\Win2000\EXAMPLE01.DBF


SQL> ed
Wrote file afiedt.buf

1 alter database rename file
2 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\Win2000\SYSTEM01.DBF' to
3* 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\Win2000\SYSTEM01.DBF'
SQL> /

Database altered.



SQL> select member from v$logfile;

MEMBER
----------------------------------------------------------------------------

C:\ORACLE\PRODUCT\10.2.0\ORADATA\Win2000\REDO03.LOG
C:\ORACLE\PRODUCT\10.2.0\ORADATA\Win2000\REDO02.LOG
C:\ORACLE\PRODUCT\10.2.0\ORADATA\Win2000\REDO01.LOG

SQL> alter database rename file
2 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\Win2000\REDO03.LOG'
3 to
4 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\Win2000\REDO03.LOG';

Database altered.


11) Double check the new file names/locations and start the database.

SQL> select name from v$datafile;

NAME
----------------------------------------------------------------------------

D:\ORACLE\PRODUCT\10.2.0\ORADATA\Win2000\SYSTEM01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\Win2000\UNDOTBS01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\Win2000\SYSAUX01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\Win2000\USERS01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\Win2000\EXAMPLE01.DBF

SQL> select member from v$logfile;

MEMBER
----------------------------------------------------------------------------

D:\ORACLE\PRODUCT\10.2.0\ORADATA\Win2000\REDO03.LOG
D:\ORACLE\PRODUCT\10.2.0\ORADATA\Win2000\REDO02.LOG
D:\ORACLE\PRODUCT\10.2.0\ORADATA\Win2000\REDO01.LOG

SQL> alter database open;

Database altered.

12) Take the hot/cold backup of the database.

In the same fashion, database from Windows 2000 box have been restored to Windows XP box. In addition to that following restore combinations have been tried.

Database from Windows XP box to Windows 2003 box.
Database from Windows XP box to Windows 2000 box.
Database from Windows 2003 box to Windows XP box.
Database from Windows 2003 box to Windows 2000 box.

In all the above combinations successful restoration of database has been achieved.

Conclusion:- Thus among 2000, XP and 2003 Windows, databases can be restored. Experiments have been done for oracle 10gR2 version. This will help to prepare test machines regardless of specified Windows Platforms.