Tom Kyte

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

Default date part of TO_DATE function

Thu, 2018-01-25 21:46
Hi AT-Team, LiveSQL seems to be broken, so sorry no test case there. We are building a system in which developers are converting strings holding time-only data to a DATE in Oracle SQL in order to build queries involving the time of day later on...
Categories: DBA Blogs

Security model for SQL Tuning sets

Thu, 2018-01-25 21:46
Hello Tom, Is it possible for a user without DBA priv and Select privs on base tables to create SQL Tuning Tasks? what if we create a procedure under another user with DBA priv and grant the user execute on the procedure? Thx for the Supp...
Categories: DBA Blogs

Exchange partition issue

Thu, 2018-01-25 21:46
Hi, We have exchanged partition of the landing table with the history table using the below statement <code>ALTER TABLE lnd.hist_tbl EXCHANGE PARTITION v_partname WITH TABLE lnd.curr_tbl WITHOUT VALIDATION; v_partname - max partition name ...
Categories: DBA Blogs

JSON_QUERY and JSON_VALUE not able to handle more than 1 lakh characters

Thu, 2018-01-25 21:46
JSON_QUERY and JSON_VALUE not able to handle more than 1 lakh characters. I've a big report (size can change at any time) and push it to oracle as JSON and parse there. I'm getting JSON Syntax Error, may be since the JSON is truncated to 4000 bytes. ...
Categories: DBA Blogs

Connect to local server 'SYSTEM' Error with sql developer

Thu, 2018-01-25 03:26
I install SQL Developer and log in my local server SYSTEM once. but today when I try to log in, is says error: An error was encountered performing the requested operation: ORA-12560: TNS:protocol adapter error 12560. 00000 - "TNS:protocol ada...
Categories: DBA Blogs

Remove SQL Qeury in CSV Output

Thu, 2018-01-25 03:26
I have a sql query that needs to spool a select statement in a csv file. Here is the query: <code> set colsep '|' set echo off set feedback off set linesize 1000 set pagesize 0 set sqlprompt '' set trimspool on set headsep off Spool 's...
Categories: DBA Blogs

Career advice for a very frustrated Oracle DBA

Thu, 2018-01-25 03:26
Hi, I am a senior Oracle DBA working in a prestigious government department. I have good benefits, a good salary, my job is not so stressful. I have been working for the government for the last nine years before that are used to work for the private...
Categories: DBA Blogs

When replace PACKAGE do I need to recreate GRANT and SYNONYM

Thu, 2018-01-25 03:26
I have PACKAGEs that already set the GRANTs and SYNONYMs. I want to make some changes to a PACKAGE: create or replace PACKAGE "AAA_PKG" After that do I need to reset the GRANTs and SYNONYMs? CREATE OR REPLACE SYNONYM ABC.AAA_PKG F...
Categories: DBA Blogs

Using /*insert*/ and SET SQLFORMAT

Wed, 2018-01-24 09:06
Hi Tom, I am interested in using the SQL formatting option - /*insert*/ to create an insert script. Your example of a simple insert script - where one pulls from a single table and creates an insertion script of that table - bypassing having ...
Categories: DBA Blogs

geenrate xml from relational data

Wed, 2018-01-24 09:06
I want to export dept/emp table to xml file as follows <envelope> <dept> <deptName> </deptName> <emp> <empNo> </empNo> <lastName> </lastName> <salary > </salary> </emp> </dept> ...... ..... </envelope> And another program to rea...
Categories: DBA Blogs

locking disconnected tables

Wed, 2018-01-24 09:06
Hi Tom, I have this problem: I need to "lock for update" records in two disconnected tables. For "disconnected" I mean two tables that haven't any relation, for example the tables EMPLOYEE and FOLDER: when I have to update an employee record I woul...
Categories: DBA Blogs

"APOS;" appearinig in SQL*PLus output. Want actual apostrophe instead.

Wed, 2018-01-24 09:06
How can I suppress the appearance of "apos;" and similar web-friendly tags from my SQL*Plus output. In place of "apos;" I'm actually looking for the apostrophe "'". Thank you!! - Mike Kemp
Categories: DBA Blogs

Upgrade 11204 to 12201 with standby in place

Wed, 2018-01-24 09:06
We are upgrading 11204 Databases to 12201. We build new linux server, create 12201 DB, expdp from 11204 then impdp into 12201. We don't do "in place" upgrade. To upgrade a master with physical standby, what will you recommend ? I think the be...
Categories: DBA Blogs

clob data conversion

Wed, 2018-01-24 09:06
I Have table, which has clob data type, value stored is in hexadecimal data type. Please let me know how can i convert clob data to varchar
Categories: DBA Blogs

How much overhead is caused by adding a mostly empty BLOB column to a table?

Wed, 2018-01-24 09:06
Let's say I have a large-ish (a few hundred thousand rows) table that contains file names, file sizes and other meta-information for files, but not the actual files themselves. I then decide to add a BLOB column to the table to make it possible to...
Categories: DBA Blogs

Evaluation of expressions

Wed, 2018-01-24 09:06
Does Oracle perform short-circuit boolean evaluation when evaluating expressions? For example, consider the following part of a query: select lcs_id, loan_id, client_id, security_id, masterfund_id, category, calc_lvr, price, ...
Categories: DBA Blogs

Critical Production Databases running on Standard editions

Tue, 2018-01-23 14:46
Dear Tom Please explain the consequences of running a production database on Standard edition and no-archivelog mode. Regards Hari
Categories: DBA Blogs

Primary key with Non Unique Index

Tue, 2018-01-23 14:46
Hi Team, I was just playing around with some stuff so thought sharing with you. My understanding for Primary key was that Oracle uses Unique Index to enforce primary key constraint on table's column to be unique. Below is the code that I tri...
Categories: DBA Blogs

@DATENOW()

Tue, 2018-01-23 14:46
We have a situation where the golden gate is used to replicate the source data into target ( both source and target are oracle 12c). on the target we have a column ( Target Timestamp) , primarily to track the date/time the a row is replicated. this ...
Categories: DBA Blogs

XMLTYPE null element behavior on 11gR2 vs 12.1/12.2

Tue, 2018-01-23 14:46
Hi Guys, I've just noticed inconsistency between behavior of conversion of object/collection data to XMLTYPE between 11g R2 and 12.1/12.2 database. Testcase: <code> create or replace type test_dummy_object as object ( id number, "name...
Categories: DBA Blogs

Pages