DBA Blogs

Creating dummy data for chart

Tom Kyte - Tue, 2019-04-09 22:06
Hi, I am generating a bar chart in Apex and my data has few gaps as a result there are gaps between bars.If I fill data in query with zero value then my bar chart will be correctly displayed. Below is data and required output <code>create table...
Categories: DBA Blogs

Like Operator with IN clause

Tom Kyte - Tue, 2019-04-09 22:06
I have to compare more than one Patter using LIKE operator Say I want to prepare query SELECT name FROM employee WHERE name <b>LIKE IN</b> ('sasho','shashi%','rags')
Categories: DBA Blogs

Goldengate TDE Extract support for SQL Server

VitalSoftTech - Tue, 2019-04-09 13:41
Does GoldenGate support extraction for Transparent Data Encrytion on SQL Server Database?
Categories: DBA Blogs

10 Tips for Marketing IT Services

VitalSoftTech - Tue, 2019-04-09 12:46
Based on my experience in marketing IT services, the competition is really high and very challenging. I’m sure you will agree to that! A lot of online entrepreneurs, especially those that are dealing with IT services like you are always on the search for the best marketing tips. You always needed new information so that […]
Categories: DBA Blogs

JSON path expression syntax error

Tom Kyte - Tue, 2019-04-09 03:46
Hi TOM, I am trying to use json_value function infor loop as below. then getting error as 'PL/SQL: ORA-40442: JSON path expression syntax error'. Could you please help me with solution. Thanks set serveroutput on; DECLARE p_dn_list CLO...
Categories: DBA Blogs

Parallel recursive WITH Enhancements

Tom Kyte - Tue, 2019-04-09 03:46
Team, was reading about this feature Parallel recursive WITH clause enhancements in 12.2 database from the below link: <u>https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/SELECT.html#GUID-CFA006CA-6FF1-4972-821E-6996142A51C...
Categories: DBA Blogs

Attempting primary / standby's using same scan listener name and configuration on both clusters.

Tom Kyte - Tue, 2019-04-09 03:46
Hi Tom, I've never gotten through to you in 20 years, so hopefully you'll be available as I could use some sound advice (you're so in demand, I gave up about 10 years ago :-) Here are the particulars: * We're planning a migration from data cen...
Categories: DBA Blogs

Check db_link from several databases, from one server via dv_links to these servers

Tom Kyte - Tue, 2019-04-09 03:46
Hello, I need to check db_links from all of my servers and I want to check it from procedure or view on statistic db server. Statistic db server have db_links to all of my db's. It is possible? If it is possible how I can do this?
Categories: DBA Blogs

Why the same query performs much slower in PL/SQL procedure/package than directly querying

Tom Kyte - Tue, 2019-04-09 03:46
Dear Tom, I have a complex query joining multiple tables which takes about 10 to 15 seconds to return results. There are 3 layers (2 sub queries) of the "FROM" to get the final results. However, if I create a procedure to run the same codes, whic...
Categories: DBA Blogs

To create multiple instances and databases on same unix machine

Tom Kyte - Tue, 2019-04-09 03:46
I have a requirement to create 2 instances and 2 databases on same unix machine. Do I need multiple homes, I think not because it is same version (11g). I already created one instance/database using OUI. Are these steps right for creating second one?...
Categories: DBA Blogs

Partitioning -- 15 : Online Modification of Partitioning Type (Strategy)

Hemant K Chitale - Mon, 2019-04-08 03:59
Oracle 18c introduces the ability to convert a Partitioned Table from one Type to another -- e.g. from Hash Partitioning to Range Partitioning.  This is effectively a change of the Partitioning strategy for a table without actually having to manually rebuild the table.

I start with a Hash Partitioned Table.

SQL> create table customers(customer_id number, customer_name varchar2(200), customer_city_code number)
2 partition by hash (customer_id) partitions 4;

Table created.

SQL> select partitioning_type from user_part_tables
2 where table_name = 'CUSTOMERS'
3 /


SQL> select partition_name from user_tab_partitions
2 where table_name = 'CUSTOMERS'
3 /


SQL> insert into customers
2 select dbms_random.value(1,1000001), dbms_random.string('X',25), mod(rownum,5)
3 from dual
4 connect by level < 1000001
5 /

1000000 rows created.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats('','CUSTOMERS');

PL/SQL procedure successfully completed.

SQL> select partition_name, num_rows
2 from user_tab_partitions
3 where table_name = 'CUSTOMERS'
4 /

---------------- ----------
SYS_P221 250090
SYS_P222 249563
SYS_P223 250018
SYS_P224 250329


I now want to convert this Hash Partitioned Table to a Range Partitioned Table online.

SQL> alter table customers
2 modify
3 partition by range (customer_id)
4 (partition P_100K values less than (100001),
5 partition P_200K values less than (200001),
6 partition P_300K values less than (300001),
7 partition P_400K values less than (400001),
8 partition P_500K values less than (500001),
9 partition P_600K values less than (600001),
10 partition P_700K values less than (700001),
11 partition P_800K values less than (800001),
12 partition P_900K values less than (900001),
13 partition P_1MIL values less than (1000001),
14 partition P_2MIL values less than (2000001),
15 partition P_MAXVALUE values less than (MAXVALUE))
16 online;

Table altered.

SQL> select partitioning_type
2 from user_part_tables
3 where table_name = 'CUSTOMERS'
4 /


SQL> exec dbms_stats.gather_table_stats('','CUSTOMERS');

PL/SQL procedure successfully completed.

SQL> col high_value format a12
SQL> select partition_name, high_value, num_rows
2 from user_tab_partitions
3 where table_name = 'CUSTOMERS'
4 order by partition_position
5 /

---------------- ------------ ----------
P_100K 100001 100116
P_200K 200001 99604
P_300K 300001 99941
P_400K 400001 100048
P_500K 500001 99841
P_600K 600001 99920
P_700K 700001 100081
P_800K 800001 100024
P_900K 900001 100123
P_1MIL 1000001 100302
P_2MIL 2000001 0

12 rows selected.


The Hash Partitioned Table is now converted to a Range Partitioned Table.  The number of Partitions has been changed.  And the operation was performed online with the ONLINE keyword added to the ALTER TABLE ... statement.  The UPDATE INDEXES clauses can also be used to update existing Indexes on the Table.

Categories: DBA Blogs

Running GoldenGate Installers within Docker containers on MacOS

DBASolved - Fri, 2019-04-05 21:56

Over the last few days, I’ve been trying to improve the speed at which I can setup and configure Oracle GoldenGate for testing purposes. What I settled on what setting up a Docker container to be a VM subsistute. Besides I’ve been playing with Docker, off and on, for over a year now; yet I’m finally investing the time to drive this forward.

One of the items I’ve been trying to solve is “how do I test the installers for Oracle GoldenGate”? In order to do this, I’ve had to figure out how to run an X11 interface from the Docker container. In order to do this, I’ve had to use the product XQuartz (download here).

Configure XQuartz

After you download the XQuartz software, you will need to enable the security setting “Allow connections from network clients”. This setting enables connections from remote applications, i.e. within the Docker container.

XQuartz -> Preferences

After setting the settings in XQuartz, restart the application. The restart will enable the settings. Then start XQuartz and minimize the window.

Prepare Docker

With te XQuartz running, now I can start using Docker to run the installers. The steps that I need to this are:

Start a Docker container:
When you start the Docker container, you will need to specify the DISPLAY port in the run command. Also notice that I’m using “docker.host.internal”.

docker run -dit –privileged –name oraogg -e DISPLAY=host.docker.internal:0 oraogg:18.1.0

Enable XHost from the OS:
Before you can bring the installer GUI up on my host display, I need to enable the host to allow the connection. This is simply enabling xhost for the localhost.

xhost +

Set DISPLAY within the Docker Container:
With the Docker container running, I can now access the container and specify the DISPLAY variable. Also notice that I’m using “docker.host.internal” inside of the Docker container.

docker exec -it oraogg /bin/bash
su – oracle

export DISPLAY=host.docker.internal:0

Run the Installer:
Now I’m ready to start the Oracle GoldenGate Installer(s). I navigate to the $OGG_HOME/bin and run the Oracle GoldenGate Configuration Assistant (OGGCA).


At this point, I can use xQuartz to provide the GUI interface for items I want to install within a Docker container.


Categories: DBA Blogs

AWS CloudFormation Linter Installation and Quick Introduction

Pakistan's First Oracle Blog - Thu, 2019-04-04 23:27
AWS Cloudformation is an evolving managed service which facilitates infrastructure as a code in the cloud. What it means is that you can create AWS resources like EC2 instances, S3 buckets and many more just by writing code instead of using GUI console.

For instances, if you want to create 100 EC2 instances for production, and then later you have to create same for development and for testing and then may be later on, you might need to change the configuration of those EC2 instances then doing that by GUI would be a very tedious task. With CloudFormation, you just describe those EC2 instances once in a file called as template and then run it.

Now another cool thing here is that you don't have to write code for all those 100 EC2 instances in that template. You may just describe one EC2 instance and then use CloudFormation Macros to provision 100 or even more of them  (AWS resource limits apply).

Anyway, CloudFormation templates are either written in JSON or in YAML and they grow big fairly quickly and it becomes hard to keep track of syntactical and other errors. With aws cloudformation validate-template or with GUI you can identify the mal-formatted template file but what about if you have written any property of a resource wrongly? What if the resource specification is not as per AWS resource specification document?

Enters AWS CloudFormation Linter.

Linting is an ancient concept from good old C era which refers to a tool to check the source code for any syntax or bugs. So CloudFormation Linter or cfn-lint is a nifty tool to check for syntactical and bugs in the template. I tried to catch any logical errors with cfn-lint in the template but it was unable to identify them. For example, in my template of building a code pipeline, I omitted the cfn-init signal to let CFN know that launch configuration is complete so that deployment group of CodeDeploy could use it, but it was unable to catch that. So it would be a great future feature for cfn-lint.

Following is an example as how to install it and then I have passed through one of my templates to identify any errors:

Another cool blog post by Chuck Meyer is here about cfn-lint and git pre-commit validation.

Categories: DBA Blogs

Format columns correct datatype for CSV output

Tom Kyte - Thu, 2019-04-04 13:46
Hello, I generate in SQL Developer version Version files in CSV format (execute as SQL script). The table has column values like <b>2-4 or 9/11</b>, that is in CSV file displayed as 02. Apr. I tried with <code>col testcol format...
Categories: DBA Blogs

APEX 4.x.x. Support For 18c Database

Tom Kyte - Thu, 2019-04-04 13:46
Hi , I would like to know does Oracle 18c database support Apex Version 4.x.x. our application is build on APEX 4.x.x right now we do not want to upgrade APEX version to 5.x.x. but want to upgrade only Database version from 12c to 18c
Categories: DBA Blogs

Display all combination of given number

Tom Kyte - Thu, 2019-04-04 13:46
I want to print those combination which have exact 110 sum. Here I give some data for an example. 10 20 30 60 40 70 99 90 80 And output is 80,20,10 70,30,10
Categories: DBA Blogs


Tom Kyte - Thu, 2019-04-04 13:46
Good Day Folks, I have one query regarding the BULK COLLECT clause. Could you please let me know how we can fetch/handle multiple column's data using Bulk Collect clause. Please feel free to alter below code. <Code> -- BULK COLLECT CLAUSE WIT...
Categories: DBA Blogs

Buffer Limit

Tom Kyte - Thu, 2019-04-04 13:46
Hi, The Buffer size we give for dbms_out.put_line is different from Data base buffer cache in SGA or same? and can you show me am example on how to use dbms_out.getline function. Thanks in Advance.
Categories: DBA Blogs

Can we have multiple versions of Oracle database instance in 11gR2 RAC

Tom Kyte - Thu, 2019-04-04 13:46
We would like to install 2 11.2 database and one 10.2 database in 11GR2 RAC. Is this possible? ? Can we run all 3 instances on a Single RAC 11.2 Cluster? Will this complicate backup/recovery? Thanks
Categories: DBA Blogs

Everyone should read this

Hemant K Chitale - Thu, 2019-04-04 02:10
An excellent article that anyone promising, developing, maintaining or using any system that is non-trivial should read :


Categories: DBA Blogs


Subscribe to Oracle FAQ aggregator - DBA Blogs