Tom Kyte

Subscribe to Tom Kyte feed Tom Kyte
These are the most recently asked questions on Ask Tom
Updated: 4 weeks 1 day ago

Moving Constraints from one table to another

Wed, 2016-06-29 00:06
Hi asktom team, I want to convert a NON - PARTITIONED table to LIST - partitioned table. So to do that I am doing the following: FOR EXAMPLE take ORDERS table: -Renaming the TABLE ORDERS to ORDERS_OLD. -Creating a table ORDERS same as ORDER...
Categories: DBA Blogs

What are possible meanings of error OCCI ORA-12560: TNS:protocol adapter error?

Wed, 2016-06-29 00:06
Hi, The Oracle error messages are not always very descriptive. Searching the internet I found couple reasons why we can get ORA-12560 error: 1) Oracle database instance is not started 2) ORACLE_HOME is not set (for version 10) What are other ...
Categories: DBA Blogs

Peformance issue with user_tab_partitions query

Wed, 2016-06-29 00:06
Hi, We have the below query which is taking lot of time....while reading the partition name for the given high_value of table table_1. Here P_SEQ is the input value that we are passing through a procedure to this query. SELECT to_char(x.parti...
Categories: DBA Blogs

Inserting a BLOB file into oracle using a pl/sql script

Wed, 2016-06-29 00:06
I cannot for the life of me figure out how to successfully insert a file into a blob column in a DB, using a PL/SQL script. Should be able to reuse this script indefinitely, preferably, with several files at the same time, but as long as I can do on...
Categories: DBA Blogs

getting error for NOLOGGING NOCACHE

Wed, 2016-06-29 00:06
Hi there, We have recently migrated our database from Oracle 9i to Oracle 11g. Now,One of our procedure is having query to create external table of oracle loader type. It also include NOLOGGING NOCACHE. This procedure was working properly in Oracl...
Categories: DBA Blogs

Can Oracle 8i client connect to Oracle 12c server?

Wed, 2016-06-29 00:06
Can Oracle 8i client connect to Oracle 12c server? If yes, please anybody can help me that what are the changes does in Oracle 8i client's file and Oracle 12c Server's file...
Categories: DBA Blogs

Generating Dates between two date ranges

Wed, 2016-06-29 00:06
<code>Hi Tom , I need to write a query which will give me a list of dates between two date ranges (start date and end date) . For Example if my start date is 12/01/2003 and end Date is 12/31/2003. i need to generate my output as Date ---...
Categories: DBA Blogs

Refresh Materialized view when there is records in the source table

Wed, 2016-06-29 00:06
Hi , Need help to refresh one materialize view only when there is records in the table. say example . create materialize view MABCD as select field1,field2,field3 from abcdef ; my requirement is if abcdef have records greater than zer...
Categories: DBA Blogs

Different between FGA and virtual private database?

Tue, 2016-06-28 05:46
I am very confused with some terms such as difference between Fine Grained Auditing and virtual private database?
Categories: DBA Blogs

UTL EMAIL (HTML)

Tue, 2016-06-28 05:46
Hi Tom, I have issue with my UTL Email, when i am sendint the email the text matter comming in same line: please check comments in the code where the issue: create or replace PROCEDURE ...
Categories: DBA Blogs

Split a int value in multiple rows based on percentage and in multiple of another number

Tue, 2016-06-28 05:46
Hi Tom, I have situation where i have to divide a integer in multiple rows depends on the %value given and the rounding specified. The value in ASSIGNMENT table needs to be divided in multiple of ROUNDING depends on the CONSIGNMENT records. In...
Categories: DBA Blogs

Optimal number of partitions

Tue, 2016-06-28 05:46
Tom, I have this table with around one million records. The table has the potential to grow to 2 million, 3 million or even more... I am planning on partitioning this table using hash partitioning on the most queried column. A few questions: ...
Categories: DBA Blogs

Adaptive Cursor Sharing

Tue, 2016-06-28 05:46
Hi Tom, I am aware, you have already clarified about the same subject many times. I have a table, which stores customer transaction information. Data load is happening continuously into the table. Since it is a transaction table, given a day,...
Categories: DBA Blogs

Oracle 10g upgrade

Tue, 2016-06-28 05:46
<code>Tom You mentioned that the AskTom site was recently upgraded to 10g. I take it therefore that you consider it to be of sufficient quality to run production code, even though the officially released version is only a couple of months old ? ...
Categories: DBA Blogs

datapump export using DBMS_DATAPUMP

Mon, 2016-06-27 11:06
Good afternoon, it is a beginning programmer. I had a problem with DBMS_DATAPUMP.data_filter. Data_filter not work. create PROCEDURE exp_tables_w_qfilter ( p_schema_name IN VARCHAR2, -- 'LTP' p_table_name IN VARCHAR2, -- 'AUDITTABLE_TEST' p_t...
Categories: DBA Blogs

Transpose Rows into Columns

Mon, 2016-06-27 11:06
I have a table like: Client Process Date Status A B 21-june Y C D 22-june N A B 22-june N...
Categories: DBA Blogs

2 highest salary department wise ..alternate method

Mon, 2016-06-27 11:06
<b>select * from employees e1 where :parameter=(select count(distinct e2.salary) from employees e2 where e1.salary<=e2.salary);</b> --<i>hi can anyone explain me this query .i know this can be solve using rank,dense_rank but confused while ...
Categories: DBA Blogs

Oracle External Table ODCIEXTTABLEOPEN error while selecting count(*) of the table

Sat, 2016-06-25 04:06
Hi I am explaining from beginning, My Server is : UNIX BOX (HPSA) I have created the below folder in this UNIX server and folder permissions are mm13pb:/ $ ls -ltr drwxr-xr-x 7 root root 4096 Dec 15 2013 data mm13p...
Categories: DBA Blogs

Unused Index(s) for a specific Time?

Sat, 2016-06-25 04:06
<code>Hi Tom, I want to drop those indexes which are not used in the last 2 months so to reclaim space. For example There are two indexes on scott.emp on the empno and the other is on deptno. While the application is only using empno index but no...
Categories: DBA Blogs

SP2-0734 when try to execute line begins with "#" character

Fri, 2016-06-24 09:46
Hi, I need to execute script (package body) that contents a line with first character "#", for example: ... ... htp.p(' <script> <style> #tbTabs tr td table Port ... ... When execute line "#tbTabs tr td table Port", this raises the error:...
Categories: DBA Blogs

Pages