Tom Kyte

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

PL/SQL query with NULL variables

Thu, 2018-08-02 13:26
What is the best way to handle a query with multiple variables, and some of the variables can be null, like: <code>FUNCTION GET_RECIPE(P_RECIPE_LIST IN VARCHAR2, P_OWNER_LIST IN VARCHAR2, ...
Categories: DBA Blogs

Global temporary table error

Thu, 2018-08-02 13:26
Hi AskTom, Can you please help me with this issue. Our application uses lot of global temporary table (GTT) has on commit preserve rows option. <code> CREATE GLOBAL TEMPORARY TABLE "ODR"."GTT_POINT" ( "POINT_ID" NUMBER(10,0) NOT NULL ENAB...
Categories: DBA Blogs

How to find all Mondays between two dates?

Thu, 2018-08-02 13:26
I have to find all mondays between two date range which can be parameterized or coming from two different columns of a table. Also need to generate a sql to get next 20 mondays from sysdate. can you please help me to get sql query for these 2 r...
Categories: DBA Blogs

Procedure having OUT parameter vs Function

Wed, 2018-08-01 19:06
Thanks for taking up this question. Are there any guidelines regarding when to use a procedure(OUT parameter) vs Function. Both structures can be used to achieve the same objective in specific situation. I have created a function F1 and a procedu...
Categories: DBA Blogs

returning top 10 records based on previous records counts

Tue, 2018-07-31 06:26
Hi, I have an ordered table of products and customers as follows: <code>rn product customer 1 859274 A 2 859267 A 3 859250 A 4 863592 B 5 862250 B 6 862700 B 7 862694 B 8 862120 B 9 863592 C 10 862250 C 11 862120 D 12 86...
Categories: DBA Blogs

Library Cache Locks and Compiling New PL/SQL Code

Tue, 2018-07-31 06:26
Hello! I have a friend that told me that compiling a new piece of Pl/SQL code will put library cache locks on the dependent objects, and that there's a risk for database contention when new code is deployed. In other words, if I go out to a datab...
Categories: DBA Blogs

Performance Tuning: Tracing Over a DB Link

Mon, 2018-07-30 12:06
hi Team, currenlty in our environment i see many query execute on dblink . we face performacne problem over their , can you let us know how we can trace sqlid of those sql's which are running remotely and how to figure out their bind variables...
Categories: DBA Blogs

CLOB and BLOB datatypes !

Mon, 2018-07-30 12:06
Hi Tom, How are you ? My Question to you is as follows :- If you have a column of type CLOB or BLOB in a table Is it possible to view the contents of this column in the select statement ? Is it possible to insert data into these col...
Categories: DBA Blogs

Iterating Logic

Mon, 2018-07-30 12:06
Hi I am trying to write a SQL query that computes the values of a column(mmwl) as follows. For the DDL and DML scripts please check the livesql link <code> create table t (y date, value_pvt int, mmwl int); --sample data to generate a rec...
Categories: DBA Blogs

Hierarchical Design Patterns

Mon, 2018-07-30 12:06
There are several Database design patterns floating around for various application usages. Tree and Hierarchy Patterns are one of those. I see most of the hierarchy patterns implemented as a self referencing table. There are also other implementa...
Categories: DBA Blogs

Improve INSERT speed for loading volume sample data

Sat, 2018-07-28 05:06
Hi Team At the time of writing this, on LiveSQL I get "500 Internal Server Error", so will just paste the re-producible code here. I'm building sample application data for testing using PL/SQL. For loading 10M data in USERS_MAPPING table, cur...
Categories: DBA Blogs

SQL question with compass points

Sat, 2018-07-28 05:06
Hi sir, I have one dought is given below How to write select statement for bitmap index For example North is decode as 1 remains 0 West is decode as 1 remains 0 East is decode as 1 remains 0 South is decode as 1 remains 0 Output...
Categories: DBA Blogs

function-based index issue

Fri, 2018-07-27 10:46
Hi Tom, I have table with 1 755 001 rows and functional-based index as follows: <code> create table OC_YKB.TAB_TEST3 (guid number(16), pan varchar2(19 byte)); create sequence oc_ykb.sq_test3; INSERT /*+ APPEND */ INTO OC_YKB.TAB_TEST3 sele...
Categories: DBA Blogs

RMAN Backup

Fri, 2018-07-27 10:46
Hi, I have few clarifications needed regarding RMAN. 1. During Incremental RMAN backup - How does Oracle handles the uncommitted transactions? 2. If a backup is running for a long time (10-20 hrs) - How Oracle handles the backup process - wh...
Categories: DBA Blogs

Need to create dynamic columns in the report

Fri, 2018-07-27 10:46
Hi Tom, I am new to SQL and PLSQL, I have a requirement to create a report, which includes dynamic column for month based on number of months from Program duration. I actually tried to create scripts in LiveSQL, but right now it is not accessib...
Categories: DBA Blogs

Datapump (Export the live database & import to remote host, with no data loss)

Fri, 2018-07-27 10:46
Hello Community, Appreciate if anyone could provide a high level steps for following; I have a requirement to export (live/archive enabled/OLTP) whole/partial database, while the database is online/available and import it to a remote/different ...
Categories: DBA Blogs

Insert Into Values generates multiple rows per execution

Fri, 2018-07-27 10:46
Hi This is a snip from a tkprof. <code>SQL ID: 4wgb411d8s7ss Plan Hash: 0 INSERT INTO QP_NPREQ_LINE_ATTRS_TMP (LINE_INDEX, ATTRIBUTE_LEVEL, ATTRIBUTE_TYPE, CONTEXT, ATTRIBUTE, VALUE_FROM, VALIDATED_FLAG, APPLIED_FLAG, PRICING_STATUS_...
Categories: DBA Blogs

ALL_OBJECTS/DBA_OBJECTS not displaying results when used in Proceudre

Fri, 2018-07-27 10:46
Hi, I was having a weird issue. I wanted to use the ALL_OBJECTS or DBA_OBJECTS to check the existence of an object on a different schema in my database. When I query the database, I'm able to see the object. However when I execute the same query u...
Categories: DBA Blogs

Number Datatype

Thu, 2018-07-26 16:26
Hi Tom, I declared the datatype of a column of a table as NUMBER & Presumed it to be Number(38) to be specific. But What I found to be strange that, it is accepting digits beyong 38 digits.ie. 1.111E+125. If the Datatype's precision is 38 and ...
Categories: DBA Blogs

External table to skip the # row

Thu, 2018-07-26 16:26
I am using External table to read a csv file, which has some rows with '#' at the beginning that need to be skipped. How can I do that? <code>ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY GPC_DATA_CSV_DIR ACCESS PARA...
Categories: DBA Blogs

Pages