Feed aggregator

which delete statement is better for deleting millions of rows

Tom Kyte - Fri, 2021-06-18 14:06
I have table which contains millions of rows. I want to delete all the data which is over a week old based on the value of column last_updated. so here are my two queries, Approach 1: <code> Delete from A where to_date(last_updated,''yyyy-mm-dd'')< sysdate-7; </code> Approach 2: <code> l_lastupdated varchar2(255) := to_char(sysdate-nvl(p_days,7),'YYYY-MM-DD'); insert into B(ID) select ID from A where LASTUPDATED < l_lastupdated; delete from A where id in (select id from B); </code> which one is better considering performance, safety and locking?
Categories: DBA Blogs

Exam AI-102: Designing And Implementing a Microsoft Azure AI Solution | AI Engineer

Online Apps DBA - Fri, 2021-06-18 00:35

This training will teach you how to design and implement an Azure AI solution. It is intended for software developers wanting to build AI-infused applications that leverage Azure Cognitive Services, Azure Cognitive Search, and Microsoft Bot Framework. This training includes 24 hands-on activities in which you will use C# or Python to develop AI applications. […]

The post Exam AI-102: Designing And Implementing a Microsoft Azure AI Solution | AI Engineer appeared first on Oracle Trainings for Apps & Fusion DBA.

Categories: APPS Blogs

Exam DP-300: Microsoft Azure Database Administrator Associate

Online Apps DBA - Fri, 2021-06-18 00:15

✯ The Azure Database Administrator implements and manages the operational aspects of cloud-native and hybrid data platform solutions built on Microsoft Azure data services and Microsoft SQL Server. ✯ DP-300 Exam is initiated by Microsoft with a major aspect of administering Relational Databases on Microsoft Azure. This exam is an objective for Data Professionals, Data […]

The post Exam DP-300: Microsoft Azure Database Administrator Associate appeared first on Oracle Trainings for Apps & Fusion DBA.

Categories: APPS Blogs

How to Create and Publish Power BI App

Online Apps DBA - Thu, 2021-06-17 23:51

Did you ever struggle to share all of your Power BI content in one place? If so, then the Power BI App has got the perfect solution for you. ➤ Power BI App ↬ Power BI App is one of the features of Power BI that allows sharing of the content to a broad audience. […]

The post How to Create and Publish Power BI App appeared first on Oracle Trainings for Apps & Fusion DBA.

Categories: APPS Blogs

Cloud Vanity: A Weekly Carnival of AWS, Azure, GCP, and More - Edition 6

Pakistan's First Oracle Blog - Thu, 2021-06-17 21:55

 Welcome to the next edition of weekly Cloud Vanity. There is so much razzmatazz about cloud that for a second we tend to think that every company is already on some sort of cloud. A recent study has found out that still only 35% of companies are in the cloud and rest of them are still thinking or planning to migrate. So still lot of opportunity there.


Announcing the AWS Security and Privacy Knowledge Hub for Australia and New Zealand

Few things have changed the world more than the internet, and at the heart of the internet is the open source LAMP stack. LAMP, short for Linux, Apache, MySQL, and PHP, enabled developers to build new, interactive web experiences

Australian Commonwealth Government agencies are subject to specific requirements set by the Protective Security Policy Framework (PSPF) for securing connectivity between systems that are running sensitive workloads, and for accessing less trusted environments, such as the internet.

Today, Dave Brown, VP of Amazon EC2 at AWS, announced the Graviton Challenge as part of his session on AWS silicon innovation at the Six Five Summit 2021.

AWS Step Functions allow you to build scalable, distributed applications using state machines. Until today, building workflows on Step Functions required you to learn and understand Amazon State Language (ASL).


Now’s the time to register for the free Azure Hybrid and Multicloud Digital Event on Tuesday, June 29, 2021, from 9:00 AM–11:00 AM Pacific Time, delivered in partnership with Intel.

For over three years, I have had the privilege of leading the SAP solutions on Azure business at Microsoft and of partnering with outstanding leaders at SAP and with many of our global partners to ensure that our joint customers run one of their most critical business assets safely and reliably in the cloud. 

