DBA Blogs

Unable to set pctversion to 0

Tom Kyte - Tue, 2017-08-22 18:46
Hello Tom, I am trying below code to reset table pctversion to 0 from default 10. but on Oracle 12c 12.1.0.2 it won't work, but the same command works on Oracle 12c 12.2.0.1 and also works on 11gR2. <code> alter table ev_log modify lob (event_...
Categories: DBA Blogs

Performance of querying CHAR columns

Tom Kyte - Tue, 2017-08-22 18:46
I wasn't able to parameterize our queries because our database schema uses a lot of CHAR types on key fields, but was told to use the LIKE operator instead of =. This did allow the parameterized queries to work, but I'm concerned how this will effect...
Categories: DBA Blogs

Remove redundant entries from table

Tom Kyte - Tue, 2017-08-22 18:46
Hi Experts, Please find below table & data. <code>create table orders ( order_id varchar2(10),quantity varchar2(10), price varchar2(10), id varchar2(10)); insert into orders values ('O1','2','100','id1'); insert into orders values ('O1','3'...
Categories: DBA Blogs

Load XML File (Physical file) using SQL Loader into a XMLType Column

Tom Kyte - Tue, 2017-08-22 18:46
Hello Tom I need to load a XML File, which is available on the oracle server using a Host Program in Oracle Applications. I have done the below 1. Created a Host Concurrent Program 2. Call the SQL Loader Control File from the PROG File. But...
Categories: DBA Blogs

ORA-00972: identifier is too long

Tom Kyte - Tue, 2017-08-22 18:46
I have a question about "Identifier too long" error. I understand if I am trying to create a column name that is too long and oracle complains on it. but if I do a select and alais it my own text, why is this a problem ? Here is a sample: <co...
Categories: DBA Blogs

Enable Windows Instance access via RDP on Oracle Compute Cloud Service

Oracle Compute Cloud Service optimized for Oracle Public Cloud Machine (OPCM) provides Infrastructure as a Service (IaaS) and also acts as the base on which the other cloud...

We share our skills to maximize your revenue!
Categories: DBA Blogs

DATE output format

Tom Kyte - Tue, 2017-08-22 00:26
Hi, In database_properties table date format is in 'DD-MON-RR', but in table it showing different format in date columns. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Ad...
Categories: DBA Blogs

Database Upgrade / APEX versions

Tom Kyte - Tue, 2017-08-22 00:26
Hi Team, We're planning to upgrade our database from 11.2.0.4 to 12.1.0.2 (non-pluggable configuration). The current version of APEX installed in our environment is 5.1 My question is - during the upgrade process, is the installer smart enou...
Categories: DBA Blogs

Inserts into materialized view tables are parsed each time.

Tom Kyte - Tue, 2017-08-22 00:26
Hi We have a PL/SQL application which is inserting into database tables. These tables have materialized view logs. Noticed when looking into v$sql, that number of executions of these insert statements is much greater then number of parsing ope...
Categories: DBA Blogs

Search for top 10 queries generating huge redo

Tom Kyte - Tue, 2017-08-22 00:26
Dear Experts, Please can you help in knowing how to find for top 10 queries/sessions generating huge redo. I tried with the below queries to check redo generated per day, but not able to find top 10 such sessions which generates so. select t...
Categories: DBA Blogs

use clause optionally enclosed by '"' in sql loader 11.2.0.2.0

Tom Kyte - Tue, 2017-08-22 00:26
I have strange behavior using optionally enclosed by '"' in my .ctl file. it doesn't works as expected and I do understand why. Could you please help me? here the .ctl file: <code>OPTIONS (PARALLEL=TRUE,DIRECT=TRUE,ROWS= '150000',BINDSIZE= '4500...
Categories: DBA Blogs

Unable to extend Tablespace - ORA-01683

Tom Kyte - Tue, 2017-08-22 00:26
Hi Oracle Masters, We run End of Day (EOD) for our bank, and once in a while the EOD process aborts with the following error: ORA-01683: unable to extend index XXXXXX.PK01_TABLE_NAME partition P_1132 by 1024 in tablespace TBLSPCNAME. This kind...
Categories: DBA Blogs

BTree Index or Bitmap Index on Financial Transactions Table

Tom Kyte - Tue, 2017-08-22 00:26
Hi Tom, I need some advices on correct bitmap selection. We have a table with 200,000,000 Records of some payment cards transactions. Each transaction has a unique CardSerialNumber and a TransactionCounter which is generated by the card itself,...
Categories: DBA Blogs

Data dictionary impact of creating and deleting tables

Tom Kyte - Tue, 2017-08-22 00:26
Hi Tom! Could creating and deleting tables in warehouse many times (20000 per day during 1-2 years) damage data dictionary and dramatically decrease performance? If yes how we can restore it? Thank you
Categories: DBA Blogs

Use materialized views instead of PL/SQL

Tom Kyte - Tue, 2017-08-22 00:26
Hi Masters, I have a question relating to materialized views. We have 3 tables: INVOICE_MAIN, INVOICE_BALANCE and INVOICE_LINE and one view INVOICE joining INVOICE_MAIN and INVOICE_BALANCE. In the table INVOICE_LINE there are the columns INVOICE_...
Categories: DBA Blogs

Change partition column

Tom Kyte - Mon, 2017-08-21 06:06
Hi, We have a existing table. And this table is partitioned by range interval. Now i would like to change the column its referring to. Can i do that? Eg: <code>CREATE TABLE partition_test ( PROD_ID NUMBER NOT NULL, DATE_SK ...
Categories: DBA Blogs

Adding a column with a default value to a table

Tom Kyte - Mon, 2017-08-21 06:06
Tom, I have a doubt about adding columns with default values to a table. Recently, I have stumble upon an Arup Nanda article on it where he states the following "When a user selects the column for an existing record, Oracle gets the fact about the...
Categories: DBA Blogs

Incorrect time in Database

Tom Kyte - Mon, 2017-08-21 06:06
We have 5 oracle 12C 12.1.0.2.0 databases running on 2 nodes. on One of the Database on both nodes sysdate returns as below select sysdate from dual; SYSDATE -------------------- 18-AUG-2017 * on all other 4 database when sysdate was...
Categories: DBA Blogs

Data warehouse performance

Tom Kyte - Mon, 2017-08-21 06:06
Hi Tom! We have problem with database (warehouse) performance. Our DBA said that the main reason is some kind of company software create and delete about 20000 tables per day and it?s impacts on data dictionary. Can it be truth? And what we can do...
Categories: DBA Blogs

Package compilation speed

Tom Kyte - Mon, 2017-08-21 06:06
Hi All, I've PL/SQL package body (~50k lines) that used to take a few mins to compile in Oracle 11g & 12.1. The package consists of mostly insert statements to some tables, plus about half a dozen stored procedures. Recently we upgraded our databa...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs