Skip navigation.

DBA Scripts and Articles

Syndicate content
This blog is intended to share my knowledge on Oracle and various scripts that I use as an oracle DBA.
Updated: 1 day 2 hours ago

Top 5 wait events from v$active_session_history

Mon, 2014-11-24 09:30

This query returns the top 5 wait events for the last hour from v$active_session_history view. Be careful, this view is part of the diagnostic pack, you should not query this view if you don’t have license for it. Top 5 wait events from v$active_session_history [crayon-547411b39d0be279072425/] This is obviously an approximation, because v$active_session_history contains only 1 second samples, […]

The post Top 5 wait events from v$active_session_history appeared first on Oracle DBA Scripts and Articles (Montreal).

Categories: DBA Blogs

OCP 12C – Oracle Data Redaction

Mon, 2014-10-27 07:00

What is Oracle Data Redaction ? Oracle Data Redaction is meant to mask (redact) sensitive data returned from application queries. Oracle Data Redaction doesn’t make change to data on disk, the sensitive data is redacted on the fly before it is returned to the application. You can redact column data by using one of the following methods: Full […]

The post OCP 12C – Oracle Data Redaction appeared first on Oracle DBA Scripts and Articles (Montreal).

Categories: DBA Blogs

OCP 12C – Resource Manager and Performance Enhancements

Fri, 2014-10-24 07:16

Use Resource Manager for a CDB and a PDB Managing Resources between PDBs The Resource Manager uses Shares ans Utilization limit to manage resources allocated to PDBs. The more “Shares” you allocate to a PDB, the more resource it will have. Shares are allocated through DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN_DIRECTIVE. One directive can only concern one PDB and you can’t […]

The post OCP 12C – Resource Manager and Performance Enhancements appeared first on Oracle DBA Scripts and Articles (Montreal).

Categories: DBA Blogs

OCP 12C – SQL Enhancements

Thu, 2014-10-23 08:20

Extended Character Data Type Columns In this release Oracle changed the maximum sixe of three data types  In Oracle 12c if you set a VARCHAR2 to 4000 bytes or less it is stored inline, if you set it to more than 4000 bytes then it is transformed in extended character data type and stored out […]

The post OCP 12C – SQL Enhancements appeared first on Oracle DBA Scripts and Articles (Montreal).

Categories: DBA Blogs

OCP 12C – DataPump, SQL*Loader, External Tables Enhancements

Wed, 2014-10-22 13:57

Oracle DataPump Enhancements Full Transportable Export and Import of Data In Oracle 12c you now have the possibility to create full transportable exports and imports. A full transportable export contains all objects and data needed to create a copy of the database. To create a fully transportable export of your database you need to specify […]

The post OCP 12C – DataPump, SQL*Loader, External Tables Enhancements appeared first on Oracle DBA Scripts and Articles (Montreal).

Categories: DBA Blogs

OCP 12C – Partitioning Enhancements

Wed, 2014-10-22 09:13

Online Partition operations Table Partitions and subpartitions can now be moved online. [crayon-547411b39ec05116195423/] Compression options can also be added during an online partition move. [crayon-547411b39ec10799997252/] Reference Partitioning Enhancements Truncate or Exchange Partition with Cascade option With Oracle 12c, it is now possible to use the CASCADE option to cascade operations to a child-referenced table when […]

The post OCP 12C – Partitioning Enhancements appeared first on Oracle DBA Scripts and Articles (Montreal).

Categories: DBA Blogs

OCP 12C – Index and Table Enhancements

Tue, 2014-10-21 07:16

Table Enhancements Oracle 12c offers you to create invisible columns, these columns are not visible until you explicitly mention their names in the SQL statement. This functionnality allows developpers to make change to the database without conflicting with the existing application. To create an invisible column: [crayon-547411b39f66a047124531/]  You can’t create invisible columns on : External […]

The post OCP 12C – Index and Table Enhancements appeared first on Oracle DBA Scripts and Articles (Montreal).

Categories: DBA Blogs

OCP 12C – ADR and Network Enhancements

Mon, 2014-10-20 11:59

ADR enhancements In oracle 12c the Automatic Diagnostic Repository contains a new log directory with 2 subdirectories : DDL Debug The DDL log When you active the DDL logging in Oracle 12c using enable_ddl_logging=true, Oracle writes all DDL operations into the specific DDL log instead of writting it to the alert log. The DDL logging feature […]

The post OCP 12C – ADR and Network Enhancements appeared first on Oracle DBA Scripts and Articles (Montreal).

Categories: DBA Blogs

OCP 12C – Emergency Monitoring, Real-Time ADDM

Mon, 2014-10-20 11:48

Emergency Monitoring Emergency monitoring is meant for extreme circumstances where it’s impossible for you to connect to the database because the database is hung. Emergency monitoring allows you to connect to the database in diagnostic mode and run a lightweight analysis to see what’s happening. You can access real-time performance data from ASH and access […]

The post OCP 12C – Emergency Monitoring, Real-Time ADDM appeared first on Oracle DBA Scripts and Articles (Montreal).

Categories: DBA Blogs

OCP 12C – SQL Tuning

Fri, 2014-10-10 12:23

What’s new ? Oracle 12c introduces a major update called Adaptive Query Optimization which is based on : Adaptive execution plans Adaptive Statistics These two functionnalities are used to improve execution plans by using dynamic statistics gathered during the first part of the SQL execution. This allow to create more efficient plans that those using […]

The post OCP 12C – SQL Tuning appeared first on Oracle DBA Scripts and Articles (Montreal).

Categories: DBA Blogs

OCP 12C – Real-Time Database Operation Monitoring

Fri, 2014-10-03 09:34

What is Real Time Database Operation Monitoring ? Real Time Database Operation Monitoring will help you track the progress of a set of sql statements and let you create a report. Real Time Database Operation Monitoring acts as a superset of all monitoring components like : ASH, DBMS_MONITOR … You can generate Active Reports which are […]

The post OCP 12C – Real-Time Database Operation Monitoring appeared first on Oracle DBA Scripts and Articles (Montreal).

Categories: DBA Blogs

OCP 12C – RMAN and Flashback Data Archive

Thu, 2014-10-02 14:36

RMAN Enhancements New Privilege A new SYSBACKUP privilege is created in Oracle 12c,  it allows the grantee to perform BACKUP and RECOVERY operations with RMAN SQL in RMAN You can now use SQL Statements in RMAN like you would do in SQL*PLUS : BEFORE : RMAN> SQL “alter system switch logfile”; NOW : RMAN> alter system switch logfile; [...]

The post OCP 12C – RMAN and Flashback Data Archive appeared first on Oracle DBA Scripts and Articles (Montreal).

Categories: DBA Blogs

OCP 12C – Privileges

Thu, 2014-10-02 13:27

User Task-Specific Administrative Privileges To continue with the objective of separating duties and the least privileges, Oracle 12c introduce new administratives privileges all destinated to accomplish specific duties: SYSBACKUP : Used for RMAN operations like BACKUP, RESTORE, RECOVER SYSDG : Used to administer DATAGUARD, In 12c when you use DGMGRL commandline interface your are automatically [...]

The post OCP 12C – Privileges appeared first on Oracle DBA Scripts and Articles (Montreal).

Categories: DBA Blogs

OCP 12C – Auditing

Thu, 2014-10-02 09:57

Unified Audit Data Trail Unifed Auditing offers a consolidated approach, all the audit data is consolidated in a single place. Unified Auditing consolidate audit records for the following sources : Standard Auditing Fine-grained auditing (DBMS_FGA) RAC security auditing RMAN auditing Database Vault auditing Oracle Label Security auditing Oracle Data Mining Oracle Data Pump Oracle SQL*Loader In [...]

The post OCP 12C – Auditing appeared first on Oracle DBA Scripts and Articles (Montreal).

Categories: DBA Blogs

OCP 12C – In Database Archiving and Temporal Validity

Wed, 2014-10-01 13:14

In Database Archiving In Database Archiving is a new feature of Oracle 12c meant to solve management of historical data inside the database. Like its name says, this functionnality leaves data into the database, so it remains accessible if you need it. With In Database Archiving, historical data remains in the database but is invisible to [...]

The post OCP 12C – In Database Archiving and Temporal Validity appeared first on Oracle DBA Scripts and Articles (Montreal).

Categories: DBA Blogs

OCP 12C – Information Lifecycle Management and Storage Enhancements

Wed, 2014-10-01 10:16

Information Lifecycle Management Before reading this flashcard I recommend you to read my article on ILM. Automatic Data Optimization (ADO) Policy based data management Let you define policies at : Tablespace level Segment level Row level Policies can either compress data or move data to a different tablespace Policies let you define when, what and [...]

The post OCP 12C – Information Lifecycle Management and Storage Enhancements appeared first on Oracle DBA Scripts and Articles (Montreal).

Categories: DBA Blogs

OCP 12C – Backup, Recovery and Flashback for a CDB/PDB

Wed, 2014-10-01 07:43

Backup a CDB/PDB To make a database backup you need the SYSBACKUP or SYSDBA privilege. You can backup the CDB and all the PDBs independantly, all together, or by specifying a list. You can backup a PDB by connecting directly to it and use: RMAN> BACKUP DATABASE: You can backup a PDB by connecting to [...]

The post OCP 12C – Backup, Recovery and Flashback for a CDB/PDB appeared first on Oracle DBA Scripts and Articles (Montreal).

Categories: DBA Blogs

OCP 12C – Managing CDBs and PDBs

Tue, 2014-09-30 10:13

Connecting to a CDB or PDB Connecting to a CDB or PDB is not different of connecting to a standard database. When you create a PDB, it is automatically assigned a service which can be used to reach this specific PDB. You can either use the EZ connect syntax, the Oracle Net Service syntax or the [...]

The post OCP 12C – Managing CDBs and PDBs appeared first on Oracle DBA Scripts and Articles (Montreal).

Categories: DBA Blogs

OCP 12C – Basics of Multitenant Container Database (CDB)

Mon, 2014-09-29 14:42

The multitenant architecture Needs enterprise edition and the multitenant option. Consists of a CDB (Container database) and zero, one or up to 252 PDBs (pluggable databases). Has a root container (the CDB itself) and a seed container (template to create PDBs) There is only one instance per CDB. A PDB doesn’t have : background processes [...]

The post OCP 12C – Basics of Multitenant Container Database (CDB) appeared first on Oracle DBA Scripts and Articles (Montreal).

Categories: DBA Blogs

OCP 12C – Enterprise Manager and Other Tools

Mon, 2014-09-29 11:20

EM Database Express The old Enterprise Manager database control is replaced by Enterprise Manager database express in Oracle 12c. It contains only basic administration capabilities as the advanced ones are included in Enterprise Manager cloud control With EM database Express you can manage Security (Users, roles, profiles), Configuration (Instance Parameters, memory, database features), Storage (tablespaces, [...]

The post OCP 12C – Enterprise Manager and Other Tools appeared first on Oracle DBA Scripts and Articles (Montreal).

Categories: DBA Blogs