Tom Kyte

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

Is there an Oracle document that has a checklist to be able to answer whether database server will handle "peak" load

4 hours 39 min ago
Greetings, A question from the client that comes up every few years is to predict if the Oracle database server will be able to handle a new application's peak load. Instead of trying to think of all that needs to be considered on the fly, it would be great if there was an Oracle document that had a checklist with all of the questions that we must answer so that we can give the client a definite answer of yes we can predict if x,y and z or performed. I know that in most cases, this will be nearly impossible to answer as it will take too much time to answer and we can't control the variables for other apps that share the same resources like database, network, SAN, etc. For instance, usually the network and SAN are shared with the database server so we will need to get peak loads of all the other applications plus the expected max throughput for the network and SAN. Thanks for your help, John
Categories: DBA Blogs

APEX Message box label

4 hours 39 min ago
Is it possible to change the labels of the confirm dialog buttons from ?Cancel/Ok? to ?No/Yes? in APEX?
Categories: DBA Blogs

Avoiding overlap values...

Tue, 2021-05-04 12:26
Hai Mr Tam, U said U have a 'trick' for the following problem. It couldbe nice if u tell me that..thanks. A form(5.0) with tabular style is displayed like below.. to from discount -- --- ----- 10 40 1.5 50 65 2.5 70 90 1.2 . . . . 60 99 ----> should not be allowed. 65 80 ----> should not be allowed. Is there a way . . . . But I would like to stop OVERLAPPING range like above shown with arrow marks. How can I do it. Thanks once again rgs priya
Categories: DBA Blogs

Fluctuating counts on ROWID splits using DIY parallelism.

Mon, 2021-05-03 18:26
Hi Tom, Chris, Connor and all, I've been a user of your DIY Parallel solution for many years now as a way to pull data out of large unpartitioned tables in parallel to send to other non-Oracle databases or for file system archiving. I've ran into a situation recently at my last company and now my new company where the solution is acting different. I first noticed the change at my old company when the data warehouse I was supporting was moved to an Exadata system in OCI. The version of the database stayed the same, 11.2.0.4, making the only change being the hardware/datacenter move. What happened was during a row count validation of a table export based upon rowid splits the counts didn't match what was exported. Upon further investigation I found that the row count for a given rowid split was flutuating. Upon doing one count it would return a value and then the value would change upon subsequent counts. The count didn't just go up, it would go up and down between a set of three or four different values, making getting an accurate count imposssible. The SQL I'd used to do this was of the formats: <code>SELECT COUNT(*) FROM X.XXX WHERE ROWID BETWEEN 'AAC2GBAFRAAD+c4AAP' AND 'AAC2GBAGIAAJ97wABX';</code> or <code>SELECT COUNT(*) FROM X.XXX WHERE ROWID > 'AAC2GBAFRAAD+c4AAP' AND ROWID <= 'AAC2GBAGIAAJ97wABX';</code> I can see where the counts could increment up if data is being added to the table but these were static tables and the count bounced back and forth between a few different sets of numbers. I'm now seeing this happen on other databases at my new job and I'm not sure what the cause of it is. I can't pin it down to a type of table or version or whether it's Exadata related or maybe something related to background work ASM is doing. I did a search to see if anyone else is having this occur to them without any luck. I'm seeing where lots of folks have implemented it but not where the row counts for a given split fluctuates. Do you have any idea what could be causing this and how to make it stop? It doesn't happen on all rowid splits for a table and it doesn't happen for all tables in a given database, it appears to be very random. Thanks, Russ
Categories: DBA Blogs

How to send a sql query result as an attachment in Oracle apex_mail.send procedure.

Fri, 2021-04-30 17:06
I have a Oracle sql query which needs to be run and need to send the data returned by the query as an attachment to a mail. Could you please guide how can i do it using apex_mail.send procedure. I am calling apex_mail from database. I have already configured apex mail. I can call the apex_mail.send to send the mail. But i am not sure how can i attach the result returned by my oracle sql query in apex_mail.add attachment.
Categories: DBA Blogs

Regarding On Update Cascade

Fri, 2021-04-30 17:06
Dear Tom, We know that when we delete a parent record, automatically child record also will be deleted if we used "on delete cascade". Is it possible to update automatically a child record when we update parent record? (Do we have "On Update Cascade" option? Or any other like..)
Categories: DBA Blogs

CTE failes when used with a db link

