Tom Kyte

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

IP address

Tue, 2020-10-27 22:06
I have got an Oracle version 8.1.1 I'd like to know how you get the IP address from users when they have already logged on the Database. Would you, please, send me infomations about versions older than 8.i? Thanhs beforehand.
Categories: DBA Blogs

Equvivalent function for ISDATE()

Tue, 2020-10-27 22:06
Hi Tom, I've been using Oracle for Sometime now, I use a lot of MSSQL and Sybase, I'm trying to load some ascii file using SQL Loader(sqlldr), I want to know if there is any oracle function equvivalent to ISDATE() function in MSSQL or Sybase. If my Ascii file contains data which is not of date datatype, I want to insert a NULL instead of loading some wrong data or getting an error in sqlload. Thanks for your Help Srini
Categories: DBA Blogs

DB link access between (20 Containers in the same Zone ( Oracle Suppoer cluster)

Tue, 2020-10-27 03:46
Hi Tom, We have a customer, who deployed multiple databases on the same supercluster zone and required a full segergation between each database. Here let's talk about one of zones which are having 2 databases and required a full network segergation between each other. We configured for each database a seprate network with different subent on the cluster and for sure different scan IPs and VIPs. Everything went smootly except the connectivity between the databases in the same zone with each others so we agreed to implment TCP.EXCLUDED_NODES to prevent each database to connect to the other but the problem it is not working and still database A can connect on B and viceversa, we did a test between databases in different zones and this parameter "TCP.EXCLUDED_NODES" is working properly. My Question is why it is not working with two databases in the same zone? - DB 12.1 - Zone O.S is Solaris Sparc : 11.4 - (2) Containers with (2) Different databases in the same Zone , we need to stop DB access from one DB to the other
Categories: DBA Blogs

Locking issue on self created Counter scheme in our HMIS Application

Tue, 2020-10-27 03:46
Dear Team We had recently upgraded our Application counter scheme ( Previously using oracle sequences) to our own created procedure to generate Next number/counter due to our requirement to generate seperate counters for our different Hospital Campuses. Every year we have to reset all sequences because our scheme is using year based counter values. Now the issue is locking problem on our Physician Encounter where we have to generate 7-8 different counters on the same time and our procedure sometimes get locked and does not return any counter value due to locking issue Our application user may loss data to No Counter return We have two options of Row update, wait 1 sec and Nowait, sample code is mentioned below. can you please guide us on method close to Oracle sequence so that we can update code and overcome this locking issue. Also Please guide do you recommend to use FOR UPDATE without wait or NOWAIT option in SQL ? <code> IF NVL(P_LOCK_WAIT, 'N') = 'Y' THEN SELECT ROWID INTO P_ROWID FROM COUNTERS.SYSTEM_COUNTERS_VALUES T WHERE T.COUNTER_ID = P_COUNTER_ID AND T.SERIAL_NO = P_SERIAL_NO FOR UPDATE WAIT 1; ELSE SELECT ROWID INTO P_ROWID FROM COUNTERS.SYSTEM_COUNTERS_VALUES T WHERE T.COUNTER_ID = P_COUNTER_ID AND T.SERIAL_NO = P_SERIAL_NO FOR UPDATE NOWAIT; END IF </code>
Categories: DBA Blogs

Materialized View Log

Tue, 2020-10-27 03:46
Db Version: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production <code>desc Par_trials; Column Name Pk Null? Data Type PARTY_TRIAL_ID 1 N NUMBER (11) PARTY_ID N NUMBER (11) PARTY_ROLE_TYPE_ID N NUMBER (11) CLINICAL_TRIAL_ID N NUMBER (11) START_DT Y DATE END_DT Y DATE CORE_CREATE_DT N DATE CORE_LAST_UPDT Y DATE EDC_FLAG Y VARCHAR2 (1 Byte) CPAC_FLAG Y VARCHAR2 (1 Char) ELMS_FLAG Y VARCHAR2 (1 Char) CREATE MATERIALIZED VIEW LOG ON par_trials WITH ROWID, SEQUENCE (party_id,party_role_type_id,clinical_trial_id,start_dt,end_dt,core_create_dt,core_last_updt,edc_flag,cpac_flag,elms_flag), PRIMARY KEY INCLUDING NEW VALUES Insert into PAR_TRIALS (PARTY_TRIAL_ID, PARTY_ID, PARTY_ROLE_TYPE_ID, CLINICAL_TRIAL_ID, START_DT, CORE_CREATE_DT) Values (365352, 50858, 528, 1055, TO_DATE('10/30/2019 20:53:51', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('11/07/2019 12:03:59', 'MM/DD/YYYY HH24:MI:SS')); COMMIT; update par_trials set core_create_dt=sysdate where party_Trial_id=365352; select * from MLOG$_PAR_TRIALS</code> Question: 1) Would like to include a new column called "OPERATION$" in MLOG$_PAR_TRIALS table which should have below column values "UU" which indicates before update and should contain all its corresponding old values "UN" which indicates after update and should contain all its corresponding new values 2) In mlog creation script, have included "INCLUDING NEW VALUES" and i do have requirement to "exclude old values". Expectation here is just to retain only new values in mlog. Is that possible? If yes, kindly share example. 3) Can we do delete operation against MLOG$_PAR_TRIALS?If yes, kindly share example.
Categories: DBA Blogs

