Jump to content

Search the Community

Showing results for tags 'oracle'.



More search options

  • Search By Tags

    Type tags separated by commas.
  • Search By Author

Content Type



Find results in...

Find results that contain...


Date Created

  • Start

    End


Last Updated

  • Start

    End


Filter by number of...

Joined

  • Start

    End


Group


AIM


MSN


Website URL


ICQ


Yahoo


Jabber


Skype


Location


Interests

Found 8 results

  1. We have a very complex view that finishes execution only on first time, but hungs up when run from the same session second time. **complex view -** view with nested views, lots of inner and outer joins, multiple union all etc... But no locking statements, only pure read. $ sqlplus sys/system as sysdba; SQL> SELECT * FROM v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Release 11.2.0.3.0 - 64bit Production PL/SQL Release 11.2.0.3.0 - Production CORE 11.2.0.3.0 Production TNS for Linux: Version 11.2.0.3.0 - Production NLSRTL Version 11.2.0.3.0 - Production SQL> alter session set current_schema = DB_2017_2018; SQL> alter session set NLS_DATE_FORMAT = 'YYYY-MM-DD'; SQL> select 1 from complex_view where company_id = 1 and invoice_month = 1 and invoice_year = 2018; -- runs in 33 sec SQL> select 1 from complex_view where company_id = 1 and invoice_month = 1 and invoice_year = 2018; -- hangs up.. Ctrl + C after 3 minutes SQL> select * from complex_view where company_id = 1 and invoice_month = 1 and invoice_year = 2018; -- runs in 1m 30 sec SQL> select * from complex_view where company_id = 1 and invoice_month = 1 and invoice_year = 2018; -- hangs up.. Ctrl + C after 5 - 10 minutes -- ALL THESE COMMANDS / QUERIES RUN IN SAME SESSION I figured that running the exact same query second time is when the problem occurs. If I simply change any select column or where condition, then it runs for first time and hangs up from second time (see the above commands). Suspect 1 One thing I suspect from this is may be it's related to oracle's cache. So when I run the following commands before each query execution, I didn't get any query hang up. SQL> alter system flush shared_pool; SQL> alter system flush buffer_cache; Suspect 2 (root cause might be Suspect 1) Once any query hangs up, even if I run the same query in another session, it still hangs up. So If I kill the active sessions that's running the hang up query, then I can run the query successfully only for first time but stuck with same problem from second run. SQL> ALTER SYSTEM KILL SESSION '12,256' IMMEDIATE; AFAIK, only thing I can be sure is that something is badly wrong with oracle's `buffer_cache` and `shared_pool` in our version. This problem doesn't happen for tables or simple views, only for this complex view (didn't tested other complex views). Also we were facing this kind of strange issue only in last 2 - 3 weeks. It's hosted in cloud (AWS EC2), so it may be related to spectre / meltdown patches by Amazon as that's what changed in last 2 - 3 weeks (unrelated ?!). Or might be due to some db data threshold level reached in that period. Ready to provide any other analysis or metrics Post in DBA stackexchange
  2. Hi, I have a program working with xml files that is not performing anymore since we installed Oracle 12 databases. The error we suddenly have is this one : ORA-31011: XML parsing failed ORA-06512: at "MYUSER.MYPACKAGE", line 1663 ORA-19213: error occurred in XML processing at lines 2 LPX-00209: PI names starting with XML are reserved ORA-06512: at "SYS.XMLTYPE", line 272 ORA-06512: at "MYUSER.MYPACKAGE", line 1162 ORA-06512: at line 1 It seems there is a problem on line 2 of the xml. Indeed this file is particular. There is an xml declaration on each line and lines are pretty long, defining several xml blocks : <?xml version="1.0" encoding="utf-8"?><Flag:...... <?xml version="1.0" encoding="utf-8"?><Message:...... <?xml version="1.0" encoding="utf-8"?><Message:...... ..... <?xml version="1.0" encoding="utf-8"?><Message:...... <?xml version="1.0" encoding="utf-8"?><EndFile:...... In the program the first thing done is to create a blob file from this xml file (this is a function called at the beginning : FUNCTION TransformToCLOB(filename varchar2) RETURN CLOB) src_clob:=BFILENAME('MY_REPERTOIRE', filename); dbms_lob.createtemporary(dest_clob, cache=>TRUE); DBMS_LOB.FILEOPEN(src_clob); DBMS_LOB.LoadFromFile( dest_clob , src_clob ,DBMS_LOB.GETLENGTH(src_clob) ); dbms_lob.close(src_clob); RETURN dest_clob; after that, there is this instruction (ligne 1162 where the error occurs): v_xml:=XMLTYPE(dest_clob); What has changed in Oracle 12 that causes the problem? I have the feeling that several declarations in one file are not welcome anymore.
  3. Hi Guys, I am new to Oracle. In my Dataguard configuration on a 12c environment, when I perform a switchover from primary (foo) to standby (bar) and then try to SQL plus in with user@foo, I receive the "ORA-011033: ORACLE initialization or shutdown in progress" error instead of being able to connect to the new primary(bar). Now, I know that this is because the connection is still pointing to the foo address instead of the bar address, and according to many articles, you need to create a new "virtual" service to handle the different connections, as well as a trigger to stop/start this service after a rolechange. I did this and with the service_names parameter set to this new "virtual" service, a switchover will work and can also switch back but when trying to SQL plus with user@foo it throws ORA-011033. The trigger is useless and the service continues to run on both foo and bar because it seems that having that parameter set overrides whatever the trigger tries to do. Now if I don't define that parameter, my switchover works fine, and the triggers do their job, and when I sqlplus user@foo, it will correctly redirect me to the bar address. However, when I try to switchover back to foo, I receive the following in the DG alert log: Redo transport problem detected: redo transport to database foo has the following error: ORA-16047: DGID mismatch between destination setting and target database 08/05/2016 16:01:02 Initialization of connection failed. Expected destination db_unique_name is foo. Database actually reached is bar Failed to send message to site foo. Error code is ORA-16642. Data Guard Broker Status Summary: Type Name Severity Status Configuration foo_dg Warning ORA-16607 Primary Database bar Error ORA-16778 Physical Standby Database foo Error ORA-16642 ON FOO: log_archive_dest_2='service="bar"','ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="bar" net_timeout=30','valid_for=(online_logfile,all_roles)' fal_client='foo' fal_server='bar' ON BAR: log_archive_dest_2='service="foo", ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="foo" valid_for=(online_logfile,all_roles)' fal_client='bar' fal_server='foo' Entries in LDAP: foo: (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=foo_home)(PORT=15940))(ADDRESS=(PROTOCOL=TCP)(HOST=bar_home)(PORT=15940)))(CONNECT_DATA=(SERVICE_NAME=virtual_foo)(FAILOVER_MODE=(TYPE=SESSION)(METHOD=basic)))) bar: (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=bar_home)(PORT=15940)))(CONNECT_DATA=(SERVICE_NAME=bar))) I'm not sure what I'm missing or misunderstanding. Can anyone shed any light as to what I might be doing wrong? Can provide more info if needed. Thanks & Regards Camillelola
  4. sundara

    Huge SQL data Insert

    I have couple of tables that I need to use to load a 3rd table. The total number of rows to load comes around 30 million. My Insert SQL query is a INSERT-INTO-<TABLE>-SELECT. My Insert SQL query hangs (Waited for 30 mins before killing the session). But when I used ROWNUM in the SELECT query with the ROWNUM value equal to the total number of result rows, the INSERT query ran in 43 seconds. And, both the results are consistent. I am completely lost here. Can you please throw your expert opinion on why the INSERT query without ROWNUM hangs and the INSERT query with ROWNUM runs much faster ? Details : TABLE - A : has a total of 50,000,000 rows; No index TABLE - B : has a total of 200,000 rows; Index present in COLUMN1 (Relation with TABLE-A is one to maasny) TABLE - C : Output table Original INSERT query: INSERT INTO C SELECT COLUMN1 FROM A WHERE COLUMN2 IN (SELECT COLUMN1 FROM B); INSERT query with ROWNUM : SELECT COUNT(*) FROM A WHERE COLUMN2 IN (SELECT COLUMN1 FROM B); -- No issues with this query INSERT INTO C SELECT COLUMN1 FROM A WHERE COLUMN2 IN (SELECT COLUMN1 FROM B) AND ROWNUM <=30,000,000;
  5. I saw on the internet, some oracle duplication commands used multi channels. Their log looks like: ... using channel ORA_AUX_DISK_1 using channel ORA_AUX_DISK_2 using channel ORA_AUX_DISK_3 using channel ORA_AUX_DISK_4... However, when I perform duplication, the rman uses only one channel ORA_AUX_DISK_1. And the performance is very bad. Please tell me how to configure rman when backup to use multi channels to improve duplication performance when duplication. (I duplicate oracle database without target connection.) Thanks.
  6. Lianamelissa

    oracle export and import

    Hi, this is liana working on oracle goldengate in a level 3 company in my company we use two oracle data bases we consider as D1,D2, every day after completion of my work i had exported to D1 again get back from D2 after when i used it, this is the correct procedure i am fallow are not.
  7. i just want to execute this in oracle form 6i but it will give me an error like ORA-00600: internal code error DECLARE stock_price NUMBER := 9.73; net_earnings NUMBER := 0; pe_ratio NUMBER; BEGIN -- Calculation might cause division-by-zero error. pe_ratio := stock_price / net_earnings; DBMS_OUTPUT.PUT_LINE('Price/earnings ratio = ' || pe_ratio); EXCEPTION -- exception handlers begin -- Only one of the WHEN blocks is executed. WHEN ZERO_DIVIDE THEN -- handles 'division by zero' error DBMS_OUTPUT.PUT_LINE('Company must have had zero earnings.'); pe_ratio := NULL; WHEN OTHERS THEN -- handles all other errors DBMS_OUTPUT.PUT_LINE('Some other kind of error occurred.'); pe_ratio := NULL; END; -- exception handlers and block end here
  8. We are facing issue while accessing master form in forms 11g. Generally, we have have a master form which requires DB credentials, which get successfully verified and the master form is displayed. ISSUE : --> Randomly the users who try to access this master form (the first form) are shown an applet/form window where it prompts user to enter the password of the database user, using which the form session establishes it's connection to the database. So this frustrates the user, because obviously you can't expect a user or anyone to view a login prompt asking password of that particular DB user. Attached document will help everyone understand better. Since we are approaching month end, it will be fantastic if someone can come up with useful suggestion or solution. STEPS WHICH ARE FOLLOWED FOR LOGGING ONTO THE FORMS APPLICATION.docx
×