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.
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
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
View Expansion : DBMS_UTILITY.EXPAND_SQL_TEXT
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
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
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
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
CASE Expressions and CASE Statements
OGh Oracle DBA & SQL Celebration Day
Indexing JSON Data in Oracle Database 12c
JSON Support in Oracle Database 12c
SQL/XML (SQLX) : Generating XML using SQL in Oracle
XMLTABLE : Convert XML into Rows and Columns using SQL
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
Linux : Using a Yum Repository to Install MySQL
Linux : Installing Software Packages (RPM, YUM and DNF)
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
GROUP BY, ROLLUP and CUBE in Oracle
APPROX_COUNT_DISTINCT Function in Oracle Database 11g and 12c
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
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
Method 1:-
explain plan for SELECT * FROM REGIONS WHERE REGION_ID =1;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());
Method 2:-
SELECT * FROM REGIONS WHERE REGION_ID =1;
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
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_OWNER= oracle
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 SESSION SET CONTAINER=orclpdb;
alter pluggable database orclpdb open;
SELECT dbms_xdb_config.gethttpsport() from dual;
$ lsnrctl status
SQL> show parameter dispatchers;
exec DBMS_XDB_CONFIG.SETHTTPSPORT(5501);
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
ALTER USER HR ACCOUNT UNLOCK IDENTIFIED BY password;
The system responds:
User altered.
The HR account is unlocked and its password is password.
