DBA Blogs

CLOB column over DB Link

Tom Kyte - Wed, 2021-09-15 22:26
Hi Tom, We have a query which has to get a set of rows over the db link which has a CONTAINS predicate on a column of CLOB. E.g. There is a DB A and DB B with table T1( c1 varchar2(10),c2 clob) . I want to run a query from DB A using dblink on DB B to query T1 which has a CONTAINS predicate on c2 and based on the query the rows have to return c1 from t1. Can you suggest anyway in which we can get this done. We have a couple of restrictions though: WE cant insert into DB A from DB B. Table T1 contains around a 100 thousand rows and has around 20 columns. Thanks Ramkumar
Categories: DBA Blogs

How to Call Fusion REST api in PLSQL using apex web service

Tom Kyte - Mon, 2021-09-13 15:26
Hi, I'm trying to consume Oracle fusion rest web service's in PLSQL using apex_web_service. I'm getting the below error. could you please guide me on this. also Pls suggest how to enable the basic authentication in web service like how to set the basic auth. ORA-20987: APEX - One or more cookies set in apex_util.g_request_cookies contain an invalid value. - Contact your application administrator. Details about this incident are available via debug id "49001 <code> CREATE OR REPLACE FUNCTION ATP_REST RETURN CLOB AS l_clob CLOB; l_result VARCHAR2(32767); BEGIN APEX_WEB_SERVICE.g_request_cookies.delete(); APEX_WEB_SERVICE.g_request_cookies(1).name := '';---i'm passing the username (baisc auth) APEX_WEB_SERVICE.g_request_cookies(1).value := '';---pwd l_clob := APEX_WEB_SERVICE.make_rest_request( p_url => 'https://ehpv-dev8.fa.em2.oraclecloud.com/bpm/api/4.0/tasks', p_http_method => 'GET' -- p_parm_name => APEX_UTIL.string_to_table('p_int_1:p_int_2'), -- p_parm_value => APEX_UTIL.string_to_table(p_int_1 || ':' || p_int_2) ); DBMS_OUTPUT.put_line('l_clob=' || l_clob); RETURN l_clob; END; / </code> Regards, Praveen Paulraj
Categories: DBA Blogs

Oracle 12c EMON processing features

Tom Kyte - Mon, 2021-09-13 15:26
I read the article, titled: <b>Event Monitor Process: Architecture and Known Issues (Doc ID 105067.1)</b> ? Notification implements a high-watermark scheme where when the backlog of notification events hit an in-memory limit the messages are, in the case of 9.2, spilled onto disk into the SYS.AQ_EVENT_TABLE_Q queue if a watermark has been set. In 9.2 the default value for the watermark is 0 which means that the no messages will spill onto disk and the entire shared pool could be used for notification events. In 10.1 onwards the procedures DBMS_AQADM.GET_WATERMARK and DBMS_AQADM.SET_WATERMARK are available to set the amount of memory available for notification events but the messages are no longer spilled onto disk. Instead the enqueueing process are subject to flow control until the backlog has been cleared by the emon process. ? What does it mean for me as a developer? I?ve a 12.2 EE database under Linux(64 bit). I registered a notification callback procedure. How can I know , when enqueuing a message to the queue, that my message will be approved by EMON, or it will be placed to the backlog? And why should I know that? Does ?until the backlog has been cleared by the emon process? mean that entire backlog will be lost without any further processing by EMON? I noticed many times that there are messages in the queue and in the same time EMON?s job PLSQL_NTFN hangs in state ?Waiting for messages in the queue?. And these messages seems to be never processed. May be it is the case of clearing backlog? And where can I find an information (in server logs, in database objects) about clearing backlog and about MSGId?s of messages that had been cleared? TIA, Andrew.
Categories: DBA Blogs

INLIST ITERATOR

Tom Kyte - Mon, 2021-09-13 15:26
Hi Tom, Some questions about the SQL tuning. 1) I found that when using "IN" in the where clause , INLIST ITERATOR is shown on the explain plan in a cost-based database (and using the index correctly, the response is fast). However, no such INLIST ITERATOR in rule-based (and using the full table scan, the response is slow). Is INLIST ITERATOR only occur on cost-based? Is it possible to force the optimizer to use INLIST ITERATOR in a rule-based database (without any hints added to the SQL statement or using alter session set optimizer_mode = choose)? Or is it possible to rewrite the "IN" to other forms such that the index can be used in rule-based database? I have tried to rewrite "IN" to "OR" but the index still cannot be used. The only way the index can be used is using UNION ALL the values of "IN". 2) If the database is rule-based (optimizer_mode=rule), and the table has statistics, will Oracle use cost-based to answer the query? I rememeber that Oracle will use rule-based if the optimizer_mode is set to rule (from Oracle documentation), no matter whether the table has statistics. But I found that in some situation Oracle will use cost-based. Thanks, David
Categories: DBA Blogs

Questions on Locking

Tom Kyte - Fri, 2021-09-10 14:06
Hi Tom, Here are the few questions on Locking. 1)How do I use v$lock to figure out lock details ? Details like whos has locked,on which table,who else is waiting, and what type of locking (shared or exlusive ?), since when it is locked. 2)Why and when exclusive/shared locks are used ? Could you give me example please ? Thanks for the answer, I understand better. 3)Could you tell me significance of v$mystat and v$locked_object ? Thank you . 4) Do we have page level locking in Oracle ? Thank you Thanks in advance, Gururaj
Categories: DBA Blogs

Memory used for decode, regexp

Tom Kyte - Thu, 2021-09-09 19:46
Hi Tom Want to understand how and where memory is allocated in an instance to evaluate decode, regexp functions in oracle? If we are reading data from a table and writing it any other location(not oracle), then what would be better-->while firing the query with regexp function in where clause OR taking the data out of oracle without regexp filter and applying regexp in some other server. E. G. Select col1, col2 from table1 where regexp_like(col1, '[^a-zA-Z0-9]') and date_col between to_date('08/02/2021 00.00.00') and to_date('08/02/2021 06.00.00') OR Select col1, col2 from table1 where date_col between to_date('08/02/2021 00.00.00') and to_date('08/02/2021 06.00.00')
Categories: DBA Blogs

Async in managed ODP.NET

Tom Kyte - Thu, 2021-09-09 01:26
When will Oracle be introducing proper async support into the managed provider?
Categories: DBA Blogs

Update all listeners to new Oracle Home after upgrade

Tom Kyte - Thu, 2021-09-09 01:26
Is there a way to update all listeners to a new OHome after upgrade? Going in manually to each listener (say 10) to update the new OHome seems like there should be an easier way but I'm unable to locate anything on it.
Categories: DBA Blogs

Reorganization Within Same Tablespace and To New Tablespace

Tom Kyte - Thu, 2021-09-09 01:26
Hi Tom, Just want to ask is there any difference between doing objects(tables,indexes and etc) reorganization within same tablespace and to new tablepsace? Normally i used to do reorganization to another tablespace but this method will need extra space to move the objects before we able to shrink old tablespace. Recently i used to do reorganozation within same tablespace to reuse the space but seem the reorganization within the same tablespace need to start reorg the objects in end of the blocks so that we able to shrink the size of the tablepsace. There is some incident where i need to reorg big objects few times before it able to move to below blocks and let us to resize. Just wanted whether there is any other differences between reorganization within the same tablespace and to new tablespace. Thanks.
Categories: DBA Blogs

DBSAT running on Amazon Linux

Tom Kyte - Wed, 2021-09-08 07:06
For Mr. Lopes: I have recently discovered the DBSAT, but my site runs Oracle instances off of the Amazon cloud platform. Are there any known issues for running DBSAT on Amazon Linux 2? Thanks.
Categories: DBA Blogs

Object reference in DBA_TAB_PRIVS but no object in DBA_OBJECTS

Tom Kyte - Wed, 2021-09-08 07:06
I have found records in DBA_TAB_PRIVS that do not have corresponding records in DBA_OBJECTS. <code>select * from dba_tab_privs where owner='ODSMGR' and table_name like 'BIN$%';</code> Returns privileges defined against a table. <code>select * from dba_objects where owner='ODSMGR' and object_name like 'BIN$%';</code> Returns no records. There is a process in this database that is trying to process REVOKE statements for the privs it finds in dba_tab_privs, but the statements are blowing up because the object doesn't seem to exist (in DBA_OBJECTS). How can I process these REVOKE statements (to get them out of DBA_TAB_PRIVS) when they don't show up in DBA_OBJECTS? Thanks
Categories: DBA Blogs

XML to JSON with nested XML elements

Tom Kyte - Wed, 2021-09-08 07:06
Hi, For below query, i am able to extract the header, but need the lines array in the same sql as json output. DB Version: 12.2.0.1.0 <code>SELECT JSON_OBJECT('hdr_id' VALUE hdr_id ,'prno' VALUE prno ) FROM XMLTABLE( '/hdr' PASSING XMLTYPE('<hdr> <hdr_id>2238770</hdr_id> <prno>64922</prno> <creation_date>2021-09-01</creation_date> <status>in process</status> <lines> <line> <line_id>2618885</line_id> <line_num>1</line_num> <item_description>Test1</item_description> <uom>each</uom> <unit_price>400</unit_price> <quantity>1</quantity> </line> <line> <line_id>2618886</line_id> <line_num>2</line_num> <item_description>Test2</item_description> <uom>each</uom> <unit_price>555</unit_price> <quantity>1</quantity> </line> </lines> </hdr>') COLUMNS hdr_id VARCHAR2(20) PATH 'hdr_id', prno VARCHAR2(20) PATH 'prno' );</code> Desired Output: <code>{ "hdr_id": "2238770", "prno": "64922", "lines": [ { "line_num": 1, "item_description": "Test1" }, { "line_num": 2, "item_description": "Test2" } ] }</code>
Categories: DBA Blogs

IP address in non-numerical formal

Tom Kyte - Tue, 2021-09-07 12:46
Hi Team, I am getting the below out put in Oracle 11g express edition. <code>SQL> SELECT UTL_INADDR.get_host_address from dual; GET_HOST_ADDRESS -------------------------------------------------------------------------------- fe80::30aa:1e8f:9d5b:d570%13</code> Requesting your assistance to convert the above into the regular nuerical format. Regards Kalyana Chakravarthy
Categories: DBA Blogs

