DBA Blogs

DDL rollback

Tom Kyte - Mon, 2016-11-21 17:06
Hi, I have some transactions in a procedure contains DDL Commands,I want to roll back whole the transactions done if happens anything wrong.So i would like to know is ROLLBACK of DDL is Possible in Oracle? Thank you.
Categories: DBA Blogs

Synchronising BEFORE and AFTER UPDATE triggers

Tom Kyte - Mon, 2016-11-21 17:06
Can a BEFORE UPDATE trigger on table A wait in a loop on a update to table B (by a Java batch program which also wants to read table A) before exiting and causing the AFTER UPDATE trigger on table A to commence?
Categories: DBA Blogs

ORA-00600: internal error while using rowid in merge statement.

Tom Kyte - Mon, 2016-11-21 17:06
Hello, Below Merge statement is giving internal error why? whats wrong with the code? what is the issue with rowid here? [If I use ID instead of rowid, it is giving valid error(unable to get stable set of rows)] ---Error: merge into t1 ...
Categories: DBA Blogs

Examining a new database

Tom Kyte - Mon, 2016-11-21 17:06
Every now and then, a new application with Oracle database comes to us. The manual is usually only end-user orientated. Seldom we get cubes, packages or frameworks for Cognos. A full database description is even more rare. What would be the fastest,...
Categories: DBA Blogs

Apex 5 get value from external?

Tom Kyte - Mon, 2016-11-21 17:06
Good Day, I have a Apex application I wrote where a form is completed, submit is pressed and a process kicks of a perl script as external procedure. The form requests IP Address and SNMP Community string only. What I want to achieve is to get Apex...
Categories: DBA Blogs

Error when restoring DB via RMAN

Tom Kyte - Mon, 2016-11-21 17:06
Hi Tom, we were making DB move procedure. NOTE: Source DB - uses ASM architecture. Target DB - will use filesystem. Backup of DB via RMAN (Linux OS, machine1) Restore of DB via RMAN (Linux OS, machine2) 1) backup was made RMAN> backup ...
Categories: DBA Blogs

Using Types in Read Only databases results in error when invoked for the first time

Tom Kyte - Mon, 2016-11-21 17:06
Following is a query that results in an error -- "ORA-16000: database or pluggable database open for read-only access", when invoked for the 1st time. But subsequent calls to the query are successful. The query runs successfully as long as its in t...
Categories: DBA Blogs

ORA-01555 caused by SQL statement below

Tom Kyte - Mon, 2016-11-21 17:06
Hi Tom, Sometimes we get ORA errors in alert file with message as: .... ORA-01555 caused by SQL statement below (SQL ID: gc8312bb07hav, Query Duration=11848 sec, SCN: 0x0000.35f6a778): WITH CORETABLE( DATAID, MAJOR, MINOR) AS ( SELEC...
Categories: DBA Blogs

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

Categories: DBA Blogs

DML operations inside Compound triggers

Tom Kyte - Sun, 2016-11-20 22:46
As suggested I use COMPOUND TRIGGERS, Please find requirements & the below code below. 1. IF DELETING, any record created in USER_INFO table may not be deleted. Attempting to delete a record from this table is captured, create an entry in AUD...
Categories: DBA Blogs

TDE

Tom Kyte - Sun, 2016-11-20 22:46
Under what conditions would an ORACLE.SECURITY.TS.ENCRYPTION.xxxxx not be present in the wallet after enabling tablespace encryption? All previous attempts to enable TDE on other databases resulted in the following entries - the most recent did not i...
Categories: DBA Blogs

RMAN Bakcup failure

Tom Kyte - Sun, 2016-11-20 22:46
Hi Tom, we are facing problem to rman backup failure or archive backup failure. This is not daily failure. but need to understand the exact problem and solution. rman failure with following error: channel ORA_DISK_1: starting piece 1 at 18-...
Categories: DBA Blogs

Which SQLcl build am I running?

Galo Balda's Blog - Sun, 2016-11-20 21:52

This is something I discovered a few minutes ago by pure coincidence and it’s not documented as far as I know.

sqlcl_version

You can enter version or show version and it will tell you the exact SQLcl build that you’re executing.


Filed under: Oracle, SQLcl Tagged: Oracle, SQLcl
Categories: DBA Blogs

Flashback Database -- 3 : Purging (older) Flashback Logs

Hemant K Chitale - Sun, 2016-11-20 08:34
As demonstrated earlier, Oracle may maintain Flashback Logs for a duration that is longer than the Flashback Retention Target.  This can happen when the db_recovery_filie_dest_size is large enough to support them (along with ArchiveLogs, Backups etc)

For example, in my play database I have reset the retention target to 1day but the Flashback Logs exceed 4 days :

SQL> show parameter flashback_ret

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target integer 1440
SQL> select sysdate-oldest_flashback_time from v$flashback_database_log;

SYSDATE-OLDEST_FLASHBACK_TIME
-----------------------------
4.21686343



The DBA should not manually delete Flashback Logs.

The only way I've found to purge older Flashback Logs is to reset db_recovery_file_dest_size to a lower value such that current FRA usage exceeds the dest_size.  This prompts Oracle to purge older Flashback Logs.

However, if ArchiveLogs exist and consume significant space and frequncy in the FRA, you do run the risk of

ARCH: Archival stopped, error occurred. Will continue retrying
ORACLE Instance orcl - Archival Error
ORA-16038: log 1 sequence# nnn cannot be archived
ORA-19809: limit exceeded for recovery files

and/or

ORACLE Instance orcl- Cannot allocate log, archival required
Thread 1 cannot allocate new log, sequence nnn
All online logs need archiving
Examine archive trace files for archiving errors


errors.
So, be careful to monitor your FRA usage and the Flashback Logs.  Use V$FLASHBACK_DATABASE_LOG, V$FLASHBACK_DATABASE_LOGFILE, V$FLASHBACK_DATABASE_STAT and V$FLASH_RECOVERY_AREA_USAGE view.
(see my earlier post that also points to an Oracle Support Doc about the first two views).
.
.
.


Categories: DBA Blogs

Why I get different results when comparing scalar type collection populated in PLSQL but not when it is populated in SQL

Tom Kyte - Sun, 2016-11-20 04:26
Hi guys, I'm running on Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production I started getting very strange results when doing collection data comparison within PLSQL. I've managed to narrow down the issue to a test case bel...
Categories: DBA Blogs

Pipelined function does not handle collection function calls properly

Tom Kyte - Sun, 2016-11-20 04:26
Hi, When using a pipelined function that calls another function, returning a collection I get wrong results. Here is an isolated test case <code> select * from v$version; create or replace type an_array as table of varchar2(32767); / cre...
Categories: DBA Blogs

Will PL/SQL language support real OO programming in the future?

Tom Kyte - Sun, 2016-11-20 04:26
There are always a lot of discussion & argument regarding whether to implement business rules inside the database, (i.e., close to the data, using PL/SQL) or outside the database (e.g. in the mid-tier, using Java or other language). I used to support...
Categories: DBA Blogs

Behavior of Oracle VPD considering Stored Procedures

Tom Kyte - Sun, 2016-11-20 04:26
Hi Tom, I have a particular question related to Oracle VPD (Virtual Private Database) behavior in case of Stored Procedures. I tried searching in Oracle Documentation as well as in the questions answered in AskTom, but couldn't find anything that...
Categories: DBA Blogs

Dynamic Fields

Tom Kyte - Sat, 2016-11-19 10:06
Hi, I am ramesh developing the web application using java and oracle.Our requirment is user can add the fields dynamically for ex: Step 1: user enters name,age we will store that value in parent table. ID COLUMNS_ 1 name ...
Categories: DBA Blogs

ORA-04021: timeout occurred while waiting to lock object on SYS.DBMS_AQADM.CREATE_QUEUE

Tom Kyte - Sat, 2016-11-19 10:06
Hi, i have a problem during SYS.DBMS_AQADM.CREATE_QUEUE. After 15 minutes Oracle writes : ORA-04021: timeout occurred while waiting to lock object ORA-06512: at "SYS.DBMS_AQADM_SYS", line 3647 ORA-06512: at "SYS.DBMS_AQADM", line 130 In ord...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs