Halimdba

Halim is a Sr. Database Engineer/Data Architect (in Atlanta, USA) who is an Oracle certified (OCP) DBA, (OCP) Developer, Certified Cloud Architect Professional as well as OCI Autonomous DB specialist with extensive expertise in Database design, configuration, tuning, capacity planning, RAC, DG, Scripting, Python, PL/SQL etc. He achieved 16th position in worldwide first-ever PL/SQL Challenge cup playoff- http://plsql-challenge.blogspot.com/2010/07/winners-of-first-plsql-challenge.html

Saturday, October 17, 2009

Oracle 11g Release 2 Database New Features

Oracle 11g Release 2 Database New Features
=================================
=================================
(From Oracle Magazin)

The many new features of and enhancements to Oracle Database 11g Release2 include upgrades to application development, availability, business intelligence and data warehousing, clustering, performance, security, server manageability, and unstructured-data management. For a complete list and description of the new features, see the Oracle Database New Features Guide
11g Release 2, available with the Oracle
Database 11g Release 2 documentation
(see “nextSTEPS” at the end of this article for the link).

Application Development
========================
Application developers can benefit from new application development features of
Oracle Database 11g Release 2. Oracle Application Express includes several Enhancements:

■ Application date format
■ Custom themes
■ Declarative binary large object (BLOB) support
■ Documented JavaScript libraries
■ Report printing
■ Forms conversion
■ Security
■ Interactive reporting region
■ Runtime-only installation

Availability
==================
To support high availability, Oracle Database 11g Release 2 includes
enhanced backup-and-recovery features:
■ Automatic Block Repair
■ Backup to Amazon Simple Storage Service (S3) using Oracle Secure Backup
cloud computing
■ DUPLICATE without connection to target database
■ Tablespace point-in-time recovery
■ DUPLICATE options make online application maintenance and upgrades
easier:
■ Edition-Based Redefinition
■ CREATE or REPLACE TYPE to allow FORCE
■ Fine-grained dependencies for triggers
■ IGNORE_ROW_ON_DUPKEY_INDEX hint for INSERT statement Edition- Based Redefinition makes it possible to change an application’s database objects without interrupting the application’s availability, by making the changes in a new edition.

High availability in Oracle Database 11g Release 2 is also enhanced by new
Oracle Data Guard features:
=====================================
■ Compressed table support in logical standby databases
■ Configurable real-time query apply lag limit
■ Integrated support for application failover in an Oracle Data Guard configuration
■ Support for as many as 30 standby databases

Business Intelligence and Data Warehousing Oracle Database 11g Release 2 improves many business intelligence and data warehouse capabilities, including analytics:
=============================================
■ Analytic Functions 2.0
■ Recursive WITH clause Data loading has been made simpler:
■ EXECUTE privilege for DIRECTORY objects
■ Preprocessing data for ORACLE_LOADER access driver in external tables

Partitioning in Oracle Database 11g Release 2 has been improved in several
ways:
====================================================
■ Virtual columns in the primary key or foreign key for reference partitioning
■ System-managed indexes for list partitioning

Features that enhance Performance and scalability have been added to Oracle Database 11g Release 2:
====================================================
■ In-memory parallel execution
■ Minimal-effort parallel execution—auto degree of parallelism and queuing
■ DBMS_PARALLEL_EXECUTE package many improvements have been made
to Oracle Warehouse Builder:
■ Find support in mapping editor
■ Business intelligence tool integration
■ Copy and paste of operators and attributes in mapping editor
■ Current configuration list in Design Center tool bar
■ Support for flat-file imports
■ Table function support
■ Experts available in Editor menu
■ Expression editing in Operator Edit dialog box
■ Grouping and spotlighting of objects in mapping editor
■ Management of locations registered in multiple control centers
■ User interface for managing locations
■ Key lookup operator enhancements
■ Mapping debugger enhancements
■ New Oracle JDeveloper–style user interface
■ Operator references included in generated PL/SQL code
■ Quick mapper
■ Repository Browser changes
■ Simplified Oracle Warehouse Builder repository upgrades
■ Support for extracting data from tables containing LONG datatype and
Subqueries in JOIN operator

Clustering
=========================
Oracle Real Application Clusters (OracleRAC) now provides many features for
simplifying installation and management of clusters and Oracle RAC databases:
■ Configuration Assistants, which support new Oracle RAC features
■ Cluster Verification Utility
■ Integration of Cluster Verification Utility and Oracle Universal Installer
■ Cluster Time Service
■ Oracle Cluster Registry enhancements
■ Grid Plug and Play
■ Oracle Restart
■ Policy-Based Cluster and Capacity Management
■ Oracle Clusterware resource modeling
■ Role-separated management
■ Agent development framework
■ Zero Downtime Patching for Oracle Clusterware and Oracle RAC
■ Oracle Enterprise Manager–based Oracle Clusterware resource management
■ Oracle Enterprise Manager provisioning for Oracle Clusterware and Oracle
RAC
■ Oracle Enterprise Manager support for Grid Plug and Play and Oracle Restart
■ Oracle Universal Installer support for removing Oracle RAC installations
■ Improved deinstallation support with Oracle Universal Installer
■ Scripts that support DBControl database downgrades
■ Oracle Restart integration with Oracle Universal Installer
■ Support for out-of-place Oracle Clusterware upgrade
■ Server Control Utility enhancements
■ Oracle Universal Connection Pool integrations

