Tom Kyte

The maximum number of cores before needing RAC licensing is per Cluster or for the sum of the Clusters?

How many autonomous db we could have for ExaCC full Rac?

Is there a way to create a index for a xpath that is including a xpath function? Please consider that xmltype index creation fails at oracle livesql.

Hi, We have Oracle 12C on 2 instances. I know GV$license can give maximum number of concurrent sessions since start of instances. But is there a way to get maximum we had accessing the database from both together ? Syed

Hi, <i>Background and Requirement</i> - I am working for a firm that uses <b>Oracle SQL Developer</b> for Data Cleaning and Manipulation of the data residing in the Oracle Database. We use <b>Microsoft Azure</b> for complete lifecycle management and work planning. So, we decided to use an <b>Azure-hosted cloud Git Repository</b> to host our code remotely and leverage its version control capabilities. We have a Git repository on Azure and are trying to clone the same in Oracle SQL Developer. <i>Steps followed to fulfill the requirement</i> - The following steps were followed for cloning the existing remote repository in Oracle SQL Developer. 1. Go to Teams Menu. 2. Hover over Git. 3. Select Clone option. 4. After the Clone from Git wizard opens up, entered the correct Repository URL, Username and password. 5. We work on a VPN so, I have set the corresponding proxy settings too. When testing the proxy, it gives a success message. (So, no issue in the proxy settings) 6. Click next to fetch remote repository branches. An error appears at this stage. <i>Error that occured</i> - A popup with the title <b>Validation failed</b> and the content as https://<remote repo url>/_git/<remote repo name>:cannot open git-upload-pack appears. <i>Troubleshooting Methods Tried</i> - The following troubleshooting methods have been tried. 1. A lot of troubleshooting methods online suggested that the Local git config has sslVerify set to false could help. Did that, no gain. 2. Tried cloning my personal git repository to test the working of the Git integration on Oracle SQL Developer. It was able to successfully fetch the remote branches. Hence, the error is coming up only while cloning an Azure Repository. 3. Looked at almost all the solution links online, but most of them were for Eclipse. Since both Eclipse and SQL Developer are Java-based applications, I tried doing those resolutions but most of them are regarding SSL Verify setting to false. At the end I have raised the issue here. Hoping to find some help here. Thanks in advance.

Hi Tom and Team, I guess that this issue is related to the namespace, but as I don't know well this, Could you help me to solve the error running this Select, please? <code>with testTable as ( select xmltype ('<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"> <soap:Body> <ns5:MT_Consulta_pedidos_pagamento xmlns:ns2="urn:Cpy.com/Model/ConsultaPedidosDevolucao/v0" xmlns:ns3="urn:Cpy.com/Model/AtualizaStatusPagamento/v0" xmlns:ns4="urn:Cpy.com/Model/AtualizaItensDevolvidosCancelados/v0" xmlns:ns5="urn:Cpy.com/Model/ConsultaPedidosPagamento/v0"> <codigo_empresa>&Empresa</codigo_empresa> <numero_pedido_venda>&Pedido</numero_pedido_venda> <codigo_loja>&Loja</codigo_loja> <numero_componente>&Componente</numero_componente> </ns5:MT_Consulta_pedidos_pagamento> </soap:Body> </soap:Envelope>' ) xml_val from dual ) select xmlquery('/soap' passing xml_val returning content) as dados from testTable;</code>

The latest Oracle docs has the following design tip: 9.13.5.2 Circular Dependencies Among Types Avoid creating circular dependencies among types. In other words, do not create situations in which a method of type T returns a type T1, which has a method that returns a type T. https://docs.oracle.com/en/database/oracle/oracle-database/21/adobj/design-consideration-tips-and-techniques.html Attached is a link to LiveSQL that exhibits a very simple circular dependency that will likely have issues recompiling during a datapump. Assuming we already have a large application that the compiler is having issues with is there a query we can use to find instances where T1 references T2 and T2 references T1? We would also need to find them a few generations apart (T1 references T2, T2 references T3, T3 references T1). The reference may be either in an attribute (REF) or a subprogram (parameter or return type). This would allow us to find what types may need to be changed to be brought in line with the latest documentation. Thanks in advance for your help.

Is FORCE_LOGGING enabled at CDB level in ADB-S? I checked that FORCE_LOGGING was not enabled at the PDB level and the Tablespace level.