There are many factors that can affect critical environment (CE) infrastructure availability—the reliability of the infrastructure building blocks, the controls during the datacenter construction stage, effective health monitoring and event detection schemes, a robust maintenance program, and operational excellence to ensure that every action is taken with careful consideration of related risk implications.

The power of 5G, IoT, and real-time AI will unlock new and innovative services for enterprises across the world to accelerate their transformation toward Industry 4.0 as they evolve and adopt diverse new business models. 

Cloud and edge computing are coming together as never before, leading to huge opportunities for developers and organizations around the world. Digital twins, mixed reality, and autonomous systems are at the core of a massive wave of innovation from which our customers already benefit.


As your organization evolves, the cloud can be a powerful tool to drive growth, improve efficiency, and reduce costs. In fact, the cloud is so powerful that most organizations find themselves running on multiple clouds

At its core, Data and Analytics allows us to make impactful decisions by deriving insights from our data. In the pursuit of making data meaningful, data scientists and engineers are often tasked with building end-to-end workflows to ingest, process and analyze data.

At Google Cloud, we believe moving to the cloud shouldn’t have to mean starting over from scratch. That’s why we’re on a mission to give you choices for how you run your enterprise workloads, including migrating and modernizing your Windows workloads. 

In times of significant disruption, organizations are faced with three choices: Retrench within legacy solutions, pause and do nothing while waiting for more data or different circumstances, or press ahead, potentially even accelerating to realize the desired outcome.

Our collective understanding of work—where it takes place and how it gets done—has been transformed over the last year.


The Clouds are Thickening: An Overview of The SaaS Ecosystem and Big Cloud Providers

The Move Toward Simplicity: Why a Single-Vendor Approach to AI-Powered Automation Matters

Crafting with XieSandi is an educational simulation video game that describes how to craft different things, such as handcrafting and DIY.

Imagine one hybrid cloud platform that provides the automation, observability and cloud-native capabilities necessary to keep business, technology and teams connected while delivering the best digital experiences now and in the future.

Cloud operating model refers to the operational model used by I&O organization when adopting cloud and trying to execute their cloud strategy. The model is used to transform the traditional IT management within an organization to be capable of managing the clouds that the business consumes.

Categories: DBA Blogs

Introduction to ARM Templates: Learn, Create and Deploy in Azure

Online Apps DBA - Thu, 2021-06-17 09:14

An ARM template is a block of code that defines the infrastructure and configuration for your project. It uses a declarative syntax to let you define your deployment in the form of JSON files. Template Format – In ARM Template, we need to define schema, content version, parameters, variables, functions, resources, and outputs. Azure Quickstart […]

The post Introduction to ARM Templates: Learn, Create and Deploy in Azure appeared first on Oracle Trainings for Apps & Fusion DBA.

Categories: APPS Blogs

Azure Networking | A Brief Introduction for Beginners

Online Apps DBA - Thu, 2021-06-17 09:01

Azure Networking is a communication protocol for connecting multiple resources via the Internet. Azure provides various tools and services that make your network strong and easy to manage. Azure Virtual Network (VNet) is a private network with interconnected Azure Resources like Azure Virtual Machines, Infrastructure and Network. If you want to learn more on Azure […]

The post Azure Networking | A Brief Introduction for Beginners appeared first on Oracle Trainings for Apps & Fusion DBA.

Categories: APPS Blogs

How To Determine Host Is On OCI

Michael Dinh - Wed, 2021-06-16 14:35

Learned something new today.


Host is running OEM

[oracle@oemhost ~]$ oci-metadata
Instance details:
  Display Name: oemhost
  Region: phx - us-phoenix-1 (Phoenix, AZ, USA)
  Canonical Region Name: us-phoenix-1
  Availability Domain: jjZD:PHX-AD-2
  Fault domain: FAULT-DOMAIN-1
  OCID: ocid1.instance.oc1.phx.X
  Compartment OCID: ocid1.compartment.oc1..X
  Instance shape: VM.Standard2.16
  Image ID: ocid1.image.oc1.phx.X
  Created at: 1592424850729
  state: Running
    managementDisabled: False
    monitoringDisabled: False
  ociAdName: phx-ad-2
  hostname: oemhost
    regionKey: PHX
    realmDomainComponent: oraclecloud.com
    regionIdentifier: us-phoenix-1
    realmKey: oc1
  Instance Metadata:
    ssh_authorized_keys: ssh-rsa *
Networking details:
  VNIC OCID: ocid1.vnic.oc1.phx.X
  VLAN Tag: 293
  Private IP address:
  MAC address: 00:00:17:01:47:77
  Subnet CIDR block:
  Virtual router IP address:
[oracle@oemhost ~]$
__ATA.cmd.push(function() { __ATA.initDynamicSlot({ id: 'atatags-26942-60ca5383d3879', location: 120, formFactor: '001', label: { text: 'Advertisements', }, creative: { reportAd: { text: 'Report this ad', }, privacySettings: { text: 'Privacy', } } }); });


Tom Kyte - Wed, 2021-06-16 07:06
HI Tom, I am running one procedure using dbms_job.submit. but it is getting failed immediately. i wrote dbms_output command to output the error message. So how and where can I see that error message. Thanks in advance for your great support...
Categories: DBA Blogs

I cannot append data more than 32kb in CLOB

Tom Kyte - Wed, 2021-06-16 07:06
Hi, Am using CLOB data type in stored procedure. I am appending data from temporary table in CLOB I have a following plsql. DECLARE TYPE sturecord IS RECORD (stid NUMBER, Regid NUMBER); TYPE stutype IS TABLE OF sturecord; stutable stutype; stid CLOB := NULL; Regid CLOB := NULL; BEGIN SELECT DISTINCT (st.stu_id), rg.Reg_id BULK COLLECT INTO stutable FROM Student_details st, student_registration rg WHERE st.stu_id = rg.stu_id; FOR i IN stutable.FIRST .. stutable.LAST LOOP dbms_output.put_line(i) ; stid := stid || stutable (i).stid || ','; Regid := Regid || stutable (i).Regid || ','; END LOOP; END; In line 22 am getting error like "ORA-06502: PL/SQL: numeric or value error ORA-06512: at line 22" Temporary Table having more than 44000 record, approximate size is 3mb. CLOB Datatype has max length 4GB. Please help how to resolve this error. Thanks
Categories: DBA Blogs

Split Partition

Jonathan Lewis - Wed, 2021-06-16 06:33

This is a note I drafted a little over 5 years ago in response to a question on the Oracle developer forum about turning a simple heap table into a list partitioned table; but never got around to publishing it and now it’s not going to be of much benefit to most people because in 12c Oracle makes it very easy to handle the task of re-engineering partitioned tables online with a single “alter table” command.

But people do still run 11g, and the topic of changing, or repairing, a partitioning strategy has come up again very recently (more than once) in the context of 11g, so I thought I’d edit the draft and publish it.

There are, of course, many ways to start with a simple heap table and end up with a partitioned table, or start with a partitioned table in one state and end up with the table in a different state, and the method you choose may depend on various restrictions imposed from outside the database. One of the simpler strategies would be to “create table as select” to create a new partitioned table from the existing table and then rename the two tables – with possible variations such as exporting, creating an empty partitioned table, then re-importing ignoring creation errors.

Here’s a thought, though, that you might be able to take advantage if you need to enhance the partitioning of an existing partitioned table, but don’t have to re-partition all your data immediately. It’s an idea I came up with many years ago (long before the introduction of interval partitions) when a client had a daily partitioned table but had forgotten for a several months that they were supposed to do a regular “split partition” of the maxvalue partition in anticipation of future data arriving. They had a table with about 30 daily parititons, and one partition of about 500 days (18 months).

The first step, of course, was to split the big partiiton “above” the highest current value so that Oracle didn’t have to copy data into the resulting two partitions, so we could re-impose the regular splitting mechanism on the new, small, maxvalue partition. The follow-up step was to worry about splitting the one huge partition into daily chunks over the next few days or weeks.

The client’s initial thought was to write a simple pl/sql loop that generated and executed statements like:

alter table orders 
        split partition p20050907 
        at (to_date('02-Mar-2004','dd-Mon-yyyy')) 
        into (
                partition p20040302, 
                partition p20050907

This would split the first day off the very large partition, then the next pass through the loop would move on one day and split off the next daily partition, and so on. While the code was simple it was not an efficient strategy – just think about the arithmetic:

To keep things simple, imagine we have 512 days in the intial partition and each day’s data is exactly 1GB. On the first split we read and write 512GB; on the second split we read and write 511GB, on the fird split we read and write 510GB … on the 511th split we read and write 2GB and the job is done. In total the loop reads and writes 511 * (512 + 2) / 2 = 131,327 GB.  (Average of 1st and last, multiplied by number of terms).

Now think about coding a more complex algorithm:

  • First split the big partition into 2 partitions of 256 days each, reading and writing 512GB;
  • Then split the two resulting partitions into a total 4 partitions of 128 days each, reading and writing 512GB (in total);
  • Then split the 4 resulting partitions into a total of 8 partition of 64 days each, reading and writing 512GB (in total)
  • On the 9th cycle you will have to split 256 partitions of 2GB each to produce 512 partitions of 1GB each, reading and writing a total of 512GB.

In total the algorithm reads and writes 9 * 512 = 4,604 GB which is about 1/30th of the simpler algorithm.


This example of splitting partitions is just one specific case of a general principle.

It’s easy to code up an “obvious” strategy without considering whether there might be a slightly more complex but vastly more efficient strategy. In many cases, of course, the difference in resource costs between the simplest and the most efficient strategies may not be worth the extra development and testing time, but when you start dealing with large data volumes it is always worth asking three questions:

  1. Do I have a reasonable idea of the resources that will be taken up by the obvious strategy
  2. Is the obvious strategy the only possible strategy
  3. Is it worth spending more human resources designing and testing a better strategy to save machine resources at run-time.


AWS Solution Architect Training Day 10 & Day 11 FAQ’s

Online Apps DBA - Wed, 2021-06-16 00:50

AWS Solution Architecture Associate is the comprehensive Training for those who run enterprise architecture programs, as well as solutions architects or Beginners who want to learn AWS Solution Architecture Associate covering Cloud Concepts, AWS Services, Serverless Websites, Load Balancing, etc. The blog post – https://k21academy.com/awssa53 will cover the Q/A’s from Day 10 & 11 of […]

The post AWS Solution Architect Training Day 10 & Day 11 FAQ’s appeared first on Oracle Trainings for Apps & Fusion DBA.

Categories: APPS Blogs

Google Identity and Access Management (IAM)

Online Apps DBA - Wed, 2021-06-16 00:38

Google Identity and Access Management is a service that gives cloud administrators the authority to decide who can take a particular action on a particular resource. It is important to have an IAM service associated with one’s cloud platform to prevent unauthorized access to sensitive data. Want to learn more about Google Identity and Access […]

The post Google Identity and Access Management (IAM) appeared first on Oracle Trainings for Apps & Fusion DBA.

Categories: APPS Blogs

Amazon Inspector: Overview, Benefits and How to Get Started?

Online Apps DBA - Wed, 2021-06-16 00:24

➤ Security is crucial for everyone running applications on the cloud. Cloud security refers to the technologies, policies, and services that help to protect cloud-hosted data, applications, and infrastructure from various internet threats. Amazon Inspector is a security service that helps improve the security of deployed applications on AWS. ➤ Amazon Inspector is an AWS […]

The post Amazon Inspector: Overview, Benefits and How to Get Started? appeared first on Oracle Trainings for Apps & Fusion DBA.

Categories: APPS Blogs

deterministic package function

Tom Kyte - Tue, 2021-06-15 12:46
How can I can set a package function to be deterministic? It is easy for standalone function, but I couldn`t find the right syntax for package functions.
Categories: DBA Blogs

Laser Tattoo Removal – The Lower-Cost Way

OraQA - Tue, 2021-06-15 06:06

There are numerous primary explanations why people draw tattoos on their own physiques. It may be to preserve some remembrances, being an art expression, so that you can show loyalty obtaining a reason one belief is great. However, as time passes, some might grow careful in regards to this artistic expression, and subsequently factor is they are searching for methods for wash it their physiques.

This really is frequently a properly-articulated number of four standard Laser tattoo removal methods in addition for relative cost difference.

TCA Laser tattoo removal

TCA will always be minimal pricey approach to effectively removing a tattoo inside you. Between 1979-1984, a clinical study was transported out that handle Laser tattoo removal using TCA. The initial phase inside the research incorporated 670 volunteer patients. Inside the finish within the survey, 85% within the total volunteers were pleased with their results. Another study was transported in 1988, which involved 40 volunteers who experienced the surgical procedure utilizing TCA for Laser tattoo removal, with 90% pleased with in conclusion result.

 The Definitive Guide to all Tattoo Removal Methods

Although TCA is generally helpful for facial peels and acne scarring treatment, the research transported out within the last 3 decades states it’s impressive for removing a tattoo inside you.

Laser tattoo removal cream

Up to now, there’s no medically-approved Laser tattoo removal cream. However, there are many brands available on the market claiming otherwise. Some cosmetic brands even claim their cream is excellent at removing tattoos inside the skin while guaranteeing their strategy is less pricey and impressive than laser surgery.

Cream can’t remove tattoos since it cannot penetrate your sensitive skin in which the tattoo may be the positioning on the skin pigment. Because most ink removal cream includes a skin pigment fading or melanin chemical because the primary content, the very best result these creams is able to do must be to fade your skin pigment inside the tattoo layer but cannot obliterate the tattoo.

The fallout from ink removal cream might be you are able to complete spending over $50 to $70 monthly with no make sure the tattoo will probably be removed soon.

Also, most ink removal manufacturers do not have precise detail on their own product contents on another possess a refund when the cream doesn’t perform its function.


Another status by using this Laser tattoo removal procedure is “Surgical skin planing” and “Non Deep deep deep deep deep chemical peel.” The current process for transporting out dermabrasion involves freezing the topmost skin layer inside the tattoo and using an electric surgical sander to get rid of your skin in which the tattoo pigment lies. This Laser tattoo removal technique is impressive, but concurrently, it’s pricey, bloody, and needs medical expertise to complete flawlessly.

The Cosmetic Vein &amp; Laser Center - Laser Tattoo Removal

Laser tattoo removal

Laser tattoo removal costs around $150 – $350 per session. Medical advancement allows you to certainly utilize Laser tattoo removal dark-spotted color tattoos effectively without undesirable effects. Dark colors like gray, black and blue would be the easiest colors easily removed obtaining a laser procedure. Light color like yellow, pink, and lightweight-weight-weight blue generally is a bit difficult and need several session to finally remove inside the skin pigments developing a increase in the price of removal into a lot of money.


Regardless of the dimensions and tattoo color, generally there’s surgery for removing them once the need arises.

There’s a numerous strategies which anybody thinking about removing their tattoo could use medically. Since the laser surgical technique is effective, it might be from achieve for anybody working inexepensively. The dermabrasion technique is pricey but effective.

How to read parameter values passed from java while calling a stored procedure.

Tom Kyte - Mon, 2021-06-14 18:26
Hi Tom, The web app we have uses java class to call stored procedures. User inputs are used to pass parameter values while calling the stored procedure. Is there a way in oracle that I get to know what parameter values are passed by querying some dbms table or the like? I need to know the parameters only when I encounter any error/exception, so I dont want to add a log of parameters inside all my procedures to record the parameters. Please let me know if this is possible. Thanks...
Categories: DBA Blogs

Combining multiple rows to get a single one

Tom Kyte - Mon, 2021-06-14 18:26
Source table : <code>---------------------------------------- | Employee Name | department | Emp Id | ---------------------------------------- | Sam | Sales | 101 | ---------------------------------------- | Sam | Finance | 101 | ---------------------------------------- | Dirk | marketing | 102 | ---------------------------------------- | Dirk | Research | 102 | ----------------------------------------</code> Output needed : <code>------------------------------------------------------ | Employee Name | Emp Id | department1 | department2| ------------------------------------------------------ | Sam | 101 | Sales | Finance | ------------------------------------------------------ | Dirk | 102 | marketing | Research | ------------------------------------------------------</code> Can you kindly help with what functions or query should i use to get above mentioned output?
Categories: DBA Blogs

Basic Pipeline using Snyk Container, OCI Images, Azure DevOps all part of Cloud Native Application Security

Pas Apicella - Mon, 2021-06-14 00:20

Snyk Container will find vulnerabilities in containers and Kubernetes workloads throughout the SDLC by scanning any compliant OCI image which includes those created by Cloud Native Buildpacks or other build tools that create OCI images.

So what could an Azure DevOps Pipeline look like that incorporates the following using Snyk?

Running a Snyk Scan against the project repository
Here we run a "snyk test" from the root folder of the repository itself and that report is then

Building your Artifact
Here we use a Maven task which packages the application Artifact as a JAR file ready to run

Creating an OCI compliant container image from the Artifact itself
There are various ways to create a OCI compliant image but by the far the simplest is using Cloud Native Buildpacks and for this we use the pack CLI which in turns using the Java Buildpack from our JAR file directly avoid a compilation step from the source code given we already did that on the step above.

Running a Snyk Scan against the container image directly on the Container Registry
With our container image now in our Container Registry we can use "snyk container" to check for issues directly from the registry and also check for application security issues from the open source dependancies as well.

The finished Pipeline ...

azure-pipeline.yml Pipeline used in Azure DevOps

# Starter pipeline
# Start with a minimal pipeline that you can customize to build and deploy your code.
# Add steps that build, run tests, deploy, and more:
# https://aka.ms/yaml

- master

vmImage: ubuntu-latest


- task: Maven@3
mavenPomFile: 'pom.xml'
mavenOptions: '-Xmx3072m'
javaHomeOption: 'JDKVersion'
jdkVersionOption: '1.11'
jdkArchitectureOption: 'x64'
publishJUnitResults: true
options: "-Dskiptests -Dsnyk.skip"
goals: 'package'
displayName: "Build artifact JAR"
- task: SnykSecurityScan@0
serviceConnectionEndpoint: 'snyk-token'
testType: 'app'
monitorOnBuild: false
failOnIssues: false
displayName: "snyk test from source"
- task: Docker@2
containerRegistry: 'docker-pasapples-connection'
command: 'login'
displayName: "Login to DockerHub"

- script: |
curl -sSL "https://github.com/buildpacks/pack/releases/download/v0.19.0/pack-v0.19.0-linux.tgz" | tar -C ./ --no-same-owner -xzv pack
./pack build pasapples/springbootemployee:cnb-paketo-base --builder paketobuildpacks/builder:base --publish --path ./target/springbootemployee-0.0.1-SNAPSHOT.jar
displayName: 'Build Container with Pack'

- task: SnykSecurityScan@0
serviceConnectionEndpoint: 'snyk-token'
testType: 'container'
dockerImageName: 'pasapples/springbootemployee:cnb-paketo-base'
severityThreshold: 'low'
monitorOnBuild: false
failOnIssues: false
additionalArguments: "--app-vulns"
displayName: "snyk container scan from image"

More Information

So, for Container and Kubernetes security, designed to help developers find and fix vulnerabilities in cloud native applications, click the links below to learn more and get started today.

Snyk Container

Snyk Platform

Categories: Fusion Middleware

Hello world!

OraQA - Sun, 2021-06-13 02:39

Welcome to WordPress. This is your first post. Edit or delete it, then start writing!


Subscribe to Oracle FAQ aggregator