Split a new line after 30 character and split with meaning full word

Tom Kyte - Tue, 2021-09-07 12:46
how to split new line after 30 character and split character with meaning full word like ex: 1 Split a new line after 30 char acter ex 1 split character new line but not proper word. I need the result like this Split a new line after 30 character
Categories: DBA Blogs

Autocommit in Cloud Applications (SQL Developer Web and Apex)

Tom Kyte - Tue, 2021-09-07 12:46
Hi. Is there any way to dissable autocommit in Apex or SQL Developer web? I tried with "set autocommit off" but it does not work. <code>select * from t_test</code> <code>no items to display</code> <code>insert into t_test values (1,2)</code> <code>1 row inserted</code> <code>rollback</code> <code>select * from t_test</code> ... and it shows the row inserted previously Please find my question in stackoverflow: https://stackoverflow.com/questions/68930342/dissable-autocommit-in-sql-developer-web-autonomous-database-oracle-cloud?noredirect=1#comment121820595_68930342 Thanks
Categories: DBA Blogs

In-Memory Column Store affects results of query.

Tom Kyte - Tue, 2021-09-07 12:46
Experienced this across 3 different instances now. On 19.11.0.0. When IMCS is enabled on a partition which is composite-partitioned. Partition is on a DATE column and SUB-PARTS are on a VARCHAR2 column. IMCS enabled on the latest partition changes the query plan such that INMEMORY FULL Scan is used incorrectly and retrieves no results. The problem manifests if the query uses either just a string like '01-SEP-21' or even the TO_DATE('01-SEP-21','DD-MON-YY') function. However, using the same query returns the correct results when either (a) INMEMORY is disabled ( Hence confirming my suspicion that its not jus the literal date format used ) OR (b) The query is changed to use the literal like DATE '2021-09-01' It does look like the use of implicit string-to-date conversion with INMEMORY is interracting in some way
Categories: DBA Blogs

JSON_EXISTS/ VALUE

Tom Kyte - Mon, 2021-09-06 18:26
Hi, I have the following JSON_OBJECT Tablename: trans_history <code> [ { "stepname":"CreateTransFromPain", timestamp:"2021-09.." }, { "stepname":"TransacionDuplicateValidation", timestamp:"2021-09.." }, . . . ] </code> Iam using Oracle 12.1.0.2.0 Enterprise What I need now is to check if the "stepname" -> "XXX" is existing in the DataSet. Either we give the "xxxx" out or just a True or False.
Categories: DBA Blogs

All combinations from 4 columns values in a table

Tom Kyte - Mon, 2021-09-06 18:26
Hello Tom, I am looking for a solution in oracle to create all the possible combinations from 4 different columns values. <code> NAME ID CODE POS_4 POS_5 POS_6 ABC 18 SAJ A null null ABC 18 SAJ null N null ABC 18 SAJ null A null ABC 18 SAJ null B null ABC 18 SAJ null F null ABC 18 SAJ null null H ABC 18 SAJ null null 03 ABC 18 SAJ null null null ABC 18 SAJ null null null So combination I am asking for would be like- A-N-H A-N-03 A-A-H A-A-03 A-B-H A-B-03 A-F-H A-F-03 </code> Could you please help me with the idea or a sample code to achieve this in oracle.
Categories: DBA Blogs

Get sequencial numbers from table and locking

Tom Kyte - Mon, 2021-09-06 18:26
Dear Tom, we have a table with records for different invoice types. One column contains the number of the last invoice. We need a function that locks the record counts up by one and then stores the new number ensuring that no other session gets the same number. We have this code <code> lock table codif_vdok_nr in row exclusive mode; begin select t.co_donr_nr+1,t.co_donr_jahr into p_doku_nr,p_jahr from codif_vdok_nr t where t.co_donr_vser_id = p_vser_id and t.co_donr_von <= trim(sysdate) -- 21.02.2009 and nvl(t.co_donr_bis,sysdate) >= trim(sysdate) -- to_date(sysdate,'dd.mm.yyyy') for update nowait; exception when others then po_message := 'doku_nr: Fehler Suche DokumentNr - Serie: ' || to_char(p_vser_id) || ' ' || sqlerrm; raise_application_error(-20010,po_message ); end; update codif_vdok_nr t set t.co_donr_nr = p_doku_nr where t.co_donr_vser_id = p_vser_id and trunc(t.co_donr_von) <= trunc(sysdate) and trunc(nvl(t.co_donr_bis,sysdate)) >= trunc(sysdate); </code> but it happened that two sessions got the same invoice number. Could you please tell me where the error is or how it could be solved. Regards Andreas
Categories: DBA Blogs

Is "Clustering Factor" a Misnomer?

Tom Kyte - Mon, 2021-09-06 00:06
Is Oracle's term "clustering factor" a misnomer? "Clustered" means together, so a high clustering factor should imply rows being together in the same block, but it turns out to be the opposite.
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs