To subscribe just go to
www.asktheoracle.net/oracle-tips-signup.html
In this issue:
Our Oracle resources page has recommendations for further reading to enhance your knowledge of Oracle and contains links to more information on OTN and other places.We also have many other articles about Oracle and Oracle tutorials on our site. For a complete list go here.
From: Anurag - I'm using Oracle 8i on win98SR. Please let me know how to recover database if I could not backed it up (running in archive log mode) and a table has been dropped by mistake. Thanks in Advance
If you don't have any backup of your Oracle database, you can't restore the
table, no matter what mode the database was running in. If you do have a backup
and have been running in archivelog mode, restore the data file that contained
the dropped table, start and mount the database (but don't open
it) then at the sql prompt enter:
recover database until time
---------
From:
Apoorva - I am doing SQL / PLSQL course for Oracle9i. Right now, I am doing
SQL and have came across a problem. Following is the SELECT statement :
CREATE TABLE title_copy (
copy_id number(10),
title_id number(10),
status varchar2(15)
CONSTRAINT title_copy_status_nn NOT NULL,
CONSTRAINT title_copy_status_chk CHECK(status IN ('AVAILABLE', 'DESTROYED',
'RENTED', 'RESERVED')),
CONSTRAINT title_copy_titleid_fk FOREIGN KEY(title_id) REFERENCES title_copy(copy_id),
CONSTRAINT title_copy_copyid_titleid_pk PRIMARY KEY(copy_id,title_id));
I am getting following error :
REFERENCES title_copy(copy_id),
*
ERROR at line 12: ORA-02270: no matching unique or primary key for this column-list
Please help...
Apoova,
The foreign key constraint has to reference another (different) table. You
can't refer to the same table that you're creating. The whole point of the
foreign key is to protect ensure that any value enetered in one table already
exists in the reference table.
For example, let's assume you have a table called dept with 2 columns: dept_id and dept_name to hold department details and you want to create a table called emp with 3 columns: emp_id, dept_id and emp_name to hold employee details. To ensure that you don't assign employees to a non-existant department you would create a foreign key on emp.dept_id to reference dept.dept_id as follows:-
CREATE TABLE
emp (
emp_id number(10),
dept_id number(10),
emp_name varchar2(60)
CONSTRAINT emp_name_nn NOT NULL,
CONSTRAINT emp_fk FOREIGN KEY(dept_id) REFERENCES dept(dept_id),
CONSTRAINT emp_pk PRIMARY KEY(emp_id);
---------
From: Paramesh in Singapore - We are using oracle 9i. I have some problem in SQL. We are retrieving data from 6 tables using join .(apprx'mtly returns 500 rows) ,What happends more than one user call the function . Each and everytime it retrieve data from tables(Same Data) . Some time System time out will occur. Is it possible to Store Some where instead table ie some Object , Whenever u want u can retrieve data from object instead Table directly. how to Store in Object in Oracle . thkx Param
There are a few possible solutions to this problem.
First, if the same data is being requested several times by each user then storing the result of the query in a pl/sql index-by table would be my first choice. (If you need help with Oracle PL/SQL, we highly recommend Steven Feurstein/Bill Pribyl's book Oracle PL/SQL Programming as a great resource for anyone - dbas and developers alike - using PL/SQL).
Another alternative is to use materialised views which are effectively pre-run queries with the results of the query is stored in a special table. Materialised views have the advantage that they can be set to be refreshed automatically by Oracle whenever any of the underlying tables change, or they can be refreshed on a pre-detrmined frequency or refreshed manually on demand. Note that the initialisation parameter QUERY_REWRITE_ENABLED must be set for the optimizer to be able to dynamically rewrite queries to use any materialized views.
The 3rd option is to re-design your Oracle database so that fewer tables have to be joined to get the information you want! This might sound like a glib answer, but if your application often has to join many tables, you might want to denormailize some of them.
The 4th option is to run explain plan for the query and make sure the table with the smallest result set is used to drive the join. (See our Oracle Performance Tuning articles for an explanation). This should happen automatically if statistics have been gathered recently, but it's worth checking. You could also use hints to control the order in which the tables are joined.
Two excellent books on performance tuning are:-
See our Oracle resources section for links to information on OTN and other places and recommendations for further reading to enhance your Oracle knowledge or investigate our online Oracle training and tips and tricks .
---------------------------------------
Looking for more Oracle tips and tricks ?
If you're looking for no frills, no fluff, just solid, reliable technical information, take a short cut now and subscribe to our ezine. It's jam-packed full of tips and tricks to save you hours of blood, sweat and tears searching for reliable information. Subscribe today - there's no charge - and your first issue will soon be winging its way to your mailbox.
Send us your questions and we'll answer them in the next newsletter.
Smartsoft Computing Ltd
Bristol, England
Tel: 0845 0031320
Contact Us
Click here to view our privacy policy .
This site uses hitslink.com to gather statistical information about our visitors. View hitslink privacy policy .
Oracle is a registered trademark of Oracle Corporation and/or its affiliates. UNIX is a registered trademark of The Open Group in the United States and other countries.
© Copyright Smartsoft Computing Ltd 2001-2008. All rights reserved.