QUERY parameter in Export Utility

This parameter is used in conjunction with TABLE parameter of exp (export) utility of Oracle. This parameter will enable selection of rows from the list of tables mentioned in TABLE parameter. The value to this parameter is a WHERE clause for a SELECT statement which you would normally issue.

For example if you want to query all records of employees for a particular department you will use:

SELECT *
FROM employees
WHERE dept = 10;

To export these rows into a file using exp utility you will follow the below syntax:

exp scott/tiger TABLES=employees QUERY=\"WHERE dept=10\"

Use \ for providing character or special characters like less than or greater than symbol inside the string. Also for operating system keywords you need to place \ as escape character.

For example:

exp scott/tiger TABLES=employees QUERY=\"WHERE name=\ANANTHA\' and sal \<1600\" The default value for QUERY parameter is NULL. Note: If there are multiple tables in TABLES parameter, this QUERY will be applied to all those tables.

You can also use ROWID for exporting, for example:

exp scott/tiger@slspnc1 tables=emp query=\"where ROWID='AAAMgzAAEAAAAAgAAB'\" file=test.dmp log=test.log

In Windows it is not required to put escape character for single quote character wheras you need to put escape character if you are using Linux or its variants.

exp scott/tiger@slspnc1 tables=emp query=\"where ROWID=\'AAAMgzAAEAAAAAgAAB\'\" file=test.dmp log=test.log

Restrictions When Using the QUERY Parameter:
The QUERY parameter cannot be specified for full, user, or tablespace-mode exports.
The QUERY parameter must be applicable to all specified tables.
The QUERY parameter cannot be specified in a direct path Export (DIRECT=y)
The QUERY parameter cannot be specified for tables with inner nested tables.
You cannot determine from the contents of the export file whether the data is the result of a QUERY export.

Installing Oracle silently

Oracle Universal Installer by default installs any oracle products in its GUI mode. In situations where the GUI could not be started, it is not possible to install Oracle, such as in Linux environments if X Server could not be started it is difficult to install any Oracle products.

Moreover for a DBA, it is very cumbersome to sit and click buttons for installation. For automating purposes, Oracle Universal Installer (OUI) provides a silent mode of installation.

The runInstaller script which is used for calling the OUI has some switches which can be used to achieve this functionality.

./runInstaller -record -destinationFile  

./runInstaller -silent -responseFile

Here is how:

First Step
./runInstaller -record -destinationFile ResponseFile.txt

The record parameter tells the installer to write to the response file and the destinationFile parameter defines the name of the response file. Once the response file is created you can run the installer in silent mode using the following command:

Second Step

./runInstaller -silent -responseFile ResponseFile.txt

The silent parameter indicates the OUI to install the product using the parameters found in the file passed throught responseFile parameter.

Once the installer stops for executing root.sh as root user, the installation will be completed.

Oracle XE-Data Uploading from CSV file

This is a step-by-step guide on the simplest of the simplest ways by which data can be uploaded from CSV file to Oracle table. I have done uploading using this way for as many as 10,000 records without any performance issues. It went very swiftly.

Login to OracleXE first and follow the instructions below:

Step 1 - Click Utilities

Step 2 - Click Data Load/Unload

Step 3 - Click Load

Step 4

Step 5 - Click Load Spreadsheet Data

Step 6 - Follow the screen

Step 7 - Follow the screen

Step 8 - Follow the screen

Step 9 - Follow the screen

Step 10 - Follow the screen

How to rename a table in Oracle?

There are two ways of renaming a table in Oracle:

Method 1: Simple

rename {old_table_name} to {new_table_name}

Example:

rename CUSTOMER to CUSTOMER_BACKUP

Method 2: Not so Complex

alter table {old_table_name} rename to {new_table_name};

Example:

alter table CUSTOMER rename to CUSTOMER_BACKUP;

