Tom Kyte

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

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

SET COMMAND

Thu, 2018-11-01 09:26
Sir I am getting the following error message after executing the command "SET DESCRIBE DEPTH 4 INDENT ON" at SQL prompt SET DESCRIBE DEPTH 4 INDENT ON" is Obsolete Please advise how to describe nested object types Regards DS Rao
Categories: DBA Blogs

advantage of procedure within a procedure

Thu, 2018-11-01 09:26
I've seen package code where there are procedures defined within a procedure, then the "within" procedure is called once in the main procedure - actually I've also seen 3 "within" procedures and the only thing the main procedure does is call these 3 ...
Categories: DBA Blogs

Oracle Fact Table creation and loading strategy

Wed, 2018-10-31 15:06
Thanks in advance for the support.I have a few questions: 1. I will have to load 7 years worth of history data with 128 billion rows into a new Fact Table, which also will have inserts as well as updates for the last 90 days (98% inserts and 2% up...
Categories: DBA Blogs

ORDER BY and CONSISTENT GETS divided by ten

Wed, 2018-10-31 15:06
Hello TOM, When I use an ORDER BY, I see that the consistent gets are divided by ten in my tests, can you explain why? Here are my tests. First I create a table. <code> SQL> create table test_obj01 as select OWNER, OBJECT_NAM...
Categories: DBA Blogs

Ordering serializable transactions

Wed, 2018-10-31 15:06
Hi, I'm trying to work out how to assign an ordering to the results of potentially concurrent serializable transactions so I can definitively say either: - They both had an identical view of the database, or - Identify which one had a later view...
Categories: DBA Blogs

Spatial queries involving lattitude and longitude

Tue, 2018-10-30 02:26
Team, We have a table in an application that has longitude and lattitue as NUMBER datatype. also composite index exists on the columns (LATITUDE, LONGITUDE) in this order. the sql from the application goes like this. select * from the_tabl...
Categories: DBA Blogs

ORA-08103: object no longer exists

Sat, 2018-10-20 23:06
Hi Chris/Connor, Could you please have a look ate below scenario: Whenever we process orders in bulk lets say bunch of 1000 orders, out of which certain orders are failing in the MERGE statement with an error "ORA-12801: error signaled in paralle...
Categories: DBA Blogs

Key-preserved table concept in join view

Sat, 2018-10-20 23:06
I see a lot of examples where primary keys are missing or indexes are not unique. But I didn't see anything matching this example. There are 3 tables joined, and the 3rd table has a compound key that gets one value from the first table and one from...
Categories: DBA Blogs

How to extract XML data using extract function

Thu, 2018-10-18 16:06
inserted row by using below statemet :- <code>insert into xmlt values('<?xml version="1.0"?> <ROWSET> <ROW> <NAME>karthick</NAME> <SALARY>3400</SALARY> </ROW> <ROW> <NAME>c</NAME> <SALARY>1</SALARY> </ROW> <ROW> <NAME>mani</NAME> <SALARY>1</SAL...
Categories: DBA Blogs

Elaborate why 5 & same table used in below query

Thu, 2018-10-18 16:06
<code>select distinct * from t t1 where 5 >= ( select count ( distinct t2.sal ) from t t2 where t2.deptno = t1.deptno and t2.sal >= t1.sal );</code> I'll be grateful if you can explain. how the number work ,5, without var...
Categories: DBA Blogs

Find if a string is Upper, Lower or Mixed Case, numeric, Alpha Numeric etc

Wed, 2018-10-17 21:46
Dear Experts, I populated a table with few rows of strings that are Upper/ Lower/ Mixed case, alpha-numeric, numeric etc. 1. Now I would like to evaluate they type of string using a case statement. I tried using regexp_like, but it fails when ...
Categories: DBA Blogs

Deadlock issue came while using set based sql

Wed, 2018-10-17 21:46
Hi Tom, We are using set based sql in my process, In that we are creating so many GTT tables in a package. And we are executing this package concurrently in more than ten sessions, these sessions will create temporary tables with different name a...
Categories: DBA Blogs

Foreign Keys with default values

Wed, 2018-10-17 21:46
Hello. I'm designing a database in Oracle 12.2 in Toad Data Modeler. It would get lots of inserts. I'm using identity columns as PK (basically I create a sequence and use it as default value in the column, sequence.nextval). When I connect the...
Categories: DBA Blogs

Pages