Tom Kyte

Subscribe to Tom Kyte feed Tom Kyte
These are the most recently asked questions on Ask Tom
Updated: 2 weeks 5 days ago

Compare columns in two tables and report which column is different

Fri, 2019-03-15 16:46
Compare columns in two tables and list out column names which are different. for ex:- create table t1(c1 number(2), c2 varchar2(10)); create table t2(c1 number(2), c2 varchar2(10)); insert into t1 values(1,'a'); insert into t2 values(1,'b'); result ...
Categories: DBA Blogs

Taking More Indexing Time on ORACLE database While Performing QUERYS

Thu, 2019-03-14 22:26
Hi Team, Very impressive for your way of explaining. So we have ORACLE db production server,from that we are trying to perform indexing with 40 Querys on every day.For Example i am showing few Querys:: <code>DROP INDEX GINQCON_ix1 FORCE; ...
Categories: DBA Blogs

I want to move from DBA to Developer, how to develop my skills

Thu, 2019-03-14 22:26
I am working in my DBA role for the past 5 years,vexed with on call support,shift 24*7. I want to switch to developer role and much more I am interested in it. I also want to know the roles and responsibilities what do developer do, which tool...
Categories: DBA Blogs

Number of Chunks in DBMS_PARALLEL_EXECUTE

Thu, 2019-03-14 22:26
Hi TOM, I am having a data movement activity from one table to another table. Due to Huge data size in both tables, i have planned to use DBMS_PARALLEL_EXECUTE functionality, so that i can finish it quickly. I am creating chunks based on ROWID. ...
Categories: DBA Blogs

Is there a nowait clause for an insert statement

Thu, 2019-03-14 22:26
I have inserted the same primary key data but the Second Session is locking forever! Is there an option to alter user session to raise timeout after 60seconds or NOWAIT for the second sessions INSERT command. In our environment session 1 is a lo...
Categories: DBA Blogs

A table has a owner but also a creator?

Thu, 2019-03-14 22:26
Hello Masters, I saw something weird last time and I need your expertise to understand. I create two users. <code>[ora1210[XXX] /home/ora1210]$ sqlplus / as sysdba SQL> create user ZZ01 identified by xxxxxxxxxxxxxxxxxxxx; SQL> create...
Categories: DBA Blogs

Weighted Average Inventory calculation.

Tue, 2019-03-12 15:26
Hello, Hope you are at the best of you health. I am working in Microsoft Windows environment and installed database is Oracle 11gR2. I have following two table with sample data. <code>CREATE TABLE stock_master ( vno INTEGER, vdate DATE, vtype...
Categories: DBA Blogs

How to decide to create index on column

Tue, 2019-03-12 15:26
Dear, I talk about single table contain approx 20 to 25 columns. And in production database this table used with different column combination or single column also used in different where clause at different query. When some time query run slow s...
Categories: DBA Blogs

Create table replicate along with dependents in different schema

Tue, 2019-03-12 15:26
Hi Chris/Connor, I have one requirement where we need to create a replica of table in different schema and different table space (schema and table space will be inputs to the scripts)in same database server.. This we need to do using plsql only so ...
Categories: DBA Blogs

How to distinguish "Semantic error" and "Syntactic error"?

Tue, 2019-03-12 15:26
Hello Tom, Is there a way, in a PL/SQL programm, to manage differently "Semantic error" and "Syntactic error"? In the bloc "EXCEPTION", I wish to count these kind of errors, how can I do? I found nothing in the documentation and I don't t...
Categories: DBA Blogs

Need rank based on person_no column

Tue, 2019-03-12 15:26
Hi All, I have column ord which is common for all the records , then person_no. This can be repeated. I have a column called flag which is a sequence. I was trying populate a new column(NEW_FLAG) which will basically rank the person_no according...
Categories: DBA Blogs

Using connect by level to generate dates and times

Tue, 2019-03-12 15:26
I have two SQL Queries: SQL Query 1: <code>select to_date(:sDate,'dd-mm-rrrr')+(level-1)DateRange from dual connect by level <= ((to_date(:endDate,'dd-mm-rrrr')-to_date(:sDate,'dd-mm-rrrr')) + 1);</code> SQL Query 2: <code>select level,to...
Categories: DBA Blogs

Number Data Type Precision

Tue, 2019-03-12 15:26
Hi, From Oracle Docs "The absence of precision and scale designators specifies the maximum range and precision for an Oracle number." https://docs.oracle.com/database/121/SQLRF/sql_elements001.htm#SQLRF30020 When i declared number with ou...
Categories: DBA Blogs

Updating a table when a match is found in another table

Mon, 2019-03-11 21:06
Hi Tom, I have a table (T1) and a view is created upon T1 by masking few PI information say (V1) T1 area_code integer (100 USA, 200 UK) id integer Name string age integer DOB date type string (Individual/Company) V1 (Masked versi...
Categories: DBA Blogs

Not getting required date after using TO_CHAR function with DATE column

Mon, 2019-03-11 21:06
<code> create table demo_tab(id number(2),name varchar2(50),from_date date); Table created. insert into demo_tab values(2,'AUDI','04-MAR-2019'); 1 row created. insert into demo_tab values(3,'BMW','06-MAR-2019'); 1 row created. i...
Categories: DBA Blogs

Date format

Mon, 2019-03-11 21:06
Hi, I have data in the format 2019-02-21T13:58:18.000+00:00 in a varchar2 column(loaded into staging table from a CSV file). When merging this staging table I need to put this value into a DATE data type column. How to do this ? Format of the dat...
Categories: DBA Blogs

TO_DATE() on SYSDATE failed in insert all query.

Mon, 2019-03-11 21:06
Hello, Yesterday I used insert all query to enter 8 rows in two tables directly from toad. At that time I used TO_DATE(SYSDATE, 'DD-MON-YYYY HH24:MI') for inserting sysdate in my table (I know sysdate was enough but 'coz of since last four m...
Categories: DBA Blogs

Database operations monitor 12c

Mon, 2019-03-11 02:46
Team, Was reading about Real time operations monitor in Oracle 12c @ <u>https://docs.oracle.com/en/database/oracle/oracle-database/12.2/tgsql/monitoring-database-operations.html#GUID-DC573FB7-40C5-4D6D-BE81-EF356900B444 </u> and here is a quick...
Categories: DBA Blogs

SQL fetch phase need library cache lock/pin

Mon, 2019-03-11 02:46
Hi , thks for all your works here,i've learnd much a lot! now, i have some question on <b>SQL fetch phase</b> and <b>library cache lock/pin </b>which stuck me for days. here is something i find from some website, <i> SELECT statement follow...
Categories: DBA Blogs

Need to find the Oracle EBS Version 12 Data Model

Mon, 2019-03-11 02:46
Need to find the Oracle EBS Version 12 Data Model
Categories: DBA Blogs

Pages