These improvements include faster relocation of services on node failure,support for as many as five copies of Oracle Cluster Registry for improved availability of the cluster, and storage of Oracle Cluster Registry in Oracle Automatic Storage Management.

Performance
============================
Server performance capabilities are improved by new features in Oracle
Database 11g Release 2:

■ Stored outlines migration to SQL plan management
■ Client result cache using table annotations support
■ Support for 4K-sector disk drives

Security
===========================
New auditing and encryption features of Oracle Database 11g Release 2 make
deployments more secure:
■ Audit Trail Cleanup
■ Tablespace master key

Audit Trail Cleanup provides for automatic file completion; the new encryption
key management functionality enables complete integration with hardware
security modules and increased performance for transparent data encryption.

Server Manageability
===========================
Oracle Database 11g Release 2 is easier to use than its predecessors, because of features that enhance manageability:
■ Oracle Automatic Storage Management Cluster File System
■ Oracle Automatic Storage Management Dynamic Volume Manager
■ Oracle Automatic Storage Management FS Snapshot
■ Oracle Cluster Registry and voting disk on Oracle Automatic Storage
Management
■ Oracle Automatic Storage Management intelligent data placement
■ Oracle Automatic Storage Management Configuration Assistant
■ Oracle Automatic Storage Management file access control
■ ASMCMD command extensions
■ Oracle Enterprise Manager support for Oracle Automatic Storage Management
Cluster File System
■ Oracle Enterprise Manager integration for Oracle Automatic Storage
Management file access control

Oracle Automatic Storage Management now supports all types of data, including database files, Oracle Clusterware files, and file system data such as Oracle homes and binaries. Oracle Automatic Storage Management Cluster File System
extends Oracle Automatic Storage Management by providing a file system
for Oracle binaries, report files, trace files, alert logs, and other application
data files.
Database management features complement storage management features
for general ease of use:

■ Oracle RAC configuration for Oracle Enterprise Manager
■ Patch application with DBControl
■ Automatic patching of time stamp with time zone data
■ Local time zone datatype
■ Segment creation on demand
■ Zero-size unusable indexes and index partitions
■ Metadata SXML Comparison Tool
■ Replay compare period reports
■ SQL Tuning Set comparisons
■ Sampling for Oracle Active Data Guard
■ Oracle Exadata simulation
■ Global Oracle RAC active session history report plus Automatic Database
Diagnostic Monitor backward compatibility
■ Oracle multithreaded server and Oracle Streams support
■ Parallel query plus alt plan recording, export of SQL Tuning Set enhancements
■ Synchronization controls

Unstructured-Data Management
=================================
Developers can achieve significant productivity gains with improvements to unstructured-data management capabilities:
■ Attribute extraction of requested attributes only
■ Client-side Digital Imaging and Communications in Medicine (DICOM)attribute
extraction
■ DICOM enhancements
■ Watermarking and image processing enhancements
■ Visualization framework
■ Network data model enhancements
■ GeoRaster Java API
■ Raster reprojections and ground control point-based georeferencing
■ Routing and geocoding enhancements
■ Database file system
■ LZO support for SecureFiles
■ Binary XML enhancements
■ Oracle XML DB repository performance improvements and guidelines
■ XMLIndex enhancements
■ XMLType partitioning

Other Database Capabilities
===========================
Oracle Database 11g Release 2 supports other functionality as well, including
improvements to Oracle Scheduler:

■ E-mail notification
■ File watcher
■ Multiple-destination jobs
■ Remote database jobs Other enhancements improve database operations:
■ Flashback Data Archive support for data definition languages
■ Instance caging
■ Data Pump Legacy Mode utility
■ IPv6 support for JDBC thin clients,Java virtual machine, and Java Debugger
■ Oracle Enterprise Manager Support Workbench for Oracle Automatic Storage Management

Thursday, October 15, 2009

How to connect Oracle to SQL Server with ODBC

How to connect Oracle to SQL Server?
=============================================
Here is an article I wrote on Hetergeneous Services.

In order to connect to SQL*Server from Oracle, you must do several things:

1. Install ODBC drivers for the SQL*Server database. The drivers are installed on your laptop or on the server that contains the Oracle code tree.

2. Setup the ODBC connection on your laptop using the Windows ODBC Data Source Administrator

3. Test the ODBC drivers to ensure that connectivity is made to the non-Oracle database.

4. Ensure the Global_names parameter is set to False in the Oracle database.

5. Configure the Hetergeneous services. This consists of creating an initodbc.ora file within the Oracle Oracle database installation.

7. Modify the Listener.ora file on the database installation to connect to the Oracle instance and ODBC drivers.

8. Modify the Tnsnames.ora file to point to the proper code tree.

9. Reload the listener

10. Create a database link on the Oracle installation.

11. Run a Select statement for the Oracle installation using the database link.

-----------------------------------------------------------------------------------

