Tom Kyte

Greetings, How does the data structure of a compound index in Oracle look like? I'm wondering how Oracle is able to use a "skip scan" by using the second entry in a compound index? I know that the first field uses a b-tree structure, but I am unsure how the second field is stored. I started to think about this recently since I have been supporting MongoDB. In MongoDB, it is not able to use a compound index if the prefix field isn't part of the query. I've added the following as optional information to show a little bit about how Mongo stores a compound index and to show why Mongo can't use the secondary field of a compound index because the value of the second field is just concatenated into the value of the first field. For instance, in Mongo, if we have the following data: "test" has these four docs/records: { x: 1, y: 2 } { x: 2, y: 3 } { x: 2, y: 1 } { x: 1, y: 4 } Next, we create a compound index: db.test.createIndex({x: 1, y: 1}) Then we search for records that have x=2 and y >= infinity and get explain plan: Mongo reports that only two index leaf keys were read. This means that Mongo stores entries in the b-tree leaf as: (field1:field2) Thanks for your help, John

I got a requirement to capture user login logout timestamp of database in any method like login via application, Sql development tool or any source. the purpose is to track all user login/logout information of database. We tried with logon and log off trigger but issue is log off Triger not fire when kill session or database shutdown. We tried to enable dba_audit_session but this table not capture session history because after database upgrade from 12c to 19c our DBA disabled audit session not sure about the reason. Now dba not able to enable again to fix this issue dba recommended to re-patch 19c upgrade but re-patch will impact current system behavior. So apart from these two option do we have any other options to captre user login/ logout timestap in database level. please help on this. Thanks, Vinoth kumar.J

Good Morning. My question is sending signed emails from oracle. We were hoping to utilize an oracle built in functionality but it seems oracle requires a hardcoded passwords. Even oracle support stated I had to hardcode the password. I am coming here because I have been provided incorrect information from them before and the knowledge from the community is amazing. Also didnt want to reinvent the wheel if a solution is already available. So does it require a hardcoded password to use SMTP package? We do have wallets configured. We have one configured for TLS login and would like to untilize that if possible. ?this was sample from oracle support <code>DECLARE mailhost VARCHAR2(64) := 'mailserverhostname'; sender VARCHAR2(64) := 'user@domainname'; recipient VARCHAR2(64) := 'user@domainname'; wallet_pwd VARCHAR2(64) := 'walletpassword'; <== this would never pass a security audit wallet_loc VARCHAR2(64) := 'file:/etc/ORACLE/WALLETS/'; user_name VARCHAR2(64) := 'myemailusername'; user_pwd VARCHAR2(64) := 'myemailuserpassword'; <== this would never pass a security audit mail_connection utl_smtp.connection; BEGIN -- Make a secure connection using the SSL port configured with your SMTP server -- Note: The sample code here uses the default of 465 but check your SMTP server settings mail_connection := utl_smtp.open_connection ( host => mailhost, port => 465, wallet_path => wallet_loc, wallet_password => wallet_pwd, secure_connection_before_smtp => TRUE ); utl_smtp.ehlo(mail_connection , mailhost); utl_smtp.starttls(mail_connection); utl_smtp.ehlo(mail_connection , mailhost); -- Call the Auth procedure to authorized a user for access to the mail server -- Schemes should be set appropriatelty for your mail server -- See the UTL_SMTP documentation for a list of constants and meanings UTL_SMTP.AUTH( c => mail_connection, username => user_name, password => user_pwd, schemes => 'LOGIN' ); -- Set up and make the the basic smtp calls to send a test email utl_smtp.mail(mail_connection, sender); utl_smtp.rcpt(mail_connection, recipient); utl_smtp.open_data(mail_connection); utl_smtp.write_data(mail_connection, 'This is a test message using SSL with SMTP.' || chr(13)); utl_smtp.write_data(mail_connection, 'This test requires an Oracle Wallet be properly configured.' || chr(13)); utl_smtp.close_data(mail_connection); utl_smtp.quit(mail_connection); exception when others then dbms_output.put_line('Error in the anonymous plsql block: '|| sqlerrm); end; /</code> Thnaks in advanced. Dave

