DBA Blogs

multiple layers of aggregation

Tom Kyte - Mon, 2016-08-15 06:46
greetings experts, I haven't seen this matter among other questions, certainly won't mind having it pointed out if I've missed it. I have an awkward requirement from management that requires summing a set of values, and each value is often going t...
Categories: DBA Blogs

regarding subquery terminology

Tom Kyte - Mon, 2016-08-15 06:46
When we can use any query with ''From '' clause, can we call it subquery??? eg. select * from (select * from emp order by sal desc);
Categories: DBA Blogs

Hide sensitive Data

Tom Kyte - Mon, 2016-08-15 06:46
Hi ask tom team, I want to hide sensitive data when querying from SQL Plus or any other SQL tool (Toad, SQL Developer), but show data when accessing from application server. How can i do this ? preferably without physical access to database se...
Categories: DBA Blogs

oracle locks,blocks,deadlocks

Tom Kyte - Mon, 2016-08-15 06:46
what is mean by locks,blocks,deadlocks in oracle? Please explain with one good example because I am learner,use e.g(emp,dept ) tables
Categories: DBA Blogs


Tom Kyte - Sun, 2016-08-14 12:26
There is one table.How sir. table1(input)...........target(output). 1 0000000001 12 0000000012 123 0000000123 1234 0000001234 12345 123456
Categories: DBA Blogs

ORA-02292: integrity constraint <constraint name> violated

Tom Kyte - Sun, 2016-08-14 12:26
Hello, I am attempting to delete a record / object (that may use several tables at the DB level) from an application. The delete statement is raising a ORA-02292: integrity constraint <constraint name> violated How can I find the Delete state...
Categories: DBA Blogs

Finished Mathematics for Computer Science class

Bobby Durrett's DBA Blog - Sat, 2016-08-13 17:07

Today I finally finished the Mathematics for Computer Science class that I have worked on since December. For the last year or two I have wanted to do some general Computer Science study in my free time that is not directly related to my work. I documented a lot of this journey in an earlier blog post.

The math class is on MIT’s OpenCourseWare (OCW) web site. It was an undergraduate semester class and I spent about 9 months on it mostly in my spare time outside of work. I wanted to test out OCW as a source for training just as I had experimented with edX before. So, I thought I would share my thoughts on the experience.

The class contained high quality material. It was an undergraduate class so it may not have been as deep as a graduate level class could be but world-class MIT professors taught the class. Some of my favorite parts of the video lectures were where professor Leighton made comments about how the material applied in the real world.

The biggest negative was that a lot of the problems did not have answers. Also, I was pretty much working through this class on my own. There were some helpful people on a Facebook group that some of my edX classmates created that helped keep me motivated. But there wasn’t a large community of people taking the same class.

Also, it makes me wonder where I should spend time developing myself. Should I be working more on my communication and leadership skills through Toastmasters? Should I be working on my writing? Should I be learning more Oracle features?

I spent months studying for Oracle’s 12c OCP certification exam and I kind of got burnt out on that type of study. The OCP exam has a lot of syntax. To me syntax, which you can look up in a manual, is boring. The underlying computer science is interesting. It is fun to try to understand the Oracle optimizer and Oracle internals, locking, backup and recovery, etc. There is a never-ending well of Oracle knowledge that I could pursue.

Also, there is a lot of cloud stuff going on. I could dive into Amazon and other cloud providers. I also have an interest in open source. MySQL and PostgreSQL intrigue me because I could actually have the source code.

But, there is only so much time in the day and I can’t do everything. I don’t regret taking the math for computer science class even if it was a diversion from my Toastmasters activities and not directly related to work. Now I have a feel for the kind of materials that you have on OCW: high quality, general computer science, mostly self-directed. Now I just have to think about what is next.


Categories: DBA Blogs

Can I declare the record variables dynamically

Tom Kyte - Fri, 2016-08-12 23:46
Hi Tom , I am writing a procedure for field to field comparison of tables. Create or Replace procedure data_check AS type all_tabs is table of table of all_tables.table_name%type; v_tab_stg all_tabs := all_tabs(); v_tab_pstg all_tabs := all_t...
Categories: DBA Blogs

sql ,plsql

Tom Kyte - Fri, 2016-08-12 23:46
Is oracle support on update cascade and on delete cascade in child table? e.g I want to delete the particular value from parent table column which primary key column and this column have relationship with child table column so if try to delete ...
Categories: DBA Blogs

Purging Partition and subpartitions

Tom Kyte - Fri, 2016-08-12 23:46
Hi, We have a table with 120 partitions and we create around 3500 sub partitions in total every month. Not all subpartitions are having data in it eg (weekends, holidays). But over the period these subpartitions are increased to 70000. And now thi...
Categories: DBA Blogs

Presenting table data into a different format

Tom Kyte - Fri, 2016-08-12 23:46
I have a table with the following sample data: Branch Department ======== ========== Sydney Sales Sydney Research Sydney Finance London Sales New York Sales New York IT How do I present this data in the table as this format: B...
Categories: DBA Blogs

Database Design

Tom Kyte - Fri, 2016-08-12 23:46
Tom: 1. I am designing a database for order entry. I have a table PO (po_no, po_date, po_status) and another child table (po_no, stock_no,stock_desc,qty, unit). Because a user may not find a stock_no in a reference table (for all stock numbers)...
Categories: DBA Blogs

SQL*Net message from client for MView refresh

Tom Kyte - Fri, 2016-08-12 23:46
Hi tom, I am getting wait events with SQL*Net message from client continuously while I am doing mview refresh. Now here while refreshing the mview it is accessing the data through db link & source db & destination db are on same server. So can u ...
Categories: DBA Blogs

How to copy data in XMLTYPE type (or CLOB) over 32K in a local table into a LONG type column in a remote table

Tom Kyte - Fri, 2016-08-12 23:46
The purpose is to copy data contained in XMLTYPE type column residing in a local table into a LONG type column in a remote table. Our current PLSQL (package) code first selects XMLTYPE column into a XMLTYPE variable (lv_xml_doc), converts into CLOB a...
Categories: DBA Blogs


Tom Kyte - Fri, 2016-08-12 23:46
Sir this is my questions ************************ I have a sourcetableA with 10 records....and target tables are like(table1...table2....table5) id 1 2 3 4 5 . . 10... i wnat load 2 records into table1 and next 2 records into ta...
Categories: DBA Blogs

Freeing up space

Tom Kyte - Fri, 2016-08-12 23:46
Hi, I have a quick question that doesn't require a test script or anything, if I may? I have a table that is partitioned by date. Each year has it's own ASSM locally managed tablespace. The dates go back as far as 2006. There is a BLOB which ho...
Categories: DBA Blogs

To TRANDATA or To SCHEMATRANDATA? … That is the #GoldenGate questions of the day!

DBASolved - Fri, 2016-08-12 19:30

If you are familiar with using Oracle GoldenGate, you know that on the source side of the equation you have to enable supplemental logging and sometimes force logging on the database. I traditionally do both just to make sure that I capture as much as I can into the redo stream from the transactions on the database. For Oracle GoldenGate purposes, this is not the only thing you need to turn on to ensure all needed information is captured to the trail files.

There are two Oracle GoldenGate GGSCI commands that can be ran to enable supplemental logging at the schema or table level. These commands are ADD TRANDATA and ADD SCHEMATRANDATA. What is the difference between the two, you may ask?

ADD TRANDATA – is used to enable supplemental logging at the table level
ADD SCHEMATRANDATA – is used to enable supplemental logging at the schema level

That is such a high-level view of the concept. What is the difference between the two trandata approaches, really?


ADD TRANDATA command is used to enable Oracle GoldenGate to acquire the transaction information that it needs from the transaction records. This version of the command can be used on the following databases:

  • DB2 for i Database
  • DB2 LUW Database
  • DB2 z/OS Database
  • Oracle Database
  • MS SQL Server
  • Sybase Database

For an Oracle Database, ADD TRANDATA enables the unconditional logging of the primary key and conditional supplemental logging of all unique key(s) and foreign key(s) of the specified table. Additionally, you can use ADD TRANDATA with the COLS option to log any non-key columns that can be used with the FILTER statements and KEYCOLS clauses in the TABLE and MAP parameters.

An example of adding trandata to a schema would be:

GGSCI> dblogin useridalias gate
GGSCI> add trandata soe.*


Once transdata has been added to the schema/tables, you can verify the existence of trandata from GGSCI using the INFO TRANDATA command as demonstrated in the below command set.

GGSCI> dblogin useridalias gate
GGSCI> info trandata soe.addresses
2016-08-12 15:07:23  INFO    OGG-06480  Schema level supplemental logging, excluding non-validated keys, is enabled on schema SOE.
2016-08-12 15:07:23  INFO    OGG-01980  Schema level supplemental logging is enabled on schema SOE for all scheduling columns.
Logging of supplemental redo log data is enabled for table SOE.ADDRESSES.

Now that ADD TRANDATA has been ran, what exactly does ADD TRANDATA do to the database it is ran against? For an Oracle Database, ADD TRANDATA adds a Supplemental Log Group (SLG) on to the table. This can be seen from the DBA_LOG_GROUP view under SYS. The SLGs that are corrected are all labeled with a prefix of “GGS”. The following output shows what this looks like after running it for a whole schema.

select owner, log_group_name, table_name, log_group_type, always, generated 
from dba_log_groups
where owner = 'SOE'
and log_group_name like 'GGS%';

OWNER           LOG_GROUP_NAME       TABLE_NAME                     LOG_GROUP_TYPE                 ALWAYS                         GENERATED                    
--------------- -------------------- ------------------------------ ------------------------------ ------------------------------ ------------------------------
SOE             GGS_105669           CUSTOMERS                      USER LOG GROUP                 ALWAYS                         USER NAME                     
SOE             GGS_105702           ADDRESSES                      USER LOG GROUP                 ALWAYS                         USER NAME                     
SOE             GGS_105735           CARD_DETAILS                   USER LOG GROUP                 ALWAYS                         USER NAME                     
SOE             GGS_105768           WAREHOUSES                     USER LOG GROUP                 ALWAYS                         USER NAME                     
SOE             GGS_105769           ORDER_ITEMS                    USER LOG GROUP                 ALWAYS                         USER NAME                     
SOE             GGS_105802           ORDERS                         USER LOG GROUP                 ALWAYS                         USER NAME                     
SOE             GGS_105835           INVENTORIES                    USER LOG GROUP                 ALWAYS                         USER NAME                     
SOE             GGS_105836           PRODUCT_INFORMATION            USER LOG GROUP                 ALWAYS                         USER NAME                     
SOE             GGS_105837           LOGON                          USER LOG GROUP                 ALWAYS                         USER NAME                     
SOE             GGS_105870           PRODUCT_DESCRIPTIONS           USER LOG GROUP                 ALWAYS                         USER NAME                     
SOE             GGS_105871           ORDERENTRY_METADATA            USER LOG GROUP                 ALWAYS                         USER NAME                     
SOE             GGS_254721           TEST1                          USER LOG GROUP                 ALWAYS                         USER NAME

Now, there are some who will argue that the same effect can be done by just asking a SLG to a table manually. Although this is true, Oracle GoldenGate uses the GGS_ prefix to keep track of the tables that are in the replication process. Also, easier to clean up when you issue DROP TRANDATA, which will remove all the associated SLG items from the tables.

The ADD TRANDATA approach should be used with 11g or older versions of Oracle GoldenGate. As you move towards new version of Oracle GoldenGate, Oracle is pushing that everyone pick up and use the ADD SCHEMATRANDATA method. So let’s take a look at that now.


The ADD SCHEMATRANDATA is used on all the current and future tables in a given schema to automatically log a superset of available keys that Oracle GoldenGate needs for row identification. Using this version of TRANDATA, it can be used with both the integrated and classic capture processes.

There are four key reasons why you should use ADD SCHEMATRANDATA:

  • Enables supplemental logging for new tables created with a CREATE TABLE DDL command.
  • Updates supplemental logging for tables affected by an ALTER TABLE DDL command that adds or drops columns
  • Updates supplemental logging for tables affected by RENAME TABLE command
  • Updates supplemental logging for tables affected by adding or dropping of unique or primary key constraints


