Tom Kyte

Subscribe to Tom Kyte feed Tom Kyte
These are the most recently asked questions on Ask Tom
Updated: 12 hours 6 sec ago

How to get MIN and MAX of Consecutive Numbers

Mon, 2018-01-15 23:46
Thanks for your awesome help on "Trying to split serial number ranges" question today. I have another opportunity to ask a question and it's somewhat related to my earlier question. I hope this would be an easy one. I have a table with the foll...
Categories: DBA Blogs

Coding Parallel Processing on 12c

Mon, 2018-01-15 23:46
Hello Tom, I gave the below link a try and applied the method on 12c. https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4248554900346593542 But it takes same time as serial processing. Could you please light me up what I'...
Categories: DBA Blogs

Using FBA with Materialized Views

Mon, 2018-01-15 05:26
Please refer to the LiveSQL link. NB some of the statement do not work because the user has insufficient privs. to create and manage Flashback areas in the LiveSQL environment. The code creates a table, inserts data , creates a flashback archive t...
Categories: DBA Blogs

DBMS_AQ.LISTEN to listen to a Single/Multi-Consumer Queue

Sun, 2018-01-14 11:06
Dear Experts, Need your guidance/suggestions to resolve this issue: Part of oracle advance queueing implementation, we've to dequeue the message as soon as it has been enqueued into the queue. This should happen immediately without any manual inter...
Categories: DBA Blogs

Doing DB upgrade RAC , via DBUA, from 11gr2 to 12cr2 . Using TDE (tablespace level) on source database

Sun, 2018-01-14 11:06
I am running a DB 11.2.0.4 (RAC db) that has TDE implemented - Tablespace level. Source db (11.2.0.4) has TDE implemented. sqlnet.ora file on each node has the entry ENCRYPTION_WALLET_LOCATION. Also each node has the wallet and auto login file (t...
Categories: DBA Blogs

Audit Trail : Disable my bash script audit

Sun, 2018-01-14 11:06
Hello Tom. I set audit trail to "XML,EXTENDED" , because my $AUD table was growing to much. I have a lot of 4kb files generated. I have several scripts in my crontab, and that is what is being audited. The content of the files are like this:...
Categories: DBA Blogs

YTD logic using analytic functions

Sun, 2018-01-14 11:06
Hi Tom, I am trying to get YTD in a view. I have below view, <code>create or replace view billsummary as select szRegionCode, szState, szPartitionCode, szProduct, TO_CHAR(dtSnapshot,'YYYY.MM') szMonthYear, szJioCenter, ...
Categories: DBA Blogs

Configuring a SQL Loader control File to exclude the second row

Sun, 2018-01-14 11:06
Hi, I am trying to configure a control file that excludes the second line of data from the load. The system is automated and I have been tasked to see if there is a solution to this. I am very new at this. I have been told about a discard file of ...
Categories: DBA Blogs

Dynamic query to print out any table

Sun, 2018-01-14 11:06
Hi Tom How i can use procedure have a parameter type of query 'any query' and print the data looks like comma separated ? please help ..
Categories: DBA Blogs

Truncate statement in data dictionary,

Sun, 2018-01-14 11:06
Hello, I have observed truncate statement (command_type = 85) doesn't appear in V$SQL. However, it does in V$SQLTEXT and V$SQLTEXT_WITH_NEWLINES. My intention is to extract the time of the truncate statement. How can I achieve this task witho...
Categories: DBA Blogs

Tracking User logins between 7:00 pm and 7:00 am

Sun, 2018-01-14 11:06
Hello Sir, I have a requirement to track and generate a report of the users logging into the database after office hours, i.e., between 7:00 pm and 7:00 am on a daily basis. We have audit_trail set to 'DB' I appreciate if you can help me in ...
Categories: DBA Blogs

Loading CLOB Columns from File

Sun, 2018-01-14 11:06
Loading table data from external source to oracle for data mining and analysis. One particular table has 7 8000 byte character fields which must be loaded into CLOB columns. Most of these are empty. The data is provided as a tab delimited text file w...
Categories: DBA Blogs

Not getting connection with database in cmd window

Sun, 2018-01-14 11:06
Hi, I have just installed the Oracle Database 11g Express Edition from www.oracle.com. When I was connecting database in command prompt window I am getting error while entering paasword for user name "system". I am typing below the exact error w...
Categories: DBA Blogs

Global temporary table clears on commit

Fri, 2018-01-12 22:26
Hi, Please, help me to understand - whats happenging?: I've GTT with ON COMMIT PRESERVE ROWS and after inserting values with procedure: " insert into gtt ... select ...; commit; " next sql " select * from gtt " returns nothing!!! but,...
Categories: DBA Blogs

Fast wild-card searching

Fri, 2018-01-12 22:26
What is the best way to implement a solution to the following problem so that wild-card searching is very fast. PROBLEM: Two column tabular data with ~100 million rows of the form given below. Searching is on the first column. The number of sear...
Categories: DBA Blogs

Make an aggregate for statistics

Fri, 2018-01-12 22:26
Hello Tom. I've to translate a mathematical formula from "Google Sheet" to a "trigger in Oracle". A part of this formula in Google sheet is <b>MOYENNE(3;1;MAX(1;1;9;1);MAX(1;1;9;1))</b> I know translate this formula like that : <code>SELE...
Categories: DBA Blogs

Possibility of functional index/key in foreign key

Fri, 2018-01-12 22:26
Hi, I want to ask if there is possibility to use functional index/key in foreign key. As an example : In T1 there is opera_user_id column, with index upper(opera_user_id), in T2 a column ID, indexed as PK. From T1 there is FK defined by the column ...
Categories: DBA Blogs

Index on null column

Fri, 2018-01-12 22:26
We have created a functional index on one column which is always null. Other application queries our table with this column and query take more than 3 min to complete.. Table - My_Table Columns -UserId, UserName,ChinesName, Country Index - lowe...
Categories: DBA Blogs

Automate export of result data to defined path

Fri, 2018-01-12 22:26
Hi, I am using Oracle 11g and mostly works on tasks which requires data extraction for clients. I have been trying to automate the process of extraction and I found the below code in one of your answers. <code> --------------------------------...
Categories: DBA Blogs

How the compression works for Direct path loading with Parallel session

Fri, 2018-01-12 04:06
Hi Team, We have implementing the row level compression with applying the Advance compression at tablespace level. but I have observed that the my table size is getting higher even though compression level is Advance. We are loading the data fro...
Categories: DBA Blogs

Pages