DBA Blogs

Join us at the Oracle EMEA IaaS & PaaS Partner Community Forum

Dear Partner,  The 2019 Oracle EMEA IaaS & PaaS Partner Community Forum will take place in Rome, Italy, on 8th & 9th May 2019. This year’s forum will be focused...

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

Join us at the Oracle EMEA IaaS & PaaS Partner Community Forum

Dear Partner,  The 2019 Oracle EMEA IaaS & PaaS Partner Community Forum will take place in Rome, Italy, on 8th & 9th May 2019. This year’s forum will be focused...

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

Quickly build self-signed certificates for Oracle GoldenGate 18c

DBASolved - Wed, 2019-03-27 13:30

With Oracle GoldenGate Microservices 12c and 18c, the architecture can be configured to use SSL certificates for securing and replicating between sites. This is a huge improvement for securing the replication framework and makes it a lot simpler to replicate data over standard HTTPS ports.

In order to configure Oracle GoldenGate 12c/18c Microserivces with SSL certificates, you either have to generate a self-signed certificate or bring your own certificate (BYOC) to use. This also requires you to build an Oracle Wallet to store the certificates in. Additionally, to secure both sides of the replication environment within a Microservices environment; the wallet is required to be copied to any other environment where you will be replicating to or from.

In this post, I’m providing you with a simple python script that will build the Self-Signed Certificates needed for testing purposes. By using this overly-simplified, script you assume responsibility of using the generated certificates.

For more details on configuring Oracle GoldenGate Microservices 12c/18c using Self-Signed Certificates, refer to the Oracle documentation for Securing Oracle GoldenGate Microservices (here).

My overly-simple python script is located here.

Happy Securing and Enjoy!!!

Categories: DBA Blogs

ORA-00984 column not allowed here

VitalSoftTech - Wed, 2019-03-27 09:45
What is the cause of the "ORA-00984 column not allowed here" error? How do I resolve this?
Categories: DBA Blogs

Non-Deterministic Functions and Scalar Subquery Caching

Tom Kyte - Tue, 2019-03-26 09:26
Hi, i got one question regarding scalar subquery: Why does Oracle not restrict scalar-subquery-caching to functions which are declared to be deterministic? With the deterministic keyword the developer states that a function is deterministic...
Categories: DBA Blogs

dbms_assert package

Tom Kyte - Tue, 2019-03-26 09:26
Hi, Oracle provides dbms_assert package with functions which can be used to safeguard SQL injections. Shall I use this package like below in my java application. select DBMS_ASSERT.sql_object_name ('test') from dual; I didn't see people usi...
Categories: DBA Blogs

How to move to a new tablespace a column LOB of the dataype ANYDATA ?

Tom Kyte - Tue, 2019-03-26 09:26
Hello Masters, I have a table with a PUBLIC.ANYDATA column. <code>SQL> desc XL_EURO_AXAIA.EXT_TCV_TCH_VALEUR Nom NULL ? Type ----------------------------------------------- TCH_ID NOT NULL NUMBER ID NO...
Categories: DBA Blogs

Partitioning -- 14 : Converting a non-Partitioned Table to a Partitioned Table

Hemant K Chitale - Mon, 2019-03-25 09:59
Pre-12cRelease2, there were only three methods to convert a non-Partitioned Table to a Partitioned Table

(a) Create a new, empty, Partitioned Table and copy (using INSERT .... AS SELECT ... ) all the data from the non-Partitioned Table to the new, Partitioned Table (and subsequently rename the new Partitioned Table after renaming or dropping the old non-Partitioned Table)

(b) Create a new, empty, Partitioned Table and use EXCHANGE PARTITION to switch the non-Partitioned Table into the Partitioned Table (and then run subsequent SPLIT PARTITION or ADD PARTITION commands as needed to create the additional Partitions)

(c) Create an interim Partitioned Table and use DBMS_REDEFINITION to do an online copy of the data to the interim Partitioned Table and automatically switch the name at the end


12.2 introduced the ability to use ALTER TABLE  ... MODIFY PARTITION ... to convert a non-Partitioned Table to a Partitioned Table

I start with a non-Partitioned Table :

SQL> select table_name, partitioned
2 from user_tables
3 where table_name = 'SALES_DATA_NONPARTITIONED'
4 /

TABLE_NAME PAR
------------------------------ ---
SALES_DATA_NONPARTITIONED NO

SQL> select index_name, uniqueness, partitioned
2 from user_indexes
3 where table_name = 'SALES_DATA_NONPARTITIONED'
4 /

INDEX_NAME UNIQUENES PAR
------------------------------ --------- ---
SALES_DATA_UK UNIQUE NO

SQL>


I then convert it to a Range-Partitioned Table.

SQL> alter table sales_data_nonpartitioned
2 modify
3 partition by range (sale_date)
4 (
5 partition P_2015 values less than (to_date('01-JAN-2016','DD-MON-YYYY')),
6 partition P_2016 values less than (to_date('01-JAN-2017','DD-MON-YYYY')),
7 partition P_2017 values less than (to_date('01-JAN-2018','DD-MON-YYYY')),
8 partition P_2018 values less than (to_date('01-JAN-2019','DD-MON-YYYY')),
9 partition P_2019 values less than (to_date('01-JAN-2020','DD-MON-YYYY')),
10 partition p_MAXVALUE values less than (MAXVALUE)
11 )
12 online
13 update indexes
14 /

Table altered.

SQL>
SQL> alter table sales_data_nonpartitioned rename to sales_data;

Table altered.

SQL>
SQL> select partition_name, high_value
2 from user_tab_partitions
3 where table_name = 'SALES_DATA'
4 order by partition_position
5 /

PARTITION_NAME HIGH_VALUE
---------------- --------------------------
P_2015 TO_DATE(' 2016-01-01 00:00
P_2016 TO_DATE(' 2017-01-01 00:00
P_2017 TO_DATE(' 2018-01-01 00:00
P_2018 TO_DATE(' 2019-01-01 00:00
P_2019 TO_DATE(' 2020-01-01 00:00
P_MAXVALUE MAXVALUE

6 rows selected.

SQL>
SQL> select index_name, partitioned, uniqueness, status
2 from user_indexes
3 where table_name = 'SALES_DATA'
4 /

INDEX_NAME PAR UNIQUENES STATUS
------------------------------ --- --------- --------
SALES_DATA_UK NO UNIQUE VALID

SQL>


The SALES_DATA_NONPARTITIONED was converted to a Range Partitioned Table.  If I didn't have to rename the table (e.g. if the table name was actually, properly SALES_DATA only), then there would be no need to lock the table as the RENAME command does.



Categories: DBA Blogs

Independant Review of What is Oracle GoldenGate

VitalSoftTech - Mon, 2019-03-25 09:12
Golden Gate in Oracle provides continuous and reliable access to real time routing, capturing, transforming and delivering of transactional data.
Categories: DBA Blogs

Python Practice Produced Pretty Pictures

Bobby Durrett's DBA Blog - Sat, 2019-03-23 18:57

I wrote a Python program that made some fun pictures so I thought I would share them even though this is not really a database post.

I practice Python programming by doing Rosetta Code programming tasks that no one has implemented in Python. This is a fun way of keeping up my Python skills. My most recent contribution made pretty pictures so I thought I would show them here. The code takes a cube and breaks up the faces into smaller and smaller pieces that change the cube into a rounder shape.

Here is the input:

Input to the program, a cube

Here is the output after one subdivision:

After one subdivision. Chunky.

Here is the output after two subdivisions:

Two subdivisions. Pretty round.

Note that it is getting rounder. Lastly, after four subdivisions it is remarkably round considering that it started as a cube:

Four subdivisions. Quite round.

The main point of this post was to show the pretty pictures. But, to be more serious, if someone is reading this blog and looking for a programming task to do for practice you can do what I do and find a Rosetta Code task for the language you are learning and you can get some good practice.

Bobby

Categories: DBA Blogs

Migration of a very large warehouse database

Tom Kyte - Fri, 2019-03-22 13:46
This question is more of a solicitation for advice than an actual question. I've been tasked with migrating a large warehouse database from one set of hardware in one datacenter to a new set of hardware in a different datacenter. Excluding temp, ...
Categories: DBA Blogs

How to show special/unseen characters from a column in a plsql ?

Tom Kyte - Fri, 2019-03-22 13:46
Hi Tom, I have a table with special characters in a column. The column values are like this with the plsql below. <code>set serveroutput on; declare c varchar2 (100); a number; begin for i in ( select ekd0756_cur...
Categories: DBA Blogs

how to copy chrome bookmarks from one computer to another

Matt Penny - Fri, 2019-03-22 12:20

copy "C:\Users\matty\AppData\Local\Google\Chrome\User Data\Default\Bookmarks"
"\sh00001\c$\Users\matty\AppData\Local\Google\Chrome\User Data\Default" -verbose

Categories: DBA Blogs

When AWS SCT Unable to Connect to MySQL in RDS or EC2

Pakistan's First Oracle Blog - Fri, 2019-03-22 05:27
AWS Schema Conversion Tool (SCT) is one of the must tool for a successful migration of databases to AWS RDS.

If you are trying to connect to MySQL hosted on EC2 instance or RDS and unable to make the connection despite of setting the security groups correctly and making sure that port, hostname, username and password are right, then first check the log of SCT.




2019-03-22 19:40:16.866 [   1]     GENERAL INFO    global_settings:
    app_folder=AWS Schema Conversion Tool
    bucket-name=sct-cloud9-test
    console_log_folder=C:\Users\mirza\AWS Schema Conversion Tool\Log
    convert_without_statistic_reminder=false
    data_migration_settings=
    db2luw_driver_file=
    debug_mode=false
    ext_pack_version_alert=true
    facade_log_folder=C:\Users\mirza\AWS Schema Conversion Tool\Log
    file-system-thread-count=1
    forget-state=false
    greenplum_driver_file=
    install_dir=C:\Users\mirza
    log.max_file_size_mb=100
    log.max_files_count=20
    log_folder=C:\Users\mirza\AWS Schema Conversion Tool\Log
    log_folder_extractors=C:\Users\mirza\AWS Schema Conversion Tool\Extractor Log
    log_folder_extractors_ask_before_load=false
    log_folder_extractors_cassandra=C:\Users\mirza\AWS Schema Conversion Tool\Extractor Log\Cassandra
    maria_driver_file=
    message_level.CLONE_DATACENTER=INFO
    message_level.COMPARE_SCHEMA=INFO
    message_level.CONTROLLER=INFO
    message_level.GENERAL=INFO
    message_level.LOADER=INFO
    message_level.PARSER=INFO
    message_level.PRINTER=INFO
    message_level.RESOLVER=INFO
    message_level.TELEMETRY=INFO
    message_level.TRANSFORMER=INFO
    message_level.TYPEMAPPING=INFO
Caused by: com.amazon.sct.dbloader.DbLoaderHandledException: Connection wasn't established. Check connection properties.
at com.amazon.sct.dbloader.DbLoader.checkConnection(DbLoader.java:512)
at com.amazon.sct.dbloader.DbLoader.connect(DbLoader.java:349)
at com.amazon.sct.dbloader.DbLoaderContainer.checkAccessibility(DbLoaderContainer.java:25)
at com.amazon.sct.task.TestConnectionTask.call(TestConnectionTask.java:26)
at com.amazon.sct.task.TestConnectionTask.call(TestConnectionTask.java:12)
at javafx.concurrent.Task$TaskCallable.call(Task.java:1423)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
... 1 more

In this case, there are no errors about time out which commonly refers to security group configuration or any about the credential issue. Here the issue is not with SCT or any of AWS component, rather its about the user which you are using to connect to MySQL.

Create user MySQL as follows and try and it should work:

CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'mypass';
CREATE USER 'myuser'@'%' IDENTIFIED BY 'mypass';

GRANT ALL ON *.* TO 'myuser'@'localhost';
GRANT ALL ON *.* TO 'myuser'@'%';

Then first test connection using MySQL workbench and then try with SCT.

Hope it helps.

Categories: DBA Blogs

Intro: Initial Thoughts On Oracle Autonomous Database Cloud Services (Automatic For The People)

Richard Foote - Thu, 2019-03-21 22:37
I’m currently writing up a few blog pieces on indexing in the Oracle Autonomous Database environments, but I thought I’ll begin by introducing what exactly are Oracle Autonomous Database Cloud Services and some of my initial thoughts, as there’s still some confusion on all this. Introduced by Uncle Larry at Oracle OpenWorld 2017, Oracle Autonomous […]
Categories: DBA Blogs

ORA-02396: exceeded maximum idle time, please connect again

Tom Kyte - Thu, 2019-03-21 19:26
Dear Mr. Tom, i have an issue according the IDLE_TIME parameter.. I used to open more than one session to the database (different scheam's on same Database)... and our dba set IDLE_TIME as 15 minutes, and even if the session is not idle (i'm ...
Categories: DBA Blogs

truncating empty table generate ORA-02266:

Tom Kyte - Thu, 2019-03-21 19:26
Tom , why truncating empty table generates the error ORA-02266 ? SQL> select count(*) from t_data; COUNT(*) ---------- 0 SQL> truncate table t_data * ERROR at line 1: ORA-02266: unique/primary keys in t...
Categories: DBA Blogs

SQL query to find FK IDs with a series of values

Tom Kyte - Thu, 2019-03-21 19:26
we have below tables <code>create table bca(id number(2)); insert into bca(10); insert into bca(11); insert into bca(7); create TABLE abc( di NUMBER(2), fk_id NUMBER(3), yek VARCHAR2(20), elv VARCHAR(15...
Categories: DBA Blogs

Announcement: “Oracle Performance Diagnostics and Tuning” Webinar – 9-12 July 2019 !!

Richard Foote - Thu, 2019-03-21 02:30
I’m very excited to announce the first public running of my new “Oracle Performance Diagnostics and Tuning” Webinar will run between 9-12 July 2019 (6pm-10pm AEST): Webinar Series 9-12 July 2019 (start 6pm AEST, end 10pm AEST):  This is a must attend seminar aimed at Oracle professionals (both DBAs and Developers) who are interested in Performance Tuning.  […]
Categories: DBA Blogs

Date constraint to validate all bookings are in the future

Tom Kyte - Thu, 2019-03-21 01:06
Hi, I need to create a constraint which will not allow appointments to be booked in the past. I wonder if someone could help me with this please. Thank you. Juliana
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs