Being an Oracle Professional, I like to share all my Real Life Performance Tuning Challenges and Experiences. The Content and Views on this site are my own and not necessarily those of Oracle. While, I write on my real life experiences, the resolutions mentioned are solely mine. Comments / Criticisms are always a welcome.
I want to refresh my UAT Database from an existing Production Data.
I want to move an Oracle Database to an Autonomous AI Database. This Oracle Database could be anything – an OnPremise or on any Cloud.
I want to move a Non-Oracle Database to an Autonomous AI Database
For 1 above, Autonomous AI Database provides Database Cloning option. It is just few clicks and the job is done. For 2, there are multiple methods and all of these are documented in Autonomous AI Database Documentation. Each of these methods are based on the migration scenario, database size etc. Few months back, Oracle also released a much awaited Tablespace migration option. For 3, there are multiple migration tools available that takes the data from Non-Oracle Sources, converts the data into Oracle format and then loads. All well…
But, what if the scenario is changed a bit i.e.What if, I have to refresh only a subset of Data from Production? Say, my Production is 50 TB and I just need few schema’s that are around 5 TB in size? Cloning an entire 50 TB doesn’t make sense.
One of my customers, a SaaS provider, with 20 to 30 Schema’s, had a requirement to refresh only few schema’s. They were testing few application changes and wanted to test these changes only for these schema’s. Cloning was not a solution here, as they wanted to avoid overriding other schema’s. A new clone and then dropping other schema’s means additional cost, time and efforts. The option for them was to – Take an EXPDP of these Schema’s on to Object Storage and then Import it on UAT. Manual effort and continuous monitoring.
How about a simplified way of achieving this, with an option to stop and resume from where it left? This blog is about a feature released few days back that achieves this, with just few steps. No manual efforts required. No continuous monitoring required and top of it, resilient to failures. What else do we need? Click here for the Documentation Link to know more about this feature.
In this blog, I will walk you through the steps that achieve this with just few steps. Underneath, it uses DBMS_PARALLEL_EXECUTE API to distribute an object into multiple chunks, and process these chunks individually in parallel sessions simulating parallelism. In the past, I have written many blogs about Manual Parallelism using DBMS_PARALLEL_EXECUTE. I call it manual parallelism, as I have a control on the number of threads, I can increase or reduce the number of threads at run-time and I also have an option to just process a failed thread instead of processing an entire object again. Just search on dbms_parallel_execute or manual parallelism on this blog search and you would find much of my challenging tasks accomplished using this API.
With Autonomous AI Database, you just need to run few steps and internally, all the steps needed to create multiple chunks, scheduling these are done automatically for you. So, lets get into the steps.
In this case, I want to move a Schema called MAKROOCT25 from one of my Autonomous AI Database to another Autonomous Database. The schema is in ATPAI Database (Source) and is to be moved to ATPMAY2026 Database (Target).
The steps are :
On my Target, I created a Directory called ATPAI_WALLET. I will download cwallet.sso file of ATPAI Database into this directory.
Then, created a credential that stores the Username and Password of a privileged user from my Source. My process (on Target) will connect to this User via Database Link, to process the chunks.
Finally, we then run an API DBMS_CLOUD_IMPORT and pass the relevant parameters. Internally, this API will create a Database Link for us, connect to specified service, read the remote object(s) and then load the data into our Target.
From Oracle to Oracle, the process will create the required Indexes and Constraints as well.
create directory ATPAI_WALLET as ‘ATPAI_WALLET_DIR’;
begin dbms_cloud.get_object( credential_name =>’VIVEK_OBJ_CRED’, object_uri =>'<Object_Storage_URL>/cwallet.sso’, directory_name=>’ATPAI_WALLET’); end; /
HostName and ServiceName can be extracted from OCI Console (Under Connections Tab for the Source Database) or from TNSNAMES.ORA.
Once this block DBMS_CLOUD_IMPORT is executed, the API will create a database link MAKROOCT25_4M_ATPAI_DB_LINK, connect to the Username that is provided as a part of DBMS_CLOUD.CREATE_CREDENTIAL and then will schedule an Import Job on Target.
What I have observed is, even if I specify HIGH or MEDIUM Service, the data is read using LOW Service Name and this makes sense. DBMS_PARALLEL_EXECUTE itself creates multiple chunks and processes these chunks using parallel threads. This means, a table is read and loaded by these parallel threads. For each parallel thread, a database link network channel is opened. More the number of CPUs, more the number of parallel threads.
You can monitor the progress from DBA_DATA_IMPORT_TASK_STATUS. Sample output is attached.
In case the job fails or doesn’t start, you can also validate whether the Database Link that was created implicitly by the API is working as expected. For this, you can run the following query. The DBLink is created as <TASKNAME>_DB_LINK.
select sysdate from dual@MAKROOCT25_4M_ATPAI_DB_LINK;
If the job fails in-between, you can check it from
SELECT CHUNK_ID, TASK_OWNER, TASK_NAME, STATUS, START_ROWID, END_ROWID, ERROR_MESSAGE FROM DBA_PARALLEL_EXECUTE_CHUNKS;
The best part of this approach is, you can suspend the job and resume it later. For example, if you are moving some large data and it coincides with some critical batch job, you can suspend it temporarily and resume it later. The job will resume from where it left. It is also resilient to failures, meaning, by any chance if the instance crashes or is terminated, the failed job can be restarted and it will load the data from the point it failed.
I don’t want to make this blog too long, hence would conclude here. The idea here was to post about this brilliant feature and the underneath logic that it uses. Is it restricted to only Autonomous AI Database? Answer is NO. You can very well use this logic on Non-Autonomous Databases as well. I wrote about DBMS_PARALLEL_EXECUTE in the past. You can use it for processing large amount of data, in a controlled, efficient manner and with same resiliency.
As a final note, please note that this feature does more than why I posted. You can Migrate/Move data from Non-Oracle Databases to Autonomous AI Databases as well. Autonomous Database will do all the Data-type conversion. You do not need any migration tool to accomplish this. At the time of writing this blog, this feature supports MySQL, PostgreSQL and Amazon Redshift Databases. I am sure, more Non-Oracle Databases would be added to this list. Read the Documentation for more information about this feature.
This is a personal blog. The thoughts and opinions expressed here are entirely my own and do not necessarily represent those of my employer, team, or any organization associated with me. Any code, scripts, or technical examples are shared for learning purposes and should be used with appropriate caution.
It’s been two years since I last blogged. Staying connected with the community is important to me, and blogging helps me stay in touch with my readers.
Over these two years, I’ve had the opportunity to learn several new technologies, and I’d now like to start writing about those learnings as well.
So, it’s time to restart… see you soon with new blogs and new learnings.
Imagine, we communicate with our Database in Business Language and Database responding with a result that matches the Business criteria. Natural Language Processing (NLP) a branch of Artificial Intelligence has this capability of generating a matching result based on the question asked.
SELECT AI, released early this year on Autonomous Database, allow business users to interact with the database and then convert it into Oracle SQL. Under the hood, it leverages generative AI with Large Language Models (LLM) to covert the user input into SQL.
In this blog, I will not write much about what it is, how to configure it and so on…I would request reading Oracle Autonomous Database Documentation that goes through these details (like, Creating Credentials, Creating Profiles for various AI providers etc.) and is regularly updated.
In this blog, I will walk you through few Interesting examples or analysis on Indian Premier League (IPL). For my Indian Readers, IPL is not something that will need an introduction. Most of the Indians are Cricket Fans. In fact, all Cricket playing nation may know about IPL. If not, then a brief introduction is here.
Before I get into some analysis, I thought of sharing something important i.e.How is SELECT AI able to generate a meaningful query, especially if the column names are a bit cryptic ? Remember, LLM’s are models that we need to train and to train these models, we need to provide it all the relevant details. Let us go through two examples.
In this case, I have a Table TEST_VIVEK with two columns X and Y. X has the Customer Names and Y contains Company Names.
CREATE TABLE TEST_VIVEK ( X VARCHAR2(32), Y VARCHAR2(64));
INSERT INTO TEST_VIVEK VALUES(‘VIVEK’,’ABC CORPORATION’); INSERT INTO TEST_VIVEK VALUES(‘TOM’,’XYZ INC.’); INSERT INTO TEST_VIVEK VALUES(‘DICK’,’LLM PRIVATE LIMITED’); INSERT INTO TEST_VIVEK VALUES(‘HARRY’,’AI INDIA’); COMMIT;
Now, How do I provide meaningful names to these columns (X & Y)? One way is to create a View.
CREATE OR REPLACE VIEW TEST_VIVEK_VW AS SELECT X CUSTOMER, Y COMPANY FROM TEST_VIVEK;
While creating a profile, I provide TEST_VIVEK_VW as the Object to be used. Let us ask few questions (using SQL Developer).
And the Output
The other method is using an in-built database feature COMMENTS. These basically allows you to provide meaningful description and notes about TABLE(S) and COLUMNS. Marty Gubar has a nice blog with an explanation on how to make use of COMMENTS to ensure LLM’s come-up with right query. See this blog.
Time to get into some Analysis on Indian Premier League. I downloaded the data from one of the cricket websites. The files were in CSV format. I loaded it into my ADB using Data Transform. There are two tables IPL_MATCHES and IPL_DETAILS. IPL_MATCHES is a kind of Primary table with 1 Row per match and IPL_DETAILS is a Child Table with Ball-by-Ball Data. Both these tables has a column MATCH_ID. I created a Primary Key on this column on IPL_MATCHES Table and then a Foreign Key on IPL_DETAILS.
The COMMENTS for both the tables and all the relevant columns:
comment on table IPL_MATCHES is ‘contains IPL Season, City, date, player of the match, stadium, winner, target runs, target overs, match result’; comment on column IPL_MATCHES.MATCH_ID is ‘Match ID. Join this column to other Tables’; comment on column IPL_MATCHES.IPL_SEASON is ‘IPL Season. Each IPL season will have multiple matches’; comment on column IPL_MATCHES.CITY is ‘City where the IPL match was played’; comment on column IPL_MATCHES.MATCH_DATE is ‘Date when the IPL Match was played’; comment on column IPL_MATCHES.MATCH_TYPE is ‘Type of the IPL Match’; comment on column IPL_MATCHES.PLAYER_OF_THE_MATCH is ‘Name of the Player who won the player of the Match award’; comment on column IPL_MATCHES.VENUE is ‘Stadium where the IPL match was played’; comment on column IPL_MATCHES.TEAM1 is ‘Team 1 playing the ipl match’; comment on column IPL_MATCHES.TEAM2 is ‘Team 2 playing the ipl match’; comment on column IPL_MATCHES.TOSS_WINNER is ‘Team that won the Toss’; comment on column IPL_MATCHES.TOSS_DECISION is ‘Decision taken by the Team winning the toss’; comment on column IPL_MATCHES.WINNER is ‘Winner of the IPL Match’; comment on column IPL_MATCHES.RESULT is ‘Result of the IPL Match’; comment on column IPL_MATCHES.RESULT_MARGIN is ‘Margin by which a team won the match’; comment on column IPL_MATCHES.TARGET_RUNS is ‘Target Runs or Runs required to win’; comment on column IPL_MATCHES.TARGET_OVERS is ‘Target Overs’; comment on column IPL_MATCHES.SUPER_OVER is ‘super over’; comment on column IPL_MATCHES.METHOD is ‘Win Type’; comment on column IPL_MATCHES.UMPIRE1 is ‘Umpire 1’; comment on column IPL_MATCHES.UMPIRE2 is ‘Umpire 2’;
comment on table IPL_DETAILS is ‘Contains ball by ball Detail of all the ipl matches, Batsman Name, Bowler Name, runs scored, player dismissed’; comment on column IPL_DETAILS.MATCH_ID is ‘Match ID. Join this column to other Tables’; comment on column IPL_DETAILS.INNING is ‘Inning Number’; comment on column IPL_DETAILS.BATTING_TEAM is ‘Team Batting’; comment on column IPL_DETAILS.BOWLING_TEAM is ‘Team Bowling’; comment on column IPL_DETAILS.OVER_NUMBER is ‘Over Number’; comment on column IPL_DETAILS.BALL_NUMBER is ‘Ball Number’; comment on column IPL_DETAILS.BATTER is ‘Batsman’; comment on column IPL_DETAILS.BOWLER is ‘Bowler’; comment on column IPL_DETAILS.NON_STRIKER is ‘Non Striker’; comment on column IPL_DETAILS.RUNS_OFF_BAT is ‘Runs scored’; comment on column IPL_DETAILS.EXTRA_RUNS is ‘Extra Runs’; comment on column IPL_DETAILS.TOTAL_RUNS is ‘Total Runs on a Ball’; comment on column IPL_DETAILS.EXTRAS_TYPE is ‘Extras’; comment on column IPL_DETAILS.IS_WICKET is ‘Wicket Taken. 1 is Yes and 0 is No’; comment on column IPL_DETAILS.PLAYER_DISMISSED is ‘Batsman Dismissed, Player Dismissed’; comment on column IPL_DETAILS.DISMISSAL_KIND is ‘Dismissal Type’; comment on column IPL_DETAILS.FIELDER is ‘Fielder’;
Object List in the CREATE_PROFILE API has the two Tables IPL_MATCHES and IPL_DETAILS. One additional parameter that I have is COMMENTS:TRUE. Once done, we can go through some interesting analysis.
begin dbms_cloud_ai.set_profile( profile_name => ‘VIVEK_AI_PROFILE’ ); end; /
Indian Premier League started in 2008 and the latest League concluded on 26th May 2024.
First Ever Match was played on 18th April 2008. Latest Season of IPL was 2024 and the final match was played between Kolkata Night Riders and Rajasthan Royals on 26th May 2024.
Next, let us get the count of matches played in each of the IPL Season’s. This is typically a count of matches group by Season.
Number of matches in 2011, 2012 and 2013 were more than the other previous seasons. Then these count dropped before going up in 2022. Any specific reasons for this? Let’s see.
We can now see that the number of Teams in 2011, 2012 and 2013 were more than previous seasons. The format of this league is that each team has to play against each other. Hence, as a new team is added to the league, the number of matches go up. In 2014, number of teams reduced back to 8. Season 2022 onwards, we again see an increase in this number.
Next, let’s see the top 5 Batsman with most runs. This will be computed from IPL_DETAILS table as the data here is ball by ball. This means, we have to take a sum of run scored by a batsman on each ball. Now. if you again scroll up and see the column names in IPL_DETAILS, we do not have a column called BATSMAN, nor we have a column called Runs Scored. We do have BATTER and RUNS_OFF_BAT. We passed the relevant information via COMMENTS.
The Query SELECT AI came up with:
As per the analysis, Virat Kohli (V Kohli) is the top batsman with highest number of runs. Next, let’s check top 5 Bowler’s having dominated and dismissed Virat Kohli the most.
Seasonwise IPL Winners and Player of the Final Match.
Let’s ask a bit complex question. I want to calculate the Batting Strike Rate. This is basically computed as the Sum of Runs Scored divided by the Number of Balls faced. The numbers are per 100 ball i.e.if the strike rate is 150, this means, at an average a Batsman Scores 150 runs in 100 Balls. Now, the way our data is stored, we have Runs_Off_Bat and Ball_Number column. This means, I will have to take a sum of Runs_off_Bat, divide it by Count of Ball_Number and this value will then have to be multiplied by 100. To be honest, I had to play around with this question to get it right and after 3 or 4 attempts, I was able to get it.
In a game of Cricket, Strike Rate doesn’t change every few minutes. It only changes during or after a match. Therefore, the next task that I would want to try is creating a Materialized View with a pre-computed Strike Rate, adding it to the object list and then running a simple query to get the Strike Rate. Once I am done, will post the results as an update to this blog. We can play around with this data and perform some more analysis. I actually did many more, but to make this blog short thought of publishing only few of them. I actually wanted to demonstrate the SELECT AI capabilities and how easily it was able to get us the required details. Hope you enjoy this.
Happy to announce that I will be traveling across 6 cities in India speaking on my favorite topic around Database and Application Performance. This event is organized by “All India Oracle User Group” and is scheduled from 20th July 2024. Prominent Speakers from across the globe will be traveling as well. A great opportunity to Learn and to build a Network. For registration, click on the following link.
Autonomous Database provides an option of Scaling UP and DOWN of CPU & Storage. Scaling UP/DOWN of CPU resources is a common practice that most of the customers implement. Take for example, throughout the day load on the system is higher and during night time, only few users extract some information. Scaling DOWN during night time will save on CPU Cost. Similarly, a Weekly/Monthly/Quarterly Load may need additional CPU’s. While this can be done manually from the Console, many of our customers requested a scheduler based approach to achieve this. I thought of writing about this topic.
For this, I will be using OCI Resource Principal, a very simple, secure and easier way of accessing OCI resources and services from an Autonomous Database. You don’t need to setup an authentication token and register that token inside ADB. For OCI Resource Principal, we just need to create Policies and Dynamic Groups. Policies are just like Privileges and Dynamic Groups are like Database Roles. You create a policy and a role, attach a policy to a role and then, this role (dynamic group) can be assigned to a single resource or group of resources.
Following figure shows a typical flow:
So, let’s start implementing this.
I have an Autonomous Database, which is in VivekCompartment. I will implement this for Scaling UP and DOWN based on a schedule.
So, as a first step I will create a Dynamic Group called VivekSharmaDG. This is available under “Identity & Security”. A Dynamic Group requires a Rule that specifies list of resource(s) to be included in this Group. You can have an Individual Resource, like a Single ADB Instance, or group of ADB’s within a compartment.
In my case, I will create a rule that says – Include all Autonomous Databases within a Compartment OCID. This Compartment OCID is of VivekCompartment. This is the compartment where all my ADB’s are created.
All {resource.type=’autonomousdatabase’, resource.compartment.id = ‘<compartment_ocid>’}
Once the Dynamic Group is created, we need to create a Policy. This is again under “Identity & Security”. This policy will have a statement that allows the Dynamic Group to access the OCI Resources. The relevant policy statement will be :
Allow dynamic-group VivekSharmaDG to use autonomous-database-family in compartment VivekCompartment
This means, a single policy will be applied to the Dynamic Group. As the name implies, Dynamic Group is dynamic in nature. You change the rule, to add or remove the resources and the policy will be applied to those resources automatically.
Once done, we need to enable Resource Principal to Access OCI Resources and following procedure in my ADB.
CREATE OR REPLACE PROCEDURE change_ocpu_count( ocpu_count IN NUMBER )
IS
autonomous_database_details dbms_cloud_oci_database_update_autonomous_database_details_t;
response_body dbms_cloud_oci_database_autonomous_database_t;
response dbms_cloud_oci_db_database_update_autonomous_database_response_t;
adb_ocid VARCHAR2(200);
adb_region VARCHAR2(100);
BEGIN
SELECT json_value(cloud_identity, '$.REGION'), LOWER(json_value(cloud_identity, '$.DATABASE_OCID'))
INTO adb_region, adb_ocid FROM v$pdbs;
DBMS_OUTPUT.PUT_LINE('ADB OCID:'||adb_ocid);
DBMS_OUTPUT.PUT_LINE('ADB Region:'||adb_region);
DBMS_OUTPUT.PUT_LINE('New OCPU count:'||ocpu_count);
autonomous_database_details := dbms_cloud_oci_database_update_autonomous_database_details_t();
autonomous_database_details.cpu_core_count := ocpu_count;
response := DBMS_CLOUD_OCI_DB_DATABASE.UPDATE_AUTONOMOUS_DATABASE (
autonomous_database_id => adb_ocid,
update_autonomous_database_details => autonomous_database_details,
region => adb_region,
credential_name => 'OCI$RESOURCE_PRINCIPAL'
);
END;
/
Once this is done, you can test this by running the procedure from SQL*Plus and passing the New OCPU Count as an Input to the procedure. If the configuration is set right, the procedure should work absolutely fine. You can now schedule this procedure using DBMS_SCHEDULER to Scale DOWN/UP the CPU resources.
For further details, you may want to read Oracle Documentation link here..
My session on “Database Performance Analytics using Python”
Last 2 years, we all connected over the Virtual World. It was a great experience. However, when it comes to connecting with the speaker and even for the speaker to connect with the participants, virtual world can’t replace physical, in-person sessions. It gives us an opportunity to collaborate with each other and enhance our social world. The good news here is – All India Oracle User Group is back with physical and in-person events, with Oracle Community Yatra 2022. This is scheduled across 6 cities in India, starting 23rd July 2022. I am speaking as well and to know about my session, please continue reading…
DBA’s – Don’t Worry about your future. With Databases on Cloud, and especially with Autonomous Database gaining much importance, many DBA’s fear that their value would come down as most of their tasks would be automated. This is true, if you are still stuck in performing regular and mundane jobs. Why not use your existing technical skillset and move to a next level? You had been responsible for maintaining health of your Application and Database. Let’s reuse the same skill and knowledge, add some spice to it and elevate from a Performance Analyst to a Performance Analytics field. In this session, I will walk you through some important concepts of Database Performance and then take you to the field of Analytics. During the session, I will also speak about some real life scenario’s and how this Analytics helps us in providing a global view of the entire database performance. It will be a collaboration between my performance skills and python. See you soon.
Autonomous Database was released around March 2018. The first was a Data Warehouse Implementation that supports business Intelligence and Analytical Workload. It was Autonomous Data Warehouse. Next, the transaction workload was added to this family, called as Autonomous Transaction Processing followed by Autonomous JSON and Autonomous APEX.
One of the coolest feature of Autonomous Data Warehouse (ADW) is easy integration with Data Lake, which means, while we can store our data natively within our Autonomous Database – for faster processing, this feature allows us to store and access our data from an external storage. On Cloud, when we talk about external storage, we usually refer to an Object Storage. This is by way of creating an External Table. In this, the Table Metadata is stored in the Data Dictionary and the actual data is stored externally. This allows us to save on Storage cost. Refer to Oracle Documentation for more details on this feature.
External Table can be Non-Partitioned, Partitioned and Hybrid Partitioned. As with Oracle Partitioning that improves Query performance by pruning the partitions that are not needed for our Query. This same improvement applies to External Tables as well. With Partitioned External Table, you get the benefit of Partition Pruning thus scanning only required external data. Remember, Queries on External Data will not be as fast as queries on database tables. However, if I have an Archived Table that is in-frequently accessed and is not performance sensitive, I can always move these to External Storage. And, to improve the query performance on External Storage, External Partitioning is highly recommended. As mentioned earlier, the other benefit of this feature is COST. Hybrid Partitioning is basically mix of both i.e. I can have few historical partitions on an External Storage and latest performance sensitive data within the database.
In this blog, I will talk about External Partition Tables, and subsequent blogs would take this topic further.
Assuming you have a large table with around 5 year of Data. Users frequently query latest data. The table is currently non-partitioned. As the data is growing, I want to ensure consistent performance. How do I do that?
There are multiple strategies, like :
Archive the Data to a New Table and Purge it from the Original Table. Reports requiring latest data would run on the Original Table and Reports on the Historical Data would access the Archived Table.
Partition the Table and leverage the benefits of Partition Pruning. This is the most common strategy.
My topic for today is on Strategy#1 i.e. Archiving to a New Table. This solution can also be applied if a table has only historical data. Anyways, many customers do implement strategy#1 and move the table to some other database, called as Archival Database. With Autonomous Database, We don’t need to do that and we will see this in action. My subsequent blogs will cover both the strategies that can be optimized further on Autonomous Databases.
For this demonstration, I will use LINEORDER table which is under SSB (Sample Star Benchmark) Schema. This schema is created by-default when you provision an Autonomous Database. It’s a huge table. Therefore, I will create another table as Sample 2% and will use it as my Source table. The very first step would be to identify the partition strategy. In this case, monthly partition on LO_ORDERDATE column is a first choice. However, the other requirement is to have sub-partition it on LO_TAX column, based on Tax Codes. So, what we need here is an External Table with Monthly partitions and Sub-Partitions on LO_TAX.
OT OWNER NAME NUM_ROWS BLOCKS
--- -------------------- ------------------------------ ---------- ----------
1T ADMIN LINEORDER 119993317 644744
Next step is to move the data on to the External Storage (an Object Storage). This data will be moved keeping our partitioning strategy in mind. Before I move to the next step, it is important to know that Autonomous Database support many different file formats and structure. On Autonomous Database you can create partitioned external tables from HIVE style partitioned data or from simple folder partitioned data stored on your Cloud Object Store. For example, a Hive format partitioned file will look like :
The advantage of this approach is the partitioned information is available in the data file path name itself and therefore, the partitioned columns need not be part of the actual data. Partition pruning is done directly based on the path name. So, in our case the path name would be :
LINEORDER/MONTH_YEAR=JUN1993/TAX=2/filename.gz
In this case, the files will be compressed so that these occupy less space. MONTH_YEAR is a derived column, but TAX is LO_TAX column of LINEORDER Table. Since these columns and column values are part of my file path name, my CSV files need not contain these columns and column values. With Simple Folder partitioned data, the columns and column values of the partitioned columns have to be in the data files. So, in this case since I am using HIVE style partitioned data, I will dynamically create folders with column name and column values.
In order to access my Object Storage, the very first mandatory requirements is to create a credentials. Alternatively, you can use OCI Resource Principals. I am assuming you have all the required credentials. If not, please follow the documentation.
Next, I will use dbms_cloud.export_data api to export the data into compressed CSV files on to my Object Storage. To speed up the process, I created a CONTROL_TABLE with distinct MONYYYY values from LINEORDER and then scheduled a pl/sql block from three different sessions. This pl/sql block will export the data for a specified Month and a Tax Code. The data inserted into the control table has a condition to_char(lo_orderdate,’MONYYYY’)!=’AUG1998′. I will explain this later.
create table control_table (
partition_name varchar2(100),
partition_number number,
status char(1)
);
insert into control_table(partition_name, status)
SELECT DISTINCT TO_CHAR(LO_ORDERDATE,'MONYYYY') MONTH_YEAR, 'N' FROM LINEORDER WHERE to_char(lo_orderdate,'MONYYYY')!='AUG1998';
COMMIT;
UPDATE control_table set partition_number=rownum;
commit;
DECLARE
FILE_URI_O VARCHAR2(1000);
FILE_URI_F VARCHAR2(128);
FILE_URI VARCHAR2(1000);
L_STATEMENT VARCHAR2(1000);
BEGIN
FOR OUTER_LOOP IN (SELECT PARTITION_NAME MONTH_YEAR, PARTITION_NUMBER FROM CONTROL_TABLE WHERE PARTITION_NUMBER>=1 AND PARTITION_NUMBER<=25 AND STATUS='N' ORDER BY PARTITION_NUMBER)
LOOP
FOR INNER_LOOP IN (SELECT DISTINCT LO_TAX TAX FROM LINEORDER WHERE TO_CHAR(LO_ORDERDATE,'MONYYYY')=OUTER_LOOP.MONTH_YEAR)
LOOP
L_STATEMENT:='select lo_orderkey, lo_linenumber, lo_custkey, lo_partkey, lo_suppkey, lo_orderdate, lo_orderpriority, lo_shippriority, lo_quantity, lo_extendedprice, lo_ordtotalprice, lo_discount, lo_revenue, lo_supplycost, lo_commitdate, lo_shipmode from lineorder where to_char(lo_orderdate,''MONYYYY'')='||''''||OUTER_LOOP.MONTH_YEAR||''''||' and lo_tax='||INNER_LOOP.TAX||'';
FILE_URI_F:='MONTH_YEAR='||OUTER_LOOP.MONTH_YEAR||'/TAX='||INNER_LOOP.TAX||'/';
FILE_URI_O:='https://objectstorage.us-ashburn-1.oraclecloud.com/n/my_tenancy/b/External_Partitions/o/LINEORDER/'||FILE_URI_F;
FILE_URI:=FILE_URI_O||OUTER_LOOP.MONTH_YEAR||'_'||INNER_LOOP.TAX;
DBMS_CLOUD.EXPORT_DATA(CREDENTIAL_NAME => 'MY_CREDENTIAL', FILE_URI_LIST => FILE_URI, QUERY=>L_STATEMENT, FORMAT=> JSON_OBJECT('TYPE' VALUE 'CSV', 'quote' value '"', 'COMPRESSION' VALUE 'GZIP', 'MAXFILESIZE' VALUE '20485760'));
END LOOP;
UPDATE CONTROL_TABLE SET STATUS='Y' WHERE PARTITION_NUMBER=OUTER_LOOP.PARTITION_NUMBER;
COMMIT;
END LOOP;
END;
/
L_STATEMENT variable has the query that is used to export the data. I have the required columns in the SELECT Clause alongwith the relevant WHERE predicate. Each run of the statement will create a CSV file for a required partition and sub-partition. Further, I dynamically create the folders. If the folders doesn’t exist, the api will create it for us else it will create the relevant file within it’s folder. The folder name has the partition column name and it’s value. For example, I will have folder names as:
HIVE Format Folders and Files
Now, I have all the data on to my Object Storage and these are HIVE Format. I have compressed CSV files for each Month and Tax. I will now create an External Partition Table. This is done using dbms_cloud.create_external_part_table api.
This creates our external partition table. The data resides on our Object Storage and the metadata is stored in the data dictionary. Now, you can run your queries against this table and the data will be retrieved from the Object Storage.
Time to run some queries and check the run time plan. Run time pan will help us validate whether the queries are able to leverage the benefits of partition pruning. We will join this external partitioned table to another table with data stored natively and from the run time plan, we see that Adaptive Optimization kicks in as well.
# Query 1
select lo_custkey, lo_orderdate, lo_shipmode, lo_quantity from lineorder_ext_part where month_year='AUG1993' and tax=2 and lo_orderkey=3517992324;
LO_CUSTKEY LO_ORDERDATE LO_SHIPMOD LO_QUANTITY
---------- -------------------- ---------- -----------
29663183 10-AUG-1993 00:00:00 MAIL 9
Elapsed: 00:00:01.75
SQL> select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS LAST -bytes +predicate +note +cost +adaptive'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID dmak3yzpw71a2, child number 0
-------------------------------------
select lo_custkey, lo_orderdate, lo_shipmode, lo_quantity from
lineorder_ext_part where month_year='AUG1993' and tax=2 and
lo_orderkey=3517992324
Plan hash value: 1982497926
--------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 3 (100)|
| 1 | PARTITION LIST SINGLE | | 1 | 3 (0)|
|* 2 | EXTERNAL TABLE ACCESS FULL| LINEORDER_EXT_PART | 1 | 3 (0)|
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("MONTH_YEAR"='AUG1993' AND "TAX"=2 AND
"LO_ORDERKEY"=3517992324))
# Query 2
select lo_custkey, c_name, lo_orderdate, lo_shipmode, lo_quantity
from lineorder_ext_part,
vivek.customer
where month_year='DEC1997'
and tax=0
and lo_orderkey=3187415872
and c_custkey = lo_custkey;
LO_CUSTKEY C_NAME LO_ORDERDATE LO_SHIPMOD LO_QUANTITY
---------- ------------------------- -------------------- ---------- -----------
11640322 Customer#011640322 04-DEC-1997 00:00:00 FOB 6
Elapsed: 00:00:02.27
SQL> SQL> @apc
SQL> set echo on
SQL> set lines 200
SQL> set pages 1000
SQL> select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS LAST -bytes +predicate +note +cost +adaptive'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 6pn53c37c2dfb, child number 0
-------------------------------------
select lo_custkey, c_name, lo_orderdate, lo_shipmode, lo_quantity from
lineorder_ext_part, vivek.customer where month_year='DEC1997' and
tax=0 and lo_orderkey=3187415872 and c_custkey = lo_custkey
Plan hash value: 1739419959
--------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 5 (100)|
|- * 1 | HASH JOIN | | 1 | 5 (0)|
| 2 | NESTED LOOPS | | 1 | 5 (0)|
| 3 | NESTED LOOPS | | 1 | 5 (0)|
|- 4 | STATISTICS COLLECTOR | | | |
| 5 | PARTITION LIST SINGLE | | 1 | 3 (0)|
| * 6 | EXTERNAL TABLE ACCESS FULL| LINEORDER_EXT_PART | 1 | 3 (0)|
| * 7 | INDEX UNIQUE SCAN | CUSTOMER_PK | 1 | 1 (0)|
| 8 | TABLE ACCESS BY INDEX ROWID | CUSTOMER | 1 | 2 (0)|
|- 9 | TABLE ACCESS FULL | CUSTOMER | 1 | 2 (0)|
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("C_CUSTKEY"="LO_CUSTKEY")
6 - filter(("MONTH_YEAR"='DEC1997' AND "TAX"=0 AND
"LO_ORDERKEY"=3187415872))
7 - access("C_CUSTKEY"="LO_CUSTKEY")
Note
-----
- automatic DOP: Computed Degree of Parallelism is 1
- this is an adaptive plan (rows marked '-' are inactive)
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
A query against all the sub-partitions or partitions works as well.
SQL> select lo_orderkey, lo_orderdate, lo_shipmode, lo_quantity from lineorder_ext_part where month_year='AUG1993' and lo_custkey=29663183;
LO_ORDERKEY LO_ORDERDATE LO_SHIPMOD LO_QUANTITY
----------- -------------------- ---------- -----------
3517992324 10-AUG-1993 00:00:00 MAIL 9
2251158885 21-AUG-1993 00:00:00 REG AIR 29
Elapsed: 00:00:12.28
SQL> @apc
SQL> set echo on
SQL> set lines 200
SQL> set pages 1000
SQL> select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS LAST -bytes +predicate +note +cost +adaptive'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 5m709tc22ua7u, child number 0
-------------------------------------
select lo_orderkey, lo_orderdate, lo_shipmode, lo_quantity from
lineorder_ext_part where month_year='AUG1993' and lo_custkey=29663183
Plan hash value: 146054153
--------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 14 (100)|
| 1 | PARTITION LIST ITERATOR | | 7 | 14 (15)|
|* 2 | EXTERNAL TABLE ACCESS FULL| LINEORDER_EXT_PART | 7 | 14 (15)|
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("MONTH_YEAR"='AUG1993' AND "LO_CUSTKEY"=29663183))
Note
-----
- automatic DOP: Computed Degree of Parallelism is 1
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
27 rows selected.
From the queries that we executed against this External Partitioned Table, we can clearly see that for the application it doesn’t matter whether the data is fetched from within the database storage or from an External Storage. It is completely transparent to the application. One important point to note here is that these External Tables are READ ONLY and the Data is not managed by the Database.
Now, lets add some more data to this External Partition Table. While generating thh CSV files, I excluded the data for AUGUST 1998. I will not generate the CSV files for AUG1998 and we will see whether I am able to query the data.
DECLARE
FILE_URI_O VARCHAR2(1000);
FILE_URI_F VARCHAR2(128);
FILE_URI VARCHAR2(1000);
L_STATEMENT VARCHAR2(1000);
BEGIN
FOR INNER_LOOP IN (SELECT DISTINCT LO_TAX TAX FROM LINEORDER WHERE TO_CHAR(LO_ORDERDATE,'MONYYYY')='AUG1998')
LOOP
L_STATEMENT:='select lo_orderkey, lo_linenumber, lo_custkey, lo_partkey, lo_suppkey, lo_orderdate, lo_orderpriority, lo_shippriority, lo_quantity, lo_extendedprice, lo_ordtotalprice, lo_discount, lo_revenue, lo_supplycost, lo_commitdate, lo_shipmode from lineorder where to_char(lo_orderdate,''MONYYYY'')='||''''||'AUG1998'||''''||' and lo_tax='||INNER_LOOP.TAX||'';
FILE_URI_F:='MONTH_YEAR=AUG1998/TAX='||INNER_LOOP.TAX||'/';
FILE_URI_O:='https://objectstorage.us-ashburn-1.oraclecloud.com/n/my_tenancy/b/External_Partitions/o/LINEORDER/'||FILE_URI_F;
FILE_URI:=FILE_URI_O||'AUG1998_'||INNER_LOOP.TAX;
DBMS_CLOUD.EXPORT_DATA(CREDENTIAL_NAME => 'MY_CREDENTIAL', FILE_URI_LIST => FILE_URI, QUERY=>L_STATEMENT, FORMAT=> JSON_OBJECT('TYPE' VALUE 'CSV', 'quote' value '"', 'COMPRESSION' VALUE 'GZIP', 'MAXFILESIZE' VALUE '20485760'));
END LOOP;
END;
/
Now, let me run a query against this newly added CSV file. While the files are uploaded and available in the correct HIVE format folders, you can see that Oracle could not access the data and the run time plan says PARTITION LIST EMPTY.
SQL> select lo_orderkey, lo_orderdate, lo_shipmode, lo_quantity from lineorder_ext_part where month_year='AUG1998' and tax=6 and lo_custkey=16287940;
no rows selected
Elapsed: 00:00:00.11
SQL> @apc
SQL> set lines 200
SQL> set pages 1000
SQL> select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS LAST -bytes +predicate +note +cost +adaptive'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 5s7wagh8za9bx, child number 1
-------------------------------------
select lo_orderkey, lo_orderdate, lo_shipmode, lo_quantity from
lineorder_ext_part where month_year='AUG1998' and tax=6 and
lo_custkey=16287940
Plan hash value: 3670407871
--------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 2 (100)|
| 1 | PARTITION LIST EMPTY | | 1 | 2 (0)|
|* 2 | EXTERNAL TABLE ACCESS FULL| LINEORDER_EXT_PART | 1 | 2 (0)|
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("MONTH_YEAR"='AUG1998' AND "TAX"=6 AND
"LO_CUSTKEY"=16287940))
SQL> select count(*) from lineorder_ext_part where month_year='AUG1998';
COUNT(*)
----------
0
As I said earlier, External Tables are READ Only and the Data is not managed by Oracle Database. For this, we need to Refresh the External Table using SYNC_EXTERNAL_PART_TABLE procedure of dbms_cloud. Once done, the newly added CSV files would be considered as a new partition and the data can then be queried.
BEGIN
DBMS_CLOUD.SYNC_EXTERNAL_PART_TABLE(table_name => 'LINEORDER_EXT_PART');
END;
/
SQL> select lo_orderkey, lo_orderdate, lo_shipmode, lo_quantity from lineorder_ext_part where month_year='AUG1998' and tax=6 and lo_custkey=16287940;
LO_ORDERKEY LO_ORDERDATE LO_SHIPMOD LO_QUANTITY
----------- -------------------- ---------- -----------
3004306725 02-AUG-1998 00:00:00 REG AIR 14
Elapsed: 00:00:42.34
SQL> @apc
SQL> set echo on
SQL> set lines 200
SQL> set pages 1000
SQL> select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS LAST -bytes +predicate +note +cost +adaptive'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 5s7wagh8za9bx, child number 1
-------------------------------------
select lo_orderkey, lo_orderdate, lo_shipmode, lo_quantity from
lineorder_ext_part where month_year='AUG1998' and tax=6 and
lo_custkey=16287940
Plan hash value: 917046205
----------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 2 (100)|
| 1 | PX COORDINATOR | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 1 | 2 (0)|
| 3 | PX BLOCK ITERATOR | | 1 | 2 (0)|
|* 4 | EXTERNAL TABLE ACCESS FULL| LINEORDER_EXT_PART | 1 | 2 (0)|
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter(("MONTH_YEAR"='AUG1998' AND "TAX"=6 AND
"LO_CUSTKEY"=16287940))
Note
-----
- automatic DOP: Computed Degree of Parallelism is 16 because of degree limit
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
31 rows selected.
Elapsed: 00:00:00.08
SQL> select count(*) from lineorder_ext_part where month_year='AUG1998';
COUNT(*)
----------
99688
Elapsed: 00:00:01.44
SQL>
Wow! a cool feature and easy to use. Isn’t it? Last, with the latest enhancement to this feature, two new hidden columns are added to the External Table. These are file$path and file$name. These columns can be queried to check the Source File Path and Name.
SQL> select lo_orderkey, file$path, file$name from lineorder_ext_part where month_year='AUG1993' and tax=2 and lo_custkey=29663183;
LO_ORDERKEY FILE$PATH FILE$NAME
----------- -------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------
3517992324 https://objectstorage.us-ashburn-1.oraclecloud.com/n/my_tenancy/b/External_Partitions/o/LINEORDER/MONTH_YEAR=AUG1993/TAX=2 AUG1993_2_10_20220704T111509Z.csv.gz
This is very easy to implement on Autonomous Database and is a Cost Saving option. Performance insensitive and historical data can be easily moved to an external storage. Accessing this data is as simple as accessing natively stored data. Do try it out and let me know how it works.
Availability of Database Vault on Autonomous Database (Shared Infrastructure) was announced in February 2020. Database Vault does not require any introduction as this feature has been available since Oracle 10g. As a brief, it provides powerful security controls to help protect sensitive application data from Unauthorised access and comply with privacy and regulatory requirements. This means, a superuser (or a user with DBA privilege) can access the sensitive application data. Oracle Database Vault implements this security at the database layer.
Database Vault provides additional security to the Autonomous Database Family and is just few steps. This blog demonstrates the steps needed to implement Database Vault on Autonomous Data Warehouse. Before I go through the steps, it is important to know a concept of REALMS. Realm is a grouping of database schemas, database objects and roles that must be secured for a given application. It is a kind of a Zone of Protection for your database objects. So, one of the steps required will be to create a Realm.
Every Autonomous Database has an ADMIN user, which is a super user. ADMIN user can query any table within the Database (by default). All the steps required for implementing Database Vault is to be executed using ADMIN user. Once done, ADMIN user will continue to be a super user, but will not be able to query the application data. In my case, assume VIVEK is an application schema containing all the tables accessed by my application and one such table is CREDIT_CARD.
- Connect to ADMIN User and Query the CREDIT_CARD Table. ADMIN is able to query the data.
CONNECT ADMIN/xxxxxxxxxxxxx@ADWxxxxxx_low
SELECT * FROM VIVEK.CREDIT_CARD;
CC_NO CC_NAME EXPIRY_DT
-------------------------------- -------------------------------- --------------------
1234 5678 8765 4321 ADB_1 29-FEB-2024 00:00:00
5678 8765 1234 4321 ADB_2 31-DEC-2021 00:00:00
- Check whether Data Vault is Configured and Enabled. It is not configured.
SELECT * FROM DBA_DV_STATUS;
NAME STATUS
------------------- ----------------------------------------------------------------
DV_CONFIGURE_STATUS FALSE
DV_ENABLE_STATUS FALSE
- Create Database Vault Owner and Database Vault Account Manager User.
CREATE USER ADB_DBV_OWNER IDENTIFIED BY "xxxxxxxxxxxxx";
CREATE USER ADB_DBV_ACCTMGR IDENTIFIED BY "xxxxxxxxxxxxx";
GRANT CREATE SESSION, SELECT ANY TABLE TO ADB_DBV_OWNER;
- Check whether we can select the data from CREDIT_CARD table, before implementing Database Vault
- Database Audit Vault User has SELECT ANY TABLE privilege.
CONNECT ADB_DBV_OWNER/xxxxxxxxxxxxx@ADWxxxxxx_low
SELECT * FROM VIVEK.CREDIT_CARD;
CC_NO CC_NAME EXPIRY_DT
-------------------------------- -------------------------------- --------------------
1234 5678 8765 4321 ADB_1 29-FEB-2024 00:00:00
5678 8765 1234 4321 ADB_2 31-DEC-2021 00:00:00
- Configure and Enable Database Vault
CONNECT ADMIN/xxxxxxxxxxxxx@ADWxxxxxx_low
EXEC DBMS_CLOUD_MACADM.CONFIGURE_DATABASE_VAULT('ADB_DBV_OWNER', 'ADB_DBV_ACCTMGR');
- Database Vault Configured, but not Enabled
SELECT * FROM DBA_DV_STATUS;
NAME STATUS
------------------- ----------------------------------------------------------------
DV_CONFIGURE_STATUS TRUE
DV_ENABLE_STATUS FALSE
- Enable Database Vault. For this to come into effect, we need to restart the database
EXEC DBMS_CLOUD_MACADM.ENABLE_DATABASE_VAULT;
- Restart the Database. Check after Database Restart
- Database Vault Configured and Enabled
SELECT * FROM DBA_DV_STATUS;
NAME STATUS
------------------- ----------------------------------------------------------------
DV_CONFIGURE_STATUS TRUE
DV_ENABLE_STATUS TRUE
- Admin can still Query the Data
SELECT * FROM VIVEK.CREDIT_CARD;
CC_NO CC_NAME EXPIRY_DT
-------------------------------- -------------------------------- --------------------
1234 5678 8765 4321 ADB_1 29-FEB-2024 00:00:00
5678 8765 1234 4321 ADB_2 31-DEC-2021 00:00:00
- Create REALM, Add Object to the Realm
BEGIN
DBMS_MACADM.CREATE_REALM(
realm_name => 'Vivek Application Schema',
description => 'Realm created for Vivek Schema',
enabled => DBMS_MACUTL.G_YES,
audit_options => DBMS_MACUTL.G_REALM_AUDIT_FAIL + DBMS_MACUTL.G_REALM_AUDIT_SUCCESS,
realm_type => 1,
realm_scope => DBMS_MACUTL.G_SCOPE_LOCAL);
END;
/
BEGIN
DBMS_MACADM.ADD_OBJECT_TO_REALM(
realm_name => 'Vivek Application Schema',
object_owner => 'VIVEK',
object_name => '%',
object_type => '%');
END;
/
- Add Authorised User, which is the User that can Query the Application Table
- In this case, VIVEK is the application owner
BEGIN
DBMS_MACADM.ADD_AUTH_TO_REALM(
realm_name => 'Vivek Application Schema',
grantee => 'VIVEK',
auth_options => DBMS_MACUTL.G_REALM_AUTH_OWNER);
END;
/
- Check whether ADMIN can Query the sensitive Data from Vivek Schema
CONNECT ADMIN/xxxxxxxxxxxxx@ADWxxxxxx_low
SELECT * FROM VIVEK.CREDIT_CARD;
*
ERROR at line 1:
ORA-01031: insufficient privileges
- Vivek being an Application Owner, can Query the data. No other Users allowed to Query
CONNECT VIVEK/xxxxxxxxxxxxx@ADWxxxxxx_low
SELECT * FROM VIVEK.CREDIT_CARD;
CC_NO CC_NAME EXPIRY_DT
-------------------------------- -------------------------------- --------------------
1234 5678 8765 4321 ADB_1 29-FEB-2024 00:00:00
5678 8765 1234 4321 ADB_2 31-DEC-2021 00:00:00
As can be seen, the steps are pretty simple. This is a simplified version of Database Vault configuration. Refer to Oracle Documentation on the various configuration options available with Database Vault. Hope this helps.
Recently I was working on an issue related to loading data from a flat file on to Autonomous Data Warehouse using dbms_cloud api. While lot has been written on this subject, I thought of writing on this as the challenge here was that the file in this case was without any delimiter. The Original data to be loaded was around 90 Million, but for testing purpose customer was loading around 200k rows.
What is a Delimiter? Delimiter is one or more character that separates two strings. These characters are used to identify different columns.
One of the best blog on this topic is this blog from Nilay Panchal.
Coming back to my topic of this blog. In this case, customer was trying to load 200k rows into a table and the way they were using it, the time it took to load these 200k rows was around 25 minutes which is too huge considering that actual production number of rows would be 90 Million.
As mentioned, the challenge here was that the content in this flat file had no delimiter or any character to identify column break. The original flat file had around 500 columns, but for testing the file was truncated to 5 columns. The idea here is to demonstrate an efficient way of loading the data into a table when the data in the flat file is without any delimiter. The sample data is as under:
CCODEDEBUTRUNSHUNDREDSPLAYER_NAME
IND151119891592151SACHIN TENDULKAR
AUS081219951337841 RICKY PONTING
SA141219951328945 JACQUES KALLIS
SL200720001240038 KUMAR SANGAKARA
WI061219901195334 BRIAN LARA
IND20062011 720227 VIRAT KOHLI
Our very first step will be to create cloud object storage credentials using dbms_cloud.create_credentials. If you have already created a credential, then you can skip this. Check dba_credentials for the list of Credentials.
begin
dbms_cloud.create_credential(
credential_name=>'VIVEK_CREDENTIAL',
username=>'VIVEKS',
password=>'paste_auth_token_here'
);
end;
/
SQL> column owner for a20
SQL> column credential_name for a30
SQL> column username for a20
SQL> select owner, credential_name, username from dba_credentials;
OWNER CREDENTIAL_NAME USERNAME
-------------------- ------------------------------ --------------------
ADMIN VIVEK_CREDENTIAL VIVEKS
I have uploaded by data.txt file that contains the required data to be loaded on the Object Storage. Next Step will be create a table in which the data is to be loaded. For COPY_DATA, you need to ensure the Target Table is already created as the procedure would fail.
Now, I will create by COPY_DATA procedure with the required inputs. It is important to note the format and field_list option. In my case, the date was in DDMMYYYY format and hence I had to specify the dateformat explicitly to allow copy_data to parse the date accurately. Without the dateformat value, my copy_data failed with an error. The other parameter is field_list, which ensures that in the absence of delimiter, the copy_data parses the column values based on the start and end position. You need to ensure that these position are accurate.
begin
dbms_cloud.copy_data(
table_name =>'CRICKETER_DATA',
credential_name =>'VIVEK_CREDENTIAL',
file_uri_list =>'&MY_OBJECT_STORAGE_URL/data.txt',
format => json_object('skipheaders' value '1', 'dateformat' value 'ddmmyyyy'),
field_list =>'CCODE POSITION(1:3), DEBUT POSITION(4:11), RUNS POSITION(12:16), HUNDREDS POSITION(17:18), PLAYER_NAME POSITION(19:34)');
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.50
SQL> SQL> select * from cricketer_data;
CCO DEBUT RUNS HUNDREDS PLAYER_NAME
--- -------------------- ---------- ---------- ----------------
IND 15-NOV-1989 00:00:00 15921 51 SACHIN TENDULKAR
AUS 08-DEC-1995 00:00:00 13378 41 RICKY PONTING
SA 14-DEC-1995 00:00:00 13289 45 JACQUES KALLIS
SL 20-JUL-2000 00:00:00 12400 38 KUMAR SANGAKARA
WI 06-DEC-1990 00:00:00 11953 34 BRIAN LARA
IND 20-JUN-2011 00:00:00 7202 27 VIRAT KOHLI
6 rows selected.
If for some reason the copy_data procedure fails, it creates a error log table, which is reported immediately along with the error number. To drill down to the actual error, you need to query the log table. For example, when I omitted FORMAT option from my COPY_DATA procedure, it failed with following error. I queried the COPY$22_LOG to extract the actual error.
select * from COPY$21_LOG;
RECORD
------------------------------------------------------------------------------------------------------------------------------------
LOG file opened at 01/28/20 05:15:18
Total Number of Files=1
Data File: &MY_OBJECT_STORAGE_URL/data.txt
Log File: COPY$21_331103.log
LOG file opened at 01/28/20 05:15:18
Bad File: COPY$21_331103.bad
Field Definitions for table COPY$Q18IZ07MUUMYRU3IG6MU
Record format DELIMITED BY
Data in file has same endianness as the platform
Rows with all null fields are accepted
Fields in Data Source:
CCODE CHAR (3)
Record position (1, 3)
Terminated by "|"
DEBUT CHAR (8)
Record position (4, 11)
Terminated by "|"
RUNS CHAR (5)
Record position (12, 16)
Terminated by "|"
HUNDREDS CHAR (2)
Record position (17, 18)
Terminated by "|"
PLAYER_NAME CHAR (16)
Record position (19, 34)
Terminated by "|"
error processing column DEBUT in row 1 for datafile &MY_OBJECT_STORAGE_URL/data.txt
ORA-01858: a non-numeric character was found where a numeric was expected
40 rows selected.
Everything revolves around DATA. One of the challenges for an Organization is to quickly adapt to the ever changing Business User Needs. These needs are driven by Competition and Business Users Responsible for the Company’s Growth at the same time remain Competitive.
Data Arrives in different format – Structured and Unstructured. It is important to understand the 3V’s of Big Data Technologies. Data arrives in different format (Variety) as these are generated from different sources. Data generation is not restricted to a Single Device, like Laptops or Desktops. These are generated from different devices 24×7. This means Volume has gone up tremendously. And since, it is generated 24×7, the Speed (Velocity) with which the data arrives, gives less time for the Business User to analyse and derive some meaningful insights from this data. Less time, because Data that arrives in different format is either Transformed into Structured format and is loaded into a relational database that enforces data modelled by a Schema (Schema on Write) or is kept in an Unstructured Format and queried using some other database models (NoSQL, NewSQL or Big Data). This means, IT has to provide different means for connecting to these disparate systems which introduces Complexity. Integration poses another challenge. Combining these data for faster and nearly accurate analysis is another Big Challenge. Therefore, it has now become more critical for a CIO or an IT Department to come up with an Innovative Solutions to complex processes and problems revolving around disparate systems storing different formats of DATA.
POLYGLOT Persistance
It is a concept of using different data storage technologies to handle different data storage needs within a given Software application. Every Application Vendor or an ISV or System Implementers provide best database to address their application demand/requirements but no one company has a Single Application. Each Application caters to different use cases with different data models thus different data storage. Integrating these to provide a Unified View is a biggest challenge.
Multi-Model Databases provide an Organization the required Agility. The flexibility to use different data models, to address different use cases, to store Variety of Data for the Business Users within a Single Integrated Backend Database. This addresses the complexity challenge introduced by Polyglot Persistance.
SIGNIFICANT Recognition
One of the Significant Recognition for Oracle Database is that it scored highest in both the current offerings and strategy categories by Forester. This is the ability to support converged OLTP and Analytical (Translytical) Workloads using Database In-Memory, Exadata and extensive Multi-Model Capabilities.
It’s a privilige to be a part of such an Innovative Technology and, Yes – to be a part of an Innovative Organization.