Recursive Function

Tue, 2020-10-27 03:46
Can you please give me an example for recursive function in plsql Which takes i_date as an input variable using this i_date it should perform operations like it should fetch data from table into cursor From this cursor I should insert data into a file .This file should be generated seperately for date that is passed in as input Let us take that my input date is 01012016 the function should recursively perform until it reaches 31122016 (for every last day of a month i.e total 12 times)
Categories: DBA Blogs

How to reset sequences?

Tue, 2020-10-27 03:46
Sir, Greetings. I would just like to know if it is possible to truncate a sequence to reset back to its original starting number? Pls. help me. Thank you. Merry Christmass. - Vince Crismer C. Villena
Categories: DBA Blogs

Problem with the number 1/19

Tue, 2020-10-27 03:46
Oracle is under the impression that 1/19 * 19 - 1 = -6 x 10^(-40). You can replace 19 with 19 multiplied by any positive power of 100 and the problem remains. (When you multiply 19 by an odd power of 10, the answer is zero. For example, 1/1900 * 1900 - 1 = -6x10^(-40), but 1/190 * 190 - 1 = 0.) I understand it has to do with the way numbers are stored internally, but is there a simple way around it? Note that this also happens in version 10.2.4
Categories: DBA Blogs

Oracle Redaction

Tue, 2020-10-27 03:46
Hello I have a question on oracle redaction. Can oracle redaction be turned on based on a value in a column? please let me know Thanks subramanyam
Categories: DBA Blogs

Number of CPUs and Degree of Parallelism

Tue, 2020-10-27 03:46
Hi Tom, 1) Is there or What is the relationship between number of CPUS the host has to the degree of Parallelism selected say while running DBMS_STATS. 2) Is there a relationship between DB sessions and Degree of Parallelism. If I have say 4 CPUS and select degree of 4 When I run DBMS_STATs will it show 4 DB sessions and consume 4 CPUS ? Or It will definitely show 4 DB sessions but no. of CPU depends upon availabilty from OS side. So even 2 CPUS will be used for 4 sessions ? 3) Is there always one to one relationship between a DB session ( from v$session) and OS process (seen with ps command ) 4) DBMS_SCHEDULER does it need to have job_queue_processes set like the old days with dbms_job Please answer with respect to 9208 and 11107 version.
Categories: DBA Blogs

Query never finish without clear reason

