Thursday, September 5, 2013

APPEND THE DATE AND TIME TO A SQL*PLUS OUTPUT FILENAME


APPEND THE DATE AND TIME TO A SQL*PLUS OUTPUT FILENAME
======================================================

    If you've got an output file that you SPOOL to regularly from SQL*Plus,
but you don't want to overwrite the file, you can uniquely identify it by
appending the date and time to the filename.  Here's an example:

    column date_column new_value var1
    select to_char (sysdate, 'YYYY-MM-DD-HH24MISS') || '.txt' date_column
    from   dual
    /
    spool c:\temp\filename-&var1

        < stuff you want to spool >

    spool off

Wednesday, September 4, 2013

Decommission of Oracle database

How to purge databases

There can be many databases that are hosted on an Oracle server, and requirement is to delete one DB without impacting other databases. This can be done by two ways:
1.    Manual method
2.    Using DBCA

Dynamic SQL in Oracle

 What Is Dynamic SQL?
Dynamic SQL enables you to write programs that reference SQL statements whose full text is not known until runtime.

How to clone the Oracle database software

This blog post describes the process of cloning an Oracle Home - in this case we are cloning a 11g Release 2 Oracle Home from source machine devastator to target machine vixen.

On source (devastator)

ORACLE_HOME location is : /u01/app/oracle/product/11.2.0/dbhome_1

Go to directory on source machine one level higher and tar the db_home1 directory and all sub-directories

How to use SQL Tuning Advisor in Oracle 10G

1       Introduction

Purpose

SQL tuning is one of the challenging tasks faced by Database Administrators. It is an interesting and creative, but at the same time, daunting task. Manual tuning of SQL statements requires through knowledge of how the statements are executed in the background and experience to understand suitable access paths to yield better response times. Moreover, in a typical application, there are just too many SQL statements to tune and advise the developers.

Tuning Oracle Database Basics

1       Introduction

Purpose

One of the biggest responsibilities of a DBA is to ensure that the Oracle database is tuned properly. The Oracle RDBMS is highly tunable and allows the database to be monitored and adjusted to increase its performance.