Thu, 2021-04-29 22:46
I am trying to use a cte in a query that copies data from one database to another. The cte is used because I am unable to handle a cycle with connect by. In this simple illustration the db link used by the insert causes an error (ORA-00942: table or view does not exist). <code>insert into don.T2@gstest (RELATE_STRING) with cte (LVL, I, PARENT_I, RELATE) as ( select 1 as LVL, I, PARENT_I, '+' || RELATE as RELATE from don.T1@gsdev where PARENT_I is null union all select c.LVL + 1, t.I, t.PARENT_I, c.RELATE || '+' || t.RELATE from cte c join T1 t on t.PARENT_I = c.I) select RELATE from cte order by LVL, I; </code> The illustration doesn't have a cycle issue, so connect by can be used to demonstrate. If I ensure that the code is executed from the target database and I remove the db link, the code works. <code>insert into don.T2 (RELATE_STRING)</code>... I was unable to figure out how to make a db link in liveSql.
Categories: DBA Blogs

Timestamp with time zone comparison Issues

Thu, 2021-04-29 22:46
Hi, I am facing an issue while validating timestamp with timezone data with systimestamp in 11g R2. My DB server is in US/Central zone. I have a table with timestamp with timezone data type column and I have inserted a future timestamp for same timezone (US/Central or UTC-5). While selecting data from table, we get same data. I also have an anonymous block which verifies if timestamp in table crossed systimestamp of not. Before daylight saving changes on March, this process was working correctly. both methods returns correct output when systimestamp is greater than timestamp with timezone column. However, after daylight saving changes, record which was inserted by giving timezone as US/Central format, returns correct output only after 1hr from actual time. I have given a sample in livesql, hope this can help to explain issue I am facing. Is there any specific reason for this behavior? Thanks in advance for your help Thanks, Manoj
Categories: DBA Blogs

Script too slow - how to optimize

Wed, 2021-04-28 10:06
Can you advise me in how optimize this batch script : <code>CREATE OR REPLACE PACKAGE BODY ADMIN_WAFA.PCK_Trait_Pre IS -------------------------------------------------------------------------------- FUNCTION Fun_Traiter_Prelevement(P_CONTROL_ANO VARCHAR2) RETURN VARCHAR2 IS ------------------------------------------------------------------------------------------------------------------------- ---curseur prelevement----- CURSOR Prov_Cursor IS SELECT * FROM Prov_Prelevement; --------------------------- Code_Tier NUMBER; v_sql VARCHAR2(1000); --------------- ?????????????????????????????? ------------------------------------ BEGIN /*********************** Calcul des STATS ************************/ BEGIN DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'ADMIN_WAFA', TABNAME => 'PROV_PRELEVEMENT', DEGREE => 8, CASCADE => TRUE, METHOD_OPT => 'FOR COLUMNS SIZE AUTO', ESTIMATE_PERCENT => 20); END; /************************ Resoudre PB PERF ************************/ -- alter session set optimizer_mode=RULE; EXECUTE IMMEDIATE ('alter session set optimizer_mode=RULE'); --Debut FZ HANOUNOU IF(P_CONTROL_ANO ='O') THEN Proc_Test_Anomalie; END IF; --Fin FZ HANOUNOU --------------------------------------------- --Modification par Karim EL HALOUI --le 15/12/2010 --pour la mise a jour de la vue materialisee --------------------------------------------- -- suppression BEGIN EXECUTE IMMEDIATE ('DROP MATERIALIZED VIEW vue_anomalie_ie_aff'); EXCEPTION WHEN OTHERS THEN NULL; END; -- creation v_sql:='CREATE MATERIALIZED VIEW vue_anomalie_ie_aff AS SELECT a.ano_num_aff,a.ano_ide_reg,a.ano_dat_ech FROM anomalie a where a.ano_pret_a>45'; execute immediate v_sql; ------------------ ?????? ------------------- v_sql:='alter FUNCTION F_ECH_ANOM compile'; execute immediate v_sql; v_sql:='alter package PKG_CTRL_PROMESSE_REG compile body'; execute immediate v_sql; --------------------------------------------- v_nom_fonction:='Proc_Traiter_Prelevement'; open Prov_cursor; LOOP FETCH Prov_cursor INTO Prov_record; EXIT WHEN Prov_cursor%NOTFOUND OR Prov_cursor%NOTFOUND is NULL; ---------------------------------- -- recherche du code_societe ---------------------------------- select s.code_societe into v_code_societe from societes s where s.lib_societe=prov_record.prov_societe; --------------------------------------------- IF not Fun_Test_New_aff THEN IF Fun_Test_New_tier THEN Code_Tier:=Fun_Ajout_Tier; ELSE Code_Tier:=Fun_Cle_Tier; END IF; ELSE Code_Tier:=Fun_Ajout_Tier; END IF; Proc_Ajout_Prelevement(Code_Tier); END LOOP; execute immediate('truncate table prov_prelevement'); v_nom_fonction:='proc_generation_interne'; pck_ban.proc_generation_interne; -------------------------------------------------------------------- v_nom_fonction:='proc_saisie_imp_bqe_interne'; proc_saisie_imp_bqe_interne; v_nom_fonction:='pck_ban.proc_generation_doti'; pck_ban.proc_generation_doti; update /*+choose*/ prelevement p set p.pre_dat_ech=to_char(sysdate,'DD/MM/YYYY') where exists (select null from vacation v where v.pre_ide_reg=p.pre_ide_reg) and p.pre_ide_reg<0 and exists (select null from tiers t where p.tier_cle=t.tier_cle and t.tier_code_societe=51 and t.tier_nom_agence='REPRCTX') and p.pre_dat_ech>to_char(sysdate,'DD/MM/YYYY'...
Categories: DBA Blogs

editing large clobs

Wed, 2021-04-28 10:06
Hi, I've developed an application in APEX which provides a feature to maintain files stored in a CLOB (well, actually it's a BLOB but I know how to handle this). This works very well as long as the size of the content doesn't exceed the 32k limit. If a file is bigger than this limit an error 'ORA-06502: PL/SQL: numeric or value error' is raised. Is there any solution which I can use to handle files with more then 32k chars in an apex app?
Categories: DBA Blogs

How can we query one database and get all databases' cell or IO usage from one database?

Wed, 2021-04-28 10:06
Hi Ask Tom Team, We had an issue in all of our production databases on an Exadata cluster. One RAC database's IO issue caused all databases on the cluster slowdown, user transactions took much more time, batch jobs hung. We need to run some scripts to consistently monitor the cluster besides OEM, so that we can identify the issues quicker and take actions sooner. OEM provides good information on our needs. We need your help to have related SQL scripts to generate same or similar information. In Oracle AWR reports. we can see a portion that is specific for Exadata. It appears in all AWR reports no matter in which database we run the awr report (global data), "Exadata Top Database Consumers". The information includes Cell usage and IO of all databases even though the AWR report is run only in one database (every AWR report has Exadata Top Database Consumers). Can you please help with related SQLs that generate that part of the AWR report? For example, how can we run a query to get "Top Databases by Requests" and so on by running queries only in one of the databases on the cluster. We checked all of the dictionary tables/views but failed to find any of them provide cluster information showing in AWR reports. We need to run the scripts (or stored procedure) periodically and have the system send us an email if any specific database's IO requests surpass a threshold we set. We can query live tables (GV$xxxxx) or DBA_HIST_XXXXX. Please advise. Thanks!
Categories: DBA Blogs

"java.lang.ClassNotFoundException: oracle.jdbc.driver.OracleDriver"

Wed, 2021-04-28 10:06
May I ask about the "java.lang.ClassNotFoundException: oracle.jdbc.driver.OracleDriver". Per Java document, the PATH and CLASSPATH shall be configured in the Windows 10 environment variables . Below are information regarding the system/database version and what I did. The Java version is as below. C:\>java -version java version "1.8.0_281" Java(TM) SE Runtime Environment (build 1.8.0_281-b09) Java HotSpot(TM) 64-Bit Server VM (build 25.281-b09, mixed mode) The JAVA_HOME is C:\Program Files\Java\jre\1.8.0_281 The CLASSPATH has C:\Program Files\Java\jdk\1.8.0_281 C:\Program Files\Java\jdk\1.8.0_281\bin C:\Program Files\Java\jre\1.8.0_281 C:\Program Files\Java\jre\1.8.0_281\bin C:\Oracle_RDBS_installation\product\12.2.0\client_1\bin C:\Oracle_RDBS_installation\product\12.2.0\dbhome_1\bin The compilation of Java program was successful by using "javac". But the class not found error message was returned during the run time. I have no problem accessing the Oracle database from Sql*Plus at all. I created two users, created a simple table for the sake of testing. Everything worked fine. I have no problem with the listener and the TNSNAES.ORA. Below is the information about database: SQL*Plus: Release 12.2.0.1.0 Production on Wed Apr 21 09:08:05 2021 Copyright (c) 1982, 2016, Oracle. All rights reserved. Enter user-name: c##_chiara Enter password: Last Successful login time: Sat Apr 17 2021 09:46:21 -04:00 Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production Below is the Java code: <Begin of the Java Code> import java.sql.*; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.Properties; public class Thirdj3 { public static void main(String[] args){ System.out.println("Beginning of the Thirdj3 program."); try{ //step1 load the driver class Class.forName("oracle.jdbc.driver.OracleDriver"); //step2 create the connection object Connection con=DriverManager.getConnection( "jdbc:oracle:thin:@DESKTOP-JBRID90.home:1521:orcl","c##_chiara","chiara"); //step3 create the statement object Statement stmt=con.createStatement(); //step4 execute query //*ResultSet rs=stmt.executeQuery("select * from emp"); //*while(rs.next()) //*System.out.println(rs.getInt(1)+" "+rs.getString(2)+" "+rs.getString(3)); //step5 close the connection object con.close(); } catch(Exception e){ System.out.println(e);} }//End of main }//End of Thirdj3 Class <end of the java code>
Categories: DBA Blogs

Set line delimiter in Textarea page item (CRLF vs LF)

Tue, 2021-04-27 15:46
Hi, in an app I'm developing in APEX I've seen, that lines entered in a text area page item are terminated with the windows style CRLF chars. It causes problems in an application which processes this data on UNIX systems. Sure we could transform this chars from CRLF to LF on the client, but would it be possible to define the way the text gets created/stored in the application directly? Is there any parameter or so to tell the page item how to set the line delimiter?
Categories: DBA Blogs

ODP.NET Monitoring on Linux (PerformanceCounters)

Tue, 2021-04-27 15:46
Using Oracle.ManagedDataAccess.Core 3.21.1 to create .Net Core 5.0 and C#-based microservices running in Linux-based containers. I'd like to at least log perfomance counters for later analysis of performance. I know System.Diagnostics.PerfomanceCounters is only available on Windows .Net Framework (right?), is there any chance I can get those numbers when on Linux? EventCounter for .Net Core? I might be on a wrong track here, can't find any information on this. What I want to do is a simple log of open/closed connections in the pool. To explain why it takes a while to open a connection sometimes. Don't want to do it with a huge software package, just a simple log-post to our Elastic/Kibana. If you have any idea who can help me I would be very grateful.
Categories: DBA Blogs

Retrieve data from Have I Been Pawned compromised password URL, create APEX REST Service

Tue, 2021-04-27 15:46
I am using Oracle Cloud Infrastructure (OCI) Autonomous Transaction Processing (ATP) I would like to be able to check for a password's existence in the Have I been pawned compromised password list without loading the file into oracle. (SHA1 encrypt password a user is trying to change to and then compare to list to see if it is in a public database of exposed passwords to warn user) They offer a url: https://api.pwnedpasswords.com/range/ that returns all password hashes (SHA1) that start with the first 5 characters provided that can be checked for the password in question. I have unsuccessfully tried various combinations to setup a web service in APEX. It works fine in Postman. I created an APEX Rest source with the https://api.pwnedpasswords.com/range/:hash link and it populates the :hash variable with a suitable hex example (21BD1). The URL returns a series of complete hex strings like: 0018A45C4D1DEF81644B54AB7F969B88D65:3 00D4F6E8FA6EECAD2A3AA415EEC418D38EC:2 011053FD0102E94D6AE2F8B83D76FAF94F6:1 012A7CA357541F0AC487871FEEC1891C49C:2 0136E006E24E7D152139815FB0FC6A50B15:3 01A85766CD276B17DE6DA022AA3CADAC3CE:3 However, it errors saying it is unable to parse it as XML or JSON. I tried manually defining the data profile as a varchar2(100), but same error. I also tried utl.http request, but APEX gave me insufficient ACL privilege's and it appears to me since I am using ATP it won't let me do that? If that would work it would be an acceptable solutions as well. BTW, I tried the livesql link this morning and it gave a bad gateway error: The connection pool named: |apex|| is not correctly configured, due to the following error(s): Exception occurred while getting connection: oracle.ucp.UniversalConnectionPoolException: Cannot get Connection from Datasource: java.sql.SQLRecoverableException: IO Error: Unknown host specified Thanks Joe
Categories: DBA Blogs

Materialize Hint not working on CLOB data in Oracle

Tue, 2021-04-27 15:46
INSERT INTO cities(state_no, city_name) VALUES (1, 'Vizag'); INSERT INTO cities(state_no, city_name) VALUES (1, 'Kakinda'); INSERT INTO cities(state_no, city_name) VALUES (1, 'Tirupathi'); INSERT INTO cities(state_no, city_name) VALUES (2, 'Hyd'); INSERT INTO cities(state_no, city_name) VALUES (3, 'Chennai'); INSERT INTO cities(state_no, city_name) VALUES (3, 'Madurai'); I have the above rows in my cities table. I have the below query WITH t1 AS ( SELECT /*+ materialize */ state_no, DBMS_XMLGEN.CONVERT(XMLAGG(XMLELEMENT(e1,city_name || ';')).extract('//text()').getclobval(),1) as col2 FROM cities group by state_no ) SELECT state_no, col2 FROM t1 my output is: 1 NULL 2 NULL 3 NULL When I use the materialize hint , I m getting all values in col2 as NULL. When I removed the hint or using the inline hint I m getting the col3 values as col2 NULL. when Materialized hint is removed. my output is : 1 ' vizag,kakinada,tirupathi' 2 ' Hyd' 3 'Chennai, Madurai' Why I m getting NULLS when using the materialize hint. What I need to do if I want to use materialize hint in this scenario. Thanks in advance
Categories: DBA Blogs

Partitioning existing tables

Tue, 2021-04-27 15:46
I am attempting to partition a Table T1 with existing data. Table T1 is as follows: COLUMN DATATYPE ----------------- COLUMN1 NUMBER PK COLUMN2 NUMBER COLUMN3 NUMBER I am using this approach: 1- ALTER TABLE T1 RENAME TO T1_TEMP 2- CREATE TABLE T1 ( COLUMN1 NUMBER, COLUMN2 NUMBER, COLUMN3 NUMBER) PARTITION BY HASH (COLUMN1) ( PARTITION P1 TABLESPACE T1, PARTITION P2 TABLESPACE T1 ); 3- INSERT INTO T1 SELECT * FROM T1_TEMP 4- DROP TABLE T1_TEMP CASCADE CONSTRAINTS The problem with the above method is that I have tables T2, T3, Tn with a foreign key reference on COLUMN1 of T1. The reference is automatically moved to T1_TEMP but not back to T1. QUESTION: --------- How to modify all references to T1_TEMP to point back to T1 after having copied the data in step 3? THANKS!
Categories: DBA Blogs

Convert CrLf to Lf with utl_file.put_line

Tue, 2021-04-27 15:46
Hi, Tom! I have next pl/sql code for export of data from oracle to file in format of raw: CREATE OR REPLACE PROCEDURE my_proc_export_to_csv is begin declare file_type utl_file.file_type; cursor row_cur is select * from sys.my_new_dir; data_export sys.my_new_dir%rowtype; begin file_type:=utl_file.fopen('MY_DIR','DATA_EXPORT.RAW','w'); open row_cur; loop fetch row_cur into data_export; exit when row_cur%notfound; dbms_output.put_line(data_export.contact_id||' '||data_export.phone); utl_file.put_line(file_type,data_export.contact_id||' '||data_export.phone); end loop; close row_cur; utl_file.fclose(file_type); end; end my_proc_export_to_csv; when I exported file and opened it, I saw at the end of the line crlf: 1313131 HoldersInCure4 CrLf but i need it in Lf: 1313131 HoldersInCure4 Lf how i can get it? Help me plz.
Categories: DBA Blogs

Will Oracle automatically lock the table stats of one schema?

Tue, 2021-04-27 15:46
Hi Tom, Recently, we found that our database had a slow performance. After checking the table stats, we found that most of the table stats of one schema was locked while other schema were not. And I have to unlocked all the table stats and regather those table stats manually. SQL> select count(*) from user_tables; COUNT(*) ---------- 311 SQL> select table_name from user_tab_statistics where stattype_locked is not null; COUNT(*) ---------- 285 SQL> Here's my questions: 1) Will oracle automatically lock the table stats of some certain schema or not? 2) If someone locked the table stats manually, how could we find out when he/she did this (the audit_trail is set to NONE)?
Categories: DBA Blogs

Is row chaining implicit on "wide" tables?

Tue, 2021-04-20 00:26
Hi Tom, in our ETL tool we have a process by which a flat file is read and transformed. A table is created with columns to hold the flat file content and columns for the functions that are applied sequentially per row. the file content is loaded via bulk copy. After the bulk copy a series of update statements are applied into the function columns, again applied sequentially because updates are dependent on previous updates in many cases. All these tables have PCTFREE = 10. The total count of columns is over 255 in all these examples and i was expecting all rows to be chained because of this. However in one case i have ~5% chaining, in others i have 99% chaining. Notes These are edge cases..normally files are not so wide. We are looking at separating the tables. We cannot alter the order of the columns We cannot drop any columns Examples Flat File Col Count Function Col Count % Chain 261 116 7.5% 252 73 99% So some questions: 1. Can the diff in chaining be explained by which columns are being referred to in the update statements? The whole splitting of the table (in memory terms) from the right-hand side perhaps 2. Can PCTFREE settings have any positive impact on chaining with wide tables? 3. Are there any other tactics you can suggest?
Categories: DBA Blogs

Pages