Tom Kyte

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

Difference between Essbase cloud Versions

14 hours 40 min ago
Please explain the difference between the now de-coupled OAC/Essbase version that existed back in December and the new Marketplace version that is recommended today. Question is for customers that want to use OAC and Essbase,
Categories: DBA Blogs

redo log in nologging mode

Wed, 2020-02-26 09:22
I was trying to understand how redo works in nologging mode. Here is a interesting situation i encountered in XE. <code>SQL> @redo_question.sql Table dropped. Table created. LOG_MODE ------------ NOARCHIVELOG Statistics ------...
Categories: DBA Blogs

Between function not working on varchar2 column

Wed, 2020-02-26 09:22
Hi Team, We are facing issue while using Between function in our SQL query. We are trying to use Between function on a column which is of datatype VARCHAR2. Data insert in this column can be alphanumeric as well as numeric, But it seems this funct...
Categories: DBA Blogs

Compare execution performance stats between two PL/SQL procedures

Tue, 2020-02-25 20:17
Hi From the past I am only aware of Tom Kyte's Runstats package, which allows comparing some performance metrics across two procedures. I also found that the DBMS_SQLPA package also provides a feature that could help in this kind of scenario. ...
Categories: DBA Blogs

maximum row update at a single go in Oracle DB

Tue, 2020-02-25 20:17
Hi, We've near to 6 million records in our database and we want to update one specific column value for all the records. query will be like this: update table_xx set column_xx = '' where column_xxx = ''; Is there any limitation in Oracle DB? If ...
Categories: DBA Blogs

Request a new feature: bulk collect in batches

Tue, 2020-02-25 20:17
Hello, I know we can use BULK COLLECT to load all the records from a cursor into a set of collection variables, plus we can also use LIMIT clause to set the max number of records per load. It would be very convenient if Oracle can support the feat...
Categories: DBA Blogs

18c Global Temporary Tablespace

Tue, 2020-02-25 20:17
Our DB version is 18.3.0.0. We want to set a Global Temporary tablespace at CDB level and drop the Temporary tablespace at the PDB level. 1. The DEFAULT_TEMP_TABLESPACE is set in root container. 2. While in the Pluggable, when trying to drop the...
Categories: DBA Blogs

Best way to check for existence based on last event date

Tue, 2020-02-25 16:13
Hello, I am trying to find out the best way to include a criteria in a sql to check is the latest event for a user has occurred before a specific date. Below is my table setup and brief description USER_NP => Parent table listing a...
Categories: DBA Blogs

How to find users with DBA role

Tue, 2020-02-25 16:13
using DBA_ROLE_PRIVS and DBA_USERS, how can i determine users with DBA role (also hidden role)
Categories: DBA Blogs

Tuning with index of date datatype

Tue, 2020-02-25 16:13
Hi Team , I have table named "CALL_LOGG" with ~1M record's , table is partitioned with range-hash we have index in one of column named as logdate which is having date datatype . most of time i see query using major filter as logdate is u...
Categories: DBA Blogs

Tuning where column name is not null

Tue, 2020-02-25 16:13
Hi Team , I am trying to create test scenario where the table name TEST is created with 1 column ID. <code> Name Null? Type -----...
Categories: DBA Blogs

Case sensitive search in case insensitive database

Tue, 2020-02-25 16:13
Hi, I'm using a database that is mostly case insensitive, using this login trigger: <code>create trigger milenio_central_logon after logon on milenio_central.schema begin execute immediate 'alter session set NLS_COMP=LINGUISTIC'; execute imm...
Categories: DBA Blogs

NVARCHAR Vs. VARCHAR

Tue, 2020-02-25 16:13
In a database with character sets defined as: NLS_CHARACTERSET = AL32UTF8 NLS_NCHAR_CHARACTERSET = UTF8 Would there be any difference in the (language) character sets that could be stored by VARCHAR2 Vs. NVARCHAR2? As in NAME VARCHAR2(...
Categories: DBA Blogs

Downloading ppts from sessions

Thu, 2020-02-20 21:12
HI ALl, say what is the url to the ppt for: ?BCS Office Hours - Multitenant Fundamentals & Hands On Lab? ? /thanks /paul
Categories: DBA Blogs

Synchronous refresh in mview ORA-31922: Foreign key must contain partition key in table

Tue, 2020-02-18 06:11
Team, Here is my testcase which got failed during Synchronous refresh in mview. <code>create table products as select rownum as prod_id, object_name as prod_name, object_type as prod_category, object_id as prod_category_id, data_object...
Categories: DBA Blogs

Oracle 19C database issue with table types and pipelining

Tue, 2020-02-18 06:11
I have a package working fine in 11g version. But when I deploy the same package in 19c version, the behavior is different. PFB the description. Package specification has an cursor and created a table type with cursor%rowtype. Having a pipel...
Categories: DBA Blogs

Sqoop ojdbc8.jar throws error ORA-06502:PL/SQL:: numeric or value error

Tue, 2020-02-18 06:11
Hi Tom, We are trying to do a sqoop import to hive from Oracle and struck with a weird error below: WARN[main] org.apache.hadoop.mapred.YarnChild: Exception running child : java.io.IOException: java.sql.SQLExcepion: ORA-00606:error occurred ...
Categories: DBA Blogs

Scheduling Compilation or Execution of Stored Procedures

Tue, 2020-02-18 06:11
Hello We have a Oracle 11g Release 2 database with five identical working Schemas being accessed by a VB Client Server business application The db server, with 32GB RAM just hosts this one database Copies of Client applications are installed a...
Categories: DBA Blogs

Finding when someone dropped an object

Tue, 2020-02-18 06:11
Hi Team, I have a DB, where a table is dropped from the schema accidentally. We are trying to find whether it got dropped due to manual execution of DROP query and by whom? Is there any way that we can find that the DROP query which is executed ...
Categories: DBA Blogs

How to fetch part of a string for LONG datatype

Tue, 2020-02-18 06:11
HI, I am writing a query to find missing table partitions for next year using all_tab_partitions table, I am able to fetch the records with the help of column partition positions, but I have to extract the last partition date (YYYY-MM-DD) from HIG...
Categories: DBA Blogs

Pages