Tom Kyte

Subscribe to Tom Kyte feed Tom Kyte
These are the most recently asked questions on Ask Tom
Updated: 18 hours 13 min ago

Writting DDL_EVENT Triggers

Mon, 2023-11-27 15:26
Do you have a full example of writing a ddl_Even trigger. I want to audit on a schema level any creates, alters, or drops of any objects....and write the actual calling syntax to a table. Oracle8 has the new feature of DDL_EVENT, and their is an example in the SQL Reference, but it just shows a pl/sql_block not how to extract the calling DDL. If their is not an easier way I would end up querying v$sql_text and looking for text with some ddl call in it. I would then write the text to a seperate table. Any other ideas or examples would be appreciated. Thanks
Categories: DBA Blogs

Unified Auditing on tables

Thu, 2023-11-23 20:06
We have been trying to enable auditing on few DDL activities in autonomous database. As per the following code we created, enabled and verified the audit configs. Everything looks normal to us, however none of the table create/drop activities (listed at the end) are not being tracked in <code>unified_audit_trail</code> table. We can only see audit management related activities like <code>DBMS_AUDIT_MGMT BEGIN dbms_audit_mgmt.flush_unified_audit_trail; END;</code> in <code>unified_audit_trail</code> table. I must be missing something, can you please help me navigate through it. <code> -- Connect to ADMIN user -- DB Version SELECT * FROM v$version; -- Verfiy Audit Status SELECT parameter , value FROM v$option WHERE parameter = 'Unified Auditing'; -- Enable immediate write mode BEGIN dbms_audit_mgmt.set_audit_trail_property(audit_trail_type => dbms_audit_mgmt.audit_trail_unified , audit_trail_property => dbms_audit_mgmt.audit_trail_write_mode ,audit_trail_property_value => dbms_audit_mgmt.audit_trail_immediate_write); END; / -- Validate audit configs SELECT * FROM dba_audit_mgmt_config_params; -- Create an audit policy CREATE AUDIT POLICY xx_ddl_test_policy PRIVILEGES CREATE ANY TABLE , ALTER ANY TABLE , DROP ANY TABLE WHEN 'SYS_CONTEXT (''USERENV'', ''SESSION_USER'') IN (''ADMIN'',''XXTST'')' EVALUATE PER STATEMENT ; -- Enable Audit Policy AUDIT POLICY xx_ddl_test_policy; -- Validate policy SELECT * FROM audit_unified_enabled_policies WHERE policy_name IN ('XX_DDL_TEST_POLICY'); SELECT * FROM audit_unified_policies WHERE policy_name in (SELECT policy_name FROM audit_unified_enabled_policies); -- Flush audit trail EXEC dbms_audit_mgmt.flush_unified_audit_trail; -- Connect to XXTST user -- Test Audit Policy CREATE TABLE xx_test (vc_col VARCHAR2(25)); INSERT INTO xx_test ('Test 01'); COMMIT; TRUNCATE TABLE xx_test; DROP TABLE xx_test; -- Verify audit trial SELECT * FROM unified_audit_trail ORDER BY event_timestamp DESC; </code>
Categories: DBA Blogs

Statistics Gathering using older version feature

