External Tables Partitioning in Autonomous database - Part II

This will be the continuation of the previous blog post.
 
To efficiently scan data in object store, Autonomous database (ADB) provides various optimizations for all common data types, table formats and directory structures in object storage. With the recently introduced new feature (called Implicit partitioning) for external tables, this enables query performance by enable pruning of data based on logical conditions. This new feature also make it much easier to create and manage these partitioned tables, by simply pointing the root directory for your tables (data) files. That is, we can easily create external tables across thousands of partitions and millions of files within a single command.
 
External tables with Implicit partitioning automatically discover the underlying partition structure based on the hierarchical structure of files and add these additional partition keys to external table structures.
 
Here is the structure of our object storage.
 
 
demo@ATP19C> select object_name, bytes
  2      from dbms_cloud.list_objects(
  3         credential_name => 'API_KEY_CRED',
  4        location_uri => :uri);
 
OBJECT_NAME                                              BYTES
--------------------------------------------------- ----------
Sales/                                                       0
Sales/Country=UK/                                            0
Sales/Country=UK/year=2024/                                  0
Sales/Country=UK/year=2024/data_deptno_10.parquet         2163
Sales/Country=UK/year=2024/data_deptno_20.parquet         2218
Sales/Country=US/                                            0
Sales/Country=US/year=2023/                                  0
Sales/Country=US/year=2023/data_deptno_30.parquet         2257
 
8 rows selected.
 
To emphasize this external table as Automatic Implicit partitioning, we need to create it as using DBMS_CLOUD API
 
demo@ATP19C> begin
  2     dbms_cloud.create_external_table(
  3             table_name => 'xt_implicit_hive_demo'
  4             , credential_name => 'API_KEY_CRED'
  5             , file_uri_list => :uri
  6             , format => '{"schema":"first","type":"parquet","implicit_partition_type":"hive"}');
  7  end;
  8  /
 
PL/SQL procedure successfully completed.
 
 
Partition columns are automatically detected, and implicit partitioning is enabled by setting the format option IMPLICIT_PARTITION_TYPE to HIVE.
 
To detect the partition columns ADB starts searching from the beginning of the URL path, specified by FILE_URI_LIST for "=" , when found the left part of = is treated as the column name and the right part of = as value, then the search continues for next equal sign "=" until after the first partition key column and so on.
 
Query using implicit partitioning.
 
demo@ATP19C> select * from xt_implicit_hive_demo;
 

EMPNO ENAME  JOB            MGR HIREDATE           SAL    COMM  DEPTNO COUNTRY YEAR

_____ ______ ____________ _____ ______________ _______ _______ _______ _______ _____

 7839 KING   PRESIDENT          17-NOV-1981       5000              10 UK      2024

 7782 CLARK  MANAGER       7839 09-JUN-1981       2450              10 UK      2024

 7934 MILLER CLERK         7782 23-JAN-1982       1300              10 UK      2024

 7566 JONES  MANAGER       7839 02-APR-1981       2975              20 UK      2024

 7788 SCOTT  ANALYST       7566 19-APR-1987       3000              20 UK      2024

 7902 FORD   ANALYST       7566 03-DEC-1981       3000              20 UK      2024

 7369 SMITH  CLERK         7902 17-DEC-1980        800              20 UK      2024

 7876 ADAMS  CLERK         7788 23-MAY-1987       1100              20 UK      2024

 7698 BLAKE  MANAGER       7839 01-MAY-1981       2850              30 US      2023

 7499 ALLEN  SALESMAN      7698 20-FEB-1981       1600     300      30 US      2023

 7521 WARD   SALESMAN      7698 22-FEB-1981       1250     500      30 US      2023

 7654 MARTIN SALESMAN      7698 28-SEP-1981       1250    1400      30 US      2023

 7844 TURNER SALESMAN      7698 08-SEP-1981       1500       0      30 US      2023

 7900 JAMES  CLERK         7698 03-DEC-1981        950              30 US      2023

 
demo@ATP19C> select count(*) from xt_implicit_hive_demo where country ='US' and year = '2023';
 
  COUNT(*)
----------
         6
 
By specifying predicates country ='US' and year = '2023', forces the database to read only the underlying directory that corresponds to this filter.
 
Moreover, we can add new files and directories in object storage, this new data is automatically recognized by ADB, since all partition pruning is done at run-time.
 
demo@ATP19C> select count(*) from xt_implicit_hive_demo where country ='US' and year = '2025';
 
  COUNT(*)
----------
         0
 
Once we upload a sample data for the year 2025, ADB automatically prunes partitions at runtime.
 
demo@ATP19C> select object_name, bytes
  2      from dbms_cloud.list_objects(
  3         credential_name => 'API_KEY_CRED',
  4        location_uri => :uri);
 
OBJECT_NAME                                              BYTES
--------------------------------------------------- ----------
Sales/                                                       0
Sales/Country=UK/                                            0
Sales/Country=UK/year=2024/                                  0
Sales/Country=UK/year=2024/data_deptno_10.parquet         2163
Sales/Country=UK/year=2024/data_deptno_20.parquet         2218
Sales/Country=US/                                            0
Sales/Country=US/year=2023/                                  0
Sales/Country=US/year=2023/data_deptno_30.parquet         2257
Sales/Country=US/year=2025/                                  0
Sales/Country=US/year=2025/data_deptno_40.parquet         2414
 
10 rows selected.
 
 
demo@ATP19C> select count(*) from xt_implicit_hive_demo where country ='US' and year = '2025';
 
  COUNT(*)
----------
         8
 
This shows how ADB automatically manages partitions by enhancing query performance by skipping unnecessary data segments based on predicates, if the underlying data structure of the object store changes, then there is no need to modify / Sync anything on ADB.
 
 
 
 
 

External Tables Partitioning in Autonomous database - Part I

Partitioning is a well-established technique for improving performance and manageability of the database system by diving larger objects into smaller ones, including any large data warehouse takes advantages of it. This is also true for large objects for both inside and outside the database such as data lakes in object stores. In this blogpost we will see about how Partitioning the external tables got evolved in Autonomous database.
 
Autonomous database allows the creation of partitioned external table through DBMS_CLOUD API for quite some time, this API simplifies the definition of external table structure, but it relies on you to provide a lot of table metadata, for example we have to
 

• Specify the list of all columns with data types.
• Maintain table definition. If some new data – equivalent to partitions – is added or removed from object storage, then the table must be manually adjusted using ADD / DROP partitions.
• Value of the partitioned column not in the query results

 
Lets have a look at what all of these means from a relatively small partitioned table in example
 
Got a sample of employee data organized based on department number in my object store.
 
demo-user@ATP19C> exec :uri := 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/';
 
PL/SQL procedure successfully completed.
 
demo-user@ATP19C> exec :namespace := 'idcglquusbz6';
 
PL/SQL procedure successfully completed.
 
demo-user@ATP19C> select object_name, bytes
  2  from dbms_cloud.list_objects(
  3     'my_demo_cred',
  4     :uri||:namespace||'/b/MY_DEMO_BUCKET/o/DEMO03/');
 
OBJECT_NAME                              BYTES
----------------------------------- ----------
DEPTNO=10/dept10.csv                       129
DEPTNO=20/dept20.csv                       213
DEPTNO=30/dept30.csv                       258
 
Let’s create a partitioned external table for each department using the traditional DBMS_CLOUD Interface.
 
