The Oracle (tm) Users' Co-Operative FAQ

Sponsored by JL Computer Consultancy


Disclaimer

The offerings on the Co-operative FAQ have been supplied by users around the world on a 'best-efforts' basis. Unless specified in the author box Jonathan Lewis has had nothing to do with creating the content. There is no guarantee that the information published on these pages will be appropriate to your situation, relevant to your versions of hardware or software, or even correct.

DBA topics

20th Feb 2007

PL/SQL

14th Feb 2007

Performance

4th Jan 2007

SQL Techniques

14th Feb 2007

SQL*Plus

23rdJan 2006

Replication et. al.

31st Jan 2006

Recovery

28th Mar 02

Miscellaneous

31st Mar 05

Tools

4th April 2005

 

Now available through RSS:  I don’t know how it works, but if you know how to use RSS, then go to http://feeds.feedburner.com/JonathanLewisArticles to get updates to this page through RSS. For further details on how this was set up see Eddie Awad’s article at http://awads.net/wp/2006/04/17/orana-powered-by-google-and-feedburner/


Google


WWW JL Computer Consultancy


Volunteers and References please

There are many questions on this page not marked as hyperlinks. If you would like to supply an answer, then email me on jonathan@jlcomp.demon.co.uk with the subject FAQ Author to check that the question has not yet been claimed by someone else (some items may have a WIP marker, others may be in-hand but not yet have their status changed). If you think the question should be re-phrased, feel free to do that as part of your answer. And if you think there are questions that should appear on any of the list, please write both the question and answer.

For consistency, please write your answers in the FAQ template with minimal extra formatting. Some answers will inevitably be very short and direct; however there are many cases when a good answer requires some background material, perhaps some answers will contradict a 'well-known myth' or point out that comments relevent to a previous version of Oracle are no longer suitable. Feel free to make your answers as long or as short as you think fit. When you have written an answer, please email it to me with a subject of FAQ Answer.

Ideally you should include your name and email address in the relevant box on the answer page so that other authors can co-operate with you to update and enhance the page at a later date. BUT please note - names and email addresses are not mandatory. If you would like to annotate, update, or enhance an existing answer, please contact the original author in the first case and then send me an updated page with a topic of FAQ Answers.

If you would like to volunteer a question (with or without an answer), please email it to me at the same address with a subject of FAQ Questions. Please include the heading(s) under which the question should be placed.

If you find yourself on comp.databases.oracle.server answering the same old question for the umpteenth time - please consider downloading the FAQ template, filling in your answer and sending it to me.

If you find a good document somewhere on the Internet that answers some specific questions that you think should be on the FAQ, send me an email with a topic of FAQ Answers quoting the question(s) (inventing it if it doesn't exist), the author's name, your name and email, any comment on the document, and the title and URL of the document. (If you prefer you can download and complete the FAQ reference template. Note - even if there is an answer in place, it is perfectly sensible to add other references to it - the FAQ is attempting to collate useful information with precision, so that people can understand their problems, not just hope that a given quick answer happens to work.

Template download - there have been a couple of problems with this. I started with IE 4.0 which magically acquired Frontpage Express the last time my ISP sent me a CD. My method is to open the page, then do 'File -> Save as' to save it locally. Then I re-open it locally and do 'Edit -> Page'. You may need to use something like 'View -> Source' then save the resulting text file. If all else fails, ask me to send you an electronic copy by email.

Usage

This page is simply a list of questions that has been split into a number of topics. The same question may appear under a number of headings so that you can browse a section with a chance of finding related questions which match. If there is an answer currently available, the question will appear as a hyper-link, if not then it will appear in simple text.

There is no search engine on this site, but the site is registered with Google, so you may be able to use a Google advanced search against a large fraction of the FAQ with a fair degree of success. The sponsoring site (www.jlcomp.demon.co.uk) is also indexed by Steve Adams (www.ixora.com.au) so you can use his search engine on the documents listed below. The most recently added, or updated, answers will be moved to the top of their sections, with a date stamp to show the last date change.

I hope to be able to update the set of questions and answers roughly once every two weeks.


DBA and general server technology

Back to top

I'm trying to make a tablespace read only, but it seems to hang forever. What's wrong ? (Updated)

20th Feb 2007

 

 

What bitwise operations does Oracle support ?  (Updated)

14th Feb 2007

How can I tune a tablescan?

4th Jan 2007

Why is dbms_stats so much slower than Analyze?

4th Jan 2007

Can Bind Variable Peeking Cause Problems even with no Histograms on the Predicate Columns

31st Dec 2006

How to generate an Execution Plan with Bind Variable Peeking?

24th Mar 2006

Why do I keep seeing tables with garbage names like BIN${something}

5th Feb 2006

How do I pin a table in memory so that it is permenently in the cache ?

2nd Feb 2006

How do you create an 'auto number' or 'auto increment' column similar to the offering from Microsoft's SQL Server ?

21st Jan 2006

How does Oracle handle PGA memory ?

14th Sept 2005

What is the relationship between Oracle's buffer cache and UNIX's buffer cache

22nd Aug 2005 

Queries against dba_free_space are slow - is there any way to speed them up?

22nd Aug 2005

How do I change the default password of user internal ?

22nd Aug 2005 

How do you use sql_trace ?

22nd Aug 2005

Is it possible to run several different versions of Oracle on the same server machine?

24th Jan 2005 

I have deleted a trace file with the session still live. I now want the session to resume tracing, but a new file doesn't appear

24th Jan 2005

Is there a Windows version of the oerr program?

5th Nov 2004

Why does it seem that a SELECT over a db_link requires a commit after execution ?

11th Sept 2004

How can I tell what events are set ?

21st Aug 2004

How can I select a random collection of rows from a table ?

3rd May 2004

How can I maintain a history of user logins ?

16th April 2004

According to autotrace, I am not doing partition elimination - how do I fix this ?

26th Mar 2004

When I rebuild an index, I see Oracle doing a sort. Why should this be necessary, why doesn't it simply read the existing index ?

22nd Mar 2004

I lock my table before truncating it, but I still get ORA-00054: resource busy ... Why ?

20th Jan 2004

Can I see all the tables that are in the database if I have the DBA privilege ?

10th Jan 2004 

How do you purge old statspack snapshots automatically

5th Dec 2003

How do I find out who is currently using the space in the temporary tablespace ?

18th Sept 2003

Is there any reason why the tablespace_name in user_tables and all_tables is blank (empty) for an Index Organized Table.

18th Sept 2003

Our PL/SQL loop to load 20M rows into the database slows down after the first 1M or so, why ?

9th Aug 2003

Queries against dba_free_space with locally managed tablespaces are slow - is there any way to speed them up ?

9th Aug 2003

How can an end-user see whether a row is locked, and who is locking it, before they try to lock it themselves ?

27th Feb 2003

I have deleted 6M rows from a table - how do I reclaim the free space ?

24th Jan 2003

How do I clone a database from one machine to another - when the filesystems are different ?

15th Dec 2002

I have a two-column partition key, and my data keeps going in the wrong partition. Why ?

15th Dec 2002

dba_segments and dba_tables show different numbers for blocks. What has gone wrong ?

15th Dec 2002

What is the unit used in the time-related columns of the v$views

15th Dec 2002

My locally managed tablespaces seem to have lost some space according to dba_free_space. Where has it gone ?

15th Dec 2002

What is the difference between cpu_costing in Oracle 9 and the old costing method of Oracle 8 ?

15th Dec 2002

How do I find distributed queries / transactions (either issued from or connecting to this instance)?

1st Nov 2002

What happens if I drop a partition from a partitioned table whilst a query is running ?

1st Nov 2002

Why does a global index on my partitioned table work faster than a local index ?

1st Nov 2002

When I update one table from another, lots of my data turns to nulls - what have I done wrong ?

1st Nov 2002

What are all these tables with names like EVT% and SMP% that have recently appeared in my system ?

29th Oct 2002

How can I track ddl changes in my database ?

29th Oct 2002

What's my best strategy for laying out an Oracle database across my available I/O devices ?

29th Oct 2002

Is it possible to re-set a sequence without dropping and recreating it ?

5th Sept 2002

How do I retrieve information about partitioned tables from the data dictionary ?

5th Sept 2002

Why is my import so much slower than my export ?

5th Sept 2002

What is an object of type 'Non-existent' ?

23rd July 2002

I am getting a database startup error about being unable to start "with new and and old " values for parameters. (9.0.1)

18th July 2002

I am getting several hundred "buffer busy waits" per day on data blocks, even though I have increased FREELISTS on all objects.

20th June 2002

I need to delete a lot of small records from LOB-tablespace then load some larger ones. How can I avoid the fragmentation?

17th June 2002

Where has svrmgr gone in 9i - how do I start and stop the database now ?

12th June 2002

How have the log_checkpoint_interval and log_checkpoint_timeout changed from version 7 ?

11th June 2002

Why isn't my output from tkprof with the explain option showing row counts ?

7th May 2002

How does Oracle handle updating a row that causes it to move from one partition to another ?

7th May 2002

Where is the UGA located, and how does sorting affect its use ?

7th May 2002

After using 'set transaction use rollback segment xxx', I still get ORA-1555 on rollback segment 'yyy' and I do not understand why.

7th May 2002

Is there a way to get the Date/Time when a table was last updated ?

7th May 2002

What's the difference between DELETE ROWS and TRUNCATE TABLE

28th Mar 2002

Every time when I restart the database I have to bring all my rollback segments back online manually. Why ?

28th Mar 2002

Is there a way to get the Date/Time when a row was last updated ?

4th Mar 2002

What is table fragmentation, and should I worry about it ?

4th Mar 2002

How do I dump a data block ?

4th Mar 2002

What is an object of type 'Undefined' ?

4th Mar 2002

Which of my indexes are redundant and can be dropped ?

4th Mar 2002

What is the difference between a unique index and a unique constraint ?

4th Mar 2002

Is there a performance impact on the database of doing an analyze?

28th Jan 2002

How do I associate an active session with a rollback segment ?

28th Jan 2002

Which version of the database server am I running ?

28th Jan 2002

I've got a corrupted data file, and Oracle won't start. I don't need the data so how do I open the database and drop data file ?

28th Jan 2002

Why is Explain Plan showing a different execution path from tkprof ?

11th Jan 2002

I have taken over an existing tablespace and need some ideas for setting extent sizes when re-organizing it

10th Jan 2002

What is a good blocksize for my database ?

10th Jan 2002

How do I rename a data file ?

10th Jan 2002

Is there a good way to turn a simple table into a partitioned table ?

10th Jan 2002

What would be a simple list of events a DBA should be alerted about ?

10th Jan 2002

How do I find out which users have the rights, or privileges, to access a given object ?

10th Jan 2002

How big, in bytes, is a ROWID ?

6th Dec 2001

What is the largest number of columns allowed in a table ?

6th Dec 2001

Is there any way to estimate how long a create table/index has left to run ?

27th Nov 2001

How much redo is filled in my redo logs?

23rd Nov 2001

How do I make a log file switch occur at regular intervals ?

23rd Nov 2001

How do I identify which tablespaces are locally managed ?

23rd Nov 2001

How do I list the parent tables, and relevant columns for a table at the child end of a foreign key constraint ?

23rd Nov 2001

Why does the view family xxx_EXTENTS run so slowly on Oracle 8.1 ?

23rd Nov 2001

I am unable to add a PK constraint to a table - what is the best way of finding the problem rows ? (Hint - novalidate/validate)

23rd Nov 2001

Dropping a tablespace seems to take a very long time - how can I speed it up ?

23rd Nov 2001

Do I need to include my online redo logs in a hot backup ?

23rd Nov 2001

I have a very large table, but it only has a few rows left in it. How can I reclaim the space ?

23rd Nov 2001

How do I delete an entire database (UNIX only) ?

23rd Nov 2001

How do I delete an entire database (NT bias) ?

23rd Nov 2001

How long is a 'short' tablescan ?

2nd Nov 2001

What is the effect of using the option "consistent = y" when running exp ?

2nd Nov 2001

Should I migrate my dictionary managed tablespaces to locally managed tablespaces ?

2nd Nov 2001

When are rollback segments supposed to shrink ?

2nd Nov 2001

What are the pros and cons of an IOT (index organized table) ?

2nd Nov 2001

I have created a table with the nologging option, but any insert, update or delete still generates redo log, why ?

2nd Nov 2001

Is there a simple guide to calling external routines from Oracle?

21st Oct 2001

I often get "ORA-03113 "end-of-file on communication channel" under all sorts of conditions. What causes it and how do I stop it?

21st Oct 2001

How do I export a table when the dump file is too big for my operating system (There is a 2GB limit) ?

21st Oct 2001

In a 'start with / connect by' query, how do I get child rows under each parent to come out in sorted order ?

21st Oct 2001

How do I switch on sql_trace in another session that is already running ?

8th Sept 2001

What is tablespace fragmentation and should I worry about it ?

4th Sept 2001

Help, I can't drop a table which I created in my Oracle database using MS-Access.

4th Sept 2001

What's the quickest way of deleting all (or a large fraction of) the data in a table ?

4th Sept 2001

A connection to the database (ca. v7.3) is starting to take a long time, why ?

4th Sept 2001

How can I limit the number of times a single user can connect to the database at any one time ?

4th Sept 2001

I have done a truncate on a table, and it takes hours to run - what is going on ?

4th Sept 2001

How I determine how much free space a tablespace has left?

4th Sept 2001

Trying to shut the database down, 'shutdown immediate' takes forever - why isn't it immediate ?

24th Aug 2001

(UNIX) I can't run SQL*Plus and all the other tools on the server unless I connect as the 'oracle' unix account - why not ?

24th Aug 2001

How do I make people change their password every 90 days ?

24th Aug 2001

How do I truncate a table which is the parent for a foreign key relation ?

24th Aug 2001

How do I find information about a database object (table, index, constraint etc.)

18th Aug 2001

Is there a way to detect processes that are rolling back, and can I figure out how long it will take ?

18th Aug 2001

How much space does a number take up ?

14th Aug 2001

I keep getting errors in the alert log 'Thread XXX cannot allocate new log - checkpoint not complete'. Is this serious ?

14th Aug 2001

How do I increase the size of my log files ?

14th Aug 2001

Can I make a procedure run every time someone logs on to the system ?

13th Aug2001

I've just installed Oracle on my Windows PC - what are the passwords for SYSTEM and SYS ?

13th Aug2001

Is there a good way of counting the number of rows in a table ?

11th Aug 2001

How do I produce a report of all tables in the database with current number of rows ? ?

11th Aug 2001

Which type of index can Oracle access faster, a character key or a numeric key ?

7th Aug 2001

An import has just left a table in the wrong tablespace, Why ?

7th Aug 2001

Is there a way to trace a unix process id to a SID and SERIAL# ?

27th July 2001

How can I associate the parallel query slaves with the session that's running the query?

27th July 2001

How do I export a database table to a flat file ?

26th July 2001

What is the best way to identify and eliminate duplicate rows in a table ?

26th July 2001

How do you identify all the locked rows in a table ?

26th July 2001

I often get "ORA-01555 Snapshot too old" when running reports. What is this and how do I stop it happening ?

26th July 2001

How do you find out who is locking a specific row in a table ?

25th July 2001

How can I find what the values are for the hidden (underscore) parameters ?

23rd July 2001

How do I move a table from one tablespace to another ?

23rd July 2001

How can I reclaim unusable fragments of space in a tablespace ?

7th July 2001

How can I check if I have the right indexes for the foreign key constraints on a child table ?

28th June 2001

How do I move a tablespaces from one file system to another ?

28th June 2001

How can I move the database to a different machine ?

27th June 2001

How do I increase the number of files in the database ?

27th June 2001

After I do a commit, there is still a lot of space used in the rollback tablespace, why is this ?

21st June 2001

How can I schedule a particular piece of code run at a regularly scheduled time ?

17th June 2001

How do I change the database block size ?

17th June 2001

How do I drop a column from a table ?

16th June 2001

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

14th June 2001

Why should I use varchar2 instead of char types ?

14th June 2001

Why do different data dictionary views give me different figures for the amount of space used by a table/index ?

31st Jan 2001

My temporary tablespace never clears down, what should I do ?

29th Jan 2001

How big is a row ? What about rows that grow over time ?

27th Nov 2000

Why can't I see the output from dbms_output ?

24th Nov2000

How do I find out how much space is actually USED by a table/index

13th Nov 2000

Why do I keep losing sequence numbers ?

9th Aug 2000

How do you rename a column in a table ?

20th July 2000

 

 

Should I worry about having some relationship between extent sizes and db_file_multiblock_read_count ?

 

How can I apply an Oracle database (application) change to many equal instances located across the country on a single date ?
We're looking for he most efficient way to apply our database releases to dozens of Oracle instances on the same day. 

 

Are there any good strategies for backing up a data warehouse ?

 

Why is the Comment_Text Column on my Sys.Dba_Audit_Trail Table is always empty ?

 

I am getting error while restarting the database (9.0.1) after changing the parameter db_block_buffers in SPFILE<SID>.ora file. The error was "could not start the database with old and new parameter". What is wrong ?

 

I have audit_trail=db - but want to capture the program name from v$session as well. How can I do this ?

 

Why do I keep getting Oracle error - ORA-1002: FETCH OUT OF SEQUENCE in my batch code ?

 

I lock my table before truncating it, but I still get ORA-00054: resource busy ... Why ?

 

How do I determine the degree of fragmentation of a table ?

 

How do I determine how much space I could reclaim by deleting a chunk of data from a table (and cleaning up afterwards)

 

How do I decide on a sensible size for my redo log buffer

 

I created a user with the wrong default tablespace - How can I move his entire schema from one tablespace to another.

 

I have created a trigger on a table, and this seems to have wrecked the performance of some bulk inserts - why ?

 

v$sysstat shows the number of sorts that have occurred. How do I find the number of hash joins that have occurred ?

 

I have a table partitioned on column timestamp, with a primary key on column id. How do I create a locally partitioned index for this PK ?

 

How do I calculate suitable values for PCTFREE and PCTUSED for a table.

 

What is the best way to move a 500GB from 32 bit 8.1.7.4 to 64 bit 9.2 ? exp/imp, mig, SQLLDR, or CTAS ?

 

Is it only committed transactions that are written into the redo log ? 

 

I wish to update certain portions inside long column in 8.1.7. If this were a varchar2 field I would use the replace function. How do I achieve the same thing in with a long ?

 


Performance and Tuning

Back to top

How can I tune a tablescan?

4th Jan 2007

Why is dbms_stats so much slower than Analyze?

4th Jan 2007

 

 

Can Bind Variable Peeking Cause Problems even with no Histograms on the Predicate Columns

31st Dec 2006

Why should a reverse key index be useful when the leading column of the index is generated from a sequence ?

9th Mar 2005

According to autotrace, I am not doing partition elimination - how do I fix this ?

26th Mar 2004

Queries against dba_free_space with locally managed tablespaces are slow - is there any way to speed them up ?

9th Aug 2003

Will compressing my indexes improve performance ?

26th Feb 2003

What is the difference between cpu_costing in Oracle 9 and the old costing method of Oracle 8 ?

15th Dec 2002

How are the Cache Buffers Chains and Cache Buffers LRU Chains used in the Buffer Cache management ?

15th Dec 2002

Why does a global index on my partitioned table work faster than a local index ?

1st Nov 2002

Which Oracle features require and/or force the use of Cost Based Optimisation ?

1st Nov 2002

What is the difference between a soft parse and a hard parse ?

5th Sept 2002

How do I find out which tables are accessed with a full table scan, and the number of times this occurred ?

18th July 2002

What's the difference between DELETE ROWS and TRUNCATE TABLE

28th Mar 2002

Is it possible to flush data from the db_block_buffer for testing purposes.

28th Mar 2002

Which of my indexes are redundant and can be dropped ?

4th Mar 2002

If I store text in a column, can I index individual words ?

4th Mar 2002

Is there a performance impact on the database of doing an analyze?

28th Jan 2002

How do I associate an active session with a rollback segment ?

28th Jan 2002

Why is Explain Plan showing a different execution path from tkprof ?

11th Jan 2002

Oracle seems to be very slow reporting large execution plans through explain plan. Is there any way to speed this up ?

10th Jan 2002

What is the most efficient way to update one table with values from another table ? (Update/Delete with subquery) (170K file)

6th Dec 2001

I am unable to add a PK constraint to a table - what is the best way of finding the problem rows ?

23rd Nov 2001

Dropping a tablespace seems to take a very long time - how can I speed it up ?

23rd Nov 2001

What is the difference between an 'index full scan' and an 'index fast full scan' ?

2nd Nov 2001

I have a column with a default value, but I keep finding it set to NULL - what's going wrong ?

2nd Nov 2001

I have created a table with the nologging option, but any insert, update or delete still generates redo log, why ?

2nd Nov 2001

How much benefit is there in rebuilding a table/index so that it fits in a single extent ?

21st Oct 2001

The number of redo copy latch misses reported in V$LATCH is a large fraction of the gets. What should I do ?

8th Sept 2001

What's the quickest way of deleting all (or a large fraction of) the data in a table ?

4th Sept 2001

A connection to the database (ca. v7.3) is starting to take a long time, why ?

4th Sept 2001

I have done a truncate on a table, and it takes hours to run - what is going on ?

4th Sept 2001

How do you write a query that ignores the effects of upper and lower case ?

24th Aug 2001

How much space does a number take up ?

14th Aug 2001

Is there a good way of counting the number of rows in a table ?

11th Aug 2001

 

 

Why do some queries go very fast and others very slowly when the optimizer_mode is choose ?

 

Are composite indexes more useful than single column indexes ?

 

Is is possible to allow Microsoft Query receive the output from the dbms_output package ? If not, what are the alternatives ?

 

Is there any way to identify which init.ora parameters are relevant to tuning the database ?

 

How do I decide on a good value for pga_aggregate_target in 9.2 ?

 

I have a query that I run on two identical databases - on one it is very quick on the other it is very slow - what's up ?

 

Is there any way of identifying how parameters in the Oracle INIT.ORA control file can be used for tuning the database including: (1) Memory usage (2) CPU usage (3) Disk usage

 

What is the fastest way of dumping a database table to a flat file - is utl_file a good idea ?

 

How do I get global histograms onto partitioned tables in Oracle 8.1 - apparently they can only be generated properly in v9.

 

In documentation about tuning SQL, I see references to parse trees. What is a parse tree ?

 

Are there any benefits or drawbacks to regular reboot of a machine running an Oracle database ?

 

Is there any way to keep track of when datafiles are resized.

 


PL/SQL

Back to top

Is there a way to read the names of a set of files with a given extension from a directory as if from a SQL cursor (Updated)

14th Feb 2007

How do I send an email from PL/SQL, possibly with attachments? (Updated)

14th Feb 2007

 

 

How do I delete an O/S file from within pl/sql ? (updated)

24th Nov 2006

In a change password procedure can I check that the old password has been entered correctly before changing to a new password

22nd Aug 2005 

Is there a way to suspend a task, or make it sleep, for a period of time ?

24th Jan 2005

How can I select a random collection of rows from a table ?

3rd May 2004

Can Oracle prompt the user for input from a procedure ?

5th Dec 2003

Can Oracle Spool Output from a procedure?

5th Dec 2003

How do you write an aggregate function that can be used in SQL ?

5th Dec 2003

Can Oracle call a batch job from a procedure ?

5th Dec 2003

Our PL/SQL loop to load 20M rows into the database slows down after the first 1M or so, why ?

9th Aug 2003

How do I identify and remove a job from the job queue ?

7th Apr 2003

Using dbms_output as a debugging tool, sometimes my output seems to get lost. Why does this happen ?

7th Apr 2003

How do I use a variable as a table name inside pl/sql ?

4th Feb 2003

Why am I getting errors when I try to use the utl_file package to write a flat file ?

29th Oct 2002

How can I use utl_file as a debugging aid ?

29th Oct 2002

How can I turn Oracle error messages into my own 'friendlier' error messages ?

4th Mar 2002

How can I set up a variable that is visible only to my session and persists for the duration of the session ?

6th Dec 2001

How can I get a PL/SQL procedure to output high volumes of debugging messages ?

24th Aug 2001

Can I find out how many rows will be affected by an SQL statement without actually running it ?

24th Aug 2001

Why does my SQL run quickly standalone, but slowly inside a PL/SQL procedure ?

15th Aug 2001

The line length in the utl_file package is limited to 1023 bytes - is there a workaround ?

14th Aug 2001

How do I return a set of records from a pl/sql procedure

27th July 2001

How can I get a PL/SQL procedure to output debugging messages ?

26th July 2001

How can I find out how many rows were affected by an SQL statement after I have executed it ?

25th July 2001

Should I always use explicit cursors where coding PL/SQL

23rd July 2001

My PL/SQL procedure fails with 'insufficient privileges', but all the SQL works in SQL*Plus. What's going wrong ?

7th July 2001

How can I create/drop/truncate a table inside a PL/SQL block ?

7th July 2001

How can I schedule a particular piece of code run at a regularly scheduled time ?

17th June 2001

 

 

How in 8i do I import a large comma delimited file from within a procedure that will (1) create a temp table to receive the file (2) import the file (3) capture some stats on the data, like from and to date range of a field (4) transfer the data to a data warehouse table (5) delete the temp table

 

In general, what is the best way to handle propagation of exceptions through called/calling procedures ?

 

How do I pass ref cursors between procedures in a package ?

 

How do I detect the number of command-line parameters supplied to a PL/SQL script ?

 


SQL techniques

Back to top    

What bitwise operations does Oracle support ? (Updated)

14th Feb 2007

How do I reorder characters in a column based on a new “Order By” rule in a SQL Statement ?

14th Feb 2007

How do I count occurrences of characters in a SQL Statement

14th Feb 2007

How do I find all the starting positions and counts of consecutive occurrences of non-alphanumeric characters (at each position) in a SQL Statement

14th Feb 2007

How do I replace the data of the “Target” Table with the data of the “Source” Table in a SQL Statement?

14th Feb 2007

How do I collapse all nulls for every table column in a SQL Statement?

14th Feb 2007

How do I display combinations using the table format in a SQL Statement?

14th Feb 2007

How do I filter and locate the position of non-alphanumeric characters in a SQL Statement?

14th Feb 2007

How do I repeat each row multiple times based on a table column value in that row in a SQL Statement?

14th Feb 2007

 

 

How do I exclude duplicated elements from the “STRAGG” (String aggregation) in a SQL Statement?

2nd Jan 2007

How do I generate both the report header and summation row at each aggregation level in a SQL Statement?

29th Dec 2006

How do I find the shortest path (distance) between the source and the destination in a SQL Statement?

29th Dec 2006

How do I find the nearest common ancestor node in a SQL Statement?

29th Dec 2006

How do I find all shortest path (distance) between the source and all the possible destinations in a SQL Statement?

29th Dec 2006

How do I split comma-delimited data in an SQL statement

15th Dec 2006

How do I display the combinations and their summation values in a SQL Statement?

15th Dec 2006

How do I determine which row has a reciprocal value pair in a SQL Statement?

15th Dec 2006

How do I solve a “Bin Fitting” problem in a SQL Statement

11th Dec 2006

How do I implement a “STRAGG”  (string aggregation) solution in a SQL Statement

11th Dec 2006

How do I display all the permutation Or Combinations of data in a SQL Statement

11th Dec 2006

How do I update a join view ?

22nd Aug 2005

How do I report the top 10 members of the sales team

22nd Mar 2004

When I use sql of the form "select where NOT IN subquery" I get no rows returned - is this a bug ?

10th Jan 2004

How do I return a result set in batches, e.g. get rows 1 - 10, then 11-20, then next 10 and so on ?

5th Dec 2003 

How do I report the top 10 product members of the sales team in each state/county ?

28th Mar 2002

How do I embed single-quote marks in an SQL statement

4th Mar 2002

Oracle seems to be very slow reporting large execution plans through explain plan. Is there any way to speed this up ?

10th Jan 2002

What is the most efficient way to update one table with values from another table ? (Update/Delete with subquery) (170K file)

6th Dec 2001

How do I find my SID (session id) from SQL*Plus

6th Dec 2001

In a 'start with / connect by' query, how do I get child rows under each parent to come out in sorted order ?

21st Oct 2001

How can I convert numbers from Hex to Decimal (or any other bases) ?

8th Sept 2001

What's the quickest way of deleting all (or a large fraction of) the data in a table ?

4th Sept 2001

I am using to_char(num_col,'9999') to get a 4-character output, but is keeps coming out one column too wide. What's wrong ?

24th Aug 2001

How do you write a query that ignores the effects of upper and lower case ?

24th Aug 2001

Can I find out how many rows will be affected by an SQL statement without actually running it ?

24th Aug 2001

How can I select a random collection of rows from a table ?

7th Aug 2001

How do I export a database table to a flat file ?

26th July 2001

What is the best way to identify and eliminate duplicate rows in a table ?

26th July 2001

How can I find out how many rows were affected by an SQL statement after I have executed it ?

25th July 2001

 

 

Is it possible to write a trigger referencing more than one table - I want a trigger to audit a master/detail pair.

 

Is it possible to easily convert existing SQL scripts into a format that cannot be read or altered? This is to improve security on an existing site.

 


SQL*Plus

Back to top

How can I set the SQL*Plus prompt to something useful ? (minor update)

23rd Jan 2006

How do I set up an 'environment' for SQL*Plus ? (minor update)

23rd Jan 2006

 

 

How do I execute a SQL statement that uses a variable as a table name ?

29th Oct 2002

How to I insert an apostrophe (') into a column using simple SQL ?

4th Mar 2002

How can I write a script to output a table as a flat file which is a list of "insert into tableX(....) values (...)" statements.

4th Mar 2002

How do I embed the ampersand (&) as part of the literal text I use in an SQL statement ?

10th Jan 2002

How do I find my SID (session id) from SQL*Plus

6th Dec 2001

I often get "ORA-03113 "end-of-file on communication channel" under all sorts of conditions. What causes it and how do I stop it?

21st Oct 2001

I get strange error messages when trying to use AUTOTRACE in SQL*Plus. How do I fix this ?

4th Sept 2001

(UNIX) I can't run SQL*Plus and all the other tools on the server unless I connect as the 'oracle' unix account - why not ?

24th Aug 2001

How can I start an SQL*Plus batch without showing the id/password on the command line ?

14th Aug 2001

How do I get SQL*Plus to store an ampersand (&) in the database ?

14th Aug 2001

Why does everyone except SYSTEM (and possibly SYS) get a strange error message when starting up SQL*Plus ?

14th Aug 2001

How do I get the return code (or exit code) from SQL*Plus to a UNIX variable ?

7th Aug 2001

Is it possible to return a set of results from SQL*Plus to a UNIX variable ?

7th Aug 2001

My PL/SQL procedure fails with 'insufficient privileges', but all the SQL works in SQL*Plus. What's going wrong ?

7th July 2001

Why do I lose the leading spaces and blank lines when I try to format output through the dbms_output package ?

2nd Feb 2001

 

 

When I try to insert text a CLOB I get error: "SP2-0027: Input is too long ( > 2499 characters) - line ignored". What should I do ?

 

How do I translate UNIX shell into NT Batch, e.g. echo "set time on; \n exec pl_sql_program; \n exit" | sqlplus uid/pswd

 

Is there any way to remove, or undeclared a bind variable in SQL*Plus (created using -  variable X number) ? 

 

 

 


Replication and related technologies

Back to top

Can I use streams to replicate a schema to another database ?

31st Jan 2006

 

 

How can I replicate only some columns (rows) from given table ?

26th Feb 2005

What's the problem with using replication on 24x7 sites?

23rd Sept 2001

I've got ORA-00439 while executed ADD_MASTER_DATABASE (API or Replication Manager). Why ?

8th Sept 2001

Can advanced replication be managed without the overhead of using Enterprise Manager ?

8th Sept 2001

Is there any way to turn replication off within the current session?

8th Sept 2001

 

 


Recovery

Back to top

How do I recover from ... loss of a multiplexed control file member

28th Mar 2002

 

 

How do I recover from ... loss of one member of an online redo log group

4th Mar 2002

How do I recover from ... loss of an unarchived online log file

4th Mar 2002

How do I recover from ... a situation where there is no datafile backup

4th Mar 2002

How do I recover from ... loss of a datafile that belongs to an indexes-only tablespace

4th Mar 2002

How do I recover from ... loss of a datafile that belongs to a temporary tablespace

4th Mar 2002

How do I recover from ... loss of a datafile that belongs to a readonly tablespace

4th Mar 2002

How do I recover from ... loss of an active redo log group

4th Mar 2002

How do I recover from ... loss of the current redo log group

4th Mar 2002

How do I recover from ... loss of an inactive redo log group

4th Mar 2002

How do I recover from ... loss of a datafile that belongs to the system tablespace

10th Jan 2002

How do I recover from ... loss of a datafile that belongs to a traditional rollback segment tablespace

10th Jan 2002

 

 

How do I recover from ... loss of a datafile that belongs to a typical data tablespace

 

How do I recover from ... having added a datafile since last hot backup

 

How do I recover from ... an instance crash where tablespaces are still in hot backup mode

 

How do I recover from ... loss of all online redo log groups

 

How do I recover from ... loss of all archived log files

 

How do I recover from ... complete loss of a control file

 

 

 


Miscellaneous

Back to top

What is a parse tree ?

31st Mar 2005

 

 

How do I use a custom function in a SQL*Loader script?

4th Mar 2002

How do I use SQL*Loader to load sequential files

4th Mar 2002

Names of Oracle supplied utilities from 7.x to 8i on Windows NT and 2000.

8th Sep 2001

 

 

How do you return an exception to an Oracle Forms?

 

I have created an Oracle Report (Reports 6i) which needs to always print two copies. It also must be uncollated, meaning it should print page 1,1,2,2,3,3,etc. However, Oracle Reports ignores the collation option - what can I do ?

 

How can I apply an Oracle database upgrade/patch to many equal instances located across the country on a single date?

 

How can I connect from a servlet via JDBC to an Oracle database as sysdba ?

 


Tools

This section has been set up in response to a request for information about such utilities, freeware, shareware and expensiveware as TOAD, SQL*XL, Patrol, Spotlight, etc. The pages referenced will follow a standard format, allowing users to make their personal comments about the good, bad, or indifferent points about such tools. No anonymous postings will be accepted.

Back to top

Performance

 

Orca for Oracle

4th April 2005

Performance Explorer-I / Statspack

11th Sept 2004

SQL*XL

30th Aug 2004

Template page for product notes