Tom Kyte

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

How to prevent some firewall from terminating SQL*Plus sessions with long-running statements?

Wed, 2018-11-07 11:06
Hi Tom, In know that I should talk to the firewall admins to get things fixed but this is - unfortunately - out of scope. So here we go: We have a database running on some Linux server and some client application running on some W2k16 server. T...
Categories: DBA Blogs

Who last updated an Oracle object

Wed, 2018-11-07 11:06
Hello, My goal is to produce a list of all objects (procedures, packages, functions, triggers, and tables) that have changed in the database since they were first created. I have a query that compares "created" and last_ddl_time" in the vi...
Categories: DBA Blogs

Counter to indicate rate changes

Tue, 2018-11-06 16:46
Hi Tom! Glad to have you back. Oracle: 12.1.0.2 My challenge is as follows: The t_res table contains hotel room reservations that consist of a reservation id (res_id), date of stay (res_date), and a rate card (rate_id) which typically dic...
Categories: DBA Blogs

How to split big union all query

Tue, 2018-11-06 16:46
Hi Tom In my application uses SSRS tool and PLSQL .According the requirement I have category as a in parameter like 'AD','SA','SSV' and 'SOA' , so I have written single query with union all operator for all category. Now problem is my query has b...
Categories: DBA Blogs

Data pump import unable to import table that data pump export exported

Tue, 2018-11-06 16:46
I'm having trouble importing a table that export exported. The error I'm getting is "ORA-14308: partition bound element must be one of: string, datetime or interval literal, number, or NULL", but according to the documentation "The partit...
Categories: DBA Blogs

Authorization with External User Access in Application Express

Mon, 2018-11-05 22:26
I need to be able to have external (business) users to be able to access only a few features of the site, for example, accounting can only see pages A and B while executives can see A, B and C. I need to be able to have multiple groups of people...
Categories: DBA Blogs

Execute package procedures in parallel

Mon, 2018-11-05 22:26
Hi all, For our customer, we start a process in the night to generate orders for 400 stores. Each store can regenerate its order during the day so the procedure is executed separately for each store. For each store we start a scheduled job with...
Categories: DBA Blogs

Insert Rows from one table to columns of other table.

Mon, 2018-11-05 22:26
Hi, Hope you are doing good and the Open World was exciting. I have got 2 tables, Table1 has the columns and data that has to be inserted as rows into Table2. I have shared an example of 5 columns, however the number is not constant. This can ch...
Categories: DBA Blogs

Upgrade DR to 12.2.0.1

Mon, 2018-11-05 22:26
Hi, The project is to upgrade 11gR2 RAC database to 12cR2(12.2.0.1) with minimal downtime. The approach is using standby database. Logical standby database cannot be done. Number of nodes in 11gr2 cluster 2. What i am doing >>> 1> P...
Categories: DBA Blogs

Query Builder does not Auto Join Tables in version 18.2.0.183

Fri, 2018-11-02 22:06
Your blog is terrific, BTW... When I drag and drop tables into query builder within SQL*Developer, the "chain link Icon" does not appear next to the table graphic. The tables have enabled foreign key constraints, however query builder will not sh...
Categories: DBA Blogs

Redo log and controlfile I/O contention.

Fri, 2018-11-02 03:46
Hi Tom, Can you explain to me how often an Oracle database needs to read from the control file? And can the placement of the control file on the same disk/diskgroup as the redo logs cause I/O contention? Problem context: We have a 3 node RAC...
Categories: DBA Blogs

DB Copy

Fri, 2018-11-02 03:46
Hi, Tom. I have two DB instance on different server. One DB is main, second db is "archive". We need copy only data from first db to second which contains CLOB and BLOB info, whole table. It's not copy all data from one to second. It's copy some da...
Categories: DBA Blogs

Manual dataguard

Fri, 2018-11-02 03:46
hi there we are using oracle database 11g (11.2.0.1.0 Standard Edition one) for our production db i have gone through the docs to setup automatic log apply which is available only for Enterprise Edition.. i saw the notes from the doc saying ...
Categories: DBA Blogs

How can I create a table with a parallel option

Fri, 2018-11-02 03:46
Hi Tom, I will create a table with 1000M records. Can I create the table with some level parallel option setting. Because later on, some people(end user) will be accessing the table with kinds of queries with different conditions, my purpose is to...
Categories: DBA Blogs

Performancetuning UTL_SMTP

Fri, 2018-11-02 03:46
Hi Tom, we are using UTL_SMTP so send a large amount of mail. It's a newsletter-project and the mails must be delivered in a short time-period. All Mails are relayed through a local linux-box mailserver (exim) with an 2Mbit Internet-Connection....
Categories: DBA Blogs

Can you see the values of bind variables without enabling trace?

Fri, 2018-11-02 03:46
Sometimes I want to capture the SQL a process is running, and often the quickest method is to use the session browser tool in Toad, SqlDeveloper, or just the v$session and v$sql tables directly. However what I obviously see is this: <code> Select...
Categories: DBA Blogs

How do i prevent end users from connecting to the database other than my application?

Fri, 2018-11-02 03:46
Hi Tom, Thanks for your help. We have a client/Server and a web interface. We want our end users to connect to the database only through our applications. How can we prevent the user from connecting to the database using SQL*PLUS or Microso...
Categories: DBA Blogs

Exchange partition fails with equal fields and different bytes due to cast ( timestamp )

Thu, 2018-11-01 09:26
<i></i>Hi, I'm testing a few Exchange Partition concepts to improve a process that I created. The database version I'm using is: <b><i>Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production PL/SQL Release 11.2.0.3.0 - ...
Categories: DBA Blogs

Get the first word in a string

Thu, 2018-11-01 09:26
Haai.. i have input like this in below.. <code>Create table Char ( name varchar2(1000) ); Insert all into Char(name) Values('Ask Tom') into Char(name) Values('Become Again Tom') into Char(name) Values('United Sates') select * from du...
Categories: DBA Blogs

Enforce either of column1 or column2 to be not null

Thu, 2018-11-01 09:26
Hi, I have a table where I need either of the column to be not null, meaning at any point of time only one of these column should have a value and the other should be null. I tried below: <code>CREATE TABLE "BCKC"."TEST" ( "ID" NUMBER(9,0),...
Categories: DBA Blogs

Pages