demo-user@ATP19C> begin
  2    dbms_cloud.create_external_part_table(
  3               table_name => 'emp_demo',
  4               credential_name => 'my_demo_cred',
  5               file_uri_list => 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/idcglquusbz6/b/MY_DEMO_BUCKET/o/DEMO03/*',
  6               column_list => 'empno number,ename varchar2(12),sal number,hiredate date,DEPTNO number',
  7               field_list =>  'empno,ename,sal,hiredate date mask "dd-mon-yyyy hh12:mi:ss am",DEPTNO',
  8               format => json_object( 'type' value 'csv' ,
  9                     'partition_columns' value json_array( json_object('name' value 'DEPTNO', 'type' value 'number') )
 10                        ) );
 11  end;
 12  /
 
PL/SQL procedure successfully completed.
 
Now using this single external table, we can query data files in the object storage and represented as multiple logical partitions.
 
demo-user@ATP19C> set autotrace traceonly exp
demo-user@ATP19C> select * from emp_demo where deptno = 10;
 
Execution Plan
----------------------------------------------------------
Plan hash value: 636994847
 
----------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |          |    82 |  4592 |     8   (0)| 00:00:01 |       |       |
|   1 |  PX COORDINATOR               |          |       |       |            |          |       |       |
|   2 |   PX SEND QC (RANDOM)         | :TQ10000 |    82 |  4592 |     8   (0)| 00:00:01 |       |       |
|   3 |    PX BLOCK ITERATOR          |          |    82 |  4592 |     8   (0)| 00:00:01 |     1 |     1 |
|   4 |     EXTERNAL TABLE ACCESS FULL| EMP_DEMO |    82 |  4592 |     8   (0)| 00:00:01 |     1 |     1 |
----------------------------------------------------------------------------------------------------------
 
It looks like the predicate was used by the database and pruned down to only the necessary partitions to satisfy the query.
 
Looking into the table metadata it shows the files in the object storage are mapped to individual partitions in the table.
 
demo-user@ATP19C> select dbms_metadata.get_ddl('TABLE','EMP_DEMO') from dual;
 
DBMS_METADATA.GET_DDL('TABLE','EMP_DEMO')
--------------------------------------------------------------------------------
 
  CREATE TABLE "DEMO_USER"."EMP_DEMO"
   (    "EMPNO" NUMBER,
        "ENAME" VARCHAR2(12) COLLATE "USING_NLS_COMP",
        "SAL" NUMBER,
        "HIREDATE" DATE,
        "DEPTNO" NUMBER
   )  DEFAULT COLLATION "USING_NLS_COMP"
   ORGANIZATION EXTERNAL
    ( TYPE ORACLE_LOADER
      DEFAULT DIRECTORY "DATA_PUMP_DIR"
      ACCESS PARAMETERS
      ( RECORDS DELIMITED BY DETECTED NEWLINE NOLOGFILE NOBADFILE NODISCARDFILE
         READSIZE=10000000 CREDENTIAL 'my_demo_cred'
filename_columns="['DEPTNO']"
file_uri_list="https://objectstorage.us-ashburn-1.oraclecloud.com/n/idcglquusbz6/b/MY_DEMO_BUCKET/o/DEMO03/*"
credential_schema="DEMO_USER"
credential_name="MY_DEMO_CRED"
    FIELDS CSV WITHOUT EMBEDDED NOTRIM ( empno,ename,sal,hiredate date mask "dd-mon-yyyy hh12:mi:ss am",DEPTNO )
  )
    )
   REJECT LIMIT 0
  PARTITION BY LIST ("DEPTNO")
 (PARTITION "P1"  VALUES ((10))
      LOCATION
       ( 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/idcglquusbz6/b/MY_DEMO_BUCKET/o/DEMO03/DEPTNO=10/*' ),
 PARTITION "P2"  VALUES ((20))
      LOCATION
       ( 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/idcglquusbz6/b/MY_DEMO_BUCKET/o/DEMO03/DEPTNO=20/*' ),
 PARTITION "P3"  VALUES ((30))
      LOCATION
       ( 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/idcglquusbz6/b/MY_DEMO_BUCKET/o/DEMO03/DEPTNO=30/*' ))
  PARALLEL
 
So what happens in case if we add / remove files from the object storage buckets supporting these partitions , to cope with that situation we do have to manually adjust the external table definition, but simply use another new interface in DBMS_CLOUD to do the job
 
Say we got new file for deptno = 40 in the object storage bucket
 
demo-user@ATP19C> select object_name, bytes
  2  from dbms_cloud.list_objects(
  3     'my_demo_cred',
  4     :uri||:namespace||'/b/MY_DEMO_BUCKET/o/DEMO03/');
 
OBJECT_NAME                              BYTES
----------------------------------- ----------
DEPTNO=10/dept10.csv                       129
DEPTNO=20/dept20.csv                       213
DEPTNO=30/dept30.csv                       258
DEPTNO=40/dept_40.csv                       83
 
And our existing external table got reference to three partitions each got reference to indivjual files in object storage.
 
demo-user@ATP19C> select deptno,count(*)
  2  from emp_demo
  3  group by deptno;
 
    DEPTNO   COUNT(*)
---------- ----------
        30          6
        10          3
        20          5
 
Now the call to SYNC the external table will be like this
 
demo-user@ATP19C> exec dbms_cloud.sync_external_part_table(table_name =>'EMP_DEMO');
 
PL/SQL procedure successfully completed.
 
And post that the query returns for all the departments available in the object storage.
 
demo-user@ATP19C> select deptno,count(*)
  2  from emp_demo
  3  group by deptno;
 
    DEPTNO   COUNT(*)
---------- ----------
        40          2
        30          6
        10          3
        20          5
 
Looking into the table metadata it shows the new files got mapped to additional partitions in the table.
 
demo-user@ATP19C> select dbms_metadata.get_ddl('TABLE','EMP_DEMO') from dual;
 
DBMS_METADATA.GET_DDL('TABLE','EMP_DEMO')
-----------------   ---------------------------------------------------------------
 
  CREATE TABLE "DEMO_USER"."EMP_DEMO"
   (    "EMPNO" NUMBER,
              ....
              ....
        "DEPTNO" NUMBER
   )  DEFAULT COLLATION "USING_NLS_COMP"
   ORGANIZATION EXTERNAL
    ( TYPE ORACLE_LOADER
      DEFAULT DIRECTORY "DATA_PUMP_DIR"
      ACCESS PARAMETERS
      ( RECORDS DELIMITED BY DETECTED NEWLINE NOLOGFILE NOBADFILE NODISCARDFILE
              ....
              ....  )
    )
   REJECT LIMIT 0
  PARTITION BY LIST ("DEPTNO")
 (PARTITION "P1"  VALUES (10)
              ....
              ....
 PARTITION "P4"  VALUES (40)
      LOCATION
       ( 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/idcglquusbz6/b/MY_DEMO_BUCKET/o/DEMO03/DEPTNO=40/*'
       ))
  PARALLEL
 
 
In the next blogpost we will see about how this Sync process got improved in the recent releases.

Using database links with ADB-S without wallet

In the previous post, we saw about how to setup a database link connecting two different ADB-S accessible over the public endpoint using wallet in place. In this blogpost we will see about how to establish database link connecting two different ADB-S accessible over the public endpoint without wallet in place.
 
On the target ADB details page, under Network, click Edit in the Mutual TLS (mTLS) authentication field & change the value to allow TLS Authentication by deselecting Require mutual TLS (mTLS) authentication and click update, the ADB lifecycle state changes to updating and post that mutual TLS (mTLS) authentication field changes to show Not required. 

 

To create database link to a public target, the target database must be accessible, some database including ADB limit access (using ACL), so make sure to enable target database to allow access from source database using database link, if we limit access with ACL, then make sure to find the outbound IP address of source database and allow that IP address to connect to your target database. By adding outbound IP address of source database to ACL of the target database.
 
On the source database
 
demo-user@ATP19C> select jt.*
  2  from v$pdbs, json_table( cloud_identity,'$.OUTBOUND_IP_ADDRESS[*]'
  3      columns( outbound_ips varchar2(20) path '$') ) jt;
 
OUTBOUND_IPS
----------------
150.136.133.92
 
On the target Autonomous database 
 

 
Then create credentials to access the target database, the username and the password for the dbms_cloud.create_credentials are the credentials to the target database.
 
demo-user@ATP19C> begin
  2     dbms_cloud.create_credential(
  3             credential_name =>'DB_LINK_CRED',
  4             username =>'DEMO_USER',
  5             password=>'Good2go1!1234' );
  6  end;
  7  /
 
PL/SQL procedure successfully completed.
 
Then create the database link to the target database using DBMS_CLOUD_ADMIN package, like this
 
demo-user@ATP19C> begin
  2     dbms_cloud_admin.create_database_link(
  3             db_link_name => 'DB_LINK_TEST',
  4             hostname => 'adb.us-ashburn-1.oraclecloud.com',
  5             port => 1522,
  6             service_name => 'g26be7c92912cdb_atp21c_low.adb.oraclecloud.com',
  7             credential_name =>'DB_LINK_CRED',
  8             directory_name => null );
  9  end;
 10  /
 
PL/SQL procedure successfully completed.
 
demo-user@ATP19C> select * from dual@DB_LINK_TEST;
 
D
-
X
 
To create a database link with DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK to a target Autonomous Database on a public endpoint using a secure TCP connection without a wallet, the directory_name parameter must be NULL.
 
demo-user@ATP19C> select banner_full from v$version;
 
BANNER_FULL
------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.23.0.1.0
 
 
demo-user@ATP19C> select banner_full from v$version@db_link_test;
 
BANNER_FULL
------------------------------------------------------------------------
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0

Using database links with ADB-S with wallet

Autonomous database serverless (ADB-S) users have an option to deploy their instance on either public or private endpoints, whether your connections are made over the pubic internet or through the Virtual client network (VCN), there is one thing in common, they are all secure and uses the Transport layer security (TLS1.2) protocol, so any connection between the client and database is encrypted and both the client and database can authenticate each other. When it comes to authenticating the client and server, there are couple of options.
 

• Both client and server authenticate each other (mutual TLS)
• Only the client authenticates the server (one-way TLS)

 
ADB-S uses the mutual TLS by default regardless of network configuration, so both the client and database can verify each other certificates. To complete server side authentication, any client connecting to an ADB-S instance must present their client credentials which can be downloaded as a zip file and contains SSO wallet, keystore, truststore and other network config files, this pretty much sums up how mTLS works and why you need to download a wallet to connect to autonomous database.
 
In this blogpost, we will see about how to create a database link from an ADB-S (source Autonomous Transaction processing 19c) to publicly accessible another ADB-S (Autonomous JSON database 21c)  with a wallet (mTLS)
 
Copy the Target database wallet, cwallet.sso containing the certificates for target database to an object storage bucket.
 
demo-user@ATP19C> variable uri varchar2(200)
demo-user@ATP19C> exec :uri := 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/idcglquusbz6/b/MY_DEMO_BUCKET/o/DBLINK_TEST/';
 
PL/SQL procedure successfully completed.
 
demo-user@ATP19C> select object_name
  2  from dbms_cloud.list_objects('my_demo_cred',:uri);
 

no rows selected

demo-user@ATP19C>

 
use dbms_cloud.get_object to upload the target database wallet into a directory created / available  on the source database.
 
demo-user@ATP19C> begin
  2     dbms_cloud.get_object(
  3             credential_name =>'my_demo_cred',
  4             object_uri => 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/idcglquusbz6/b/MY_DEMO_BUCKET/o/DBLINK_TEST/cwallet.sso',
  5             directory_name => 'DATA_PUMP_DIR' );
  6  end;
  7  /
 
PL/SQL procedure successfully completed.
 
demo-user@ATP19C> select object_name
  2  from table( dbms_cloud.list_files('DATA_PUMP_DIR') )
  3  order by created desc
  4  fetch first 1 row only;
 
OBJECT_NAME
------------------------------
cwallet.sso
 
 
on the ADB-S instance create credentials to access the target database, the username and the password for the dbms_cloud.create_credentials are the credentials to the target database.
 
demo-user@ATP19C> begin
 2    dbms_cloud.create_credential(
 3            credential_name => 'target_db_cred',
 4            username => 'demo_user',
 5            password => 'Good2go1!1234' );
 6 end;
 7 /
 
PL/SQL procedure successfully completed.
 
Then create the database link to the target database using DBMS_CLOUD_ADMIN package, like this
 
demo-user@ATP19C> begin
 2    dbms_cloud_admin.create_database_link(
 3            db_link_name=>'target_db_link',
 4            hostname => 'adb.us-ashburn-1.oraclecloud.com',
 5            port => 1522,
 6            service_name => 'g26be7c92912cdb_ajd21c_low.adb.oraclecloud.com',
 7            credential_name => 'target_db_cred',
 8            directory_name => 'DATA_PUMP_DIR' );
 9 end;
 10 /
 
PL/SQL procedure successfully completed.
 
Then when we try to access the data on target database using database link, it fails like this
 
demo-user@ATP19C> select * from dual@target_db_link;
select * from dual@target_db_link
                  *
ERROR at line 1:
ORA-01017: invalid username/password; logon denied
ORA-02063: preceding line from TARGET_DB_LINK
 
But the real problem is not due to the Incorrect password, instead it was due to USERNAME listed in lowercase, instead it should be in upper case.
 
demo-user@ATP19C> begin
  2     dbms_cloud.create_credential(
  3             credential_name => 'target_db_cred',
  4             username => 'DEMO_USER',
  5             password => 'Good2go1!1234' );
  6  end;
  7  /
 
PL/SQL procedure successfully completed. 

Once that was fixed, the database link works perfect.
 
demo-user@ATP19C> select host from dba_db_links where db_link = 'TARGET_DB_LINK';
 
HOST
-----------------------------------------------------------------------------------------------
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST="adb.us-ashburn-1.oraclecloud.com")(PORT=1522))
(CONNECT_DATA=(SERVICE_NAME=g26be7c92912cdb_ajd21c_low.adb.oraclecloud.com))
(SECURITY=(MY_WALLET_DIRECTORY="/u03/dbfs/E47379BFF4313E4EE0539118000A6636/data/dpdump")
(SSL_SERVER_DN_MATCH=TRUE)))
 
demo-user@ATP19C>
demo-user@ATP19C> select banner_full from v$version;
 
BANNER_FULL
-------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.23.0.1.0
 
 
demo-user@ATP19C> select banner_full from v$version@target_db_link ;
 
BANNER_FULL
-------------------------------------------------------------------------
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0