DBA Blogs

Bad cardinality in join with column with skewed data

Tom Kyte - Sun, 2016-11-27 01:26
Hi guys. I have a problem with the estimation of the cardinality of a skewed column The distribution of the data is as follows: <code>select m.m_pricelist_id, count(*) from m_pricelist_version m group by m.m_pricelist_id 2 3 ;</code>...
Categories: DBA Blogs

Lot of time to add partitions to a Big Table

Tom Kyte - Sat, 2016-11-26 07:06
Hi Tom, I have a big table with 6 partitions(hash), the table have <b>270 Million records</b>, i try to add 2 partitions(i want to have 8 partitions for use "power of 2"), after prepare temp space and undo space, then i run process to add to partiti...
Categories: DBA Blogs

Synonyms

Tom Kyte - Sat, 2016-11-26 07:06
Hi, Please answer the below questions. 1. What Is the Difference between PUBLIC and PRIVATE synonyms? 2.Do we need GRANT privilages to acces PUBLIC SYNONYM? 3.Is PUBLIC Synonym a NON-Schema Object Or Schema Object?
Categories: DBA Blogs

INSTEAD OF TRIGGERS

Tom Kyte - Sat, 2016-11-26 07:06
INSTEAD OF triggers are executed instead of DML statements that fired it. Is it correct??
Categories: DBA Blogs

Drop MetaData

Tom Kyte - Sat, 2016-11-26 07:06
Hi, I installed 11g(enterprise) on my personal laptop.I Droped the Metadata DBA_OBJECTS,DBA_USERS(my intention is to know DB fuctioning),my Data base working properly.But When I execute Select query(SELECT * FROM dba_objects) getting *ora-10775 ...
Categories: DBA Blogs

Moving table partitions

Tom Kyte - Sat, 2016-11-26 07:06
Tom, Is it possible to move a table partition from one tablespace to another in the same way it is now possible to move a table from one tablespace to another ?
Categories: DBA Blogs

Oracle database In Memory

Tom Kyte - Sat, 2016-11-26 07:06
Hello Tom, I seen a new feature for Oracle 12c : In-Memory database cache. I seen it's a new memory area to organize data in column format for BI/DWH queries. I would like to know how Oracle decides when use the data from In-memory area or from...
Categories: DBA Blogs

Links for 2016-11-25 [del.icio.us]

Categories: DBA Blogs

Reading xml data from nested xml nodes using PL/SQL

Tom Kyte - Fri, 2016-11-25 12:46
Hello Tom, I am new to xml and don't know Java and, unfortunately, do not have time to learn it right now as I need to have a little prototype done of this in a couple of days. I did a lot of reading and reviewed many of the OTN references but I...
Categories: DBA Blogs

Oracle 12.2 new features: long names

Tom Kyte - Fri, 2016-11-25 12:46
I was just going through https://blogs.oracle.com/sql/entry/12_things_developers_will_love and checking the new Oracle 12.2 features, and noticed something about the "Loooooooooooooooong Names". So say if I define a variable as v_table_name use...
Categories: DBA Blogs

Synonyms

Tom Kyte - Fri, 2016-11-25 12:46
Hi, Please Go through the below code SQL> select * from san; --querying tbale NUM ---------- 1 2 SQL> select * from sam; --querying table VAR ---------- santhosh reddy SQL> SE...
Categories: DBA Blogs

Relation

Tom Kyte - Fri, 2016-11-25 12:46
Hi, Is it possible to create a relationship between tables without using foreign key?
Categories: DBA Blogs

Impact of archivelogs on DB during Hot backup and Rman inc0 backup

Tom Kyte - Fri, 2016-11-25 12:46
Hi Tom, Please clarify on below queries. 1. if database is in Hot backup mode (between 2:00 AM to 4:00 AM), the parallel tx at that time will be written to archive logs. And once the hot backup mode finished these logs will be applied to ...
Categories: DBA Blogs

oracle function - rounding

Tom Kyte - Fri, 2016-11-25 12:46
how to round 122.41 value to 122.40. thank you.
Categories: DBA Blogs

Package

Tom Kyte - Fri, 2016-11-25 12:46
Hi, I would like to know the benefits of creating a package with multiple procedures and functions instead of creating them individually?
Categories: DBA Blogs

12.2 New Features -- 3 : Flashback Pluggable Database

Hemant K Chitale - Fri, 2016-11-25 08:35
12.1 allows Point In Time Recovery of a Pluggable Database but not Flashback of an individual PDB.

12.2 now allows Flashback of an individual PDB.   This is easier with a Local Undo Tablespace instead of a Shared Undo Tablespace.

Here is a quick demo (all times in UTC timezone) :

[oracle@HKCORCL ~]$ sqlplus system/Oracle_4U@PDB1

SQL*Plus: Release 12.2.0.1.0 Production on Fri Nov 25 14:19:06 2016

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Last Successful login time: Thu Nov 24 2016 01:03:52 +00:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select count(*) from hr.employees_part;

COUNT(*)
----------
107

SQL> drop table hr.employees_part purge;

Table dropped.

SQL> connect / as sysdba
Connected.
SQL> alter system switch logfile;

System altered.

SQL> alter pluggable database pdb1 close;

Pluggable database altered.

SQL>
SQL> select sysdate, sysdate-oldest_flashback_time
2 from v$flashback_database_log;

SYSDATE SYSDATE-OLDEST_FLASHBACK_TIME
--------- -----------------------------
25-NOV-16 2.36273148

SQL>
SQL> flashback pluggable database pdb1
2 to timestamp sysdate-2/24;

Flashback complete.

SQL> alter pluggable database pdb1 open;
alter pluggable database pdb1 open
*
ERROR at line 1:
ORA-01113: file 17 needs media recovery
ORA-01110: data file 17:
'/u02/app/oracle/oradata/HKCORCL/4157E08302CC2021E053B2D4100AABA3/datafile/o1_mf
_pdb1undo_d3dgxhbh_.dbf'


SQL> alter pluggable database pdb1 open resetlogs;

Pluggable database altered.

SQL>
SQL> connect system/Oracle_4U@PDB1
Connected.
SQL> select count(*) from hr.employees_part;

COUNT(*)
----------
107

SQL>


(Note : The 12.2 documentation shows the TO TIME clause, it is actually TO TIMESTAMP).
I have done a flashback of PDB1 to a time as of 2hours ago, when the table HR.EMPLOYEES_PART still existed.

Let's look for messages in the alert log.

2016-11-25T14:19:52.992589+00:00
Archived Log entry 11 added for T-1.S-11 ID 0x38800462 LAD:1
2016-11-25T14:19:57.621705+00:00
alter pluggable database pdb1 close
2016-11-25T14:19:57.640353+00:00
PDB1(3):JIT: pid 7920 requesting stop
2016-11-25T14:19:58.885892+00:00
Pluggable database PDB1 closed
Completed: alter pluggable database pdb1 close
2016-11-25T14:26:10.205824+00:00
flashback pluggable database pdb1
to timestamp sysdate-2/24
2016-11-25T14:26:10.627900+00:00
Flashback Restore Start
2016-11-25T14:26:11.513882+00:00
Restore Flashback Pluggable Database PDB1 (3) until change 3536013
Flashback Restore Complete
2016-11-25T14:26:11.707236+00:00
Flashback Media Recovery Start
2016-11-25T14:26:11.718480+00:00
Serial Media Recovery started
2016-11-25T14:26:12.006472+00:00
Recovery of Online Redo Log: Thread 1 Group 2 Seq 11 Reading mem 0
Mem# 0: /u04/app/oracle/redo/redo02.log
2016-11-25T14:26:12.283587+00:00
Incomplete Recovery applied until change 3536477 time 11/25/2016 12:26:56
Flashback Media Recovery Complete
Flashback Pluggable Database PDB1 (3) recovered until change 3536477, at 11/25/2016 12:26:56
Completed: flashback pluggable database pdb1
to timestamp sysdate-2/24
2016-11-25T14:26:21.451523+00:00
alter pluggable database pdb1 open
PDB1(3):Autotune of undo retention is turned on.
2016-11-25T14:26:21.659109+00:00
Pdb PDB1 hit error 1113 during open read write (1) and will be closed.
2016-11-25T14:26:21.659410+00:00
Errors in file /u01/app/oracle/diag/rdbms/hkcorcl/HKCORCL/trace/HKCORCL_ora_7920.trc:
ORA-01113: file 17 needs media recovery
ORA-01110: data file 17: '/u02/app/oracle/oradata/HKCORCL/4157E08302CC2021E053B2D4100AABA3/datafile/o1_mf_pdb1undo_d3dgxhbh_.dbf'
PDB1(3):JIT: pid 7920 requesting stop
2016-11-25T14:26:21.804780+00:00
Errors in file /u01/app/oracle/diag/rdbms/hkcorcl/HKCORCL/trace/HKCORCL_m000_9995.trc:
ORA-01110: data file 9: '/u02/app/oracle/oradata/HKCORCL/PDB1/system01.dbf'
ORA-1113 signalled during: alter pluggable database pdb1 open...
2016-11-25T14:26:22.086212+00:00
Errors in file /u01/app/oracle/diag/rdbms/hkcorcl/HKCORCL/trace/HKCORCL_m000_9995.trc:
ORA-01110: data file 10: '/u02/app/oracle/oradata/HKCORCL/PDB1/sysaux01.dbf'
2016-11-25T14:26:22.175778+00:00
Errors in file /u01/app/oracle/diag/rdbms/hkcorcl/HKCORCL/trace/HKCORCL_m000_9995.trc:
ORA-01110: data file 12: '/u02/app/oracle/oradata/HKCORCL/PDB1/users01.dbf'
2016-11-25T14:26:22.270876+00:00
Errors in file /u01/app/oracle/diag/rdbms/hkcorcl/HKCORCL/trace/HKCORCL_m000_9995.trc:
ORA-01110: data file 17: '/u02/app/oracle/oradata/HKCORCL/4157E08302CC2021E053B2D4100AABA3/datafile/o1_mf_pdb1undo_d3dgxhbh_.dbf'
Checker run found 4 new persistent data failures
2016-11-25T14:26:39.804216+00:00
alter pluggable database pdb1 open resetlogs
2016-11-25T14:26:40.377390+00:00
Online datafile 17
Online datafile 12
Online datafile 10
Online datafile 9
2016-11-25T14:26:40.881181+00:00
PDB1(3):Autotune of undo retention is turned on.
PDB1(3):Endian type of dictionary set to little
PDB1(3):[7920] Successfully onlined Undo Tablespace 7.
PDB1(3):Undo initialization finished serial:0 start:868281239 end:868281333 diff:94 ms (0.1 seconds)
PDB1(3):Database Characterset for PDB1 is AL32UTF8
PDB1(3):JIT: pid 7920 requesting stop
2016-11-25T14:26:42.441388+00:00
PDB1(3):Autotune of undo retention is turned on.
2016-11-25T14:26:42.827673+00:00
PDB1(3):Endian type of dictionary set to little
PDB1(3):[7920] Successfully onlined Undo Tablespace 7.
PDB1(3):Undo initialization finished serial:0 start:868283079 end:868283168 diff:89 ms (0.1 seconds)
PDB1(3):Pluggable database PDB1 dictionary check beginning
2016-11-25T14:26:43.706672+00:00
PDB1(3):Pluggable Database PDB1 Dictionary check complete
PDB1(3):Database Characterset for PDB1 is AL32UTF8
2016-11-25T14:26:44.083617+00:00
PDB1(3):Opatch validation is skipped for PDB PDB1 (con_id=0)
PDB1(3):Opening pdb with no Resource Manager plan active
2016-11-25T14:26:45.205147+00:00
Starting control autobackup

Deleted Oracle managed file /u03/app/oracle/fast_recovery_area/HKCORCL/415864F430FE5FFEE053B2D4100A149C/backupset/2016_11_16/o1_mf_nnndf_TAG20161116T024856_d2qldlnv_.bkp
2016-11-25T14:26:46.523130+00:00
Deleted Oracle managed file /u03/app/oracle/fast_recovery_area/HKCORCL/3E09703FB0AF1A7EE053DE4BC40A6C1D/backupset/2016_11_16/o1_mf_nnndf_TAG20161116T024856_d2qlfzqg_.bkp
Control autobackup written to DISK device

handle '/u03/app/oracle/fast_recovery_area/HKCORCL/autobackup/2016_11_25/o1_mf_s_928852005_d3jlk651_.bkp'

Pluggable database PDB1 closed
Completed: alter pluggable database pdb1 open resetlogs


The set of ORA-01113 and ORA-01110 errors are when I tried to open PDB1 without a RESETLOGS.
The OPEN RESETLOGS issued at 2016-11-25T14:26:39.804216+00:00 was successful.
(Note : The ALTER SYSTEM SWITCH LOGFILE wasn't required but I like to archive out the CURRENT redo whenever I make a significant action against the database).

.
.
.

Categories: DBA Blogs

Foreign key reference table

Tom Kyte - Thu, 2016-11-24 18:26
Hi, A table contains a primary key so it referes to another table,I know table name and its primary key column name but i dont know to which table it referes. So is it possible to know the reference table name and foreign key column name?
Categories: DBA Blogs

MV log segments growing to big and to fast

Tom Kyte - Thu, 2016-11-24 18:26
Hi, I have 2 11g DB connected over DB link. - DB1 has master table (MASTER_TBL) and - DB2 materialized view (MV) connected to master table (MASTER_TBL) with fast refresh. MASTER_TBL is: - updated constantly from different sources. - si...
Categories: DBA Blogs

Understanding 'Outline Data' in Oracle Explain Plan

Tom Kyte - Thu, 2016-11-24 18:26
Hi Tom, I am using DBMS_XPLAN.DISPLAY_CURSOR(format ==> 'Advanced') to generate the explain plan. This is generating for me 2 sections of information 'Query Block Name / Object Alias (identified by operation id):' and 'Outline Data'. Could...
Categories: DBA Blogs

INITRANS and LOB index

Tom Kyte - Thu, 2016-11-24 18:26
Hi, Is there a way to specify INITRANS for a LOB index (in my specific case on a VARRAY attribute of an object column - to be even more specific on SDO_ELEM_INFO_ARRAY and SDO_ORDINATES attributes of the column of the MDSYS.SDO_GEOMETRY). Oracle v...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs