Tom Kyte

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

SQL*Plus copy command errorring on CLOB

Fri, 2018-07-13 17:26
I am trying to move select records from one database to another using the copy command and I am getting this error below. Is there a way around this using the copy command? Thank you, A.J. SQL> copy from saturn/******@test insert SFRAREG ...
Categories: DBA Blogs

Global synonym not used

Thu, 2018-07-12 23:06
Hello, I need some help understanding the synonym behavior. We have a table in production database with a global synonym, which is being referenced by other objects in the database. As part of a weekly process that runs every weekend, we drop this...
Categories: DBA Blogs

MTS Configuration

Thu, 2018-07-12 23:06
Hi Tom ! I have configured MTS. My INIT.ORA and tnsnames.ora file configurations are given below. I have given PORT=1528 in the INIT.ORA file and when I query the V$DISPATCHER, I am surprised to see that the PORT numbers are chaning always i.e ...
Categories: DBA Blogs

Which Indexes are being used by our Application?

Thu, 2018-07-12 04:46
We have just gone live and I have noticed alarming growth in the indexes. Closer investigation shows several large, heavy transactional tables with a large number of indexes. I believe we are duplicating indexes and that some of the could be remove...
Categories: DBA Blogs

Is it Possible to Audit Manual Partition Creation Specifically?

Tue, 2018-07-10 16:06
Are you aware of any way to specifically audit the addition of new partitions? So far my searching has come up fruitless. Auditing is enabled within the DB which records each <code>alter table</code> command. However, that is too large a net. The spe...
Categories: DBA Blogs

Passing partition name dynamically to get records of a specific partitions from a partitioned table

Tue, 2018-07-10 16:06
<code> CREATE TABLE TOM_RESER_DERESERVATION ( ELEMENT_id VARCHAR2(200 BYTE), ELEMENT_LABEL VARCHAR2(200 BYTE), PRODUCT_NAME VARCHAR2(100 BYTE), CIRCLE VARCHAR2(100 BYTE), COUNTRY VA...
Categories: DBA Blogs

Partition query - limiting results

Mon, 2018-07-09 21:46
I have a situation where I am trying to determine the taxability of an invoiced line. If the invoiced line quantity is 6, for example, the detail lines should not exceed 6. The problem is that if one of the detailed lines causes the cumulative q...
Categories: DBA Blogs

COLUMNS to ROWS

Mon, 2018-07-09 21:46
Hi Team, Could you please have a look at below use case and help to form SQL/PLSQL using which I can get the below report.. Table: order_country : holds order id and country its belong. There can be 100 and more countries in that but for sampl...
Categories: DBA Blogs

Using identity columns in Oracle 12c

Mon, 2018-07-09 03:26
What is the difference between using sequence.netxval as DEFAULT value in a column or check the column as identity? Please, check the following scenarios: <b>SCENARIO 1:</b> CREATE TABLE USER1.TEST_TABLE ( ID NUMBER GENERATED BY DEFAULT AS I...
Categories: DBA Blogs

Using Factored Subqueries within SQLPLUS

Mon, 2018-07-09 03:26
I have a Perl script that invokes SQLPlus to execute an Oracle query. the SQL uses the WITH clause to create several named queries with the purpose of reusing them later within the query. the general structure of the SQL is as follows: <code> S...
Categories: DBA Blogs

Multi threading in PLSQL

Mon, 2018-07-09 03:26
Team, In current project, one of the batch process procedure is called in Java layer for Multi threading purpose. As current requirement, we need to call same stored procedure in PLSQL layer instead of Java layer. we tried to implement using DBMS...
Categories: DBA Blogs

insert primary key value by trigger

Mon, 2018-07-09 03:26
create or replace trigger t_emp_no before insert on emp_info begin insert into emp_info(emp_no) values(s_emp_no.nextval) end; / i tried to entry primary key emp_no values without showing it on oracle 10g form with S_emp_no sequence .Problem is...
Categories: DBA Blogs

Updating salary rows

Mon, 2018-07-09 03:26
hello Tom, i wrote that mentioned plsql update salary procedure.It compiled also successfully.But it didn't change any of my salary row.Actually,I am new in programming.I want to know .how does is work. how can i confirm that it is working & how ca...
Categories: DBA Blogs

Equality check and TO_NUMBER bug with trailing CHR(0) on SUBSTR function

Fri, 2018-07-06 20:46
Hello Tom ! It seems that Oracle has a bug in TO_NUMBER function on strings with trailing CHR(0). For example it happens when SUBSTR function is used for which there is not defined length parameter. Example: <code>select substr(session_id...
Categories: DBA Blogs

Inherit Privileges privilege to PUBLIC

Fri, 2018-07-06 02:26
I was doing a query on my database against dba_tab_privs where the grantee was PUBLIC. I noticed that for every user I have in my database, PUBLIC has "INHERIT PRIVILEGES" on that user. Should this be something that is allowed? How exactly is this...
Categories: DBA Blogs

LOB Storage parameters

Fri, 2018-07-06 02:26
TOM, I wrote a script to create a table with LOB columns. I specified the LOB storage parameters as follows: <code>CREATE TABLE CONFIGURATION (CFG_ID NUMBER(19,0) NOT NULL ENABLE, CFG_CONTEXT_ID VARCHAR2(255) NOT NULL ENABLE, CFG_...
Categories: DBA Blogs

maximum number of expressions

Fri, 2018-07-06 02:26
Hi, I have a this... Select * from MYDB where MYDB.ID IN (1,2,3...5000000) Any way to export without 1000 output lines ?? Thanks
Categories: DBA Blogs

How to manage disable/enable Maximize/Resize in oracle forms?

Fri, 2018-07-06 02:26
i need to manage disable/enable Maximize,Resize button by programming in oracle mdi parent forms in 10g. Please give me solution of this problems.
Categories: DBA Blogs

Where is alter session coming from? Every time I log in, I see "Session altered."

Fri, 2018-07-06 02:26
Every time I log in to our DB via sqlplus, I see this: $ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Thu Jul 5 11:48:29 2018 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c E...
Categories: DBA Blogs

Does Oracle 9i Data Guard have real time sync?

Fri, 2018-07-06 02:26
Hi We have a Oracle 9i database ,When I set up the data guard I want to use real time sync I got a error .So I want to confirm whether Oracle 9i have this future
Categories: DBA Blogs

Pages