ORA-39035: Data filter SUBQUERY has already been specified | | Oracledbwr

To fix this error, where clause requires additional single quotes – like,

query="'where 1=1'"

When we export some tables with content=metadata_only and import, the table stats get locked by Oracle.

Post import, the stats will need to be unlocked.

How to exclude/ignore columns while importing – Ask TOM (oracle.com)

Impdp not failing even if target table have missing column – Ask TOM (oracle.com)

Convert CentOS to Oracle Linux

ORACLE-BASE Channel Cameos – Vol 4

Read-Only Partitions and Subpartitions in Oracle 12.2 Onward

Invisible Indexes in Oracle Database 11g Onward

Adaptive Cursor Sharing

CURSOR_SHARING : Automatically Convert Literals to Bind Variables in SQL Statements

Bind Variables : For Performance and Protection Against SQL Injection

DBMS_JOB Jobs Converted to DBMS_SCHEDULER Jobs in Oracle Database 19c Onward

Scalable Sequences in Oracle Database 18c Onward

Vagrant Oracle Real Application Clusters (RAC) Build

Temporal Validity in Oracle Database 12c Onward

Multitenant : Disk I/O (IOPS, MBPS) Resource Management for Pluggable Databases (PDBs)

Multitenant : Memory Resource Management for Pluggable Databases (PDBs)

Multitenant : Dynamic CPU Scaling – Resource Manager Control of CPU using CPU_COUNT & CPU_MIN_COUNT

Instance Caging to Manage CPU Usage

Simple Oracle Document Access (SODA) for SQLcl

Simple Oracle Document Access (SODA) for PL/SQL

Simple Oracle Document Access (SODA) for REST

Real-Time Statistics in Oracle Database 19c

Online Statistics Gathering for Bulk Loads

Multitenant : Running Scripts Against Multiple Pluggable Databases Using catcon.pl

APEXExport : Export APEX Applications and Workspaces From the Command Line

Resource Manager : SQL Quarantine in Oracle Database 19c Onward

Resource Manager : Runaway Query Management

SQLCL and Liquibase : Deploying Oracle Application Express (APEX) Applications

Hybrid Partitioned Tables in Oracle Database 19c

Liquibase : Deploying Oracle Application Express (APEX) Applications

APEX_DATA_PARSER : Convert simple CSV, JSON, XML and XLSX data to rows and columns

Podman : Generate and Play Kubernetes YAML Files

Using Podman With Existing Dockerfiles (Oracle Database and ORDS)

Install Podman on Oracle Linux 8 (OL8)

NTH_VALUE : Problem Solving using Analytic Functions

NTILE : Problem Solving using Analytic Functions

SQLcl : Format Query Results


Online Conversion of a Non-Partitioned Table to a Partitioned Table in Oracle 12.2 Onward

Invisible Columns in Oracle 12.1 Onward

Online Table Move Operations in Oracle 12.2 Onward

Online Segment Shrink for Tables : Free Unused Space

Kata Containers : Running Containers Inside Lightweight Virtual Machines on Oracle Linux 7 (OL7)

Returning REF CURSORs from PL/SQL : Functions, Procedures and Implicit Statement Results

SQLcl and Liquibase : Automating Your SQL and PL/SQL Deployments

Oracle : Secure External Password Store

Multitenant : Online Move of Datafiles in CDBs and PDBs

Oracle : Silent Installation and Database Creation

Multiable Inserts

Decoupling to Improve Performance

Schema Only Accounts in Oracle Database 18c Onward

Oracle REST Data Services (ORDS) : SQL Developer Web

Oracle REST Data Services (ORDS) : Including Hyperlinks in JSON Output

ORACLE-BASE Channel Cameos – Vol 3

ORACLE-BASE Channel Cameos – Vol 2

Oracle REST Data Services (ORDS) : HTTP Headers and ORDS-Specific Bind Variables

Oracle REST Data Services (ORDS) : RESTful Web Services Handling Media Files

Oracle REST Data Services (ORDS) : SQLcl and Oracle REST Data Services (ORDS)

Oracle REST Data Services (ORDS) : OAuth Implicit

Oracle REST Data Services (ORDS) : OAuth Authorization Code

Oracle REST Data Services (ORDS) : Database Authentication

Oracle REST Data Services (ORDS) : REST Enabled SQL

Oracle REST Data Services (ORDS) : First Party (Basic) Authentication on Tomcat

Oracle REST Data Services (ORDS) : OAuth Client Credentials Authorization

Oracle REST Data Services (ORDS) : Create Basic RESTful Web Services Using PL/SQL

Oracle REST Data Services (ORDS) : AutoREST

Docker : Docker Compose – Defining Multi-Container Applications

Docker : Oracle REST Data Services (ORDS) Build

Docker : Oracle Database Build

Install Docker on Oracle Linux 7 (OL7)

Vagrant : Oracle Database Build

Vagrant : A Beginner’s Guide

Oracle Linux 8 Installation

CORR : Problem Solving using Analytic Functions

AVG, MEDIAN, MIN and MAX : Problem Solving using Analytic Functions

FIRST_VALUE and LAST_VALUE : Problem Solving using Analytic Functions

LAG and LEAD : Problem Solving using Analytic Functions

Ranking using RANK, DENSE_RANK and ROW_NUMBER : Problem Solving using Analytic Functions

Multitenant : PDB Snapshot Carousel in Oracle Database 18c Onward

Multitenant : Proxy PDB in Oracle Database 12.2 Onward

Multitenant : Refreshable PDB Switchover in Oracle Database 18c Onward

Multitenant : PDB Refresh in Oracle Database 12.2 Onward

Multitenant : PDB Archive Files in Oracle Database 12.2 Onward

Multitenant : Relocate a Pluggable Database (PDB) in Oracle Database 12.2 Onward

Multitenant : Hot Clone a Pluggable Database (PDB) in Oracle Database 12.2 Onward

LISTAGG Analytic Function in Oracle Database

JSON_SERIALIZE Function in Oracle Database 19c Onward

JSON_MERGEPATCH Function in Oracle Database 19c Onward

JSON_EQUAL Condition in Oracle Database 18c Onward

Private Temporary Tables in Oracle Database 18c Onward

Converting Exact to Approximate Query Processing in Oracle Database 12.2 Onward

JSON_OBJECT Function Enhancements in Oracle Database 19c

SQL/JSON : Generate JSON from SQL in Oracle Database 12.2 Onward

Override External Table Parameters and Inline External Tables in Oracle Database

Real-Time Materialized Views in Oracle Database 12.2 Onward

Collation and Case-Insensitive Queries in Oracle Database

Extended Data Types in Oracle Database 12c Onward

JSON Data Guide in Oracle Database 12.2 Onward

Robot Tai Chi at Oracle OpenWorld 2017

Oracle OpenWorld 2017: EOUC Database ACES & Developer Champions

Bulgarian Oracle User Group (BGOUG) Spring 2017

#OracleCode : Prague

#OracleCode : London

OUG Ireland 2017

Table Point In Time Recovery (PITR) in Oracle Database 12c

Bulgarian Oracle User Group (BGOUG) 2016, Pravets

EOUC Database ACES Share Their Favorite Database Things #OOW16

Oracle Data Guard Broker

CASE Expressions and CASE Statements

OGh Oracle DBA & SQL Celebration Day

Indexing JSON Data in Oracle Database 12c

JSON Support in Oracle Database 12c

Flashback Table

Flashback Version Query

Flashback Query

SQL/XML (SQLX) : Generating XML using SQL in Oracle

XMLTABLE : Convert XML into Rows and Columns using SQL

SQL: The MERGE Statement

Amazon Web Services (AWS) : Relational Database Services (RDS) for SQL Server

Amazon Web Services (AWS) : Relational Database Services (RDS) for MySQL

Amazon Web Services (AWS) : Relational Database Services (RDS) for Oracle

Database as a Service (DBaaS) on Oracle Cloud

Oracle Linux Virtual Machine (VM) on Amazon Web Services (AWS)

SQL Server Databases on Microsoft Azure

Oracle Linux Virtual Machine (VM) on Micorosft Azure

Walking around UKOUG Tech15

Linux : Using a Yum Repository to Install MySQL

Linux : Installing Software Packages (RPM, YUM and DNF)

Oracle Linux 7 Installation

Memories of AIOUG Sangam15

Multitenant : Manage Tablespaces in a Container Database (CDB) and Pluggable Database (PDB)

Walking Around Oracle OpenWorld (OOW) 2015

Multitenant : Backup and Recover a Pluggable Database (PDB)

Multitenant : Clone a Remote Non-Container Database (CDB)

Multitenant : Clone a Remote Pluggable Database (PDB)

12 Oracle 12c Data Pump Enhancements

Making Dreams Come True : Video for a Superfan

Multitenant : Clone a Local Pluggable Database (PDB)

Multitenant : Manage Users and Roles for Container Databases (CDBs) and Pluggable Databases (PDBs)

Multitenant : Connecting to Container Databases (CDBs) and Pluggable Databases (PDBs)

Multitenant : Startup and Shutdown of Container Databases (CDBs) and Pluggable Databases (PDBs)

Multitenant : Unplug and Plugin a Pluggable Database (PDB)

Multitenant : Create a Pluggable Database (PDB)

SQL for Beginners (Part 10) : The DELETE and TRUNCATE TABLE Statements

SQL for Beginners (Part 9) : The UPDATE Statement

SQL for Beginners (Part 8) : The INSERT Statement

ORACLE-BASE Channel Cameos – Vol 1

SQL for Beginners (Part 7) : The GROUP BY Clause and HAVING Clause

SQL for Beginners (Part 6) : Set Operators

SQL for Beginners (Part 5) : Joins

SQL for Beginners (Part 4) : The ORDER BY Clause

SQL for Beginners (Part 3) : The WHERE Clause

SQL for Beginners (Part 2) : The FROM Clause

SQL for Beginners (Part 1) : The SELECT List

Efficient Function Calls From SQL (Part 6) : Function-Based Indexes

Efficient Function Calls From SQL (Part 5) : Pipelined Table Functions

Efficient Function Calls From SQL (Part 4) : Scalar Subquery Caching vs PL/SQL Function Result Cache

Efficient Function Calls From SQL (Part 3) : PL/SQL Function Result Cache

Efficient Function Calls From SQL (Part 2) : The DETERMINISTIC Hint

Efficient Function Calls From SQL (Part 1) : Scalar Subquery Caching

Join Elimination in Oracle


Read-Only Tables in Oracle

APPROX_COUNT_DISTINCT Function in Oracle Database 11g and 12c

Thoughts on Presenting

ALL, ANY and SOME Comparison Conditions in SQL

Session-Private Statistics for Global Temporary Tables (GTTs) in Oracle 12c

Session Sequences in Oracle 12c

Temporary Undo for Global Temporary Tables (GTTs) in Oracle 12c

Global Temporary Tables (GTT) in Oracle

Are you a Senior DBA or Developer?

Identifying Host Names and IP Addresses in SQL and PL/SQL using Built-in Oracle Functionality

Thoughts on Oracle Enterprise Manager Cloud Control 12c

PIVOT and UNPIVOT Operators in Oracle Database 11g

WITH Clause : PL/SQL Declaration Section in Oracle Database 12c

Thoughts on Upgrading to Oracle 12c

Audit Data Pump Operations in Oracle Database 12c

Multiple Indexes on the Same Set of Columns

Thoughts on Certification

Recursive Subquery Factoring : Hierarchical Queries Using Recursive WITH Clauses

Hierarchical Queries in Oracle (CONNECT BY)

AutoNumber, Identity and Using Sequences as Default Values in Oracle

External Tables: Querying Data From Flat Files in Oracle

NULL-Related Functions in Oracle

Thoughts on the Oracle Ace Program

DBMS_RANDOM : Generating Random Data (Numbers, Strings and Dates) in Oracle

UTL_MATCH : String Matching in Oracle

Top-N Queries Against an Oracle Database

Oracle Data Redaction

Method 1:-


Method 2:-

select * from table(dbms_xplan.display_cursor);

Method 3:-

right click in SQL Developer & ‘Explain Plan’

Read more:-

Explain plan cardinality and cost – Ask TOM (oracle.com)

How to Create an Execution Plan | Oracle All Things SQL Blog

The Magic of SQL – YouTube

SELECT task_name, status,count(1) FROM DBA_PARALLEL_EXECUTE_CHUNKS GROUP BY task_name, status order by status ;

$ lsnrctl stop

Now that the listener is stopped, log in to SQL*Plus as the SYSTEM user using the orcl service name to determine whether you can still connect to the database.

$ sqlplus system@orcl

$ lsnrctl start

$ lsnrctl status

A CDB includes the following components:

  • Root: The root, named CDB$ROOT, stores Oracle-supplied metadata and common users. An example of metadata is the source code for Oracle-supplied PL/SQL packages. A common user is a database user known in every container. A CDB has exactly one root.
  • Seed: The seed, named PDB$SEED, is a template that you can use to create new PDBs. You cannot add objects to or modify objects in the seed. A CDB has exactly one seed.
  • PDBs: A PDB appears to users and applications as if it were a non-CDB. For example, a PDB can contain the data and code required to support a specific application. A PDB is fully backward compatible with Oracle Database releases before Oracle Database 12c.

Each of these components is called a container. Therefore, the root is a container, the seed is a container, and each PDB is a container. Each container has a unique container ID and name within a CDB.

Environment variables


ORACLE_HOME= /scratch/u01/app/oracle/product/19.0.0/dbhome_1

Open a terminal window, execute the oraenv command to set the environment variables and connect to the database orcl.
$ . oraenv

ORACLE_SID = [oracle] ? orcl

The Oracle base has been set to /scratch/u01/app/oracle

Check if the database is a non container database.
select name, cdb, con_id from v$database;

Issue the SQL statement that returns the port that is configured for EM Express.
select instance_name, status, con_id from v$instance;

If a port number is not returned by this statement, then you must manually configure an HTTPS port for this CDB, as described in “Configuring the HTTPs Port”.
select dbms_xdb_config.gethttpsport() from dual;

Display the PDBs

select con_id, name, open_mode from v$pdbs;


alter pluggable database orclpdb open;

SELECT dbms_xdb_config.gethttpsport() from dual;

$ lsnrctl status

SQL> show parameter dispatchers;


Oracle Enterprise manager

In your Web browser, enter the URL to access Enterprise Manager: https://hostname:portnumber/em.

Note:Starting with Oracle Database 19c, Oracle EM Express, the default management option for Oracle Database, is based on Java JET technology. Currently, only the Performance menu is available in the 19c JET version. Therefore, we are using the 18c Flash Oracle EM Express in our tutorials. Use the following command to revert to Flash Oracle EM Express:

SQL> @?/rdbms/admin/execemx emx

To return to 19c JET Oracle EM Express, use the following command:

SQL> @?/rdbms/admin/execemx omx


The system responds:

User altered.

The HR account is unlocked and its password is password.

compose new post
go to top
go to the next post or comment
go to the previous post or comment
toggle comment visibility
cancel edit post or comment
<span>%d</span> bloggers like this: