DBORCLOUD Blog

Oracle Database / Oracle Errors

Ora-19025

When you work with xml file you should be solve the scenery that have an node with its properly sub node. In our case the following nodes () ; () . It is quite delicate find exactly approach that gives to you the requirement expected. The problems that I have got they were two different: – Duplicate row – ORA-19025 EXTRACTVALUE returns value of only one node or ORA-19279: XPTY0004 – XQuery dynamic type mismatch: expected singleton sequence – got multi-item sequence The following image shows the scenery described: Duplicate row means the query extract the values that are not...

Oracle Database

Oracle Database 18c: My preferred new features for DBAs

Oracle Databse 18c comes with some cool new features for DBAs. In this article I’ll list my preferred ones. Just one note: I think the name “Autonomous Database” is quite misleading, as it means that some of the new features COULD be used to configure your database as “autonomous” where previous versions required some maintenance, and this will be the direction of the next Oracle Database generations. 1. PRIVATE TEMPORARY TABLES You can create temporary tables with these characteristics: – NAME must be prefixed by “ORA$PTT_” (or the current value of PRIVATE_TEMP_TABLE_PREFIX initialization parameter) – DATA is automatically deleted at...

Oracle Database / Oracle Database 12 c / Uncategorized

Create Sequence-Oracle Database 12c

Database version : from Oracle Database 12cR1 Create sequence with session or global option When a sequence number is generated, the sequence is incremented, independent of the transaction committing or rolling back. SESSION option makes opportunity to create sequence that can used in association with global temporary table then its visibility is just for session. The following options CACHE, NOCACHE, ORDER, or NOORDER are ignored when SESSION is declared. 1) statement SQL> CREATE GLOBAL TEMPORARY TABLE glb_tmp_table (a number); Table created. SQL> CREATE SEQUENCE sq_sess SESSION; Sequence created. 2) Script execution SQL> INSERT INTO glb_tmp_table VALUES (sq_sess.NEXTVAL); 1 row created....

Oracle Database

LIST-PARTITIONED

Introducing the topic: In list partitioning, the database uses a list of discrete values as the partition key for each partition. You can use list partitioning to control how individual rows map to specific partitions. By using lists, you can group and organize related sets of data when the key used to identify them is not conveniently ordered. Here an example: 1. This example shows how the index works Assume that you create list_sales as a list-partitioned table using the following statement. The channel_id column is the partition key. check partition: Table Partition HIGH_VALUE Table Partition MEDIUM_VALUE Table Partition LOWER_VALUE...

Oracle Database / Oracle Database 12 c

ORACLE 12 rel.2 – Partitioning new features

In Oracle 12c rel.2 a great number of enhancements to the Oracle database Partitioning option have been introduced. These include: Automatic List Partitioning Filtered Partition maintenance operations Multi-Column List Partitioning Online conversion of non-partitioned to partitioned table Read-only Partitions Partitioned External Tables Automatic List Partitioning: The ability to partition a table based on distinct values in a column has been around since quite some time, but what is new in Oracle database 12.2 is that we can initially partition the table based on values that are known and present in the column we would like to partition the table on....

Oracle Database

ORACLE 18C – Autonomous Database Cloud

At Oracle OpenWorld 2017, Oracle Chairman of the Board and CTO Larry Ellison unveiled his vision for the world’s first autonomous database cloud. Powered by Oracle Database 18c, the next generation of the industry-leading database, Oracle Autonomous Database Cloud uses ground- breaking machine learning to enable automation that eliminates human labor, human error and manual tuning, to enable unprecedented availability, high performance and security at a much lower cost. “This is the most important thing we’ve done in a long, long time” said Ellison. “The automation does everything. We can guarantee availability of 99.995 percent, less than 30 minutes of planned...

Oracle Database 12 c

ORACLE 12C M.T.S. model

Until Oracle Database 12c, Oracle processes did not run as threads on UNIX and Linux systems. Every dedicated connection was represented by an operating system process. In systems where high workload was an issue, the only way to reduce CPU and memory usage was to switch from dedicated connections to shared connections using the multithreaded server (MTS) feature. Starting in Oracle Database 12c the multithreaded database model was introduced, enabling database processes to be executed as operating system threads and reducing the number of dedicated process necessary to manage database services. By default, every new 12c instance is created to...

Oracle Database

MODIFICA SQLPROMPT CONTAINER E PLUGGABLE DATABASE

Per impostare il nome dell’istanza sul prompt della macchina e contestualmente il nome utente e il nome dell’istanza in sqlplus eseguiamo con le seguenti modifiche: ENV PDB – Modificare l’export dell’istanza nell’env del PDB inserendo il nome del pluggable database export ISTANZA=RELAUP – Cambiare nell’export della variabile PS1 ORACLE_SID con ISTANZA export PS1='\u@${HOSTNAME}:${ISTANZA} # ' – Inserire l’export della variabile SQLPATH export SQLPATH=/home/oracle12/cfg ENV CDB – Inserire l’unset del SQLPATH, perchè se rimene settata ci si connette al PDB perché esegue il file login.sql all’interno della directory SQLPATH unset SQLPATH Di seguito gli env di GRELAUP e RELAUP: oracle12@lradbpvm01:RELAUP #...

Oracle Database

2 PHASE COMMIT OVERVIEW

Two-phase commit protocol is comprised of a set of actions that ensure a transaction involving multiple databases does not produce unsynchronized updates. Two-phase commit provides a way for a series of database interactions on multiple different data sources to be grouped together and completed or rolled back as a single transaction. Two-phase commit transactions that represent a series of database interactions on multiple data sources are referred to as global transactions. At the beginning of a global transaction, a global transaction ID (XID) is generated and used by an external transaction manager to drive the two-phase commit processing across all...

Oracle Database

2 PHASE COMMIT OVERVIEW

Two-phase commit protocol is comprised of a set of actions that ensure a transaction involving multiple databases does not produce unsynchronized updates. Two-phase commit provides a way for a series of database interactions on multiple different data sources to be grouped together and completed or rolled back as a single transaction. Two-phase commit transactions that represent a series of database interactions on multiple data sources are referred to as global transactions. At the beginning of a global transaction, a global transaction ID (XID) is generated and used by an external transaction manager to drive the two-phase commit processing across all...