Tom Kyte

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

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

Missing RMAN Duplexed location

Thu, 2018-07-26 16:26
Hi, We are carrying out some RMAN prototyping in our offline environment for duplexing backup sets to two different locations. We have added the following RMAN persistent configuration settings: CONFIGURE DEVICE TYPE DISK PARALLELISM 2; CONFIG...
Categories: DBA Blogs

Multiple analytical functions in a query

Thu, 2018-07-26 16:26
Dear Tom, Thanks for this wonderful platform where there is always opportunity to learn something new on things we think we already know. Really appreciate it. I have a query regarding the analytical functions in Oracle. Query is based on the Emp...
Categories: DBA Blogs

online redo log corruption

Thu, 2018-07-26 16:26
suppose i have 3 online redo log groups with two members on each group. Suppose any group is corrupted, then how to find the the corrupted exact group??. How to find weather the data in online redo log file are weather written or not on disk (datafil...
Categories: DBA Blogs

Oracle client for MACBOOK

Thu, 2018-07-26 16:26
When can we have full Oracle client software for Mac iOS platform as MacBook is widely used by Oracle users.
Categories: DBA Blogs

list reports for a given dates range

Wed, 2018-07-25 22:06
<b></b>Hello there!! i am new in PL SQL so i have been given a task to develop a procedure by joining two tables and the program should give a report of invoices within a given range of dates.I want my program to show reports as per my specificati...
Categories: DBA Blogs

how to return result set from stored procedure.

Wed, 2018-07-25 22:06
We are developing an intranet application using Asp IIS and oracle 8i. I want to return a resultset from oracle stored procedure to Asp page. How do I do it? Like in sybase you say create procedure test (parameters) resultset (col1,col2 ) Lik...
Categories: DBA Blogs

Pages