Learn with Halim

Halim, a Georgia Tech graduate Senior Database Engineer/Data Architect based in Atlanta, USA, is an Oracle OCP DBA and Developer, Certified Cloud Architect Professional, and OCI Autonomous Database Specialist. With extensive expertise in database design, configuration, tuning, capacity planning, RAC, DG, scripting, Python, APEX, and PL/SQL, he combines technical mastery with a passion for innovation. Notably, Halim secured 16th place worldwide in PL/SQL Challenge Cup Playoff on the year 2010.

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.