1.0 Introduction
==============
Heterogeneous Services is an integrated component within the Oracle database server. It allows transparent SQL access from an Oracle client to non-Oracle systems as if the non-Oracle system was an Oracle database. By using the Heterogeneous Services ODBC (HSODBC) agent with Easysoft products, this functionality can be extended on non-Windows platforms to include any ODBC or JDBC compliant database.
Important HSODBC is a normally a 32-bit application even when distributed with a 64-bit version of Oracle. You need to use the 32-bit HSODBC with a 32-bit Easysoft ODBC driver. For 64-bit platforms, choose the 32-bit version of the driver even when there's a 64-bit version available. 32-bit drivers will still function correctly on a 64-bit platform.
To check whether you have a 32-bit or 64-bit version of HSODBC, on the Oracle machine, run:

file $ORACLE_HOME/bin/hsodbc

If the command output contains ELF-Class64 (or something similar such as ELF-64 or ELF 64-bit), you have a 64-bit HSODBC. Otherwise, you have a 32-bit version.

If $ORACLE_HOME/bin/hsodbc is not present, contact your Oracle DBA.

If you have already downloaded, installed and licensed a 64-bit Easysoft ODBC driver, but need to use a 32-bit version, make a backup copy of /usr/local/easysoft/license/licenses and then remove the Easysoft installation directory.

For example:
cp /usr/local/easysoft/license/licenses /tmp
rm -rf /usr/local/easysoft
Download and install the 32-bit version of the driver, and then copy your backup licenses file to /usr/local/easysoft/license.

Note When using hsODBC to access SQL Server you may need to add "DisguiseWide=1" to your DSN as hsODBC does not seem to recognise SQL_WCHAR columns. (See 5.2 No rows returned when connecting Oracle hsODBC to MS SQL Server.)
The scope of this document is to provide a synopsis of what you need to set up to connect from SQL*Plus to an ODBC driver on a Unix based system. It assumes that the Oracle database server is configured and the HSODBC components are already installed on the machine. As HSODBC runs on the Oracle server, you will need to start the Oracle listener. This is not run on some Oracle client/server installations.

The process can be broken down into the following sections:

1. Install and configure the ODBC driver on the Oracle data-server.
=====================================================
2. Configure within Oracle:
====================================================
o Heterogeneous Services (iniths*.ora)
o Database Listener (listener.ora)
o Network Client (tnsnames.ora)
3. Create a database link through SQL*Plus utilising the above.
================================================================

==============================
2.0 Configure the ODBC driver
==============================
Whichever Easysoft product you want Oracle to load data from, they all contain the unixODBC driver manager. It is this that Oracle will load to access the ODBC driver. Essentially we are linking HSODBC to unixODBC and the driver manager is then responsible for the ODBC access. The configuration:

SQL*Plus>Oracle Client>HSODBC instance>UnixODBC>ODBC driver>Database

The Easysoft product that you will be installing has full installation instructions either within the product manual or within a 'Getting Started Guide'. For the purposes of this document it will be assumed that the software is installed, a datasource entered into /etc/odbc.ini and a successful test connection has been made to the database from the isql utility found within /usr/local/easysoft/unixODBC/bin. Once the ODBC driver has been set-up, Oracle can be configured to use this connection.

========================
3.0 Configure Oracle
========================

To allow the Oracle server to use HSODBC, you will need to edit three files and restart the Oracle Listener. These are:

• init file
• tnsnames.ora
• listener.ora

All three files reference either entries made in the other files or the datasource entry made within /etc/odbc.ini. Care needs to be taken when configuring these files as an incorrect entry in any of them can lead to a failure to connect.

3.1 Create an init file
------------------------------

On the Oracle server, login to the Oracle account and cd into $ORACLE_HOME/hs/admin. Every instance using HSODBC needs a separate iniths*.ora file. For this generic document, we created the init file inithsconnect.ora. You can name this file to identify the connection on your machine e.g inithsmssql.ora. This init file gives details of the datasource that is to be connected to and the shared object to be linked by HSODBC to provide the link. To provide this information, append the file with the following information pertinent to your system:
#
# This is a sample agent init file containing the HS parameters that
# are needed for an ODBC Agent.
#
# HS init parameters
#
HS_FDS_CONNECT_INFO = ODBC_DSN
HS_FDS_TRACE_LEVEL = 0
HS_FDS_TRACE_FILE_NAME = /tmp/hsodbcsql.trc
HS_FDS_SHAREABLE_NAME =/usr/local/easysoft/unixODBC/lib/libodbc.so

The information that should be entered is as follows:

• HS_FDS_CONNECT_INFO the datasource name as entered within /etc/odbc.ini. Note that as hsODBC uses the SQLDriverConnect ODBC API, you can actually put any valid ODBC connection string here. Here are some examples:
o "mydsn"
The name of a DSN in the odbc.ini file. Here, hsODBC will call SQLDriverConnect with "DSN=mydsn;UID=xxx;PWD=yyy;" where xxx and yyy are the username and password specified when the link is created (see Create a Database Link).
o "DSN=mydsn;UID=aaa;PWD=bbb;"
As above, except the username and password in the created link is overridden with aaa and bbb.
o "DRIVER={Easysoft ODBC-SQL Server};Server=myhost\\myinstance;UID=mydomain\\myuser; PWD=mypassword;Port=1500;"
A DSN-less connection where all the ODBC driver attributes are specified in the connection string. This example is for the Easysoft ODBC-SQL Server Driver.
o "FILEDSN=/tmp/mydsn.dsn"
This example uses the ODBC FILEDSN attribute to instruct the ODBC Driver manager to read the details for this data source from /tmp/mydsn.dsn.
• HS_FDS_SHAREABLE_NAME the location of libodbc.so on your machine. This is the driver manager that hsODBC will load to gain access to your ODBC data source.
• HS_FDS_TRACE_LEVEL is the level of tracing. You should generally leave this commented out with a # character at the start of the line, as tracing slows the operations down substantially. However, if you are having problems HS_FDS_TRACE_LEVEL may be set to a number from 1 to 4 (where 4 is the most verbose) and trace output is written to the file specified with HS_FDS_TRACE_FILE_NAME.
• HS_FDS_TRACE_FILE_NAME is the name of a trace file to us. This is only relevant if HS_FDS_TRACE_LEVEL is set to a number between 1 and 4. If HS_FDS_TRACE_LEVEL is set but HS_FDS_TRACE_FILE_NAME is not, the trace file will be created in the $ORACLE_HOME/hs/trace directory.
Once this information has been entered into the file, save into $ORACLE_HOME/hs/admin.

3.2 Edit listener.ora
-------------------------

Edit the listener.ora. This creates the instance that the tnsnames.ora references on connection through SQL*Plus. It creates a SID_NAME and specifies the Oracle executable to run on connection. The listener.ora is found within $ORACLE_HOME/network/admin. Configuring HSODBC needs the creation of a new SID_DESC within SID_LIST in the SID_LIST_LISTENER. An example looks like:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /space/oracle/OraHome10db)
(PROGRAM = extproc)
)
(SID_DESC=
(SID_NAME=hsconnect)
(ORACLE_HOME=/space/oracle/OraHome10db)
(PROGRAM=hsodbc)
(ENVS=LD_LIBRARY_PATH = /usr/local/easysoft/unixODBC/lib:
/usr/local/easysoft/lib)
))

The information that should be entered:
• SID_NAME enter the name of the init file without the init. If you have named the init file inithsconnect.ora, enter hsconnect as the SID_NAME.
• ORACLE_HOME enter the location of your Oracle home directory.
• PROGRAM enter hsodbc as this is the Oracle program used by this SID
• ENVS=LD_LIBRARY_PATH enter the Load Library Path here. You may have to add other paths (colon separated) e.g. for our 32 bit ODBC-Oracle driver, $ORACLE_HOME/lib32 is required.
Once entered save the file.

3.3 Edit tnsnames.ora
------------------------

The final Oracle file to edit is the tnanames.ora, found in $ORACLE_HOME/network/admin. This entry identifies the Oracle server to be attached to and the SID_NAME to be used. A new Oracle definition needs to be created:
hsconnectid=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(HOST=oracle_host)(PORT=1521))
(CONNECT_DATA= (SID=hsconnect))
(HS=OK)
)

The information that should be entered:
• hsconnectid This the name given to the server. This is the name used when connecting using SQL*Plus. Again, try to link your entries together in logical progression so that all entries can be attributed to each other.
• ADDRESS=(PROTOCOL=TCP)(HOST=oracle_host)(PORT=1521) This is the entry for your Oracle server.
• CONNECT_DATA=(SID=hsconnect) This must match the entry created as SID_NAME within listener.ora.
• HS=OK Any tnsnames entry that is connecting to a SID that runs HSODBC must have this entry.
Once added save the file.

3.4 Start/Restart the listener
-----------------------------

Once all the entries have been added to the above files, you will need to restart the listener so that the entries that you have made with the listener.ora are used. Note You may not have been running the Oracle listener service previously but you need it to use hsODBC. To do this:
cd $ORACLE_HOME/bin

./lsnrctl stop
./lsnrctl start
When the listener is restarted, you may get information messages similar to:
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this
service...
Service "hsconnect" has 1 instance(s).
Instance "hsconnect", status UNKNOWN, has 1 handler(s) for this
service...

The status unknown is returned by hsodbc services, as the service is not created until used.
You can also run lsnrctl and enter services to get a list and the status of the services.
After adding the tnsnames alias and restarting the listener, a connectivity check is to use tnsping
# tnsping hsconnectid
TNS Ping Utility for Solaris: Version 10.1.0.2.0 -
Production on 27-OCT-2004 14:43:57

Copyright (c) 1997, 2003, Oracle. All rights reserved.

Used parameter files:
/space/oracle/OraHome10db/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)
(HOST = hermod)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED)
(SERVICE_NAME = hsconnectid)))
OK (10 msec)
which should come back with a successful message (as shown above).Once this has been performed, a database instance can be created and connection attempted.

===============================
4.0 Create a Database Link
===============================

Connect to SQL*Plus as if connecting to your regular database. Once the prompt is loaded, you need to create a new database link as follows:
CREATE PUBLIC DATABASE LINK hslink CONNECT TO "dbuser"
IDENTIFIED BY "dbpassword" using 'hsconnectid';
In the above command, hslink is the name of the oracle database link that is created using the hsconnect oracle entry within the tnsnames.ora. Note that when creating the database link, the database user and password are entered. These are not the operating system user and passwords. Also if the 'hsconnectid' is not within single quotes, errors are returned:

ERROR at line 1:
ORA-02010: missing host connect string
Once the database link has been created, try passing in an SQL statement:

Select * from dual@hslink;

where "dual" is a table in your "hslink" linked database.
When passing in SQL to the newly created instance, the syntax is always:
table@linkname
You can even join tables in ODBC to those in your Oracle database:
select * from table@hslink,dual

=========================
5.0 Known Problems
=======================

5.1 unixODBC on AIX
-------------------------

When you configure and build unixODBC for AIX, you end up with the driver manager shared object inserted into libodbc.a as libodbc.so.1. i.e.
$ ar -tv libodbc.a
rwxr-xr-x 201/1 636475 Aug 20 09:11 2004 libodbc.so.1
libtool creates the driver manager like this on AIX.
Unfortunately, some applications (e.g. sqlplus) are built and linked against libodbc.so (no version). You can rectify this as follows:
1. cd to the directory where unixODBC's libraries are installed e.g. /usr/local/easysoft/unixODBC/lib.
2. extract the shared object from the archive with:
3. ar -xv libodbc.a
x - libodbc.so.1
4. Rename libodbc.so.1 to libodbc.so.
mv libodbc.so.1 libodbc.so
5. Point HSODBC directly at the new shared object by amending the HS_FDS_SHAREABLE_NAME in your init[name].ora file to be:
HS_FDS_SHAREABLE_NAME = /usr/local/easysoft/unixODBC/lib/libodbc.so

6. Restart your Oracle listener. Your link should now work.

5.2 No rows returned when connecting Oracle hsODBC to MS SQL Server
We have discovered that some versions of hsODBC do not seem to recognise SQL_WCHAR, SQL_WVARCHAR and SQL_WLONGVARCHAR columns. If a table contains these column types, hsODBC will just error producing no rows. A workaround for this is to add "DisguiseWide=1" to the DSN in your odbc.ini file.
5.3 Invalid user and or password specified
If you get an error similar to:
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this
message:

[Generic Connectivity Using ODBC]DRV_InitTdp: errors.h (2052): ;
[unixODBC][][unixODBC][Easysoft SQI-ISAM Driver]
Invalid user and or password specified.
(SQL State: 28000; SQL Code: 702)
ORA-02063: preceding 2 lines from MYLINK
or basically any error indicating the database username and password is incorrect, you have probably specified them incorrectly when creating the database link above. Although you can view links in the dba_db_links table you cannot see the username and password.
If you set HS_FDS_TRACE_LEVEL to 4, set HS_FDS_TRACE_FILE_NAME to a file in you init*.ora file and try again, you will get a trace output file. If you examine the trace file and locate the SQLDriverConnect call you can see the username and password being used. If this verifies the database username and/or password is incorrect then you need to delete your link and recreate it or change the HS_FDS_CONNECT_INFO in your init*.ora file to specify the UID and PWD (see above). You can delete a link using the syntax:
drop public database link MYLINK

How to connect Oracle to SQL Server

Wednesday, October 14, 2009

ID Auto generation with SYS_GUID()

CREATE TABLE test_halim (ID RAW(16) DEFAULT SYS_GUID(), NAME VARCHAR2(10))

INSERT INTO test_halim(NAME) VALUES ('HALIM' );

INSERT INTO test_halim(NAME) VALUES ('HALIM1' );

INSERT INTO test_halim(NAME) VALUES ('HALIM2' );

COMMIT;

SELECT * FROM test_halim;

Difference Between Truncate and Delete

Difference Between Truncate and Delete
-------------------------------------------

1. “Truncate is fast, delete is slow”.
2. “Truncate is DDL, delete is DML”.
3. “You can’t rollback a truncate”.
4. “Truncate is implicitly a commit”. someone once mentioned that there are
actually two commits in a truncate, one before and one .
5. “You can’t grant permission to truncate a table”.
6. “You can delete any subset of rows, but you can only truncate the complete
table, or a partition or sub partition of it
7. “Truncate makes unusable indexes usable again”. If you attempt to optimize
a data load by rendering indexes unusable and truncating a table (possibly
followed by an index rebuild and a partition exchange) then be careful of
the order.
8. “Truncate can’t maintain foreign keys”. It’s “cascading delete”,
not “cascading truncate”. That would be an interesting feature though .
Truncating an index cluster is pretty close to a “cascading truncate” to a
limited extent though. In any case no truncate is permitted on a table
referenced by foreign keys.
9. “You can’t flashback a truncate”. This is an oddity to me. We can flashback
a “drop table”, rollback uncommited deletes, or use flashback to recover
pre-commit deleted data, but a truncate is a barrier across which we cannot
flashback.
10. “Truncate deallocates space, delete doesn’t”. Unless you want it not to,
using the “reuse storage” clause. However the high water mark is reset in
either case so maybe that’s a better answer …
11. “Truncate resets the high water mark, delete doesn’t”. And on the indexes,
12. “Truncate allows special treatment of materialized view logs”.
13. “DML triggers do not fire on a truncate”. Because it’s DDL not DML.

explain plan in sqlplus

column PLAN_TABLE_OUTPUT format a90 truncate

set autotrace traceonly explain

with data
as
(select count(*) cnt from t )
select t.*, data.cnt from t, data

with data
as
(select /*+ MATERIALIZE */ count(*) cnt from t )
select t.*, data.cnt from t, data

Monday, October 12, 2009

send mail Procedure

set serveroutput on size 100000

CREATE OR REPLACE PROCEDURE send_mail_halim (
msg_from VARCHAR2 := 'oracle',
msg_to VARCHAR2,
msg_subject VARCHAR2 := 'E-Mail message from your database',
msg_text VARCHAR2 := ''
) IS
c UTL_TCP.connection;
rc INTEGER;
BEGIN ----SMTP server running and port must be correct
c := UTL_TCP.open_connection ('10.11.1.40', 25); -- open the SMTP port 25 on local machine
DBMS_OUTPUT.put_line (UTL_TCP.get_line (c, TRUE));
rc := UTL_TCP.write_line (c, 'HELO localhost');
DBMS_OUTPUT.put_line (UTL_TCP.get_line (c, TRUE));
rc := UTL_TCP.write_line (c, 'MAIL FROM: ' || msg_from);
DBMS_OUTPUT.put_line (UTL_TCP.get_line (c, TRUE));
rc := UTL_TCP.write_line (c, 'RCPT TO: ' || msg_to);
DBMS_OUTPUT.put_line (UTL_TCP.get_line (c, TRUE));
rc := UTL_TCP.write_line (c, 'DATA'); -- Start message body
DBMS_OUTPUT.put_line (UTL_TCP.get_line (c, TRUE));
rc := UTL_TCP.write_line (c, 'Subject: ' || msg_subject);
rc := UTL_TCP.write_line (c, '');
rc := UTL_TCP.write_line (c, msg_text);
rc := UTL_TCP.write_line (c, '.'); -- End of message body
DBMS_OUTPUT.put_line (UTL_TCP.get_line (c, TRUE));
rc := UTL_TCP.write_line (c, 'QUIT');
DBMS_OUTPUT.put_line (UTL_TCP.get_line (c, TRUE));
UTL_TCP.close_connection (c); -- Close the connection
EXCEPTION
WHEN OTHERS THEN
raise_application_error
(-20000,
'Unable to send e-mail message from pl/sql because of: '
|| SQLERRM
);
END;

execute SEND_MAIL_halim(msg_to =>'halim@bank.com.bd');

=================================================================================

UTL_SMTP and sending mail

UTL_SMTP, introduced for the first time in Oracle 8.1.6, is an nterface to the Simple Mail Transfer Protocol. It requires that you have an SMTP server in your network somewhere ? most sites I have been to have at least one SMTP server running as it is the most popular method for sending mail.
The UTL_SMTP package is best suited for sending small, text only emails from the database. While its API supports the sending of attachments and everything else ? it is left to you to actually encode the multi-part document ? for example turning binary attachments into mime-encoded documents.

Use UTL_SMTP that provides somewhat much more functionality ? including the ability to easily send attachments with the email. Since SMTP is a very low level protocol, we'll reuse existing public domain code to get an SMTP interface at much higher level ? and we'll get it with very little code.

UTL_SMTP ?

In the DBMS_JOB section, we explored how to make sending email using UTL_SMTP appear to execute faster. We also made email transactional in nature in that section ? if you rollback the email does not get sent, if you commit ? out it goes. I highly recommend the use of DBMS_JOB as a layer on your emails routines for these reasons. In that section, the example UTL_SMTP routine we used was:

create or replace
2 PROCEDURE send_mail (p_sender IN VARCHAR2,
3 p_recipient IN VARCHAR2,
4 p_message IN VARCHAR2)
5 as
6 l_mailhost VARCHAR2(255) := 'boss.oracle.com';
7 l_mail_conn utl_smtp.connection;
8 BEGIN
9 l_mail_conn := utl_smtp.open_connection(l_mailhost, 25);
10 utl_smtp.helo(l_mail_conn, l_mailhost);
11 utl_smtp.mail(l_mail_conn, p_sender);
12 utl_smtp.rcpt(l_mail_conn, p_recipient);
13 utl_smtp.open_data(l_mail_conn );
14 utl_smtp.write_data(l_mail_conn, p_message);
15 utl_smtp.close_data(l_mail_conn );
16 utl_smtp.quit(l_mail_conn);
17 end;
18 /
Procedure created.

begin
2 send_mail( 'halim@bank.com.bd',
3 'halim@yahoo.com',
4 'Hello' );
5 end;
6 /

PL/SQL procedure successfully completed.

That works OK but is very limited in nature. It sends email to exactly one recipient, you cannot CC or BCC anyone, you cannot setup a subject -- the email always arrives with a ?blank? subject line. We would like to support more options with this package.

A specification for a PLSQL package that supports this might look like the following. In here, we define an array type to allow for a caller to easily send a list of recipients as well as provide the external specification of the PLSQL routine we will be implementing:

create or replace package mail_pkg
2 as
3 type array is table of varchar2(255);
4
5 procedure send( p_sender_email in varchar2,
6 p_from in varchar2,
7 p_to in array default array(),
8 p_cc in array default array(),
9 p_bcc in array default array(),
10 p_subject in varchar2,
11 p_body in long );
12 end;
13 /
Package created.

The package body for this implementation is relatively straightforward ? if understand just enough of the SMTP protocol and what an email looks like (how email clients get the From, To, CC and so on). Before we look at the code, we?ll look at what an email might actually look like. Consider the following ASCII text:

