Tom Kyte

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

Storing JSON file in the database

Wed, 2019-02-06 06:26
I created a table with two JSON columns. CREATE TABLE USER.JSON_DOCS ( id RAW(16) NOT NULL, file CLOB, CONSTRAINT json_docs_pk PRIMARY KEY (id), CONSTRAINT json_docs_chk CHECK (file IS JSON) ); Then I tried to storage a JSON in...
Categories: DBA Blogs

View that can be used to get last SQL elapsed time

Wed, 2019-02-06 06:26
Hello Gentlemen, We currently don't have OEM tool. Is there a data dictionary view that can be queried that will provide the last elapsed time for a particular query that has an elapsed time of less than 5 seconds? v$sql view has a column called...
Categories: DBA Blogs

Keeping a column constant

Tue, 2019-02-05 12:26
Hi, <code>Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit</code> We have a need to keep certain values in a table constant in a cloned database. We store email addresses in a table, and use these for generating email commu...
Categories: DBA Blogs

SELECT FOR UPDATE SKIP LOCKED

Fri, 2019-02-01 16:46
Hi Team Have a scenario to select a particular set of rows from a table for further processing. We need to ensure that multi users do not work on the same set of rows. We use SELECT FOR UPDATE SKIP LOCKED in order to achieve this. EG:a simp...
Categories: DBA Blogs

Sending e-mail! -- Oracle 8i specific response

Fri, 2019-02-01 16:46
How to send personalized email to clients registered in my portal www.intrainternet.com using the information stored in our Database Oracle 8i automatically?
Categories: DBA Blogs

changing priority of sessions

Mon, 2019-01-28 21:06
Hi Tom, a salesman from Oracle told me that i can identify sessions of users that are joining terabyte tables with other terabyte tables and i am able to reduce their priority to prevent other users from suffering from such a kind of queries that ...
Categories: DBA Blogs

nls_date_format

Mon, 2019-01-28 21:06
hi Tom, i saw your FAQ regarding this but couldn't find the answer. my initORCL.ora's nls_date_format is being ignored! is there a way to adjust sqlplus so it uses a certain date format always - other than by using a sql statement or a trigg...
Categories: DBA Blogs

system tablespace free 0.01%

Mon, 2019-01-28 21:06
I do not have DBA background but I recently inherited an Oracle database on NT that's extensively used for testing. While checking the tablespaces, I noticed that system ts is only 0.01% free. What does this mean and what should I do to have more fre...
Categories: DBA Blogs

TDE Tablespace encryption - assign separate, distinct duties to database administrators and security administrators

Wed, 2019-01-23 13:26
Hi Tom, Our situation is that we are going to put some sensitive data in an Oracle DB hosted by vendor (external party). To minimize impact to App, we like to choose TDE tablespace encryption. Since the data is highly restricted, we don't allow...
Categories: DBA Blogs

Stored Procedures for insert statements

Wed, 2019-01-23 13:26
Hello, Ask Tom Team. I have like 20 tables using identity column for pk. Right now, I'm using returning clause to retrieve the pk (identity column using Oracle 12c feature) to be used as fk in the child tables. I have been asked to use stored pr...
Categories: DBA Blogs

Composite Primary Key and Number generated

Wed, 2019-01-23 13:26
Hello Tom, I have one table Invoice which has 6 columns.Out of which 2 column are composite primary key. <code>create table invoice ( area integer, invoid number generated always as identity, designation varc...
Categories: DBA Blogs

Search by timestamp(6) on big tables (several approaches)

Wed, 2019-01-23 13:26
Hello Tom, it's always instructive to read your opinion to different topics around Oracle databases. We have a big table with round about 200 million records added a month. On this table there some selects doing some calculation per day and one...
Categories: DBA Blogs

Exadata supports NLS_CHARACTERSET

Wed, 2019-01-23 13:26
Hi, We are working on DB migration from Solaris to Exadata Linux. The current NLS_CHARACTERSET is AMERICAN_AMERICA.WE8MSWIN1252. Does Exadata on Linux support NLS_CHARACTERSET AMERICAN_AMERICA.WE8MSWIN1252? Is it MUST to migrate to AL32UTF8...
Categories: DBA Blogs

RAC - ASM without clusterware?

Wed, 2019-01-23 13:26
Hello, I am confused with the requirement needed to setup 2-node (Linux server) RAC with SAN storage, as I am new to this SAN storage. I see that it is recommended to use ASM as volume manager and file system to RAC instance. I heard that conc...
Categories: DBA Blogs

Invalid reply code for Utl_stmp

Wed, 2019-01-23 13:26
Hi there, I am trying to use utl_smtp to send out email to our customers. How can we tell is the email successful delivered ? Does reply code indicate the successful of delivery ? Below is a testing procedure I had created. I got the same result o...
Categories: DBA Blogs

Solid state disks

Wed, 2019-01-23 13:26
I recently tested an Oracle database on a Solid state disk. Here is the link http://www.superssd.com/products/ramsan-400/ The performance is really great if you compare it with regular disks. I couldn't find many documents or threads to ge...
Categories: DBA Blogs

Real-Time Materialized Views

Tue, 2019-01-22 19:06
Hi, I "think" I saw Maria shown a new feature that allow query rewrite with stale MV + MV Log. (feature similar to IM store + IM trail query rewrite.) What is the name of this feature and how do I use it? Thanks, P.Huang
Categories: DBA Blogs

Uncompressing externally zipped documents

Tue, 2019-01-22 19:06
Good morning/afternoon guys. I am loading XML files from Windows into the database as BLOBs via an Apex front-end, however the performance leaves much to be desired (unsurprising, due to the 1Gb file sizes). I have a cunning plan to first zip t...
Categories: DBA Blogs

Oracle Text Substring Search on Encrypted String

Tue, 2019-01-22 19:06
Hi, Do Oracle Text have capability to sub string search on encrypted string. The Data resides in table as encrypted string (using DBMS_CRYPTO). The sub string passed from UI is non encrypted and data resides on DB is encrypted. Thanks & Re...
Categories: DBA Blogs

Autotrace, statistics and the four majors products

Tue, 2019-01-22 00:46
Hello The Masters of Oracle, Last time I was using AUTOTRACE in order to collect statistics about a SELECT. I used SQLcl to change from SQL*Plus and... oh, it was a schock! Statistics were very very differents from SQL*Plus. So, I decided to...
Categories: DBA Blogs

Pages