Hi, I am dealing with dynamic sql statements that differ only in amount of "in list" elements and want to force a specific plan on them. Simplified example: <code>1) select * from tab1 t1 join tab2 t2 on t2.col2 = t1.col1 join tab3 t3 on t3.col4 = t2.col3 where t3.col5 in (:A1, :A2) 2) select * from tab1 t1 join tab2 t2 on t2.col2 = t1.col1 join tab3 t3 on t3.col4 = t2.col3 where t3.col5 in (:A1, :A2, :A3) 3) etc? </code> I understand that I can load any existing sql_id to baseline using sql_text with "%" or by specifying a module. But I want to take into consideration any future sql_ids with different "in lists" and force the same plan on them. Is there any way to force newly generated sql_id to use sql_text pattern with wildcard like "%"? Or maybe there is a way to substitute sql plan hash value with another plan? Let's say that CBO decides to use plan with hv abc1, but substitution forces it to use plan with existing hv abc2? I am very limited with things that I can do with statistics right now, so I am trying to find simple and effective tool similar to sql_patch, baseline etc.

I have a PL/SQL package and want to find that package's code coverage. I am using the DBMS_PLSQL_CODE_COVERAGE utility. I can get the code coverage for the whole PL/SQL package. But I want to get the coverage at the Function/Procedure level. The coverage tables generated by the above utility contain only PACKAGE and PACKAGE BODY objects. Can I get coverage data for FUNCTION/PROCEDURE objects? Now I know I can use PRAGMA COVERAGE to exclude unnecessary code lines so I can get coverage stats only for the method I need. But the problem is the package has a bit complex architecture, where some methods are duplicated as inner methods. So when I get the code coverage for the whole package, I have to merge these methods by string manipulation which is not an ideal way. When I refer to the ALL_OBJECTS table I can see, that there are FUNCTION level objects. But when I try to get coverage information at the FUNCTION level I can't find any data. How I can get the coverage information at the FUNCTION/PROCEDURE level? Thank you!

In 11g RAC, the init.ora file points the spfile in ASM, so sqlplus can find it. but in 19c, sqlplus can startup even without the spfile nor init.ora, how does that happen? (I understand when I startup with svrctl , it will create a init.ora automatically) Thank you.

Hi Team, I use ApEx 22.1 and SQL Developer. In an ApEx page, users have to upload 4 photos of their workstation. Then with PL/PDF I generate the PDF containing texts, photos and ID number. I have no control over the uploaded photos. The aspect ratio of the images can be 16:9 or 4:3, portrait or landscape orientation, JPEG, PNG or TIFF. The columns of the table are: ID, NAMEFILE, MIME, BLOB, WH but I don't know how to get the WH data (= Width x Height) of the image. Is it possible to know the dimensions of the image before uploading it or after, in reading the BLOB?

Hi ASKTOM team, I'm a beginner of APEX. In our system we have many differents DBs (different hosts) but they have same table structures. If user A choose in a DB-list DB1 and logged in, APEX server will connect to DB1 and uses all data from DB1 If user B choose DB2, APEX will do the same actions but use all data from DB2. ... Which Services/Methods APEX should we use in this situation?( in just one Application as well as 1 Page). It seems like we need a dynamic dblink : Select * from DUAL@db_link; If user A then db_link := DB1; If user B then db_link := DB2; Many thanks! Best regards, Giang Doan

Greetings, In this version SQL developer 23.x, when clicking on the "+" sign next to the database, it disappears, and the database does not open. However, in older versions like version 19, the database opens normally. I have tried many versions of Java, but the issue persists. Kindly assist me in resolving this problem. Attached are two images of the issue in the following links: https://drive.google.com/file/d/1r0V3HlsHZ0ZsuhzjzXrBPouSrLhJ_Tgf/view?usp=sharing https://drive.google.com/file/d/1aeqNFNJMqh71eK0_yZ0-vRaKhLG9wzV9/view?usp=sharing Thanks,

Hi!, What is the best way to defragment SYS.AUD$? It is possible to use DBMS_REDEFINITION.REDEF_TABLE ? Thank you in advance.

Hello Sir Is there any view avaiable through which we can know from which file our instance has started.Is it pfile or spfile.Without making any changes to file and then seeing it by opening it. Thanks

Tom I've been re-visiting my knowledge on NOLOGGING as a colleague asked me a question about it today and I hate to say I couldn't remember fully why the term is a tiny bit of a misnomer (there's no denying that there is a general confusion "out there" about NOLOGGING and its consequences for redo generation, recoverability etc.). Anyway, with NOLOGGING "switched" on for already created tables and indexes, it seems that all DML is logged unless you use direct path in some way. If I have a NOLOGGING table and INSERT without /*+ APPEND */ I get logging, and if I have a LOGGING table and INSERT with /*+ APPEND */ I get logging. I only get no logging when I have a NOLOGGING table with /*+ APPEND */. But I'm not sure about ALTER TABLE MOVE and ALTER INDEX REBUILD. If I leave the tables and indexes in NOLOGGING mode and then perform either of these rebuilds without specifying the NOLOGGING clause in the ALTER statement, will the action be logged? If the answer is yes, then would it make sense just to leave all tables and indexes in NOLOGGING mode anyway? Regards Adrian

The following is a simplified version that exhibits the behavior in question. There are two tables T and F. 1% of the rows of table T are indexed by T_IX. Though F has the same number of rows as T_IX, F has 1% of rows "in common" with T_IX. The cardinality are as follows. <code> T ~ 1M T_IX ~ 10K F ~ 10K </code> The query is a semi-join that selects the rows from T that are both indexed in T_IX and "in common" with F. <code> select id from "T" where exists ( select null from "F" where T.num = F.num ) / </code> This query correctly returns 100 (0.01%) of the 1M rows in T. <code> The optimizer's plan is either i) Nested Loops join with F and T_IX, then perform the Table Access on T. PRO: Table Access on T is after the join. CON: Nested Loops starts. ii) Full Scanning T_IX then performing the Table Access on T. This is then hash joined with F. PRO: Full Scan of T_IX and the Table Access on T is batched. CON: Accessed 9,900 more rows from T then we needed (99% inefficient). </code> How can I encourage the optimizer to get the best of both worlds? <b>That is, how can I encourage the optimizer to perform a hash <i>index</i> join between F and the index T_IX <i>first</i>, and then, after many (99%) of T's rowids have been eliminated via that join, perform a Table Access (preferably batched) on T?</b> Does such an access path even exist? Note: The example below adds the hints in order to reliably generate the execution plans for the purpose of this example and is slightly modified to fit the format of this forum (mainly columns of the explain plans are removed). This example shows the two explain plans and the difference in Table Access on T. The Nested Loops join does perform the Table Access of T after the join and only has to get 100 rows, but the Hash join does not. The Hash join performs the Table Access on T before the join and has to get 10,000 rows. <code> SQL>create table T ( id number 2 , num number 3 , pad char(2e3) default 'x' 4 ) 5 / Table created. SQL>insert into T ( id, num ) 2 with "D" as 3 ( select level id 4 from dual connect by level <= 1e3 5 ) 6 select rownum 7 , decode( mod( rownum, 1e2 ), 0, rownum ) 8 from "D", "D" 9 / 1000000 rows created. SQL>create index T_IX on T ( num ) 2 / Index created. SQL>create table F ( num number 2 ) 3 / Table created. SQL>insert into F ( num ) 2 with "D" as 3 ( select level id 4 from dual connect by level <= 1e2 5 ) 6 select rownum 7 from "D", "D" 8 / 10000 rows created. SQL>...

I activated audit on some just for unsuccessful connection attempts ( my purpose was finding who/what locks some users ). This is something I had already done many times, always fine. However on a 19 version I noted something I could not find an explanation for: It appears that an additional audit popped out of nowhere, a select audit on sys owned tables: HIST_HEAD$ and HISTGRM$ I had to change audit_trail parameter, so no chance that they are some kind of inheritance from previous audit ( and I truncated aud$ in order to have a clean start ). However I would never ever dream of touching sys owned tables, my first commandment is "you shall not touch sys onwed tables ( unless under oracle support supervision, of course and with the only exception of aud$ ). I perused docs and Metalink but I was unable to find any relevant info on this. On old 10 and 11 version I never saw this. Is this a new kind of feature of 19 version? I even tried do disable this audit, no luck, from inside the pluggable db it complains because the operation is not allowed, from the root it gives me another error ... I hope that my poor English is clear enough Have a nice day Mauro Papandrea

Hello, As far as I understand it, Oracle processes regular expressions according to the POSIX standard, but also supports expressions that originate from Perl. Currently I had some missleading results when searching for a space. Theoretically, this should be found by the Perl-like expression \s. As I understand it, this is also noted in the Oracle documentation (https://docs.oracle.com/en/database/oracle/oracle-database/19/adfns/regexp.html Table 10-5). However, this does not seem to work in my example. Is this a bug - or is there a reason for this (for me unexpected) result? Should I forget about the Perl expressions and use only the POSIX expressions instead? Intention: Looking for ORA-01555, followed by a colon, space oder new line. <b>Unexpected result (expression wasn't found in string)</b> <code>SELECT 1 AS EXPECTED_RESULT, REGEXP_COUNT ('ORA-01555 caused by SQL statement below', 'ORA-01555[:|\s|\n]') AS REGEXPCNT FROM DUAL;</code> <b>Expected result if using :space: instead of \s</b> <code>SELECT 1 AS EXPECTED_RESULT, REGEXP_COUNT ('ORA-01555 caused by SQL statement below', 'ORA-01555[:|[:space:]|\n]') AS REGEXPCNT FROM DUAL;</code> Best regards, Marian

Hello, Tom We have been facing the slow rollback after killing job of huge transaction, then we discovered several ways to speed up the rollback process. So one possibility is altering hidden parameter "_cleanup_rollback_entries" from default value (100) to be 400. However, I still in doubt about the origin of 400 value. I saw some couple documents also said increase the value to be 400. But no explanation why it must be 400? Therefore, Please answer inline questions below 1) Why the recommended value is 400, how come of this value? 2) If there is larger transaction than mentioned is killed, how I ensure that value (400) is still effective for speedup rollback process? Thank in advance

Hi, <code>CREATE TYPE phone_typ AS OBJECT ( country_code VARCHAR2(2), area_code VARCHAR2(3), ph_number VARCHAR2(7)); / CREATE TYPE phone_varray_typ AS VARRAY(5) OF phone_typ; / CREATE TABLE dept_phone_list ( dept_no NUMBER(5), phone_list phone_varray_typ); INSERT INTO dept_phone_list VALUES ( 100, phone_varray_typ( phone_typ ('01', '650', '5550123'), phone_typ ('01', '650', '5550148'), phone_typ ('01', '650', '5550192'))); / INSERT INTO dept_phone_list VALUES ( 200, phone_varray_typ( phone_typ ('02', '750', '5550122'), phone_typ ('02', '750', '5550141'), phone_typ ('02', '750', '5550195'))); /</code> I can execute below query <code>select * from table(select phone_list from dept_phone_list where dept_no=100)</code> I can't execute as below Is there a way to do this ? <code>select * from table(select phone_list from dept_phone_list) ORA-01427: single-row subquery returns more than one row</code> Thanks, Girish

We would like to encrypt data at rest in an oracle database column (in one specific database table only) using an encryption key held externally in a vault. Does Oracle provide standard interfaces to make API calls ? The encryption key should not persist in the database in any form.

Hi, I am carrying out an update on dept table with update query as - "update dept set dname = initcap(dname) where deptno=20;" As, I have not committed, row lock will be placed on the row. I want to find out the rowid of dept table locked from V$LOCK. Currently, I am not able to figure out how to get it. Is there any other way?

Hello, Could you please clarify the meaning of the view V$RESERVED_WORDS and especially its contents? At first glance, the documentation has a clear explanation. The view contains reserved words, and columns RESERVED, RES_TYPE, RES_ATTR, and RES_SEMI describe, for which purposes these words can or cannot be used. But more than 90% of words are not actually reserved. In my case (Oracle Database 19c) the view contains 2491 words, and 2361 of them have value 'N' in all columns. Even if we exclude duplicates, it is still 2339 words: <code>select count(*) from v$reserved_words where reserved = 'N' and res_type = 'N' and res_attr = 'N' and res_semi = 'N' and duplicate = 'N' ; COUNT(*) ---------- 2339</code> What is the risk of using these words as object names or as variables in PL/SQL?