DBA Blogs

SQL Query to remove duplicate values across columns

Tom Kyte - Mon, 2018-05-14 09:26
I have three field in the table say from_city,to_city and distance. <code> Table name: City_distance From_city to_city distance ---------------------------------------- A B 100 B A 100 C B 200 C A 300 E F 700 F E 700 </code> Here I ...
Categories: DBA Blogs

Logical Storage Structures\chained and migrated rows

Tom Kyte - Mon, 2018-05-14 09:26
if we have multiple datafiles of 32GB and there are fully utilized. Can chained and migrated rows occurs due to the maximum datafile utilization?
Categories: DBA Blogs

performance tunning for Sql query

Tom Kyte - Mon, 2018-05-14 09:26
hi Team, I am struggling in one of query tunning , below are details assoicated with , please analyse and recommend on it . <code> SELECT * FROM (SELECT /*+ INDEX(A INDX14_TABLE1) INDEX(B IDX51_TABLE2) */ * F...
Categories: DBA Blogs

Tables Access

Tom Kyte - Mon, 2018-05-14 09:26
In our Production database is has been decided to drop a tablespace because already a datafile was lost due to accidentally file was dropped at OS level with command rm -rf, somehow luckily there was not huge loss but some of index which were cre...
Categories: DBA Blogs

Connecting to Database.

Tom Kyte - Mon, 2018-05-14 09:26
Hi, I have two versions of Oracle Databses(12C,11G) installed on my personal computer running on Windows 8. When i execute below command its connecting to 12C instance. sqlplus system/welcome; i would like to know how can i connect to 11G instan...
Categories: DBA Blogs

XMLSERIALIZE dynamic order by statement

Tom Kyte - Sat, 2018-05-12 02:26
Hi, I've the following problem. I call the function with the order-by parameter, but it will not be used. When I use the order-by hardcoded, the values ??are sorted. Do you have any idee what is wrong in the function or the calling? Than...
Categories: DBA Blogs

All Parent - Child tables in the database

Tom Kyte - Fri, 2018-05-11 08:06
Hi Tom, Can you please explain the way to get a list of all parent child relation in the database. The list should have the Grand parent as the first item and the last item will be the grand child. For Example, Parent ...
Categories: DBA Blogs

performance tuning - sql slows down after gather stats

Tom Kyte - Fri, 2018-05-11 08:06
Hi , I have faced a situation where sql id plan hash value is changed due stats gather on one of table currently i dont understand why this stats gathering cause chnage in plan and due to which execution time is poor now can you guide...
Categories: DBA Blogs

insert into local table with select from multiple database links in a loop

Tom Kyte - Fri, 2018-05-11 08:06
Hi Tom, i would like to apply the Orignial SQL Statement from Oracle MOS DOC ID 1317265.1 and 1309070.1 for license and healthcheck for all of my database instances. My Goal is to create a centralized repository with informations of my databases. Un...
Categories: DBA Blogs

Impdp not failing even if target table have missing column

Tom Kyte - Fri, 2018-05-11 08:06
My question why import is not failing even the source and target have different table structure <b>Source DB</b> has below table (with additional column COL3 and populated SQL> desc tab1 Name Null? Type ---------------------------...
Categories: DBA Blogs

Unique key across tables

Tom Kyte - Thu, 2018-05-10 13:46
Dear tom, How can i enforce unique key across multiple tables. Table1 and Table2 both have ID primary key column. Is it possible to restrict, while inserting and updating into these tables, the union of ID values from two tables are unique. r...
Categories: DBA Blogs

SEQUENCE

Tom Kyte - Thu, 2018-05-10 13:46
hi tom, during one interview i got one question in sequence i.e if there is one sequnce whose max value is 40,but after got nextval 20.without execute the select query 20,000 times and without alter the sequence i want to get 20,000 in the nextva...
Categories: DBA Blogs

How much data is there in your database?

Kubilay Çilkara - Thu, 2018-05-10 13:35
Have you ever thought how much of your database is actually data?

Sometimes you need to ask this most simple question about your database to figure out what the real size of your data is.

Databases store loads of auxiliary data such as indexes and materialized views and other structures where the original data is repeated. Many times databases repeat the data in indexes and materialized views for the sake of achieving better performance for the applications they server, and this repetition is legitimate.

But should this repetition be measured and counted as database size?

To make things worse, many databases due to many updates and deletes, over time create white space in their storage layer. This white space is fragmented free space which can not be re-used by new data entries. Often it might even end up being scanned in full table scans unnecessarily, eating up your resources. But most unfortunate of it all is that it will appear as if it is data in your database size measurements when usually it is not! White space is just void.

There are mechanisms in databases which will automatically remedy the white space and reset and re-organise the storage of data. Here is a link which talks about this in length https://oracle-base.com/articles/misc/reclaiming-unused-space 

One should be diligent when measuring database sizes, there is loads of data which is repeated and some which is just the blank void due to fragmentation and white-space.

So, how do we measure?

Below is a database size measuring SQL script which can be used with Oracle to show data (excluding the indexes) in tables and partitions. It also tries to estimate real storage (in the actual_gb column) excluding the whitespace by multiplying the number of rows in a table with the average row size. Replace the '<YOURSCHEMA>' bit with the schema you wish to measure.

SELECT
    SUM(actual_gb),
    SUM(segment_gb)
FROM
    (
        SELECT
            s.owner,
            t.table_name,
            s.segment_name,
            s.segment_type,
            t.num_rows,
            t.avg_row_len,
            t.avg_row_len * t.num_rows / 1024 / 1024 / 1024 actual_gb,
            SUM(s.bytes) / 1024 / 1024 / 1024 segment_gb
        FROM
            dba_segments s,
            dba_tables t
        WHERE
            s.owner = '<YOURSCHEMA>'
            AND   t.table_name = s.segment_name
            AND   segment_type IN (
                 'TABLE'
                ,'TABLE PARTITION'
                ,'TABLE SUBPARTITION'
            )
        GROUP BY
            s.owner,
            t.table_name,
            s.segment_name,
            s.segment_type,
            t.num_rows,
            t.avg_row_len,
            t.avg_row_len * t.num_rows / 1024 / 1024 / 1024
    );
Categories: DBA Blogs

Creating a Custom Component in Oracle JET - Gökhan

Introduction Oracle JET (JavaScript Extension Toolkit) is a collection of open source libraries for JavaScript developers to develop client-side applications. It comes with lots of responsive...

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

18c Scalable Sequences Part III (Too Much Rope)

Richard Foote - Tue, 2018-05-08 02:06
I previously looked in Part I and Part II how Scalable Sequences officially released in 18c can reduce index contention issues, by automatically assigning a 6 digit prefix to the sequence value based on the instance ID and session ID of the session. We need to be careful and consider this 6 digit prefix if […]
Categories: DBA Blogs

Build a Integrated Replicat using JSON

DBASolved - Mon, 2018-05-07 13:15

In a previous post, I showed you how to build an Integrated Extract (IE) and Distribution Path by using JSON and cURL. In this post, let’s look at how you can build an Integrated Replicat (IR) in the same manner.

To build a replicat using JSON, the JSON document is made up of the following 8:

Config – Details for the associated parameter file
Source – Where the replicat should read transactions from
Credentials – What credentials in the credential stores should be used
Checkpoint – What checkpoint table is used by the replicat
Mode – What type of replicat will be built
Registration – Register the replicat with the database
Begin – At what timeframe the replicat should start
Status – If the extract should be started or not

The resulting JSON document would look like the following:

{
“config”:[
“Replicat REPTS”,
“UseridAlias TGGATE”,
“Map SOE.*, Target SOE.*;”
],
“source”:{
“name”:”bc”
},
“credentials”:{
“alias”:”TGGATE”
},
“checkpoint”:{
“table”:”ggate.checkpoints”
},
“mode”:{
“type”:”integrated”,
“parallel”: true
},
“registration”: “standard”,
“begin”:”now”,
“status”:”stopped”
}

Now that you have a valid JSON document, a cURL command for building the integrated replicat can be done as follows:

curl -X POST \
http://localhost:17001/services/v2/replicats/REPTS\
-H ‘Cache-Control: no-cache’ \
-d ‘{
“config”:[
“Replicat REPTS”,
“UseridAlias TGGATE”,
“Map SOE.*, Target SOE.*;”
],
“source”:{
“name”:”bc”
},
“credentials”:{
“alias”:”TGGATE”
},
“checkpoint”:{
“table”:”ggate.checkpoints”
},
“mode”:{
“type”:”integrated”,
“parallel”: true
},
“registration”: “standard”,
“begin”:”now”,
“status”:”stopped”
}’

Just like the Integrated Extract (IE) and Distribution Service, the Integrated Replicat (IR) is created in a stopped state. At this point, you can start the IR and validate whatchanges need to be made to ensure replication happens.

Enjoy!!!

Categories: DBA Blogs

Migrate On-premise DB to database Cloud service using Cloud DB backup module

Are you considering migrating your Oracle Database to Oracle Database on the Cloud? Are you planning to backup your Oracle Database to the Cloud? The Oracle Database Cloud DB backup module, allows...

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

Offline Relocation of a PDB using RMAN

Hemant K Chitale - Sun, 2018-05-06 11:17
I've published a new video on Offline Relocation of a PDB using RMAN in 12.2
.
.
.

Categories: DBA Blogs

Build a Distribution Path using JSON

DBASolved - Fri, 2018-05-04 13:15

In a previous post, I showed you how to build an Integrated Extract (IE) using JSON and simple cURL command. In this post, let’s take a look at how to build a Distribution Service Path.

First think to understand is that the Distribution Service is the replace for the Extract Data Pump in the traditional Oracle GoldenGate architecture. The Distribution Service does the same thing as the Extract Data Pump with the exception of transformations. If you have a need to do transformations with Oracle GoldenGate Microservices; the transformations have to be pushed to either the extract or the replicat to be done.

The purpose of the Distribution Service is to ensure that the local trail files are shipped across the network and reach the Reciever Service which will create the remote trail files.

Note: The Receiver Service, on the target side, will be started automatically when the Distribution Service connects to the port number for it.

Within the Distribution Service, you will create Distribution Paths between the source and target hosts. The way you do this with JSON quite simple. There are 4 main items the JSON should contain.

1. Name – This is what the Distribution Path will be named
2. Status – Should the Distribution Path be running or stopped
3. Source – This specifies the local trail file that should be read for transactions
4. Target – This specifies the Login and URL to write to the remote trail files.

Note: For the Target setting, there are 4 protocols that can be used:
Secure Websockets (wss) – default
Websockets (ws)
UDP-based Data Transfer Protocol (udt)
Oracle GoldenGate (ogg)

An example of a JSON document that would be used to build a Distribution Path is as follows:

{
“name”: “TSTPATH”,
“status”: “stopped”,
“source”: {
“uri”: “trail://localhost:16002/services/v2/sources?trail=bb”
},
“target”: {
“uri”: “ws://OracleGoldenGate+WSTARGET@localhost:17003/services/v2/targets?trail=bc”
}
}

To build this Distirbution Path (TSTPATH), a cURL command as such can be used to build it:

curl -X POST \
http://localhost:16002/services/v2/sources/TSTPATH \
-H ‘Cache-Control: no-cache’ \
-d ‘{
“name”: “TSTPATH”,
“status”: “stopped”,
“source”: {
“uri”: “trail://localhost:16002/services/v2/sources?trail=bb”
},
“target”: {
“uri”: “ws://OracleGoldenGate+WSTARGET@localhost:17003/services/v2/targets?trail=bc”
}
}’

Once the Distribution Path is created, you can start it. Upon starting the path, you can check the Receiver Service on the target side. It should have been started as well.

Enjoy!!!

Categories: DBA Blogs

enq: TX - row lock contention wait event for an UPDATE statement

Tom Kyte - Fri, 2018-05-04 11:06
Hi, We are facing this wait event "enq: TX - row lock contention" when an update statement is being executed.THis update has filter columns which are part of unique index.What I observed during analysis is table has PCTFREE=10 and PCTUSED=NULL in ...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs