Hi Ramesh,
What you need cannot be achieved through a single sql. You will need to write a plsql code for this. Here is what you need. Save this code into an sql file and run that file in sql plus environment.
/************* Copy the code from here *******************/
SET SERVEROUTPUT ON
DECLARE
type arr_varchar2_ty is table of user_tables.table_name%TYPE INDEX BY BINARY_INTEGER;
vCount PLS_INTEGER;
arr_tbl_nm arr_varchar2_ty;
BEGIN
SELECT table_name
BULK COLLECT INTO arr_tbl_nm
FROM user_tables
WHERE upper(table_name) != 'PLAN_TABLE';
FOR i IN 1..arr_tbl_nm.COUNT
LOOP
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM '||arr_tbl_nm(i) INTO vcount;
IF vCOunt > 0 THEN
DBMS_OUTPUT.PUT_LINE(arr_tbl_nm(i));
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error in execution ' ||sqlerrm);
END;
/
/****************** Copy the code till here and save in an sql file and execute that file ******************/
Warm regards,
Vikas Bhat
Senior Software Engineer,
FBSI Pvt Ltd.,
Embassy Golf Links Business Park,
Intermediate Ring Road,
Bangalore - 560071
Email - vikas.[Email address protected]
Ph:91-80-56916482
Mob:91-9845589149
Those who aspire for the light of sun; need to bear its heat.
Any comments or statements made in this email are not necessarily those of Fidelity Business Services India Pvt. Ltd. or any of the Fidelity Investments group companies. The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. If you have received this in error, please contact the sender and delete the material from any computer. All e-mails sent from or to Fidelity Business Services India Pvt. Ltd. may be subject to our monitoring procedures.
-----Original Message-----
From: rameshk [mailto:oracledba-ezmlmshield-x48357959.[Email address protected]
Sent: Wednesday, August 31, 2005 2:34 PM
To: LazyDBA Discussion
Subject: RE: Parallelle processes....
Hi ,
Can any one tell me the way i can generate a simple report using SQL to get a list of all the tables having atleast one row in the database I want to pick up all these tables to avoid junk tables (with out any rows )in my database.
K Ramesh Babu
Tata Consultancy Services Limited
Mailto: rameshk.[Email address protected]
Website: http://www.tcs.com
"Cardus Ian " <oracledba-ezmlmshield-x98904560.[Email address protected] 31/08/2005 13:51
To
"LazyDBA Discussion" <[Email address protected]
cc
Subject
RE: Parallelle processes....
Try the following
SELECT 'ALTER TABLE ' || TABLE_NAME || ' PARALLEL 1;' FROM USER_TABLES WHERE DEGREE > 1;
and then run the resultant ALTER TABLE statements.
If this "parallelism" has appeared without you explicitly running ALTER TABLE...PARALLEL 8 (for example) then it may, I think, be because the table was created with a parallel query in a CTAS (CREATE TABLE ... AS
SELECT) statement - the parallelism remains on the table and any subsequent queries will run as parallel queries unless you run the above ALTER statement.
HTH
Cardy
-----Original Message-----
From: Yves
[mailto:oracledba-ezmlmshield-x82176730.[Email address protected]
Sent: 30 August 2005 18:24
To: LazyDBA Discussion
Subject: Parallelle processes....
Ever since we've moved to 10g, it seems that many queries are run in paralelle. When I do a 'top' on UNIX, I see about 12 processes like 'ora_p000_sid' to 'ora_p012_sid'.
This seems to reduce the performance of some statements. What parameters should I change to limit the number of paralelle processes for each querie.
Merci / Thanks
Yves Leonard,
Chef de Projet / Project Leader,
DDS / SDD,
RH Coats, 13-I
Téléphone / phone : 613-951-3233,
couriel / email : yves.[Email address protected]
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these terms: http://www.lazydba.com/legal.html
*****************************************************************************
This e-mail and its attachments are confidential and are intended for the above named recipient only. If this has come to you in error, please notify the sender immediately and delete this e-mail from your system. You must take no action based on this, nor must you copy or disclose it or any part of its contents to any person or organisation. Statements and opinions contained in this email may not necessarily represent those of Littlewoods Group Limited or its subsidiaries. Please note that e-mail communications may be monitored. The Registered Office of Littlewoods Group Limited and its subsidiaries is 100 Old Hall Street, Liverpool, L70 1AB. Registered number of Littlewoods Group Limited is 5059352.
*****************************************************************************
This message has been scanned for viruses by BlackSpider MailControl - www.blackspider.com
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these terms: http://www.lazydba.com/legal.html
ForwardSourceID:NT000028C2
Notice: The information contained in this e-mail message and/or attachments to it may contain confidential or privileged information. If you are not the intended recipient, any dissemination, use, review, distribution, printing or copying of the information contained in this e-mail message and/or attachments to it are strictly prohibited. If you have received this communication in error, please notify us by reply e-mail or telephone and immediately and permanently delete the message and any attachments. Thank you
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these terms:http://www.lazydba.com/legal.html
Oracle LazyDBA home page