The minimum version that supports table renaming is Oracle 8i. All the dependencies of the table will automatically updated. No need of updating them after wards.

How to enter a single quotation mark in Oracle

Q: How to enter a single quotation mark in Oracle?

Ans: Although this may be a undervalued question, I got many a search for my blog with this question. This is where I wanted to address this question elaborately or rather in multiple ways.

Method 1
The most simple and most used way is to use a single quotation mark with two single quotation marks in both sides.

SELECT 'test single quote''' from dual;

The output of the above statement would be:
test single quote'

Simply stating you require an additional single quote character to print a single quote character. That is if you put two single quote characters Oracle will print one. The first one acts like an escape character.

This is the simplest way to print single quotation marks in Oracle. But it will get complex when you have to print a set of quotation marks instead of just one. In this situation the following method works fine. But it requires some more typing labour.

Method 2
I like this method personally because it is easy to read and less complex to understand. I append a single quote character either from a variable or use the CHR() function to insert the single quote character.

The same example inside PL/SQL I will use like following:

DECLARE
l_single_quote CHAR(1) := '''';
l_output VARCHAR2(20);
BEGIN
SELECT 'test single quote'||l_single_quote
INTO l_output FROM dual;
DBMS_OUTPUT.PUT_LINE(l_single_quote);
END;

The output above is same as the Method 1.

Now my favourite while in SQL is CHR(39) function. This is what I would have used personally:

SELECT 'test single quote'||CHR(39) FROM dual;

The output is same in all the cases.

Now don't ask me any other methods, when I come to know of any other methods I will share here.

XML Type conversions in Oracle

Oracle suports XML handling through the built in datatype XMLTYPE. Here is an example of handling XML data to convert xml to object types and viceversa.
Consider the following XML


<customer>
<id>100</id>
<name>XEROX</name>
<country>FRANKFURT</country>
<status>ACTIVE</status>
</customer>


Create a sample object type customer which contains the same elements as the XML data using the following command:
CREATE OR REPLACE TYPE CUSTOMER
AS OBJECT
( ID VARCHAR2(10) ,
NAME VARCHAR2(50) ,
COUNTRY VARCHAR2(50),
STATUS VARCHAR2(15))
/

The following pl/sql block will convert the xml data into one XMLTYPE variable and then populates the object variable v_in which is of type customer and displays the object contents.

DECLARE
v_in customer;
v_xml xmltype;
BEGIN
v_xml :=XMLTYPE('<customer></customer>
<id>100</id><name>XEROX</name><country>GERMANY</country>
<status>ACTIVE</status>'); xmltype.toobject(v_xml,v_in);
DBMS_OUTPUT.PUT_LINE('Customer Id ' v_in.id ' Name 'v_in.name);
END;
Output :
Customer Id 100 Name XEROX
PL/SQL procedure successfully completed

And here, for converting the customer object to XML data, use XMLTYPE() function. See the following pl/sql code:

DECLARE
v_in customer;
v_xml xmltype;
BEGIN
v_in:= new customer('100','XEROX','GERMANY','ACTIVE');
v_xml := xmltype(v_in);
DBMS_OUTPUT.PUT_LINE(v_xml.getclobval());
END;
Output :
<customer><id>100</id><name>XEROX</name>
<country>GERMANY</country><status>ACTIVE</status></customer>
PL/SQL procedure successfully completed

Multitable Inserts using INSERT ALL

Multitable inserts allow a single INSERT INTO .. SELECT statement to conditionally, or non-conditionally, insert into multiple tables. This statement reduces table scans and PL/SQL code necessary for performing multiple conditional inserts compared to previous versions. It's main use is for the ETL process in data warehouses where it can be parallelized and/or convert non-relational data into a relational format.
--Unconditional Insert into all tables
INSERT ALL
INTO ap_cust VALUES (customer_id, program_id, delivered_date)
INTO ap_orders VALUES (order_date, program_id)
SELECT program_id, delivered_date, customer_id, order_dateFROM airplanes;
-- Pivoting insert to split non-relational data
INSERT ALL
INTO Sales_info VALUES (employee_id,week_id,sales_MON)
INTO Sales_info VALUES (employee_id,week_id,sales_TUE)
INTO Sales_info VALUES (employee_id,week_id,sales_WED)
INTO Sales_info VALUES (employee_id,week_id,sales_THUR)
INTO Sales_info VALUES (employee_id,week_id, sales_FRI)
SELECT EMPLOYEE_ID, week_id, sales_MON, sales_TUE,
sales_WED, sales_THUR,sales_FRI FROM Sales_source_data;
-- Conditionally insert into ALL tables
INSERT ALL
WHEN SAL>10000 THEN INTO sal_history VALUES(EMPID,HIREDATE,SAL)
WHEN MGR>200 THEN INTO mgr_history VALUES(EMPID,MGR,SYSDATE)
SELECT employee_id EMPID, hire_date HIREDATE, salary SAL, manager_id MGR
FROM employees WHERE employee_id > 200;

-- Insert into the FIRST table with a matching condition
INSERT FIRST
WHEN SAL > 25000 THEN INTO special_sal VALUES(DEPTID,SAL)
WHEN HIREDATE like ('%') THEN INTO hiredate_history_00 VALUES(DEPTID,HIREDATE)
WHEN HIREDATE like ('%99%') THEN INTO hiredate_history_99 VALUES(DEPTID,HIREDATE)
ELSE INTO hiredate_history VALUES(DEPTID, HIREDATE)
SELECT department_id DEPTID, SUM(salary) SAL, MAX(hire_date) HIREDATE
FROM employees GROUP BY department_id;

Deleting/Listing duplicate records without ROWID

I was hunting for an article when my friend came up and asked me this question.

How to list/delete duplicate records without using ROWID?

I never asked why he do not want to use ROWID, because I was caught in some thinking.

I done some googling, and derived the method, but first the ROWID method

First how to delete records with ROWID, and why?

ROWID is the unique record identifier within Oracle, and it is easy to get the minimum of the ROWID for the duplicating criteria and delete the rest of them.

We will take the example of EMP table for simplicity. Assume that there is no primary key in the Employee_Id column due to some data migration requirements. Once the data migration is over I need to check the existense of duplicate records. This is how traditionally we used to achieve this:

Suppose the data after migration stands like this:
Employee_Id Employee_Name
10          Scott
11          Tiger
10          Amanda
10          Nagpal
20          Rawat

It is clear from this data that there are duplicate records, but in a normal data migration there will be millions of records from which identifying the duplicate records will be a nightmare. This is where the following query will save our day:

SELECT Employee_Id, Employee_Name
FROM   Employee e
WHERE  ROWID NOT IN (SELECT MIN(ROWID)
                     FROM   Employee f
                     WHERE  e.Employee_Id = f.Employee_Id);

The above query will list the following duplicate records:
Employee_Id Employee_Name
10          Amanda
10          Nagpal

We will not get into the decision of what to do with the duplicate records as it is not our discussion topic.

Now what does the MIN(ROWID) implies in the query? It implies that the first record inserted cannot be a duplicate record, and the rest of the data are. Simple isn't it?

Now how to select the same data without ROWID?

Consider the following select statement:

SELECT Employee_Id, Employee_Name,
  decode(row_number() over( PARTITION BY employee_id ORDER BY employee_id),1,'Not duplicate','Dupilcate') Dupicate_Criteria
FROM employee;

The following is the data from the query:

EMPLOYEE_ID    EMPLOYEE_NAME   DUPICATE_CRITERIA
10             Scott           Not duplicate
10             Amanda          Dupilcate
10             Nagpal          Dupilcate
11             Tiger           Not duplicate
20             Rawat           Not duplicate

In this, you can see the usage of row_number() function with over() clause. In the over() clause we are partitioning the data on basis of the duplicate criteria, that is the Employee_Id. The Order By clause is mandatory so we will use the same Employee_Id. Now the row_number() function assigns running numbers for each Employee_Id. If there are any duplicate records, the row_number() function will return any value greater than 1 (in the running order).

So it is very easy for us to identify the usage of row_number() function and to find an alternative method of getting duplicate rows without using ROWID in our query. Now it is in your hands to do more research and find out more usages.

Thanks for Subesh Kumar who prompted me for a short research in this topic, and hence this article.

Oracle Index and Like clause

Topic: Beginners Level

From time immemorial there has been debate over the usage of like clause and its association (or rather non-association with index). What is the fuzz all about? Let's check here.

Like clause allows you to use wildcard character searches over data stored in oracle database. By this means we can do pattern searching over the existing data.

For example:
You have a daily meeting calendar in which the attendees names are stored in comma seperated values in a VARCHAR2(2000) field. You want to search on what days a particular attendee say for instance Rose has attended the meeting.

table: meeting_schedule
fields:
meeting_date date
meeting_place varchar2(200)
meeting_attendees varchar2(2000)

In such a case of searching, without the usage of wildcard characters such as % will not yeild appropriate results.

The query for such a situation would be:

SELECT meeting_date, meeting_place
FROM meeting_schedule
WHERE meeting_attendees like '%Rose%';

Now the above query will list the meeting_date and meeting_place where Rose has attended. Of course this is a simple usage of LIKE clause.

The Like clause is valid to be used in any DML statement such as SELECT, INSERT, UPDATE and DELETE.

Now what is that links LIKE clause so much with the indexes in Oracle?
Indexes as you may know are like indexes in the book where it points to a page in the book. The same way Oracle indexes point to a data location. Both Indexes and Tables contain two different copies of data, but Indexes contain far less data compared to the tables. Think of Indexes as containing distinct values of a row in a table. So if there are more and more repetitive rows in a row, then accessing Index will prove to be lot faster than accessing the table.

So by this you must have understood where an Index would help. An Index should be used only in such cases, so that it can improve performance. If otherwise used in all places it will degrade the performance.

How can an Index degrade the performance?
By using an index side-by-side of a table, we are agreeing that it has some extra work to do. Like at every insert, update or delete to the table, the index also needs to be updated. This extra work can prove fatal if there are lot of rows in the index.

So as a simple way to know where to use index, if each row of a table is different from that of other row and there are no distinct values do not use indexes. Only use indexes if atleast there are 25% distinct rows available in the table.

Of course there is no such hard core rule as to when to use Index and when not to use. It just depends upon the situation.

Automatically Calculating Percentages in Queries

Starting with Release 7.1 of Oracle, users have had access to a feature called an inline view. An inline view is a view within a query. Using this feature, you can easily accomplish your task.

Example: Show percentage of salaries for each department

Every row in the report must have access to the total sum of sal. You can simply divide sum (sal) by that total, and you'll have a number that represents the percentage of the total.

column percentage format 99.9

select deptno, sum(sal),sum(sal)/tot_sal*100 "PERCENTAGE"
from emp, (select sum(sal) tot_sal from emp)
group by deptno, tot_sal;

With Oracle8i Release 2 (8.1.6 and higher), you can calculate percentages by using the new analytic functions as well. The query using an analytic function might look like this:

column percentage format 99.9

select deptno, sum(sal), (ratio_to_report(sum(sal)) over())*100 "PERCENTAGE"
from emp
group by deptno;

The query produces the same answer—but it does so more efficiently, because it does not have to make two passes over the data to arrive at the answer. Because the analytic functions are built-in, queries that use them will find the answer more rapidly than the "pure" SQL-based approach.

Source: www dot akadia dot com

Read a file word by word using DBMS_LOB

Oracle offers several possibilities to process file from within PL/SQL. The most used package is UTL_FILE, but with the disadvantage that the read-buffer is limited to 1023 bytes. If you want to read huge chunks of files you can use the DBMS_LOB package, even for the processing of plain ASCII files.

There are two solutions to read a file with DBMS_LOB

• The file is treaded as a large binary object LOB. The whole file is read and saved in a table column of the data type LOB and then processed.

• The file is read and processed directly from the filesystem location. This Tip shows exactly this case.

Example:

Suppose we want to read a big file word by word directly from PL/SQL without saving the whole file in a table column. The words in the file are separated with a blank. For simplicity we assume, that there is exactly one blank between the words and the file is a stream with a newline at the end of the file.

First we have to create an ORACLE directory as the schema owner. Do not add a trailing "/" at the end of the directory path.

sqlplus scott/tiger
SQL> create directory READ_LOB_DIR as 'C:\Users\Zahn\Work';

Next we create the procedure READ_FILE_LOB, which reads the file word by word.

CREATE OR REPLACE
Procedure READ_FILE_LOB IS
----------------------------------------------------------------
-- Read an ASCII file word by word with DBMS_LOB package.
--
-- Before you can use this procedure create an ORACLE directory
-- object as the owner of this peocedure.
--
-- sqlplus scott/tiger
-- SQL> create directory READ_LOB_DIR as 'C:\Users\Zahn\Work';
--
-- Do not add a trailing "/" at the end of the directory path.
--
----------------------------------------------------------------

-- Input Directory as specified in create directory
l_dir       CONSTANT VARCHAR2(30) := 'READ_LOB_DIR';

-- Input File which is read word by word
l_fil       CONSTANT VARCHAR2(30) := 'testfile.txt';

-- Separator Character between words is a BLANK (ascii = 32)
l_seb       CONSTANT RAW(100) := UTL_RAW.CAST_TO_RAW(CHR(32));

-- Character at the end of the file is NEWLINE (ascii = 10)
l_sen       CONSTANT RAW(100) := UTL_RAW.CAST_TO_RAW(CHR(10));

-- Pointer to the BFILE
l_loc       BFILE;

-- Current position in the file (file begins at position 1)
l_pos       NUMBER := 1;

-- Amount of characters have been read
l_sum       BINARY_INTEGER := 0;

-- Read Buffer
l_buf       VARCHAR2(500);

-- End of the current word which will be read
l_end       NUMBER;

-- Return value
l_ret       BOOLEAN := FALSE;

BEGIN

    -- Mapping the physical file with the pointer to the BFILE
    l_loc := BFILENAME(l_dir,l_fil);

    -- Check if the file exists
    l_ret := DBMS_LOB.FILEEXISTS(l_loc) = 1;
    IF (l_ret) THEN
       dbms_output.put_line('File ' ||
       l_fil || ' in Directory ' || l_dir || ' exists');

       -- Open the file in READ_ONLY mode
       DBMS_LOB.OPEN(l_loc,DBMS_LOB.LOB_READONLY);
       LOOP

          -- Calculate the end of the current word
          l_end := DBMS_LOB.INSTR(l_loc,l_seb,l_pos,1);

          -- Process end-of-file
          IF (l_end = 0) THEN
            l_end := DBMS_LOB.INSTR(l_loc,l_sen,l_pos,1);
            l_sum := l_end - l_pos - 1;
            DBMS_LOB.READ(l_loc,l_sum,l_pos,l_buf);
            dbms_output.put_line(UTL_RAW.CAST_TO_VARCHAR2(l_buf));
            EXIT;
          END IF;

          -- Read until end-of-file
          l_sum := l_end - l_pos;
          DBMS_LOB.READ(l_loc,l_sum,l_pos,l_buf);
          dbms_output.put_line(UTL_RAW.CAST_TO_VARCHAR2(l_buf));
          l_pos := l_pos + l_sum + 1;
       END LOOP;
       DBMS_LOB.CLOSE(l_loc);
    ELSE
       dbms_output.put_line('File ' ||
       l_fil || ' in Directory ' || l_dir || ' does not exist');
    END IF;
EXCEPTION
    WHEN OTHERS THEN
        dbms_output.put_line('Error:' || SQLERRM);
        DBMS_LOB.CLOSE(l_loc);
END;
/

The file testfile.txt has the following content

martin zahn seftigen

Output of the procedure

sqlplus scott/tiger
SQL> exec read_file_lob;
File testfile.txt in Directory READ_LOB_DIR exists
martin
zahn
seftigen

PL/SQL procedure successfully completed.

Article Source: akadia dot com

How to create view without underlying table

This tip will enable to create a view even if you do not have an underlying table already present in your database.

 

In this article you will learn to

• Create view without a table
• Creating a table for that view
• How to make the view to work

 

Consider the following example:-

 

CREATE OR REPLACE FORCE VIEW force_view AS SELECT * FROM force_table;

 

Now check whether the view is created or not:

 

SELECT object_name, object_type, status, temporary, generated, secondary FROM user_objects WHERE object_name='FORCE_VIEW';

 

OBJECT_NAME OBJECT_TYPE STATUS      TEMPORARY   GENERATED   SECONDARY

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

FORCE_VIEW  VIEW        INVALID     N           N           N

 

So this ensures that the view is created, but what about the status? It says the view created is invalid.

 

Now we will try to execute the view:

 

SELECT * FROM force_view;

 

Error starting at line 1 in command:

select * from force_view

Error at Command Line:1 Column:14

Error report:

SQL Error: ORA-04063: view "RND.FORCE_VIEW" has errors

04063. 00000 -  "%s has errors"

*Cause:    Attempt to execute a stored procedure or use a view that has

           errors.  For stored procedures, the problem could be syntax errors

           or references to other, non-existent procedures.  For views,

           the problem could be a reference in the view's defining query to

           a non-existent table.

           Can also be a table which has references to non-existent or

           inaccessible types.

*Action:   Fix the errors and/or create referenced objects as necessary

 

This is the error which I get when running the view. So now you will understand that we will be just able to create the view, but it is useless until it has an underlying table attached to it.

 

Now we will create table for this view:

 

CREATE TABLE force_table (a NUMBER, b VARCHAR2(10));

 

Now will check again the status for the view.

 

SELECT object_name, object_type, status, temporary, generated, secondary FROM user_objects WHERE object_name='FORCE_VIEW';

 

OBJECT_NAME OBJECT_TYPE STATUS      TEMPORARY   GENERATED   SECONDARY

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

FORCE_VIEW  VIEW        INVALID     N           N           N

 

The status is still INVALID. Now we need to re-compile the view to make it VALID.

 

ALTER VIEW force_view COMPILE;

 

OBJECT_NAME OBJECT_TYPE STATUS      TEMPORARY   GENERATED   SECONDARY

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

FORCE_VIEW  VIEW        VALID       N           N           N

 

 

 

How do I delete an O/S file from within PL/SQL

The pl/sql package utl_file allows me to create, read and write flat files at the O/S level on the server. Also the dbms_lob package allows me to read files from the server and load them into the database. But how do I delete an O/S file from within pl/sql after I have finished with it.

One 'near-soultion' is to use the utl_file package to re-open the file for writing (without the append option), and then close the file without writing to it. This recovers most of the disk space, but still leaves the file on the system as an empty O/S file.

Another approach is to write a short piece of Java, which can then be called from PL/SQL. Java currently offers far more flexibility than PL/SQL when dealing with O/S files, for example you could use Java to invoke and load a directory listing from PL/SQL so that you know what files exist for deletion. (See further reading).

A pure simple PL/SQL solution, however, appears to exist in the dbms_backup_restore package. This is virtually undocumented (other than in the script dbmsbkrs.sql) in 8.1.7, but contains a simple deletefile() procedure.

begin
  dbms_backup_restore.deletefile('/tmp/temp.txt');
end;
/

This appears to work as required with no side-effects.

Update for 9.2

In version 9.2, the utl_file package has been enhanced with the fremove() procedure for deleting a file.

Note: The call to dbms_backup_restore.deletefile() always gives the message: “PL/SQL procedure successfully completed” even if the path or file is not present or file or path name is invalid or if open. So we can not depend on this package's output.

Read file names using PL/SQL

In 10g there is a new procedure hidden away in the undocumented DBMS_BACKUP_RESTORE package. It's called searchfiles, which is a bit of a giveaway and appears to have been introduced for the new backup features in 10g, as RMAN now needs to know about files in the recovery destination.

Calling this procedure populates an in memory table called x$krbmsft, which is one of those magic x$ tables, the only column which is of relevance to us is fname_krbmsft which is the fully qualified path and file name. This x$ table acts in a similar fashion to a global temporary table in that its contents can only be seen from the calling session. So two sessions can call searchfiles and each can only see the results of their call (which is extremely useful).

The code sample below will only really run as sys, due to the select from x$krbmsft, it's just intended as a demo. The first two parameters in the call to searchfiles are IN OUT so must be defined as variables, even though the second parameter is of no consequence to us and should be left as NULL.

Even though they are IN OUT, testing shows they don't appear to change. The first parameter is the string to search for, in much the same format as you would pass in a call to dir (Windows) or ls (Unix).

DECLARE
pattern VARCHAR2(1024) := 'C:\temp\*.csv'; -- /tmp/*.csv
ns VARCHAR2(1024);
BEGIN
SYS.DBMS_OUTPUT.ENABLE(1000000);
SYS.DBMS_BACKUP_RESTORE.searchFiles(pattern, ns);
FOR each_file IN (SELECT FNAME_KRBMSFT AS name FROM X$KRBMSFT) LOOP
DBMS_OUTPUT.PUT_LINE(each_file.name);
END LOOP;
END;
/

This procedure appears to raise no exceptions, passing an invalid search string, such as a non-existant path or one with no permissions, simply results in an empty x$krbmsft. However, if the database parameter db_recovery_file_dest is not set, you will get ORA-19801.

Interestingly, this procedure recursively searches sub directories found in the search string. So passing a string of 'C:\windows' (for example) populates x$krbmsft with not only the files found in that directory but all the files found in all directories beneath, such as C:\windows\system32.

As x$krbmsft is an in memory table, you have been warned! Calling this procedure on a directory with thousands of sub directories and files has the potential to consume large amounts of memory (or more likely just generate an exception).

How can I identify which index represents which primary or unique key constraint?

The connection between constraints and the indexes which are used to check these constraints for the current user can be described by this query:

        select  --+ rule
          o.owner index_owner,
          o.object_name index_name,
          n.name constraint_name
        from sys.cdef$ c, dba_objects o, sys.con$ n
        where c.enabled = o.object_id
        and c.con# = n.con#
        and n.owner# = uid;

If you leave away the condition and n.owner# = uid you get all the constraints. You may further limit this query to your constraint name by adding the condition and n.name = 'your_constraint_name'.

Why can indexes and constraints be so different? In particular, you may use for example an index on columns (c, a, b) to enable a unique constraint on columns (a, b, c). Remember a constraint is a logical structure whereas an index is a physical one. So a unique or a primary constraint just describe the uniqueness. If (c, a, b) is unique then all other permutations are unique as well.

Further, these indexes may also be non-unique. You need this if you have a deferred constraint that is checked only at commit time. If you would insist on a unique index the attempt to insert duplicate values would fail before the commit although another command may have undone the duplicate entry.

This original query was contributed to comp.databases.oracle.server by Thomas Kyte.

Tips on exp and imp

Export Tips:

* Use buffer parameter while using export and import by atleast 5000000( 5MB ) which will increase the performance of exp and imp by 3 folds. (conventional path)
* Take care with NLS_LANG. It must match with V$NLS_PARAMETERS.NLS_CHARACTERSET.
* For Speedy Exports set parameter db_file_multiblock_read_count =128. (OS Dependent)
* The Export parameter BUFFER applies only to conventional path Exports. For direct path Export, use the RECORDLENGTH parameter to specify the size of the buffer that Export uses for writing to the export file.
* Use direct mode export (direct=Y).
* RECORDLENGTH recommended values: Multiples of the file system I/O block size, Multiples of DB_BLOCK_SIZE


Import Tips:

* Use indexes=n to ignore the index importing.
* Using indexfile to create the index to file, create the indexes after the importing using script file.
* Using rows=n indexes=y to import index in a separate import action.


Example:

exp eva6004/eva6004@uwms file=eva6004.dmp log=eva6004.log direct=y recordlength=640000 statistics=none

imp eva6004/eva6004@uwm file=eva6004.dmp fromuser=eva6004 touser=eva6004 indexes=n

imp eva6004/eva6004@uwm file=eva6004.dmp fromuser=eva6004 touser=eva6004 indexes=y rows=n

Send a message to all logged in users

Disclaimer: This will work only in Windows

REM
REM Script: send.sql
REM
Rem
Rem Name: Send.Sql
Rem Function: Send a message to all connect users on NT.
Rem Usage: Execute the file connected as SYS from SQL*Plus
Rem

Set Pages 0 Feed Off Term Off Echo Off
Spool Temp.Bat
Select Distinct 'Net Send ' || Terminal|| ' Please log off now.'
From V$Session Where UserName Is Not Null and terminal is not null;
Spool Off
Host Temp
exit

How do I put the current date in a spool file name?

column dcol new_value mydate noprint

select to_char(sysdate,'YYYYMMDD') dcol from dual;

spool &mydate._report.txt
-- my report goes here

select * from mytable;

spool off

Get the current Session ID

SQL> select sid, serial# from v$session
2 where machine like '%AIX%';

SID SERIAL#
---------- ----------
139 6

1 select audsid, sid, serial# from v$session
2* where machine like '%AIX%'
SQL> /

AUDSID SID SERIAL#
---------- ---------- ----------
5312 139 6

SQL> desc v$mystat
Name Null? Type
----------------------------------------- -------- ----------------------------
SID NUMBER
STATISTIC# NUMBER
VALUE NUMBER

SQL> select distinct sid from v$mystat;

SID
----------
139

SQL> select sid, serial# from v$session
2 where audsid=SYS_CONTEXT('USERENV','SESSIONID');

SID SERIAL#
---------- ----------
139 6

SQL> select dbms_session.unique_session_id from dual;

UNIQUE_SESSION_ID
--------------------------------------------------------------------------------
008B00060001

SQL> SELECT TO_NUMBER(SUBSTR(dbms_session.unique_session_id,1,4),'XXXX')
2 from dual;

TO_NUMBER(SUBSTR(DBMS_SESSION.UNIQUE_SESSION_ID,1,4),'XXXX')
------------------------------------------------------------
139

SQL>

Traverse through all items in a form

The simple technique of traversing through all items in a form is demonstrated in this article. See below logic for the traversing:

001 start program
002 get first block name;
003 get last block name;
004 go to first block name;
005 assign value of first block name to current block;
006 start loop
007 get first item;
008 get last item;
009 if the loop is running for first time then assign current item's value as first item;
010 print item name;
011
012 if last item is true and current block is not last block
013 go to next block;
014 assign current block's value as new block's name;
015 else if last item is true and current block is last block
016 exit loop;
017 else if current item is not last item
018 go to next item;
019 assign current item's value as new item's name;
020 end if;
021 end loop;
022 end program;