Tom Kyte

Subscribe to Tom Kyte feed Tom Kyte
These are the most recently asked questions on Ask Tom
Updated: 1 hour 18 min ago

Case construct with WHERE clause

Tue, 2016-10-25 06:06
Hi Tom, I have a question and I don't know if this is possible or if i'm jsut doing something wrong because i get multiple errors like missing right paren, or missing keyword. I want to use the CASE construct after a WHERE clause to build an expre...
Categories: DBA Blogs

Missing Physical Reads

Tue, 2016-10-25 06:06
Hi Tom, Please find below the experimented done, sequentially. Scripts ---------- create table cust (cust_id number, last_name varchar2(20),first_name varchar2(20)); create index cust_idx1 on cust(last_name); SQL> set autotrace on; SQL> ...
Categories: DBA Blogs

Function with multi-dimensional array as parameter?

Tue, 2016-10-25 06:06
How would I define a function that takes a multi-dimensional array as an input parameter and returns json_tbl PIPELINED that has been defined as <code>CREATE OR REPLACE TYPE CIC3.json_t as OBJECT (JSON_TEXT varchar2(30000)); CREATE OR REPLACE TYPE ...
Categories: DBA Blogs

Depth of attributes,

Tue, 2016-10-25 06:06
Hello, I have a situation where my data (for a given sys_id) has values for multiple depths (level1 attribute, level2 attribute and so on). For a given sys_id, I have to select the rows that has the maximum depth. However, as an example, if a va...
Categories: DBA Blogs

Oracle Auditing - Syslog

Tue, 2016-10-25 06:06
Hi Guys, I have two questions with regard to Oracle database auditing via syslog. 1. When auditing via OS syslog, what is the ideal value for the AUDIT_SYSLOG_LEVEL parameter, where AUDIT_SYSLOG_LEVEL = facility.priority It is the priortity...
Categories: DBA Blogs

how to optimize a query that is concatenating fields routinely

Tue, 2016-10-25 06:06
Hi. I'm trying to find a way to optimize this situation below. Example table definition: create table rw_test (A varchar2(10), B varchar2(10), C varchar2(10), D varchar2(10), E varchar2(10), F number(10), entry_date date); ...
Categories: DBA Blogs

Number to Hours and minutes.

Tue, 2016-10-25 06:06
Hello- I have a field which totals the number of hours worked; and for example returns a figure of 41.75 What is the best way to represent this number as 42 hours and 15 minutes? Thanks Venkat
Categories: DBA Blogs

temp table and third party

Mon, 2016-10-24 11:46
Hi, i was use sybase and connect to it with third party name (SAP business object - Desktop intelligent) but now we decide to use oracle instead of sybase but i found problem , most script which i was run in sybase has Temp table like that --------...
Categories: DBA Blogs

Dynamically Logging parameters of procedure or function

Mon, 2016-10-24 11:46
Hi Tom. Is there any way to log the parameters of functions and procedures inside package or as standalone objects without having to write every parameter name and its value. I think of something generic where I can put into procedure/function ...
Categories: DBA Blogs

Generating rownumbers without rownum,row_number(),sum() over and rowid in oracle

Mon, 2016-10-24 11:46
Recently one of my friend faced a question in one of the interview to generate rownumbers without using rownum or Row_number() function. He had suggested to do to running sum of 1's but that was not the right solution as the table contained duplicat...
Categories: DBA Blogs

Automatic Selection of Quality Limited Data (row) for Export

Mon, 2016-10-24 11:46
I would like to use an existing functionality or function or by what other means for automatic selection of quality data for all tables in the tablespace/schema that follows through the foreign keys relation until the end of the chain (the most compl...
Categories: DBA Blogs

extracting particular pattern from data using sql

Mon, 2016-10-24 11:46
Dear Tom, Need to extract particular pattern as example. select ('|100|BXX656|:20:200100O0012|:32A:010607USD6025,10|:50:XYZ LABORATORIES PVT LTD|B/2 TESTCHAMBERS 22 B DESAI RD|CBD-26| -||||||||||||||:59:W.S.A. TEXT SZCZESIUL|USA|||:71A...
Categories: DBA Blogs

How to Maximize Performance for Date Logic Queries when Base Tables Only Contain Year and Month Columns

Mon, 2016-10-24 11:46
Hi Tom, <b>It's my first question!</b> Also, I love this forum--as a newbie to Oracle, Ask Tom is invaluable :) In our organization, we have many base tables that are built with YEAR and MONTH number type columns that serve as part of a composi...
Categories: DBA Blogs

Oracle Job and Email notification

Mon, 2016-10-24 11:46
Hi, I have a Oracle Chain, which calls multiple Jobs and internally Job calls SP to update a table Flag. Oracle Chain ------------- BEGIN DBMS_SCHEDULER.CREATE_CHAIN ( CHAIN_NAME => 'CCR_CHAIN', RULE_SET_NAME => ...
Categories: DBA Blogs

Archive log list Generating high in my Production server

Mon, 2016-10-24 11:46
Hi Tom, In my Production server generating archivelog files more than 200GB per day. Usually we taking manual backup (OS level move files) of this archive log to another drive for emergency purpose. after that will delete next day. and monthly onc...
Categories: DBA Blogs

Catch ORA-28002 in Oracle forms

Sat, 2016-10-22 04:46
Hello, How can I catch warning ora-28002 (password will expire within days) in Oracle forms 6i? I tried in several triggers but with no success. Is there a way to catch such warnings in Forms? Also for example the ora-28001 (expired) code. ...
Categories: DBA Blogs

commands execution sequence of sqlplus

Sat, 2016-10-22 04:46
dear Tom, I have command echo "exit" | sqlplus $CONDB @LONGSQL in AIX. Questions: 1. will sqlplus always execute exit after executing LONGSQL. 2. If there is no exit in LONGSQL?what is the better way to let sqlplus exit after executin...
Categories: DBA Blogs

Result of view, if base table is modified

Sat, 2016-10-22 04:46
Hi Tom, During a recent interview, I was asked that what happens to a view, if the base table is modified. For eg. I have a table emp with 3 columns viz eid, did, sal. I have created a view vw_emp as CREATE VIEW vw_emp AS SELECT * FROM emp; Thing...
Categories: DBA Blogs

ORAPWD file usage and its not using properly

Sat, 2016-10-22 04:46
Hi Tom, I dont know below is fault or am i missing something in the architecture. Iam using orapwd file , -- When connecting from OS level password will be taken from this file -- once i change / recreate the ORAPWD file its not working as ...
Categories: DBA Blogs

Extract domain names from a column having multiple email addresses

Sat, 2016-10-22 04:46
Hi Tom, i am trying to extract the domain names from a comma delimited email address and show them as comma delimited. i was successful to some extent where i am able to grab the domain name using REGEXP_SUBSTR and REGEXP_REPLACE and show them in...
Categories: DBA Blogs

Pages