Although ADD SCHEMATRANDATA can be used with both integrated and classic capture processes, it is mostly geared towards the integrated process. There are three primary reasons to use ADD SCHEMATRANDATA with the integrated capture:

Ensures that the correct key is logged by logging all the keys
Options are provided that enable the logging of the primary, unique, and foreign keys to support the computation of dependences among the tables being processed by the integrated replicats (think apply servers)
Ensures the appropriate key values are logged in the redo to allow DML to be mapped to object that have DDL issued against them.

Earlier in this post, I mentioned that I often enable “force logging” on the database when I do the minimal supplemental logging. Force logging is encouraged by Oracle, especially when using ADD SCHEMATRANDATA.

Now to add issue ADD SCHEMATRANDATA against an Oracle database, it is similar the same way as ADD TRANDATA, with the difference that you don’t have to provide any wildcards. In the examples below, I show you how this can be done:

GGSCI> dblogin useridalias ggate
GGSCI> add schematrandata soe
2016-08-12 15:47:40  INFO    OGG-01788  SCHEMATRANDATA has been added on schema soe.
2016-08-12 15:47:40  INFO    OGG-01976  SCHEMATRANDATA for scheduling columns has been added on schema soe.

After running ADD SCHEMATRANDATA, you can perform an INFO SCHEMATRANDATA on the schema to see what has been modified.

GGSCI (fred.acme.com as ggate@src12c) 9> info schematrandata soe

2016-08-12 15:51:52  INFO    OGG-06480  Schema level supplemental logging, excluding non-validated keys, is enabled on schema SOE.

2016-08-12 15:51:52  INFO    OGG-01980  Schema level supplemental logging is enabled on schema SOE for all scheduling columns.

2016-08-12 15:51:52  INFO    OGG-10462  Schema SOE have 12 prepared tables for instantiation.

Digging around in the database, to see if ADD SCHEMATRANDATA does the same as ADD TRANDATA with SLG; well, it doesn’t. ADD SCHEMATRANDATA does not create any SLGs. The only place that I have found that has any record of supplemental logging turned on with ADD SCHEMATRANDATA is in the V_$GOLDENGATE_CAPABILITIES view. Here, you can see that supplemental logging has been enabled, the number of times it has been acted upon and when it was last executed.

NAME                        COUNT TO_CHAR(LAST_USED     CON_ID
---------------------- ---------- ----------------- ----------
DBENCRYPTION                    0 12-JUN-2016 21:20          0
DBLOGREADER                     0 12-JUN-2016 21:20          0
TRIGGERSUPPRESSION              0 12-JUN-2016 21:20          0
TRANSIENTDUPLICATE              0 12-JUN-2016 21:20          0
DDLTRIGGEROPTIMIZATION          0 12-JUN-2016 21:20          0
GGSESSION                       0 12-JUN-2016 21:20          0
DELETECASCADEHINT               0 12-JUN-2016 21:20          0
SUPPLEMENTALLOG                 5 12-AUG-2016 16:02          0

Now, being that the integrated items of Oracle GoldenGate are closely related to Oracle Streams, there may be a table or view related to Streams that has this information. Once I find it, I’ll provide an update to this post.

In the mean time, I hope this post has provided some insight into the differences between ADD TRANDATA and ADD SCHEMATRANDATA.

If you are moving to or using the integrated products of Oracle GoldenGate, then ADD SCHEMATRANDATA is the method that you should be using.






Filed under: Golden Gate
Categories: DBA Blogs

Informational Error ORA-01013

Tom Kyte - Fri, 2016-08-12 05:26
Hi, This is my question : I need to log in a table in my database every action be made on a script, even like sudden interruptions such us ctrl+c. The problem is that i don't know how to catch ORA-01013 ( which is the code for abrupt interruptions...
Categories: DBA Blogs

tns and listener error

Tom Kyte - Fri, 2016-08-12 05:26
Hi tom, In our oracle database setup frequently getting error like - Fatal NI connect error 12537, connecting to: (LOCAL=NO) VERSION INFORMATION: TNS for Linux: Version - Production Oracle Bequeath NT Protoco...
Categories: DBA Blogs


Subscribe to Oracle FAQ aggregator - DBA Blogs