Re: ora1652 question...
You could also use a trigger to get the info to a temp table as follows. This will probably fire after any error and could be performance hit. (happy new year to all). : create or replace trigger system.server_1652_error_trig after servererror on database declare v_audsid number; v_username varchar2(40); begin if (ora_server_error(1) = 01652) then Chris Stephens <[EMAIL PROTECTED]> wrote: Is there an event to set where I can identify any sql that receives a 1652error message?There is some process running each night in a reporting database that hasbeen generating this error for the past week. I figured someone wouldcomplain. That didn't happen so I went and asked the reporting people ifany of the reports were blowing up. They said no. I just set up statspackand will run that every 10 minutes tonight. I also have a query that willcapture the session info on sessions currently sorting that I will run every10 minutes. Neither of the techniques are very direct. I would imaginethere is an event to set so that I can generate a trace file. Any othersuggestions of nailing this down would be appreciated...and so I don't have to ask about events anymore...where do I find whatevent means what?Thanks,Chris! -- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: Chris StephensINET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing). Do you Yahoo!? Yahoo! Hotjobs: Enter the "Signing Bonus" Sweepstakes
RE: Export/Import issue.
I think data will never change like that. Are the owners the same. Or are you doing fromuser touser. You can use the show option to see the contents of the export dump file. Then make sure all objects are dropped. [EMAIL PROTECTED] wrote: I am 100% positive . I tried this process 2-3 times using different approach but same output. I tried to create objects first using no data. I also tried to create objects as part of loading data but no difference. I enabled 10046 trace for the import process but that also didnt help, it showing err=1 at several places but I couldnt figure out why.-Original Message-Mercadante, Thomas FSent: Wednesday, December 24, 2003 11:09 AMTo: Multiple recipients of list ORACLE-LAvnish,Are you absolutely sure that the target table is either gone before theimport, or empty? You should not be getting a unique constraint violationif the table does not exists before import.The step that Oracle import follows for importing is:Create the tableimport the datacreate constraintscreate triggersapply grants.I! f you are getting a unique constraint violation during import, then theconstraints exist before the table is being imported which leads me tobelieve everthing is not as clean as you think. Either that, or the uniqeconstraint in the source database has been disabled, and you have bad data.Make sure you drop the target table before you start the import. And lookat the source data table's constraints to make sure they are all enabled.Good Luck!Tom MercadanteOracle Certified Professional-Original Message-[mailto:[EMAIL PROTECTED]Sent: Wednesday, December 24, 2003 1:44 PMTo: Multiple recipients of list ORACLE-LI am trying to export a table from one database to another. Table in targetdatabase is dropped before importing and is being created as part of importprocess. During import process data Oracle is complaining Unique constraintviolated and throwing out following mes! sages. Its complaining on indexabhset1 which is on columns [asset, book, yr_end_date]. I tried to comparedata in source and target. Somehow yr_end_date for year 2003 is changing to2002 while importing data. Table doesnt have any triggers. Indexes in bothsource and target are same. Table definition is exactly same and createdduring import. Tried to export 2-3 times but same result. Anybody ran intothis issue before. How should I approach to troubleshoot. ThanksError MessageColumn 1 101536Column 2 SLColumn 3 5Column 4 31-DEC-2002:00:00:00Column 5 53.29Column 6 0Column 7 4.84Column 8 96Column 9 01-JAN-1700:00:00:00IMP-00019: row rejected due to ORACLE error 1IMP-3: ORACLE error 1 encounteredORA-1: unique constraint (LDDEV.ABHSET1) violatedColumn 1 101537Column 2 SLColumn 3 5Column 4 31-DEC-2002:00:00:00Column 5 38.75Column 6 0Column 7 4.84Column 8 96Column 9 01-JAN-1700:00:00:00IMP-00019: row rejected due to ORACLE error 1IMP-3: ORACLE error 1 encounteredORA-1: unique constraint (LDDEV.ABHSET1) violatedData in SourceASSET BOOK COMPANY YR_END_DA DEPR_YTD DEPR_LTDBEG_DEPR_LTD LIFE METH_SW_D-- -- -- - -- -- -- -101536 SL 5 31-DEC-02 4.84 00 96 01-JAN-00101536 SL 5 31-DEC-03 53.29 04.84 96 01-JAN-00ASSET BOOK COMPANY YR_END_DA DEPR_YTD DEPR_LTDBEG_DEPR_LTD LIFE METH_SW_D-- -- -- - -- -- -- -101537 SL 5 31-DEC-02 4.84 00 96 01-JAN-00101537 SL 5 31-DEC-03 38.75 04.84 96 01-JAN-00Data in Target.ASSET BOOK COMPANY YR_END_DA DEPR_YTD DEPR_LTDBEG_DEPR_LTD LIFE METH_SW_D-- -- -- - -- -- -- -101536 SL 5 31-DEC-02 4.84 00 96 01-JAN-00ASSET BOOK COMPANY YR_END_DA DEPR_YTD DEPR_LTDBEG_DEPR_LTD LIFE METH_SW_D-- -- -- - -- -- -- -101537 SL 5 31-DEC-02 4.84 00 96 01-JAN-00-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: <[EMAIL PROTECTED]INET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other inform! ation (like subscribing).-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: Mercadante, Thomas FINET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGu
RE: Database Instance
One other disadvantage of putting all instances together is if you need to say bounce the database (for parameter change or other maintenance etc) then all other applications will get affected. Whereas with separate instances other applications will not get affected. To some extent one application failing will not affect other applications. Except if one application does not close its connections then it could lead to maximum connections (sessions) being reached and affecting other applications. If the nature of the applications is different : OLTP, warehousing then you cannot really tune the parameters. On the positive side I think putting instances together will lead to some memory savings. I would suggest : Do not worry about who wants to put the instances together just list the advantages, disadvantages and make the decision. [EMAIL PROTECTED] wrote: It is not necessarily true that an error in one application will affect allapplications. If there is a problem with oracle instance or the database,then all applications might be affected.Multiple schemas which have the same table names can be a problem. If yourapplications uses public synonyms, then you might have a big problem.If everything is working fine now, it seems pointless to move things around.But this is philosophy. I do believe that isolating applications from eachother as much as possible is usually a good thing."Good fences make good neighbors." (usually)But, if your manager insists on it, you have no choice. Just do your bestto keep the old stuff around in case it becomes apparent that the new waywill not work and you must go back to the old way.> -Original Message-> Lately, m! y manager want me to remove all the databases> and remain a single instance. I was wondering if i> move everything into single database then if one of> the application fail due to oracle error , then all> other four application will fail also rite ? > > Each of our web application needs to have 2 schema and> both schema have to be transparent to each other.> While other application schema will be invisible to> each other. Since i have 5 web app then i will need 10> schema.One major problem is all the 10 schema will> contain same table name. It will be a mess putting so> much app in a single db . > > Pls correct me if i am wrong and do let me know what> are the pro and cons or maybe you can educate me with> some of the best practice to setup a proper production> server environment.> > Thank You> > Regards,> Jkean > > > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: <[EMAIL PROTECTED]INET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing). Do you Yahoo!? Free Pop-Up Blocker - Get it now
Re: Index usage
No. The index should get used. The query result for query 2 is a subset of rows with ta.c1='val1' will get selected. Subset of query 1. So there is no need for a full table scan. The index can be used in the following way : 1) Use index ind1 to get rows with ta.c1='val1' (which is query 1). This can definitely use an index.2) Further filter using ta.c3 = 'val2' Now may be the index is not very selective and the optimizer is going in for a full table scan. What is the cardinality like? It is strange that RULE or index hint is not taking it. Can you try a simple index(ta) hint or send your hint syntax. Can you try the hint on another table to make sure hint is working. I do not know why hint should not work. Good luck. "Daniel W. Fink" <[EMAIL PROTECTED]> wrote: You answered your own question.ta.c3 is a nonindexed column, this means that the only way to satisfy thepredicate is to perform a full table scan. Since this predicate condition forcesa full table scan on ta, which will retrieve the ta.c1 column values at the sametime, there is no need to use an index. In fact, an additional index accesswould decrease the query performance.Daniel Finkbhabani s pradhan wrote:> Hi All,>> Merry Christmas to all.>> I have this interesting problem..>> For this query index ind1 on (c1,c2) columns is getting used.> SELECT 'x'> FROM tab ta> WHERE ta.c1='val1';> (gives index ind1 range scan)>> But for>> SELECT 'x'> FROM tab ta> WHERE ta.c1='val1'> AND ta.c3 = 'val2';> (gives FTS)> index ind1 is not being used. c3 is a nonindexed column.>> I have already tried index(ta ind1) , RULE hints.>> The table and the index are analyzed.>> What cud be the reason for that?>> Regards,> B S Pradhan-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: Daniel W. FinkINET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).! Do you Yahoo!? Free Pop-Up Blocker - Get it now
RE: Delete vs. truncate to free up spaces.
And synonyms will have to be re-created. (drop and create). Grants will have to be given. Jared Still <[EMAIL PROTECTED]> wrote: ... and if your table is not partitioned, consider using'CREATE TABLE AS' with WHERE clause that eliminates the rows you wish to delete, recreate indexes and constraintson the new table, drop the old table, rename the new tothe old.Keep in mind that stored procedures and triggers that reference the table will need to be recompiled.JaredOn Mon, 2003-12-22 at 14:44, Jacques Kilchoer wrote:> This is one of the cases where a partitioned table can be of great use. What version of Oracle? Standard or Enterprise Edition?> With a partitioned table you can say> alter table ... drop partition ... ;> to easily get rid of a large chunk of data and release the space.> > See> Oracle9i Database Concepts Release 2 (9.2)> Part Number A96524-01 > Chapter 11> Partitioned Tables and Indexes> http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96524/c12parti.htm#464767> > or> > http://tinyurl.com/362ba> > > -Original Message-> Nguyen, David M> > I am using delete command to delete million records in several tables to free up space in tablespace. I understand delete command does not release unused spaces as truncate command but I could not use truncate to delete ALL records in table as I need to keep one month old of records in table. Please advise a better method I can use to free up spaces.> Thanks,> David> -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net> -- > Author: Jacques Kilchoer> INET: [EMAIL PROTECTED]> > Fat City Network Services -- 858-538-5051 http://www.fatcity.com> San Diego, California -- Mailing list and web hosting services> -> To REMOVE yourself from this mailing list, send an E-Mail message> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in> the message BODY, include a line containing: UNSUB ORACLE-L> (or the name of mailing list you want to be removed from). You may> also send the HELP command for other information (like subscribing).> -- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: Jared StillINET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing). Do you Yahoo!? Free Pop-Up Blocker - Get it now
Re: Strange behavior with dbms_stats...
The proc generates the 'exec dbms_stats ' statements for all the users. Are you saving the output and running it manually or not. IT would have the same statements that you run one by one. Jose Luis Delgado <[EMAIL PROTECTED]> wrote:
List...SunOS 5.8, Oracle 8.1.6 (and 8.1.7 too).I use the proc at the bottom to generate statistics.It seems to work, but if I check statistics with:select owner, table_name, num_rows, blocks,av_row_len,to_char(last_analyzed, 'MM/DD/ HH24:MI:SS')from dba_tables;the tables have the OLD last_analyzed time!but... If I execute ONE by ONE:exec dbms_stats.gather_schema_stats(ownname =>'PERFSTAT', cascade => TRUE);it works fine!!...So, am I doing something wrong?Any help?TIAJLcreate or replace procedure get_statistics ascursor get_users_list isselect usernamefrom dba_userswhere username != 'SYS' and username != 'SYSTEM';beginfor i in get_users_listloopdbms_output.put_line('execdbms_stats.gather_schema_stats(ownname
=>'||chr(39)||i.username||chr(39)||', cascade =>TRUE);');end loop;end;/__Do you Yahoo!?New Yahoo! Photos - easier uploading and sharing.http://photos.yahoo.com/-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: Jose Luis DelgadoINET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
Do you Yahoo!?
Free Pop-Up Blocker - Get it now
Re: CLONE db
I assume both OS are same (Linux). What you can do is rename the filenames using alter tablespace or alter database rename file. So 1) Copy database files, archive logs etc. to new machine 2) Startup mount 3) Rename files 4) Start recovery You could also take a backup controlfile to trace then change the file u get in udump to change the file names. Then use it to create a controlfile on the new machine. However using controlfile and creating a new controlfile means you can only do 'resetlogs'. 1) Copy database files to new machine 2) Startup nomount 3) Create controlfile 4) Start recovery Seema Singh <[EMAIL PROTECTED]> wrote: Hi,I have hotbackup for x date.I want to create clone to another box on Linux(Oracle9i).is it possible to clone with same instance name on diffrent box with diffrent file location?I think yes.Can someone send me correct steps how to clone on linux box?Thx-Seema_Get holiday tips for festive fun. http://special.msn.com/network/happyholidays.armx-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: Seema SinghINET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] tcity.com (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing). Do you Yahoo!? Free Pop-Up Blocker - Get it now
