Tom Kyte

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

Why are all table subpartitions going in the same tablespace?

Wed, 2018-05-02 22:26
Dears, I have a problem regarding automatic list partitioning with hash sub-partitioning. the problem is the automatic created hash sub-partitions are not well distributed on the correct table spaces although the first hash-partitions are well dis...
Categories: DBA Blogs

Simple update command keeps running

Wed, 2018-05-02 22:26
hi there i have simple update command <code>UPDATE USERMAST SET USER_LOCK='N' WHERE USER_NAME='rancf';</code> when i execute this query it keeps on running.. USER_ID is the pk for USERMAST table and i tried with USER_ID TOO,same thing is ha...
Categories: DBA Blogs

Different Execution Plan across RAC node

Wed, 2018-05-02 04:06
1. is it possible to have different execution plan across RAC nodes for the same Query ? 2. let's say if i run a query on Node 1 and generated a execution plan named "node1Query1" , will it share between different node ? or it will have another one...
Categories: DBA Blogs

Redo log threads in Real Application Clusters

Wed, 2018-05-02 04:06
Tom, I have been trying to understand the concept of redo log threads in a RAC environment. I understand how the redo logs work in a single instance database. But I cannot find proper documentation anywhere that clearly explains what a redo log thre...
Categories: DBA Blogs

schema table size growth per year

Tue, 2018-05-01 09:46
Hi, As part of capacity planning, customer is asking schema table size growth per year for last 10 years. Is there any way to get it? I tried the below query but through this we are not able to get size per year for last 10 years. SELECT ds....
Categories: DBA Blogs

PRAGMA RESTRICT_REFERENCES - Deprecatedish?

Tue, 2018-05-01 09:46
Hi guys, In the 12.2 PL/SQL Language Reference it says that <code>PRAGMA RESTRICT_REFERENCES</code> is deprecated. http://docs.oracle.com/database/122/LNPLS/RESTRICT_REFERENCES-pragma.htm#LNPLS01339 In the 12.2 Data Cartridge Developers Guide...
Categories: DBA Blogs

VPD predicate executes trice

Tue, 2018-05-01 09:46
Tom, I have a simple implementation of VPD which inserts a record into a log table when a select is performed on the table. I am seeing the log record inserted twice. As you can see from my example below my sql predicate does not limit any d...
Categories: DBA Blogs

Pivoting via SQL dynamically

Mon, 2018-04-30 15:26
SQL puzzle Names table nameData creation script <code> create table Names ( name char (10), primary key (name)); insert into Names values ('Al'); insert into Names values ('Ben'); insert into Names values ('Charlie'); insert into Names ...
Categories: DBA Blogs

SQLLOADER - Error Field in data file exceeds maximum length

Thu, 2018-04-26 20:06
Hi, I am trying to load a data file into a database table using SQL loader. I received the data in an Excel spreadsheet but I saved it as a comma delimited file. When I run the SQL Loader command no records are loaded - looking at the log file I ge...
Categories: DBA Blogs

Is it safe to use ROWID to locate a row?

Mon, 2018-04-23 18:46
Hi Tom, I'm looking at a client application which retrieves several columns including ROWID, and later uses ROWID to identify rows it needs to update: update some_table t set col1=value1 where t.rowid = :selected_rowid Is it safe to do so? ...
Categories: DBA Blogs

Unable to Find Sample Schema HR Scripts

Mon, 2018-04-23 18:46
Hi Install Oracle 12C on my personal laptop, unfortunately the sample schemas HR,Scott were not created so i tried to Execute below script from command prompt but it did not worked @?/demo/schema/human_resources/hr_main.sql when i checked the loc...
Categories: DBA Blogs

utl_http.begin_request results in protocol error when url size is big

Fri, 2018-04-20 17:26
Hi, while using utl_http package, we are able to make calls to a 3rd party webservice and all was going good till we hit transaction which resulted in big URL size - for ex one transaction had multiple rejections and url size is bigger than normal ...
Categories: DBA Blogs

Upgrade to 12c - High Fetch time vs. Low execution time

Fri, 2018-04-20 17:26
Hi Tom, We are migrating our databases from Oracle 11.2.0.3 to Oracle 12.1.0.2.0R1 on Exadata and after we did this, we are seeing extreme slowness in loading 3 of our application screens, even though the queries are running as or more efficiently...
Categories: DBA Blogs

Partitioning vs Indexing

Thu, 2018-04-19 23:06
Hi Tom, i have a question in partitioning by list of a table. I have a set of tables which need to be hystoricized once a new record is inserted: then i have a STATUS column which flag an active status (AT) and a historic one (ST). To match this re...
Categories: DBA Blogs

Oralce Open v$open_cursor counts simple "updates" as open with the use of a cursor (open, execute, fetch, close, commit)

Thu, 2018-04-19 23:06
I am checking for open cursors while running our client server application (application info below) with the query below and noticed that a simple ?update? without the use of any cursors shows as open cursor. When another ?update? is issued its repla...
Categories: DBA Blogs

Performance issue/session getting hang

Thu, 2018-04-19 23:06
Hi Tom, I have a table having around 5 million records. Table Structure : DESC RPT_MSG_CHANGE <code>Name Null Type ---------- -------- -------------- OID NOT NULL NUMBER PRODUCT NOT NULL VARCHAR2(20)...
Categories: DBA Blogs

Long Raw to BLOB

Thu, 2018-04-19 04:46
Hi Tom, We are using an Oracle 8.1.7 database. Is there a way in PL/SQL or Java Stored Procedure to convert a Long Raw into a BLOB? Thanks, Firas Khasawneh
Categories: DBA Blogs

Bug in Export Wizard?

Wed, 2018-04-18 10:26
When using the export wizard and browsing to an existing file that I want to overwrite with my new data results, I find when I select that specific file from the Export File Chooser window and click SAVE, the file that is actually selected is some ot...
Categories: DBA Blogs

dbms_stats and optimize techniques

Wed, 2018-04-18 10:26
I am setting the degree parameter of dbms_stats.gather_table_stat. could any one tell me that how we can calculate the value of this parameter and how this is link with the hint optimize techniques.
Categories: DBA Blogs

Best way to index uuid

Tue, 2018-04-17 16:06
Hello, What is the best way to index uud if I only do equal comparaison on it ? I gess that Hash index is better but I'm not sure. Regards Stephane GINER
Categories: DBA Blogs

Pages