Wed, 2020-10-21 01:06
Some times an aleatory select statement stop working without a clear reason. Some times the statement is inside procedure, some times it is executed directly from ODAC FOR .NET 4 Then only thing in common it was always executed by ODAC client, so I never got this problem from one of my jobs ! When I check gv$sesion the session is active When I check plan using DBMS_XPLAN.DISPLAY_CURSOR I it is the best one When I simulate via pl/sql the query works fine with the very same plan When I kill the session and execute again from the ODAC , the same problem happen When I kill the session, SYS.DBMS_SHARED_POOL.PURGE(ADDRESS || ', ' || HASH_VALUE, 'C'), and execute again from the ODAC then BINGO the problem is SOLVED. ...however we know that it will happen again ... What kind of evidence am I missing? did you ever see this kind of behavior?
Categories: DBA Blogs

Complete transient session for testing

Wed, 2020-10-21 01:06
We have looked at dbms_flashback and dbms_wm but both don't deliver what we actually want: Is there a possibility to make an entire session transient? We would like to use this for our testing scenario's: 1. Start the session in "transient mode" 2. execute a lot of pl/sql with commits, rollbacks and savepoints 3. drop/stop the session ....and everything is magically back to before point 1. dbms_flashback.ENABLE_AT_SYSTEM_CHANGE_NUMBER is almost there by showing the data at a specified scn, but then you have to reverse every table that is hit by the -third party- code by hand. Not feasible in our environment I'm not very optimistic, but perhaps I missed a new capability of the DB. Is there a way?
Categories: DBA Blogs

Select for update statement too slow

Wed, 2020-10-21 01:06
Hi Connor, Chris, I have a FOR UPDATE SQL used to lock certain rows in ORDERS table but it seems to be bit slow (takes around 1 min). I tried getting plan from dbms_xplan.display_awr. Could you please give me some lead from your past experience and I can look for the any SQL tuning stuff. <code> SELECT PT.ORDER_ID FROM STAGING_001 PN JOIN GTT_TAB IDS ON IDS.MSG_ID = PN.MSG_ID, XMLTABLE ( 'hsbcEnvelope/hsbcMessageBody/pymtTran' PASSING PN.XML_MSG COLUMNS REF_001 VARCHAR2 (50 CHAR) PATH 'REF_001', REF_002 VARCHAR2 (50) PATH 'REF_001', REF_003 VARCHAR2 (10 CHAR) PATH 'REF_001') PMT, ORDERS PT WHERE 1 = 1 AND ( ( PMT.REF_002 IS NOT NULL AND PMT.REF_001 IS NOT NULL AND PMT.REF_002 = PT.REF_002 AND PT.REF_001 = PMT.REF_001 AND NVL (PMT.REF_003, :B1) = PT.REF_003) OR ( PMT.REF_002 IS NOT NULL AND PMT.REF_002 = PT.REF_002 AND NVL (PMT.REF_003, :B1) = PT.REF_003) OR ( PMT.REF_001 IS NOT NULL AND PT.REF_001 = PMT.REF_001 AND NVL (PMT.REF_003, :B1) = PT.REF_003) ) FOR UPDATE OF PT.ORDER_ID NOWAIT; </code> <code> ----------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ----------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 27043 (100)| | | | | 1 | FOR UPDATE | | | | | | | | | 2 | BUFFER SORT | | | | | | | | | 3 | CONCATENATION | | | | | | | | | 4 | NESTED LOOPS | | 1003 | 1935K| 11972 (1)| 00:00:01 | | | | 5 | NESTED LOOPS | | 2940 | 3930K| 210 (1)| 00:00:01 | | | | 6 | NESTED LOOPS | | 10 | 13630 | 13 (0)| 00:00:01 | | | | 7 | INDEX FAST FULL SCAN | SYS_C006227 | 10 | 130 | 2 (0)| 00:00:01 | | | | 8 | TABLE ACCESS BY INDEX ROWID | STAGING_001 | 1 | 1350 | 2 (0)| 00:00:01 | | | | 9 | INDEX UNIQUE SCAN | PK_STG_INT | 1 | | 1 (0)| 00:00:01 | | | | 10 | XPATH EVALUATION | | | | | | | | | 11 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| ORDERS | 1 | 607 | 4 (0)| 00:00:01 | ROWID | ROWID | | 12 | INDEX RANGE SCAN | IDX_PT_REF_001 | 1 | | 3 (0)| 00:00:01 | | | | 13 | NESTED LOOPS | | 1011 | 1950K| 14172 (1)| 00:00:01 | | | | 14 | NESTED LOOPS ...
Categories: DBA Blogs