Thu, 2023-11-23 20:06
Hello Connor/Chris, I am in the middle of troubleshooting a slow-running application maintenance job for a database that has been migrated/upgraded from on-premise 12.1.0.2 on linux x64 to Exadata Cloud @ Customer setup. Many of the "Top SQLs" that have regressed are internal SQLs generated by call to GATHER_TABLE_STATS call that is part of the application job. The internal SQLs are similar to below but for some reason are running 5-6 times slower on 19c database. I am running out of options to figure out what could be causing this but I was wondering whether there exists a way to replicate the behaviour of statistics gathering from 12.1.0.2 in 19c database. Example internal SQL that has regressed: <code>select /*+ full(t) no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring xmlindex_sel_idx_tbl opt_param('optimizer_inmemory_aware' 'false') no_substrb_pad */to_char(count("OWNER")),substrb(dump(min("OWNER"),16,0,64) ,1,240),substrb(dump(max("OWNER"),16,0,64),1,240), to_char(count("OBJECT_NAME")),substrb(dump(min("OBJECT_NAME"),16,0,64),1, 240),substrb(dump(max("OBJECT_NAME"),16,0,64),1,240), to_char(count("SUBOBJECT_NAME")),substrb(dump(min("SUBOBJECT_NAME"),16,0,64) ,1,240),substrb(dump(max("SUBOBJECT_NAME"),16,0,64),1,240), to_char(count("OBJECT_ID")),substrb(dump(min("OBJECT_ID"),16,0,64),1,240), substrb(dump(max("OBJECT_ID"),16,0,64),1,240), to_char(count("DATA_OBJECT_ID")),substrb(dump(min("DATA_OBJECT_ID"),16,0,64) ,1,240),substrb(dump(max("DATA_OBJECT_ID"),16,0,64),1,240), to_char(count("OBJECT_TYPE")),substrb(dump(min("OBJECT_TYPE"),16,0,64),1, 240),substrb(dump(max("OBJECT_TYPE"),16,0,64),1,240), to_char(count("CREATED")),substrb(dump(min("CREATED"),16,0,64),1,240), substrb(dump(max("CREATED"),16,0,64),1,240),to_char(count("LAST_DDL_TIME")), substrb(dump(min("LAST_DDL_TIME"),16,0,64),1,240), substrb(dump(max("LAST_DDL_TIME"),16,0,64),1,240),to_char(count("TIMESTAMP") ),substrb(dump(min("TIMESTAMP"),16,0,64),1,240), substrb(dump(max("TIMESTAMP"),16,0,64),1,240),to_char(count("STATUS")), substrb(dump(min("STATUS"),16,0,64),1,240),substrb(dump(max("STATUS"),16,0, 64),1,240),to_char(count("TEMPORARY")),substrb(dump(min("TEMPORARY"),16,0, 64),1,240),substrb(dump(max("TEMPORARY"),16,0,64),1,240), to_char(count("GENERATED")),substrb(dump(min("GENERATED"),16,0,64),1,240), substrb(dump(max("GENERATED"),16,0,64),1,240),to_char(count("SECONDARY")), substrb(dump(min("SECONDARY"),16,0,64),1,240),substrb(dump(max("SECONDARY"), 16,0,64),1,240),to_char(count("NAMESPACE")),substrb(dump(min("NAMESPACE"), 16,0,64),1,240),substrb(dump(max("NAMESPACE"),16,0,64),1,240), to_char(count("EDITION_NAME")),substrb(dump(min("EDITION_NAME"),16,0,64),1, 240),substrb(dump(max("EDITION_NAME"),16,0,64),1,240), to_char(count("SHARING")),substrb(dump(min("SHARING"),16,0,64),1,240), substrb(dump(m...
Categories: DBA Blogs

How to extract the tag value form the xml tag with colon

Tue, 2023-11-21 13:26
How to Extract the Tag value from xml which as tag name with colon. <code> <link>https://www.rba.gov.au/statistics/frequency/exchange-rates.html#JPY</link> <description>AUD/JPY representative rate as at 4.00 pm Eastern Australian time on 14 Nov 2023</description> <dc:date>2023-11-14T17:52:00+11:00</dc:date> <dc:language>en</dc:language> <dcterms:audience>general</dcterms:audience> <cb:statistics rdf:parseType="Resource"> <rdf:type rdf:resource="http://www.cbwiki.net/wiki/index.php/RSS-CB_1.2_RDF_Schema#Exchange-Rates"/> <cb:country>AU</cb:country> <cb:institutionAbbrev>RBA</cb:institutionAbbrev> <cb:exchangeRate rdf:parseType="Resource"> <rdf:type rdf:resource="http://www.cbwiki.net/wiki/index.php/RSS-CB_1.2_RDF_Schema#Exchange-Rates"/> <cb:observation rdf:parseType="Resource"> <rdf:type rdf:resource="http://www.cbwiki.net/wiki/index.php/RSS-CB_1.2_RDF_Schema#Exchange-Rates"/> <cb:value>96.64</cb:value> <cb:unit>AUD</cb:unit> <cb:decimals>2</cb:decimals> </cb:observation> <cb:baseCurrency>AUD</cb:baseCurrency> <cb:targetCurrency>JPY</cb:targetCurrency> <cb:rateType>4.00 pm foreign exchange rates</cb:rateType> <cb:observationPeriod rdf:parseType="Resource"> <rdf:type rdf:resource="http://www.cbwiki.net/wiki/index.php/RSS-CB_1.2_RDF_Schema#Exchange-Rates"/> <cb:frequency>daily</cb:frequency> <cb:period>2023-11-14</cb:period> </cb:observationPeriod> </cb:exchangeRate> </cb:statistics> </code> Tried with by inserting the xml as clob object. <code>SELECT XMLTYPE(t.xclob).EXTRACT('/rdf:RDF/item/cb:statistics/cb:exchangeRate/cb:baseCurrency/text()').getStringVal() FROM test_clob t; Getting dORA-31011: XML parsing failed ORA-19202: Error occurred in XML processing LPX-00601: Invalid token in: ' '/rdf:RDF/item/cb:statistics/cb:exchangeRate/cb:baseCurrency/text()' 31011. 00000 - "XML parsing failed" *Cause: XML parser returned an error while trying to parse the document. *Action: Check if the document to be parsed is valid.</code> Please suggest.
Categories: DBA Blogs

Oracle SE2 Licence NUP

Tue, 2023-11-21 13:26
Hello, i am very new at Oracle licensing issues and i am facing a situation with an Oracle SE2 Licence NUP. i am very confused how to count the number of NUP's i will have to purchase. is a NUP an explicit Oracle user i have created? what about system Oracle users ? i have to deal with a web/backend application using an Oracle user to connect to an Oracle instance. but this application is in fact creating parallel processes each connecting to Oracle with this user. how to count the needed NUP's in this case. is NUP licensing the right way to go ? thanks in advance for your help. regards. Laure
Categories: DBA Blogs

503 Service Unavailable - ORA-28001: the password has expired

Tue, 2023-11-21 13:26
hi sir i got this message when i try to run my app "503 Service Unavailable 2023-11-15T09:24:51.336389300Z | 8-GbuZcF3_cnIHgb1ul5Kg | ORDS was unable to make a connection to the database. This can occur if the database is unavailable, the maximum number of sessions has been reached or the pool is not correctly configured. The connection pool named: |default|lo| had the following error(s): ORA-28001: the password has expired" how can i solve it ? thanks
Categories: DBA Blogs

Question about Leading Hint and outer Joined tables

Mon, 2023-11-20 19:06
Hi Tom, I have 5 tables in a query, Tab1, Tab2, Tab3, Tab4 and Tab5 and I have the following query below. Select * From Tab1, Tab2, Tab3, Tab4, Tab5 Where Tab1.x = Tab2.y(+) and Tab1.x = Tab3.y and Tab3.x = Tab4.y(+) and Tab3.x = Tab5.y; Question 1. Can a table who's outer joined (in this case Tab2, Tab4) be a Driving table? Question 2. In the Join Order what happens to tables that are outer joined (in this case Tab2, Tab4). Do we only use tables that are inner joined in the Leading hint? Regards, Ravi
Categories: DBA Blogs

Pros and cons of DBLinks for application integration

Fri, 2023-11-17 17:46
Hello Tom, A colleague of mine is a big fan of service-oriented architectures and middle-ware. His view is that dblinks are 99.9% of the time a bad idea and that you should go via some sort of message layer in order to pass information between applications. In many circumstances I can see the value of this, particularly in heterogeneous environments, however we are an all Oracle shop and I don't see the value in writing a windows service to pull data from one database and insert it into another. What are your views on this? I realise this is not really a plsql question but I would be interested to hear your thoughts on this. Regards Alastair
Categories: DBA Blogs

Why does ROWID break when used with CONNECT BY, but only in a subquery?

Mon, 2023-11-13 22:06
Hi Tom! ROWID works in a subselect: <code>SELECT * FROM(SELECT rowid, d.* FROM DUAL d)</code> and it also works with connect by: <code>SELECT rowid, LEVEL FROM DUAL CONNECT BY LEVEL < 2</code> But when used with both: <code>SELECT * FROM(SELECT rowid, LEVEL FROM DUAL CONNECT BY LEVEL < 2)</code> then it throws: <code>ORA-01446: cannot select ROWID from, or sample, a view with DISTINCT, GROUP BY, etc.</code> I've stumbled on this, since I used CONNECT BY LEVEL as a substitute for generate_series in a view, which was used in an APEX calendar, which had "select ROWID" ticket by default.
Categories: DBA Blogs

sys_context expressions. Can they be indexed?

Thu, 2023-11-09 16:26
Dear Tom, I am trying to tune a query (12.1.0.1 version, 4 node RAC). The database appears to use DBMS_RLS policies. I see one context defined as: <code>CREATE OR REPLACE CONTEXT CRAMERSESSION USING CRAMER.PKGSESSIONCONTEXT /</code> CRAMER.PKGSESSIONCONTEXT code is obfuscated. In the explain plan, I see this: <code>74 TABLE ACCESS FULL TABLE CRAMER.SERVICEOBJECT Object Instance: 90 Filter Predicates: "SERVICEOBJECT2DIMOBJECT"=3 AND "RPPLANID"=TO_NUMBER(NVL(SYS_CONTEXT('CRAMERSESSION','PLANCONTEXT'),'0')) Cost: 124,425 Bytes: 2,898 Cardinality: 138 Time: 5</code> "RPPLANID"=TO_NUMBER(NVL(SYS_CONTEXT('CRAMERSESSION','PLANCONTEXT'),'0')) -- this repeats throughout the explain plan. This evaluates to 0: <code>select TO_NUMBER(NVL(SYS_CONTEXT('CRAMERSESSION','PLANCONTEXT'),'0')) from dual;</code> is there any way to index this expression? "RPPLANID"=TO_NUMBER(NVL(SYS_CONTEXT('CRAMERSESSION','PLANCONTEXT'),'0')) Thank you.
Categories: DBA Blogs

ROW_NUMBER over DESC date column gives wrong results when partitioned index is used

Thu, 2023-11-09 16:26
Hi, Please refer to the linked LiveSQL for the table, index, and data definition. I execute a query that uses ROW_NUMBER() OVER (ORDER BY SOME_DATE DESC). When a partitioned index on SOME_DATE DESC is used, the result contains wrong row numbers. The order of rows is correct only inside each partition, but partitions are in the wrong order. <code> SELECT ID, TO_CHAR(SOME_DATE, 'YYYY-MM-DD HH24:MI:SS'), ROW_NUMBER() OVER (ORDER BY SOME_DATE DESC) AS row_num FROM TEST_TABLE WHERE SOME_GROUP = 1 AND SOME_DATE < to_date('2023-11-08 15:00:00', 'YYYY-MM-DD HH24:MI:SS'); </code> Gives me wrong ROW_NUM: <code> ID TO_CHAR(SOME_DATE,' ROW_NUM ---------- ------------------- ---------- 3 2023-11-06 03:00:00 1 2 2023-11-06 02:00:00 2 1 2023-11-06 01:00:00 3 6 2023-11-07 03:00:00 4 5 2023-11-07 02:00:00 5 4 2023-11-07 01:00:00 6 10 2023-11-08 04:00:00 7 9 2023-11-08 03:00:00 8 8 2023-11-08 02:00:00 9 7 2023-11-08 01:00:00 10 </code> If I force a full table scan instead of an index scan with /*+ FULL() */ hint, the result is correct: <code> SELECT /*+ FULL(TEST_TABLE) */ ID, TO_CHAR(SOME_DATE, 'YYYY-MM-DD HH24:MI:SS'), ROW_NUMBER() OVER (ORDER BY SOME_DATE DESC) AS row_num FROM TEST_TABLE WHERE SOME_GROUP = 1 AND SOME_DATE < to_date('2023-11-08 15:00:00', 'YYYY-MM-DD HH24:MI:SS'); </code> Gives me: <code> ID TO_CHAR(SOME_DATE,' ROW_NUM ---------- ------------------- ---------- 10 2023-11-08 04:00:00 1 9 2023-11-08 03:00:00 2 8 2023-11-08 02:00:00 3 7 2023-11-08 01:00:00 4 6 2023-11-07 03:00:00 5 5 2023-11-07 02:00:00 6 4 2023-11-07 01:00:00 7 3 2023-11-06 03:00:00 8 2 2023-11-06 02:00:00 9 1 2023-11-06 01:00:00 10 </code>
Categories: DBA Blogs

Advanced Queuing: subscriber and dequeue

Thu, 2023-11-09 16:26
Hello T[o|ea]m, Once, a long time ago, you gave an example of queuing a message asynchronously using PLSQL. https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:8760267539329 I implemented this example myself and got it working. (I think) I understand the global principle of this example, but I certainly do not understand all implementation-details. These are my questions: - I suppose you chose the name of the procedure '<b>notifyCB</b>' yourself, this is not a fixed or prescribed name, right? - How did you choose the arguments for this procedure? Are these fixed or prescribed? - In the call <b>dbms_aq.register</b> this procedure (<b>notifyCB</b>) is specified, I do understand that and why. But I do not understand the transfer of the arguments into the procedure <b>notifyCB</b> because a 'call' of <b>notifyCB</b> is not to be found. Is this why you used the word 'automagically'? - English is not my native language (as you might have noticed). I looked up in the Oracle References to find what a '<b>context</b>' is. I hate these Oracle-texts and I still have no clue. Can you explain this in plain English? Why do you give it the value <b>hextoraw('FF')</b>? Is this '<b>context</b>' transfered to the <b>notifyCB</b>-argument '<b>context</b>'? (probably see previous question) - Perhaps this question is already answered in (one of) the previous question(s): Why does <b>notifyCB</b> have both an argument <b>payload</b> (raw) and <b>payloadl</b> (number)? It looks like that <b>payloadl</b> is not used. I hope I can come to the state that I can copy this example into my specific situation while fully understanding it.
Categories: DBA Blogs

Create trigger scripts are not captured

Tue, 2023-11-07 09:46
We are trying to create a DDL trigger to track all the scripts that are being run from a specific user or specific set of objects. We are using the following script to do so. This scripts works perfectly fine for all the objects but, it is not tracking any changes being done on triggers. Is it expected that trigger scripts cannot be tracked by a DB trigger? Note: We are trying these scripts from ADMIN account in ATP database. <code> CREATE TABLE xxy_atp_ddl_audit_log ( object_owner VARCHAR2(128) , object_name VARCHAR2(128) , object_type VARCHAR2(30) , operation VARCHAR2(30) , sql_text CLOB , sid NUMBER , sessionid NUMBER , os_user VARCHAR2(150) , user_module VARCHAR2(150) , hostname VARCHAR2(128) , ip_address VARCHAR2(150) , execution_user VARCHAR2(128) , execution_time TIMESTAMP ); </code> <code> -- Script to be run from SYS/ADMIN schema CREATE OR REPLACE TRIGGER xx_atp_ddl_audit_trg AFTER DDL ON DATABASE DECLARE l_complete_sql_statement CLOB; l_sql_statement_t ora_name_list_t; l_sql_line_count NUMBER; PRAGMA AUTONOMOUS_TRANSACTION; BEGIN -- We need to track all the change happening to custom schemas or custom objects -- We dont need to track truncate table as it doesnt impact any table structure. IF (ora_dict_obj_owner LIKE 'XX%' OR ora_dict_obj_name LIKE 'XX%') AND ora_sysevent != 'TRUNCATE' THEN dbms_lob.createtemporary(lob_loc => l_complete_sql_statement, cache => TRUE); l_sql_line_count := ora_sql_txt (l_sql_statement_t); FOR l_idx IN 1 .. l_sql_line_count LOOP dbms_lob.append(l_complete_sql_statement, l_sql_statement_t (l_idx)); END LOOP; INSERT INTO xx_atp_ddl_audit_log (object_owner , object_name , object_type , operation , sql_text , sid , sessionid , os_user , user_module , hostname , ip_address , execution_user , execution_time) VALUES (ora_dict_obj_owner , ora_dict_obj_name , ora_dict_obj_type , ora_sysevent , REPLACE(l_complete_sql_statement, CHR(0), '') , sys_context('USERENV', 'SID') , sys_context('USERENV', 'SESSIONID') , sys_context('USERENV', 'OS_USER') , sys_context('USERENV', 'MODULE') , sys_context('USERENV', 'HOST') , sys_context('USERENV', 'IP_ADDRESS') , sys_context('USERENV', 'SESSION...
Categories: DBA Blogs

How to find database objects different versions with edition-based redefinition

Tue, 2023-11-07 09:46
Hello, We are using EBR for deployment of DB objects. If any objects released in EBR in different editions need your help where to find the different versions(source code) of database objects (functions,procedure,package) stored in database as we are using EBR. Is there any table maintained for storing different versions of same object. Regards GirishR
Categories: DBA Blogs

How to upload multiple excel sheets into my oracle apex to create my application from it

Tue, 2023-11-07 09:46
Question: I am building a freelancing local website with Oracle Apex. I have all of my data in Excel spreadsheets, but I am not sure how to import them into Apex. I have multiple spreadsheets, one for each table in my database (e.g., users, freelancers, clients, jobs, proposals, contracts, etc.). The spreadsheets also contain foreign key relationships between the tables. What is the best way to import these spreadsheets into Apex? Is there a way to automatically create the tables and relationships from the Excel sheets? Are there any best practices or common pitfalls to be aware of when doing this? you can check my application sample tables here: [redacted] Thank you for your help. Sincerely,
Categories: DBA Blogs

Apex 23.1 minimum compatible parameter setting

Tue, 2023-11-07 09:46
Hi TOM, We are planning on upgrading from Apex 22.1 to Apex 23.1. We are currently on Oracle 19c using Apex 22.2: SQL> <code>SELECT * FROM v$version;</code> BANNER -------------------------------------------------------------------------------- BANNER_FULL ---------------------------------------------------------------------------------------------------- BANNER_LEGACY CON_ID -------------------------------------------------------------------------------- --------------- Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.16.0.0.0 Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production 0 1 row selected. SQL> <code>SELECT * FROM apex_release;</code> VERSION_NO ---------------------------------------------------------------------------------------------------- API_COMPATIBILITY ---------------------------------------------------------------------------------------------------- PATCH_APPLIED ---------------------------------------------------------------------------------------------------- 22.2.1 2022.10.07 APPLIED 1 row selected. From what I read here: https://docs.oracle.com/en/database/oracle/apex/23.1/htmig/apex-installation-requirements.html#GUID-02BE4A34-B631-412C-8A82-EB92DABBACE0 it states "Oracle APEX release 23.1 requires an Oracle Database release 19c or later". Based on the above query results and this statement, it would seem we are OK to upgrade, at least with regards to minimum Oracle Database release. I have a question, though. Our current compatible parameter is set to 12.1: SQL> <code>SELECT name, type, value FROM v$parameter WHERE name = 'compatible';</code> NAME TYPE -------------------------------------------------------------------------------- --------------- VALUE ---------------------------------------------------------------------------------------------------- compatible 2 12.1.0.2.0 1 row selected. Despite being on the minimum required Oracle DB release, would it be correct to assume we need to increase our compatible parameter setting? I'm guessing yes. If so, what is the minimum we can/should set it to? I'm guessing no less than 19.0.0, but please confirm, especially since I'm wondering if a compatible parameter of 12.2 would work. Thanks! SQL*PLUS-kinda'-guy
Categories: DBA Blogs

How to extract portion of an xmltype column

Mon, 2023-11-06 15:26
XML looks like this: <code><TaxRuleConditionVO> <TaxRuleConditionVORow> <ConditionGroupCode>SU CC 37,69</ConditionGroupCode> <TaxParameterCode>ACCOUNT</TaxParameterCode> <DataTypeCode>ALPHANUMERIC</DataTypeCode> <DeterminingFactorClassCode>ACCOUNTING_FLEXFIELD</DeterminingFactorClassCode> <DeterminingFactorCqCode null="true"/> <OperatorCode>=</OperatorCode> <RecordTypeCode>USER_DEFINED</RecordTypeCode> <IgnoreFlag>N</IgnoreFlag> <DateValue null="true"/> <AlphanumericValue null="true"/> <ValueLow null="true"/> <ValueHigh null="true"/> <CreationDate>2023-07-06 16:06:08.604</CreationDate> <LastUpdateDate>2023-07-06 17:04:14.235</LastUpdateDate> <CreatedBy>stuartd</CreatedBy> <LastUpdatedBy>stuartd</LastUpdatedBy> <LastUpdateLogin>FFC4CEC7678DF531E0534D60410A1325</LastUpdateLogin> <ProgramAppName null="true"/> <ProgramName null="true"/> <DeterminingFactorCode>LINE_ACCOUNT</DeterminingFactorCode> <NumericValueTrans null="true"/> <FlexFilterValue><?xml version="1.0" encoding="UTF-8"?> <FndFilter xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="http://www.oracle.com/apps/fnd/applcore/filter/FndFilter.xsd"> <KeyFlexFilter> <keyFlexfieldCode>GL#</keyFlexfieldCode> <structureInstanceCode>SUN</structureInstanceCode> <applicationShortName>GL</applicationShortName> <filterCriteriaRow> <filterCriteriaItem> <attributeName>sunCostcentre</attributeName> <columnName>SEGMENT2</columnName> <operator>EQUALTO</operator> <conjunction>AND</conjunction> <valueDataType>STRING</valueDataType> <value>85502</value> </filterCriteriaItem> <conjunction>AND</conjunction> </filterCriteriaRow> </KeyFlexFilter> </FndFilter> </FlexFilterValue> <TaxRuleConditionDetails null="true"/> </TaxRuleConditionVORow></code> The column I need lies between <value> and </value>, but this lies inside an xmltype column called Flexfiltervalue. How do I write the sql to extract that value and it's related ConditionGroupCode I have tried v<code></code>arious options select t.CONDITION_GROUP_CODE as ConditionGroupCode, instr((t.FLEX_FILTER_VALUE).getClobVal(),''), instr((t.FLEX_FILTER_VALUE).getClobVal(),''))as clobval from ZX_CONDITIONS t where t.condition_group_code = 'SU CC 50 1' THIS RETURNED THE WRONG VALUE SELECT t.FLEX_FILTER_VALUE.getStringVal() FROM ZX_CONDITIONS t THIS PRODUCED AN ERROR: ORA-19011: Character string buffer too small ORA-06512: at "SYS.XMLTYPE", line 169 select extract(t.FLEX_FILTER_VALUE, '/').getClobVal(), <'value>' from ZX_CONDITIONS t THIS GIVES ME MISSING EXPRESISON ERROR
Categories: DBA Blogs

Changed dropping behavior after assign another index to a primary key

Mon, 2023-11-06 15:26
Hi Tom, my question is about constraints in combination with indexes. I have a big amount of tables in multiple environments with primary keys and corresponding indexes like this: <code> create table Articles ( company_id number, article_id varchar2(30), article_description varchar2(500) ); alter table Articles add constraint Articles_PK primary key (COMPANY_ID, ARTICLE_ID) using index; </code> They have a good primary key, but a less good ordered index for querying. The queries contain article_id and company_id. Instead of creating new indexes with a better order, i want to change the existing one's, in order not to unnecessarily increase the number of unuesed indexes. All environments are running 24/7 and need their uniqueness on the tables. So i've readed the following article of Chris from July: https://blogs.oracle.com/sql/post/how-to-modify-constraints-in-oracle-database After this and some other research, i've decided to change them the following way: <code> create unique index Articles_IDX_TMP on Articles (ARTICLE_ID, COMPANY_ID); alter table Articles modify constraint Articles_PK using index Articles_IDX_TMP; alter index Articles_IDX_TMP rename to Articles_PK; </code> This keeps the uniqueness, keeps the order ot the primary key (which is important for other reasons), doesn't drop any foreign key constraints which use the primary key, drops the original index and replaces the new ordered index for the old one. Good till now. After this, i noticed following difference in dropping a primary key constraint. When i dropped an primary key constraint before, the corresponding index was dropped automatically. I executed the following command to drop the constraint: <code> alter table Articles drop constraint Articles_PK cascade; </code> and the database fired two commands autmatically: <code> drop index Articles_PK; alter table Articles drop constraint Articles_PK cascade; </code> I've seen this after logging all ddl with a trigger on schema level (after ddl on database). After my above three changes to replace the index of a primary key constraint, the command: <code> alter table Articles drop constraint Articles_PK cascade; </code> isn't dropping the index (index: Articles_PK) automatically any more. My question to you is: Is there any command to alter the index after this he will be automatically dropped with the drop primary key constraint togehter like before? How can i reorder the index (not primary key) and achieve the same behavior when dropping the primary key, the index will be dropped automatically with it? Or is there a better way to change them i haven't found? Thanks for your help, and a stress-free week.
Categories: DBA Blogs

Validate a column in a where clause

Mon, 2023-11-06 15:26
in a query to a table or to a view can we identify if a column is being used ? and based on that change the where clause ?
Categories: DBA Blogs

LiveSQL as (apex) app

Mon, 2023-11-06 15:26
Hi, we all love LiveSQL and use it a lot. But we would like to use it internally in our company as well. Is there a way to download the underlying apex app and or schema script(s)? If not, are there plans to do so? Maybe it's also a candidate for integration in the (APEX) App Gallery? Thanks for your answer!
Categories: DBA Blogs

Pages