Tom Kyte

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

Unable to install 18c Expression Edition

Wed, 2019-03-06 11:46
We are unable to install 18c Expression Edition in Windows 10 it is extracting and in middle, it is start rollback. Finally below the below message. Below is the message. The wizard was interrupted before Oracle Database 18c Express Edition c...
Categories: DBA Blogs

Different Plans in SQL and PL/SQL

Wed, 2019-03-06 11:46
Hi, in a PLSQL package I generate and execute dynamic SQL Statements. I wrote a litte procedure, which returns the output of dbms_xplan.display into a log table: <code> FUNCTION logPlan(p_statament VARCHAR2) RETURN CLOB IS PRAGMA autonomous_t...
Categories: DBA Blogs

Caching for PLSQL packages over ORDS

Tue, 2019-03-05 17:26
I need to cache few values when the plsql procedure is called through a rest service multiple times i.e. when it is executed from a same user multiple times for optimization. I am calling below package procedure through ORDS rest service. Belo...
Categories: DBA Blogs

Replacing card number with *

Tue, 2019-03-05 17:26
I have a 16 digit card number where I need to replace the 3rd digit of the card number to 9th digit card number with *.Tried regular expressions,replace and translate nothing worked.Need guidance in this tom!!
Categories: DBA Blogs

Declare a dynamic table type

Tue, 2019-03-05 17:26
Hello, I have a stored procedure which takes 2 input paramaters - owner and table_name. I would like to create a TYPE variable based on what is passed. This is the code: <code> create or replace PROCEDURE proc ( in_owner IN VARCHA...
Categories: DBA Blogs

Why are Index Organized tables (IOTs) not supported by interval partitioning

Mon, 2019-03-04 23:06
Hi tom, Could you please let me know why we can not use interval partitioning on index organized tables. Thanks in advance
Categories: DBA Blogs

How can I export the tablespace that has been dropped one of the datafile?

Fri, 2019-03-01 03:26
Hi,Tom During my work,i have met such question,and hope yr answer. 1. I wrongly create a datafile under the tablespace DRSYS,then I alter database 'datafile ' offline, and drop this datafile under OS command.Now the troubles arise. W...
Categories: DBA Blogs

Oracle instant client 12c EZConnect Using tcps instead of tcp

Thu, 2019-02-28 09:06
I download oracle instant client 12.2.0.1.0, I try to use sql loader to load csv data to the database. I can successfully load using following: <code>sqlLdr.exe userid=user/password@//192.9.200.228:1521/oracle ERRORS=4000 control=D:\temp\csma\xx_2...
Categories: DBA Blogs

export and import taking a lot of time

Thu, 2019-02-28 09:06
Hi, I need to export and import a table having 2 million data in it. using exp/imp command . I tried using the below commands but it took a lot of time to export and then import the data. Please help. Commands :exp test/test full=y file=REL...
Categories: DBA Blogs

How to generate DDL for APEX app from code

Thu, 2019-02-28 09:06
In Oracle SQL Developer, 'Application Express' section, we can export DDL for an APEX app by context menu, 'Quick DDL' > 'Save to Worksheet' menu. I'd like to do the same by executing some SQL or PL/SQL code. I know we have DBMS_METADATA.GET_...
Categories: DBA Blogs

Reading Header Info from CSV

Thu, 2019-02-28 09:06
Hi Team My csv looks like below 123456,20,20,1500 --- this is the header abc,efg,hij,klm,nop,qrs abc,efg,hij,klm,nop,qrs abc,efg,hij,klm,nop,qrs abc,efg,hij,klm,nop,qrs I need to read the header info. Can you pls tell me whats the best ...
Categories: DBA Blogs

Emails sending out of 12c SuperCluster Database

Wed, 2019-02-27 14:46
Oracle version: <code>SQL*Plus: Release 12.1.0.2.0 Production on Tue Feb 26 15:14:07 2019 Copyright (c) 1982, 2014, Oracle. All rights reserved. Last Successful login time: Tue Feb 26 2019 14:05:04 -06:00 Connected to: Oracle Database...
Categories: DBA Blogs

How to group by for a data set

Wed, 2019-02-27 14:46
Hi Tom, I have a table with many records for example as below. <code>create table test (a varcahr2(20)); insert into test values ('1'); insert into test values ('2'); insert into test values ('3'); insert into test values ('abc-oo cde')...
Categories: DBA Blogs

Using Access Advisor in Oracle 12

Wed, 2019-02-27 14:46
Hello, I am trying to use Access Advisor package in a 12.2 DB. When executing DBMS_ADVISOR.EXECUTE_TASK I get error: begin * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-01843: not a valid month ORA-06512: at "SY...
Categories: DBA Blogs

Oracle 12.2: Delete Privilege needs an additional Select Privilege

Tue, 2019-02-26 20:26
Hi, Why do we need an extra select privilege additional to the delete privilege? From my point of view, this does'nt make sense: <code>--execute as user a: create table b.t (col varchar2 (10)); insert into b.t values ('a'); commit; gr...
Categories: DBA Blogs

Bind Variables

Tue, 2019-02-26 20:26
Hi Tom, I tried to use Bind Variables in my Script. But that script is taking more time than expected. Please have a look at the below script. area@DBWH> truncate table t1; Table truncated. Elapsed: 00:00:00.50 area@DBWH> declare ...
Categories: DBA Blogs

can't we use nologging clause with domain index

Tue, 2019-02-26 20:26
Hi Tom, I am trying to create domain index on table, I am getting <b><i>ORA-29850: invalid option for creation of domain indexes</i></b>. Can you please explain why can't we create domain index with nologging option. <code> SQL> begin 2 c...
Categories: DBA Blogs

getting error ( ORA-06553: PLS-306: wrong number or types of arguments in call to) after migrating code from oracle 11.2.0.3.0 to 12 c

Mon, 2019-02-25 08:06
we are in the process of upgrading our oracle database from 11.2 to 12.2 ran into an error: ORA-06553: PLS-306: wrong number or types of arguments in call to I have created a simplified script that recreates the issue. the error occurs at com...
Categories: DBA Blogs

Fetching all records from a extremely large oracle table

Sat, 2019-02-23 19:26
Hi Tom, I am working on a ETL project that involves fetching all records from a Extremely large oracle table (that contains millions and millions of records) and has a very large number of partitions. I need to extract all data from the Table. ...
Categories: DBA Blogs

Measuring usage of schema objects

Sat, 2019-02-23 19:26
I have been tasked with identifying which of my group's schema objects (tables and views) are not being used anymore. What system table(s) can I query to find out which users are running SELECT statements against a specific owner's schema objects, p...
Categories: DBA Blogs

Pages