How can I do a variable "in list"

Wed, 2020-10-21 01:06
I have a simple stored procedure, that I would like to have a passed in string(varchar2) for used in select from where col1 in (var1) in a stored procedure. I've tried everything but doesn't work. Followed is my proc. Thanks CREATE OR REPLACE PROCEDURE WSREVSECT_5 pSectNos varchar2, pRetCode OUT varchar2 ) AS nCount number; BEGIN SELECT count(fksrev) into nCount FROM SREVSECT WHERE sectno IN (pSectNos ) /* as in 'abc', 'xyz', '012' */ ; pRetCode:=to_char(ncount); End;
Categories: DBA Blogs

How to return header and data using sys_refcursor in oracle pl sql

Tue, 2020-10-20 07:06
I want to return headers of column along with data while returning result using refcursor. <code> create table fetch_header_rows ( company_name varchar2(500), Company_id number, ammount number(20,8), data_commnets varchar2(500) ); insert into fetch_header_rows values('company1' , 1, 128.80,'test data1'); insert into fetch_header_rows values('company2' , 2, 129.80,'test data1'); insert into fetch_header_rows values('company3' , 3, 128,'test data1'); insert into fetch_header_rows values('company4' , 4, 100.80,'test data1'); create or replace procedure SP_fetch_header_rows(data_fetch out sys_refcursor ) as begin open data_fetch for select * from fetch_header_rows; end; </code> Here we are fetching cursor result in file. hence we required header as a first row in data. ( in current scenario we have more than 150 columns)
Categories: DBA Blogs

Generate string based on pattern

Tue, 2020-10-20 07:06
Hi Chirs, Connor, Could you please help or suggest a way to generate string based on pattern Pattern - <b>^[a-f0-9]{8}-[a-f0-9]{4}-[a-f0-9]{4}-[89ab][a-f0-9]{3}-[a-f0-9]{12}$</b> I need to generate millions of string like <b>f9f8c8e2-0b20-4160-8f74-e836f4661fc5</b> matches with the pattern. e.g. <code>SELECT * FROM dual WHERE REGEXP_LIKE('f9f8c8e2-0b20-4160-8f74-e836f4661fc5', '^[a-f0-9]{8}-[a-f0-9]{4}-[a-f0-9]{4}-[89ab][a-f0-9]{3}-[a-f0-9]{12}$');</code>
Categories: DBA Blogs

Update rows when cursor returns no data

Tue, 2020-10-20 07:06
Purchase_Mas contains data of purchase master and payment_amt contains data of payment received from party. Cursor c2 does not return any value when not found in payment but still i want to some calculation happen even data not found in payment table. run following script and when you fire a query " select * from purchase_mas_tmp ; " Look at ( ID =5 and party code=12 ) and ( ID = 6 and party code= 14 ) when payment amount not found in cursor c2 but i want to os_amt display as 10000 for 5 and 20000 for 6 id of payment. so how its possible <code>create table PURCHASE_MAS ( id NUMBER, party_code NUMBER, total_pcs NUMBER, total_amt NUMBER, purchase_date DATE, reg_flg CHAR(1), discount_amt NUMBER ); create table PAYMENT ( id NUMBER, party_code NUMBER, payment_date DATE, payment_amt NUMBER ); create global temporary table PURCHASE_MAS_TMP ( id NUMBER, party_code NUMBER, total_pcs NUMBER, total_amt NUMBER, purchase_date DATE, reg_flg CHAR(1), payment_date DATE, payment_amt NUMBER, os_amt NUMBER, discount_amt NUMBER ) on commit preserve rows; insert into PURCHASE_MAS (ID, PARTY_CODE, TOTAL_PCS, TOTAL_AMT, PURCHASE_DATE, REG_FLG, DISCOUNT_AMT) values (5, 12, 456, 10000, to_date('01-08-2018', 'dd-mm-yyyy'), 'Y', 100); insert into PURCHASE_MAS (ID, PARTY_CODE, TOTAL_PCS, TOTAL_AMT, PURCHASE_DATE, REG_FLG, DISCOUNT_AMT) values (6, 14, 878, 20000, to_date('21-08-2018', 'dd-mm-yyyy'), 'N', 200); insert into PURCHASE_MAS (ID, PARTY_CODE, TOTAL_PCS, TOTAL_AMT, PURCHASE_DATE, REG_FLG, DISCOUNT_AMT) values (1, 11, 457, 30000, to_date('11-08-2018', 'dd-mm-yyyy'), 'Y', 300); insert into PURCHASE_MAS (ID, PARTY_CODE, TOTAL_PCS, TOTAL_AMT, PURCHASE_DATE, REG_FLG, DISCOUNT_AMT) values (2, 12, 658, 40000, to_date('10-08-2018', 'dd-mm-yyyy'), 'Y', 400); insert into PURCHASE_MAS (ID, PARTY_CODE, TOTAL_PCS, TOTAL_AMT, PURCHASE_DATE, REG_FLG, DISCOUNT_AMT) values (3, 11, 1454, 50000, to_date('07-08-2018', 'dd-mm-yyyy'), 'Y', 500); insert into PURCHASE_MAS (ID, PARTY_CODE, TOTAL_PCS, TOTAL_AMT, PURCHASE_DATE, REG_FLG, DISCOUNT_AMT) values (4, 13, 1254, 60000, to_date('18-08-2018', 'dd-mm-yyyy'), 'N', 600); insert into PAYMENT (ID, PARTY_CODE, PAYMENT_DATE, PAYMENT_AMT) values (1, 11, to_date('01-09-2018', 'dd-mm-yyyy'), 2500); insert into PAYMENT (ID, PARTY_CODE, PAYMENT_DATE, PAYMENT_AMT) values (2, 12, to_date('12-09-2018', 'dd-mm-yyyy'), 3000); insert into PAYMENT (ID, PARTY_CODE, PAYMENT_DATE, PAYMENT_AMT) values (3, 11, to_date('11-09-2018', 'dd-mm-yyyy'), 30000); insert into PAYMENT (ID, PARTY_CODE, PAYMENT_DATE, PAYMENT_AMT) values (4, 13, to_date('21-09-2018', 'dd-mm-yyyy'), 400); declare cursor c1 is select id, party_code, total_pcs, total_amt, purchase_date, reg_flg , discount_amt from purchase_mas; cursor c2 is select id, party_code, sum(payment_amt) payment_amt from payment group by id, party_code ; begin for i in c1 loop insert into purchase_mas_tmp (id, party_code, total_pcs, total_amt, purchase_date, reg_flg,discount_amt) values (i.id, i.party_code, i.total_pcs, i.total_amt, i.purchase_date, i.reg_flg, i.discount_amt); end loop; for i in c2 loop update purchase_mas_tmp tbl set payment_amt = nvl(i.payment_amt,0), os_amt = tbl.total_amt - nvl(tbl.discount_amt,0) - nvl(i.payment_amt,0) where id = i.id and party_code = i.party_code ; end loop; end; -- select * from purchase_mas_tmp ; /* drop table PURCHASE_MAS purge ; drop table PAYMENT purge ; drop table purchase_mas_tmp purge ; */</code>
Categories: DBA Blogs

Oracle Processes consuming 100% CPU

Mon, 2020-10-19 12:46
The server machine on which the DB resides has 32 CPUs. (2 sockets * 8 cores per socket * 2 threads per core) I notice many oracle processes (both non-background and background) consuming high (sometimes 100%) of the CPU. Now, this CPU % is only for 1 CPU out of the total 32 in the server. And once a process hits 100% of CPU consumption, the process cannot take resources from other CPUs. (please correct me if I am wrong) Right now, THREADED_EXECUTION parameter is set to FALSE. I was thinking, if we can enable multi threading in the Database, then, may be the process that is hitting 100% and is looking for more CPU can take it from the other CPUs. Is this a good idea? If yes, then how should we enable multi threading in the DB and what is the possible impact on the DB? Please Note: This is a single instance DB (non-RAC) OS: SUSE Linux Enterprise Server 12 Thanks in Advance, Abhishek, Kolkata, India
Categories: DBA Blogs

Oracle TNS poison attack vulnerability

Mon, 2020-10-19 12:46
Hi Team We are running Non-RAC Oracle 11.2.0.3.0 and the TNS poison attack vulnerability (Oracle Security Alert for CVE-2012-1675 - https://www.oracle.com/security-alerts/alert-cve-2012-1675.html) looks affected this version of Oracle. What we have done: * Have applied the latest version of Oracle critical patches update (July/2015) on 11.2.0.3.0 What we are expecting: * We hope applying the latest CPU (July/2015) for 11.2.0.3.0 can fix it and no further actions required. My question is: * Do we still need to follow the steps in <Using Class of Secure Transport (COST) to Restrict Instance Registration (Doc ID 1453883.1)> mentioned in Oracle Security Alert for CVE-2012-1675 to fix this issue? Best Regards
Categories: DBA Blogs

DBMS_ASSERT returning the ORA-44002: invalid object name exception for existing database links and directories

Mon, 2020-10-19 12:46
Hi, In my procedure I'm trying to check whether a database link and a directory exist. If they don't I wanted to display a nice message about them needing to be created etc. I thought of using dbms_assert.sql_object_name, this seems to do the trick for tables, views, functions but not for database links or directories. Here is my test case (not my actual procedure, but I have the same issue) The table, view, function return the name / don't error when running the dbms_assert part. The database link, directory returns "ORA-44002: invalid object name" -- table <code>create table test_tbl (x number); select table_name from user_tables where table_name = 'TEST_TBL'; select sys.dbms_assert.sql_object_name('TEST_TBL') from dual;</code> -- view <code>create view test_vw as select * from test_tbl; select view_name from user_views where view_name = 'TEST_VW'; select sys.dbms_assert.sql_object_name('TEST_VW') from dual;</code> -- function <code>create or replace function test_f return date is dt date; begin dt := sysdate; return dt; end; select object_name from user_objects where object_name = 'TEST_F'; select sys.dbms_assert.sql_object_name('TEST_F') from dual</code>; -- database link <code>create database link test_link connect to user123 identified by user123 using 'dwh'; select db_link from user_db_links where db_link = 'TEST_LINK'; select sysdate from dual@test_link; select sys.dbms_assert.sql_object_name('test_link') from dual; select sys.dbms_assert.sql_object_name('TEST_LINK') from dual;</code> -- directory <code>create directory test_dir as '/apps1/oradata/big_dump'; select directory_name from all_directories where directory_name = 'TEST_DIR'; select sys.dbms_assert.sql_object_name('test_dir') from dual; select sys.dbms_assert.sql_object_name('TEST_DIR') from dual;</code> Thanks Nick
Categories: DBA Blogs

Pages