use of exists and not exists in sql

SQL> conn scott/scott
Connected.
SQL> set timing on
SQL> SELECT * FROM ( SELECT DEPTNO FROM DEPT) D;

DEPTNO
----------
10
20
30
40

Elapsed: 00:00:00.03

SQL>
1 SELECT * FROM ( SELECT DEPTNO FROM DEPT) D
2 WHERE EXISTS ( SELECT NULL
3 FROM EMP E
4* WHERE E.DEPTNO = D.DEPTNO)
5 /

DEPTNO
----------
10
20
30

Elapsed: 00:00:00.00
SQL>

SQL>
1 SELECT * FROM ( SELECT DEPTNO FROM DEPT) D
2 WHERE NOT EXISTS ( SELECT NULL
3 FROM EMP E
4* WHERE E.DEPTNO = D.DEPTNO)
5 /

DEPTNO
----------
40

Elapsed: 00:00:00.03
SQL>

use of with clause in sql

we can use with clause in a query to faster a query (replace of sub-query)
because it retrive subquery value once.

when a subquery value need to retrive for using in whole sql query.

===========================================================================

WITH q AS
(SELECT dummy
FROM DUAL)
SELECT dummy
FROM q

WITH NAME AS
(SELECT *
FROM emp
WHERE sal > 2000)
SELECT *
FROM NAME
WHERE ename LIKE 'S%'

WITH NAME AS
(SELECT empnam, b.empidn
FROM stempgen a, stfacmas b
WHERE a.empidn = b.empidn)
SELECT empnam
FROM NAME
WHERE empnam LIKE 'A%'

odd and even row number

select * from emp where (rowid,1) in (select rowid,mod(rownum,2) from emp )

select * from emp where (rowid,0) in (select rowid,mod(rownum,2) from emp )

get IP address in oracle

1. get ip address from database User basis

SELECT sid, machine,utl_inaddr.get_host_address(SUBSTR(machine,INSTR(machine,'\')+)) ip
FROM gv$session
WHERE type = 'USER'
AND username IS NOT NULL;

2. give Host name, get IP address
=================================

SELECT utl_inaddr.get_host_address('ADMINISTRATOR')
FROM dual;

3. give ip, get host_name address
=================================

SELECT utl_inaddr.get_host_name('10.11.1.105')

Random value generate by query

1.

select QUOTMESG
-- into vstr
from SYQUTMSG sample(25)
where rownum =1;

2.

SELECT empno
FROM emp
ORDER BY DBMS_RANDOM.VALUE

Queries to identify/remove duplicate rows from a table

queries to identify or remove duplicate rows from a table
==========================================================
1.
DELETE FROM table_name A WHERE ROWID > (
SELECT min(rowid) FROM table_name B
WHERE A.key_values = B.key_values);

[hints:- Delete all rowids that is BIGGER than the SMALLEST rowid value
(for a given key)]

2. delete from my_table t1
where exists (select 'x' from my_table t2
where t2.key_value1 = t1.key_value1
and t2.key_value2 = t1.key_value2
and t2.rowid > t1.rowid);

3. delete from where rowid not in
( select min(rowid)
from exp group by column1..,column2,...column3..);

4. Column value duplicate
=========================
DELETE scott.emp
WHERE rowid IN
( SELECT LEAD(rowid) OVER
(PARTITION BY ename ORDER BY NULL)
FROM scott.emp );

5. This statement deletes rows from the emp table where duplicate values of
last_name appear.
======================================================

delete from emp e
where empno in(select empno from emp d
where d.last_name=e.last_name
minus
select empno from emp f
where f.last_name=e.last_name
and rownum=1)

How much a Database user use space

How much a Database user use space ?
solution:

---in Mega bytes

SELECT SUM (BYTES) / 1024 / 1024 mb
FROM dba_segments
WHERE owner = 'SCOTT';

ORA-00942: table or view does not exist

ORA-00942: table or view does not exist

SOLUTION:

1. Your table/view not correct . check it.
2. You have no privilege on this table or view.

Update a table from Form's Check Box wise

BEGIN
GO_BLOCK ('data_block_1');
FIRST_RECORD;
LOOP
UPDATE stscinfd
SET precls = :crq2.s_present
WHERE precls = :crq2.s_previous
and studid=:data1.studid
and nvl(:data1.s_fail,'Y') <> 'N';
IF SQL%NOTFOUND THEN
null;
END IF;

EXIT WHEN :SYSTEM.LAST_RECORD = 'TRUE';

NEXT_RECORD;
END LOOP;
COMMIT;
clear_form;
END;

Sunday, October 11, 2009

Log DDL (drop and truncate) On the oracle database

By this steps, you logged all DDL attempt to your database or specific schema . you can prevent these also.

1. This is a log table
=======================

CREATE TABLE DDL_ATTEMP_LOG
(
OPERATION VARCHAR2(30 BYTE),
OBJ_OWNER VARCHAR2(30 BYTE),
OBJECT_NAME VARCHAR2(30 BYTE),
SQL_TEXT VARCHAR2(64 BYTE),
ATTEMPT_BY VARCHAR2(30 BYTE),
ATTEMPT_DT DATE,
IP_ADD VARCHAR2(200 BYTE),
OS_USER VARCHAR2(200 BYTE),
TER_NAME VARCHAR2(200 BYTE),
HOST_NAME VARCHAR2(200 BYTE)
)

=============================================================================
2. This procedure insert all drop/truncate ddl attempt to DDL_ATTEMP_LOG table
(when save_our_db_FROM_DDL trigger is fired)
=============================================================================

CREATE PROCEDURE ddl_attemp_log_proc (
ose ddl_attemp_log.operation%TYPE,
odoo ddl_attemp_log.obj_owner%TYPE,
odon ddl_attemp_log.object_name%TYPE
)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO ddl_attemp_log
SELECT ora_sysevent, ora_dict_obj_owner, ora_dict_obj_name,
'Not Allowed', USER, SYSDATE,SYS_CONTEXT ('userenv', 'ip_address')
ip_add,sys_context('USERENV','OS_USER') os_user
,sys_context('USERENV', 'TERMINAL') ter_name,sys_context('USERENV',
'HOST') host_name
FROM DUAL;
if sql%found then

COMMIT;
else
rollback ;
end if ;
exception
when others then
null ;
END ddl_attemp_log_proc;

========================================================
3. This triger is fired when Drop/truncate ddl happen in
any objects of the Database.
========================================================

CREATE OR REPLACE TRIGGER save_our_db_FROM_DDL
BEFORE DROP OR TRUNCATE
or ALTER OR RENAME OR GRANT OR REVOKE OR AUDIT OR COMMENT OR CREATE OR ANALYZE
ON database ---on schema
DECLARE
oper ddl_attemp_log.operation%TYPE;
BEGIN

begin
SELECT ora_sysevent
INTO oper
FROM DUAL;
exception
when others then
null ;
end ;

if nvl(ora_dict_obj_name,'ZZZ') in ('STGLOBTM') then---global temporary table
null ;
else
ddl_attemp_log_proc(ora_sysevent, ora_dict_obj_owner, ora_dict_obj_name);
end if ;
/*
IF oper = 'DROP'
THEN
raise_application_error (-20998, 'Attempt To Drop In Production DB Has Been
Restricted contract with DBA'
);
END IF;
*/
----ALTER TRIGGER LOGG.SAVE_OUR_DB_FROM_DDL DISABLE;
END save_our_db_FROM_DDL;
/

4) Query your log history table.
====================================

SELECT * FROM DDL_ATTEMP_LOG;

oracle SQL Tuning Script

oracle SQL Tuning Script

======================================
1) use this script for seeing
How many days the database is UP ?
======================================

SET linesize 999
SET pagesize 50000
SET feedback 1
SET trimspool on
SET trimout on

SELECT instance_name, ROUND ((SYSDATE - startup_time), 1) up_days
FROM v$instance
/

======================================================================
2. use this script to see the distribution of code based on cpu cost.
here "sql_statements" means: how many sql consumed the CPU% ?
and "PCT_TOTAL" means:- how many percentise of CPU use?
======================================================================

COL sql_text format a700 trunc
COL pct_total format 990
COMPUTE sum of sql_statements on report
BREAK on report

SELECT cpu_time_log10, sql_statements, cpu_time_rounded,
ROUND (cpu_time) cpu_time,
100 * ratio_to_report (cpu_time) OVER () pct_total,
ROUND (SUM (cpu_time) OVER (ORDER BY cpu_time_log10))
running_cpu_time
FROM (SELECT TRUNC (LOG (10,
((CASE
WHEN cpu_time <= 0 THEN 1
ELSE cpu_time
END)
)
/ 1000000
)
) cpu_time_log10,
COUNT (*) sql_statements,
POWER
(10,
TRUNC (LOG (10,
((CASE
WHEN cpu_time <= 0 THEN 1
ELSE cpu_time
END
)
)
/ 1000000
)
)
) cpu_time_rounded,
SUM ((CASE
WHEN cpu_time <= 0 THEN 1
ELSE cpu_time
END) / 1000000
) cpu_time
FROM v$sqlarea
WHERE UPPER (sql_text) NOT LIKE 'BEGIN%'
AND UPPER (sql_text) NOT LIKE 'DECLARE%'
GROUP BY TRUNC (LOG (10,
((CASE
WHEN cpu_time <= 0 THEN 1
ELSE cpu_time
END)
)
/ 1000000
)
)) a
ORDER BY a.cpu_time_log10
/

=========================================================
3. here, what are the most CPU consumed SQL (in details)
==========================================================

COMPUTE sum of cpu_seconds on report
BREAK on report

SELECT TRUNC (cpu_time / 1000000) cpu_seconds,
TRUNC (elapsed_time / 1000000) eplapsed_seconds,
ROUND ( (SYSDATE
- TO_DATE (first_load_time, 'rrrr-mm-dd/hh24:mi:ss')
)
* 24
) hours_in_cache,
executions, sql_text
FROM v$sqlarea
WHERE UPPER (sql_text) NOT LIKE 'BEGIN%'
AND UPPER (sql_text) NOT LIKE 'DECLARE%'
AND cpu_time / 1000000 > 100
ORDER BY 1, 2
/

Saturday, October 17, 2009

Thursday, October 15, 2009

Wednesday, October 14, 2009

Monday, October 12, 2009

Sunday, October 11, 2009

My Blog List