Hi Tom ? My task: move several dozen text file imports from SQLLDR (on AIX) into callable PL/SQL procedures. The text files are static in structure with daily refreshes of the contents. The contents are loaded into individual tables in our 19c EE database. The solution appeared to be external tables, so I created a proof-of-concept example that worked as expected as stand-alone code. So far, so good: <code>SELECT * FROM all_directories WHERE directory_name = 'CONNECT2'; -- returns /connect2. CREATE TABLE MY_EXT_TBL ( CUSIP VARCHAR2(25 BYTE), DESCRIPTION VARCHAR2(200 BYTE), QTY NUMBER(18,5), ACCOUNT VARCHAR2(100 BYTE) ) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY CONNECT2 ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE BADFILE CONNECT2:'MY_EXT_TBL%a_%p.bad' LOGFILE CONNECT2: 'MY_EXT_TBL%a_%p.log' DISCARDFILE CONNECT2: 'MY_EXT_TBL%a_%p.discard' FIELDS TERMINATED BY '|' MISSING FIELD VALUES ARE NULL ( CUSIP, DESCRIPTION, QTY, ACCOUNT ) ) LOCATION ('exttabletestfile.txt') ) REJECT LIMIT UNLIMITED; -- Table MY_EXT_TBL created. SELECT COUNT(*) FROM MY_EXT_TBL; -- Returns 65159. Matches file row count. </code> It was when I attempted to move the working code into a procedure that things went sour. This example shows a very basic (no Log, Bad, or Discard files) example and hints at the hazards of going that route. I accepted that challenge, but after trying every combination of single and double quotes around file names without success, I am stumped. This feels harder than it should be. If External Tables in a sproc are a valid, if tricky, solution, could you please demonstrate a working example? Or should I be using UTL_File instead? Or something else? Best regards, Dexter

<code>declare type t1 is record ( f1 number ); type t2 is record ( f1 number ); v1 t1; v2 t2; procedure q(p1 in t1) is begin null; end q; procedure p(p1 in t1, p2 in t2) is procedure q(p2 in t2) is begin null; end q; begin q(p1); q(p2); end p; begin p(v1, v2); end; /</code> Procedure p has a nested procedure with the same name of an outer procedure (q). PLSQL cannot resolve the call to q, raising the error PLS-00306: wrong number or types of arguments in call to 'Q'. If I move the nested procedure in an outer scope, the block runs ok: <code>declare type t1 is record ( f1 number ); type t2 is record ( f1 number ); v1 t1; v2 t2; procedure q(p1 in t1) is begin null; end q; procedure q(p2 in t2) is begin null; end q; procedure p(p1 in t1, p2 in t2) is begin q(p1); q(p2); end p; begin p(v1, v2); end; /</code> It seems that the local procedure q(t2) hides the outer q(t1), even if they have different signatures. Are there any reasons for that behaviour? Thanks Eddy

How can we execute a SQL script file in SQL trigger and output of this SQL script execution into the log file? We are automating one of the SQL script file execution. We want to execute this SQL script file once the data will insert into the table and we want the SQL script file execution in the trigger. Regards, Abhishek Bhargava

I was looking at this article https://blogs.oracle.com/machinelearning/post/oracle-data-miner-now-available-for-autonomous-database. Is Data Miner also supported on ADW? If so, I am looking for a tutorial to setup Oracle Data Miner to use with ADW. In particular, I am struggling with the setup of the data miner connection / user with SYS privileges to install the Data Miner Repository. I am using SQL developer 21.4.3.063 on MacOs.

Hi Tom, I went through the articles & documentation, your article answers most of the issues Asktom EBR Part 1 => http://www.oracle.com/technetwork/issue-archive/2010/10-jan/o10asktom-172777.html Asktom EBR Part 2 => http://www.oracle.com/technetwork/issue-archive/2010/10-mar/o20asktom-098897.html Asktom EBR Part 3 => http://www.oracle.com/technetwork/issue-archive/2010/10-may/o30asktom-082672.html and Whitepaper on EBR http://www.oracle.com/technetwork/database/features/availability/edition-based-redefinition-1-133045.pdf As per the documentation Table is an example of an noneditionable type. https://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_editions.htm#ADFNS99923 The requirement is application upgrade also has some data changes in the application repository tables, its not structural table changes, its changes in the table data (both insert of new rows and updates/deletes of existing rows). How can this be restricted to new edition till the edition is actualized. Is there a way or workaround, do we have any enhancement request or feature looking forward for making editionable table to solve this. Regards, SrinivasaMurthy

Greetings oracle DB gurus, On this subject I want a recommendation, the database weighs 7 teras in total but 5 of that 7 teras is only the audit table, that table only has 3 years of data (The business needs to keep all the data) and it has more than 900 million record and 2 clob fields, it is a move table, We have had several incidents related to this table, slowness in the Database for inserting that table, as it has clob fields that sometimes save 10 million characters, not if that is related, apart from that we have run out of disk space, tablespace or data file, the log is filling up very fast, it doesn't even let the alerts arrive before the disk is full, for example. This table is used by several applications at the same time and saves all the activities that users perform, the clob fields are the details of the activities The business wants to pull reports from this table when that table only has one index. here I leave the structure of the table CREATE TABLE EBTDEV.ADMIN_AUDIT ( ID NUMBER NOT NULL , EVENT_TYPE NUMBER(1, 0) , OWNER_ID NUMBER , OWNER VARCHAR2(100 BYTE) , OWNER_PERMISSIONS CLOB , EVENT_DESCRIPTION VARCHAR2(200 BYTE) , OBJECT_TYPE VARCHAR2(100BYTE) , OBJECT_ID NUMBER , BEFORE CLOB , AFTER CLOB , TERMINAL VARCHAR2(100 BYTE) , EVENT_DATE TIMESTAMP(6) , AGENCY VARCHAR2(10 BYTE) ,PORTAL VARCHAR2(20 BYTE) , UPD_FILE_DW TIMESTAMP(6) ) and this is the only index it has CREATE INDEX EBTDEV.IX_EVENT_DT_UPD_FILE_DW ON EBTDEV.ADMIN_AUDIT (EVENT_DATE ASC, UPD_FILE_DW ASC) my question is what is your recommendation to improve performance regarding the creation of reports and optimize the table so as not to have more issues of DB space and slowness in the DB

I would like to use InMemory tables in my Data Guard Logical Standby database. Are there any caveats or limitations? I haven't found any documentation about Logical Standby and InMemory tables. Thanks very much. ps. Sometimes I miss the prickly responses of a few years ago.

As stated in SQL Language Reference, the default fractional_seconds_precision is 6 in a TIMESTAMP data type. "where fractional_seconds_precision optionally specifies the number of digits Oracle stores in the fractional part of the SECOND datetime field. When you create a column of this data type, the value can be a number in the range 0 to 9. <b>The default is 6.</b>" Also stated in Format Models, FF will use the precision of the data type. "Fractional seconds; no radix character is printed. Use the X format element to add the radix character. Use the numbers 1 to 9 after FF to specify the number of digits in the fractional second portion of the datetime value returned. <b>If you do not specify a digit, then Oracle Database uses the precision specified for the datetime data type or the data type's default precision.</b> Valid in timestamp and interval formats, but not in DATE formats." So in thoery, TO_CHAR(TIMESTAMP, 'FF') should return fractional seconds in 6 digits, and if use TO_CHAR with a TIMESTAMP with specified precision, the result length should be equal to the precision. BUT, this is not the case as you can see in the LiveSQL demo: 1. With data type defined in table and a SQL directly select from it, the behaviour is as expected. 2. With data type defined in PL/SQL and to_char in PL/SQL, TO_CHAR(TIMESTAMP, 'FF') will always return 9 digits. 3. With data type defined in PL/SQL and to_char in SQL, TO_CHAR(TIMESTAMP, 'FF') will always return 9 digits too. 4. TO_CHAR(SYSTIMESTAMP, 'FF') also return different length between SQL and PL/SQL. So the question is: What's the reason behind the inconsistent behaviour? Is this intentional or BUG? Thank you.

sql having parallel hint with degree 4 is running fine on prod enviornment but on lower enviornment it is prompting error code [12805]; ORA-12805: parallel query server died unexpectedly.Could you pls help what could be the possible cause?

Team, For our reference , looks like any table structure or sample data is not needed over here. Below is the stats of our DB user <b>SEGMENT_TYPE SIZE_IN_GIG(GB) INDEX 121.2262573 LOBINDEX 0.010986328 LOBSEGMENT 3.181213379 TABLE 171.7533569</b> I had a question from my Project Manager ,stating why the index segment type is taking such huge sizes. A small note : Tables are occupying 170 GB and at the same time Indexes are occupying 120 GB . Looks like Indexes are occupying huge space. I Request our humble intellectuals to throw some light on this. Any suggestions most welcome. Any detailed recommendations on how Indexes work internally is most welcome. Further do you recommend us to rebuild the indexes using "alter index index_name rebuild online". Or Gathering the schema stats would help us in reducing the sizes ? Thanks and Regards, Vinesh

Hi, we have run into performance issues after upgrading to Oracle 19c (19.14). For some SQL query?s the optimizer will no longer create execution plans similar to them in Oracle 11g (11.2.0.4) resulting in extreme slow performance (up to a factor 1000 times). The update was done by performing new installation of ora19c (19.3), then moving data with expdp/impdp from Ora11g and last installation of the patchset 19.14. We run statistics in the database regularly as well as dictionary stats One thing we noticed in the SQL query?s with these performance issues, is that the optimizer now in 19c choose "HASH JOIN" instead of "TABLE ACCESS BY INDEX ROWID" and "HASH GROUP BY" instead of "INDEX RANGE SCAN". We tried experimenting with the below settings to make it less happy with hash join, but with no luck. *._gby_hash_aggregation_enabled *._hash_join_enabled *._optimizer_nlj_hj_adaptive_join *._optimizer_sortmerge_join_enabled We also tried activating optimizer patches after new RU installation. exec dbms_optim_bundle.enable_optim_fixes('ON','BOTH','NO'); We have seen the optimises, perhaps, one time after each shutdown/restart, pick the same execution plan as in ora11g resulting in same performance as ora11g, but next runs and the rests are with the not optimal execution plan using HASH JOIN. Most of our users experience faster performance or same performance after upgrade. It is only a couple of advanced SQL views, as we know so far, that have these problems. We believe we have enough hardware, more than in the old server and we cannot see any problem with the read/write on disk, and as mentioned above, it have picked the ?optimal? plan a couple of times. Best Regards Fredrik

Hi, Is it possible to track previously executed DCL (Data Control Language ) statements in Oracle 11g? A user might have accidentally revoked privileges on a particular sequence. I need to know who ran the DCL statement and when. Thanks in Advance! Sunil