How to get/show First Date of the Month ?

If you want to show or if you need the month first day, there is no built-in function in Oracle. But you can get it easily by using some other single-row date function. Here bellow an example of this.

SELECT LAST_DAY(ADD_MONTHS(SYSDATE,-1))+1
FROM DUAL;

Run the above sql and you will get the first day of current month. For last day of current month just use the LAST_DAY function like thisLAST_DAY(SYSDATE)

Need more help ? Just Leave you comments...

Cheers

How to Show Time with Date in a Session ?

If your want to show time with date in your sql environment like, working with Sql Developer or Sql Plus or iSql Plus then change the value of NLS_DATE_FORMAT.

To change the value log in the sql environment and write:

alter session set NLS_DATE_FORMAT ='DD-MON-RRRR HH:MI:SS AM';

then check the value...you are done..!!!

How to Apply Patch Set 10.2.0.5 on Oracle Database 10gR2 ?

Oracle Database upgrade to 10.2.0.5 from 10.2.0.3, you need Patch Set No. 8202632. Download the patch set from oracle metalink.

Here is the guide for windows environment, how to apply this patch set.

Shut down the following Oracle Database 10g services in the order specified before installing the patch set:

    Shut down all services in the Oracle home that might be accessing a database, for example Oracle Enterprise Manager Database Control:

                C:\>cd C:\oracle\product\10.2.0\db_1\bin

                C:\oracle\product\10.2.0\db_1\bin>set oracle sid=ait

                C:\oracle\product\10.2.0\db_1\bin>emctl stop dbconsole

                C:\oracle\product\10.2.0\db_1\bin>isqlplusctl stop

You can also stop these services using the Services Control Panel. These services are referred to as OracleDBConsoleSID and OracleSIDiSQL*Plus respectively.

                C:\>sqlplus /nolog

                SQL> conn sys/password@ait as sysdba

                SQL> shutdown

Use the Services Control Panel to stop the Oracle Database service OracleServiceSID where SID is the system identifier of the database.

If any problem arises, then go to service control panel. Set all oracle service property to manual and restart the pc.

To install the Oracle Database 10g patch set interactively:

1. Log on as a member of the Administrators group to the computer on which you are going to install Oracle components. If you are installing on a Primary Domain Controller or a Backup Domain Controller, log on as a member of the Domain Administrators group.

2. Start Oracle Universal Installer located in the unzipped area of the patch set. For example, Oracle_patch\setup.exe.

3. On the Welcome screen, click Next.

4. In the Specify Home Details screen, select the name of the Oracle home that you want to update, or select the Path that you want to update from the list, then click Next

5. Enter your e-mail address and password for your My Oracle Support account to be informed of security issues, then click Next.

Note:

The registration and configuration can also be done manually after patch set installation.

The OCM registration page appears only while applying 10.2.0.5 patch over existing Database instances alone.

-----======

6. If you are installing the patch set on an Oracle RAC cluster, click Next when the Selected Nodes screen appears.

7. On the Summary screen, click Install.

When the installation is complete, the End of Installation screen appears.

Start the listner..

For Oracle single-instance installations, start the listener as follows:

C:\> lsnrctl start

or use service control panel.

Start the Oracle Database Upgrade Assistant:
From the Start menu, select Programs, then Oracle - HOME_NAME, then Configuration and Migration Tools and then Database Upgrade Assistant to start the Oracle Database Upgrade Assistant.

Complete the following steps displayed in the Oracle Database Upgrade Assistant screen:

1. On the Welcome screen, click Next.
2. On the Databases screen, select the name of the Oracle Database that you want to update, then click Next.

Note:

For Oracle RAC, enter the SYS password to do the upgrade.

3. On the Recompile invalid objects screen, select the Recompile the invalid objects at the end of upgrade option, then click Next.
4. If you have not taken the back up of the database earlier, on the Backup screen, select the I would like to take this tool to backup the database option, mention the Path, then click Next.
5. On the Summary screen, check the summary, then click Finish.
6. On the End of Upgrade screen, click Exit, then click Yes to exit from Oracle Database Upgrade Assistant.