E.g: CREATE TABLESPACE securespace DATAFILE ‘/u01/app/oracle/oradata/techdatabasket/secure01.dbf’ SIZE 100M ENCRYPTION USING ‘AES256’ DEFAULT STORAGE(ENCRYPT); VIEWS: V$ENCRYPTED_TABLESPACE DBA_TABLESPACES USER_TABLESPACES Database Administration -> Database Administrator’s Guide -> 13.2.5 Encrypted Tablespaces Oracle Database Administrator’s Guide -> 20 Managing Tables -> 20.2.13 Consider Encrypting Columns That Contain Sensitive Data brunorsHi! I am Bruno, a Brazilian born and bred, and I am also a naturalized Swedish citizen. I am a former Oracle ACE and, to keep up with academic research, I am a Computer Scientist with an MSc in Data Science and another MSc in Software…
Month: March 2019
[OCM 12C] Managing additional buffer cache
“The In-Memory Column Store (IM column store) is an optional portion of the system global area (SGA) that stores copies of tables, table partitions, and other database objects. In the IM column store, data is populated by column rather than row as it is in other parts of the SGA, and data is optimized for rapid scans. The IM column store is included with the Oracle Database In-Memory option.” Configure the database to use IM Column Store; (https://www.techdatabasket.com/enabling-oracle-database-in-memory-database-in-memory-option-in-oracle-database-12c-release-1/) Setup some tables to use the IM Column Store; (https://www.techdatabasket.com/enabling-and-disabling-table-for-the-im-column-store-in-oracle-12c/) Datapump from…
[OCM 12C] Managing a fast refreshable materialized view
“Performing a refresh operation requires temporary space to rebuild the indexes and can require additional space for performing the refresh operation itself.” “Not all materialized views may be fast refreshable. Therefore, use the package DBMS_MVIEW.EXPLAIN_MVIEW to determine what refresh methods are available for a materialized view.” Refresh methods: Incremental : Two types: logg-based refresh and partition change tracking (PCT). “The incremental refresh is commonly called FAST refresh as it usually performs faster than the complete refresh.” Complete refresh DBMS_MVIEW package DBMS_MVIEW.REFRESH DBMS_MVIEW.REFRESH_ALL_MVIEWS DBMS_MVIEW.REFRESH_DEPENDENT Traditional methods: FAST (‘F’), COMPLETE (‘C’), PCT…
Using DATAPUMP utility to import tables with In-Memory Column Store in Oracle 12c
In this post I am going to show you how to perform an import using Datapump utility from one database to another with the purpose being to use IM Column Store through the export and import of one table called TECHDATABASKETBLOG under the schema SH. Therefore, the first step is to have your database configured to use IM Column Store (if not, check out : https://www.techdatabasket.com/enabling-oracle-database-in-memory-database-in-memory-option-in-oracle-database-12c-release-1/) and the second step is to have some table configured to use it (if not, check out: https://www.techdatabasket.com/enabling-and-disabling-table-for-the-im-column-store-in-oracle-12c/) . After this you are ready…
Enabling and disabling table for the IM column store in Oracle 12c
In order to Enable or Disable the IM Column Store for Tables, your database must be with the Oracle Database In-Memory (Database In-Memory) option enabled. If not, see this post : https://www.techdatabasket.com/enabling-oracle-database-in-memory-database-in-memory-option-in-oracle-database-12c-release-1/ According to Oracle, “You enable a table for the IM column store by including an INMEMORY clause in a CREATE TABLE or ALTER TABLE statement. You disable a table for the IM column store by including a NO INMEMORY clause in a CREATE TABLE or ALTER TABLE statement.”. Then, in this post I am going to show you…
Enabling Oracle Database In-Memory (Database In-Memory) option in Oracle Database 12c Release 1
According to Oracle documentation “Oracle Database In-Memory (Database In-Memory) is a suite of features, first introduced in Oracle Database 12c Release 1 (12.1.0.2), that greatly improves performance for real-time analytics and mixed workloads”. Therefore, in this post I will explain how to enable your Oracle Database to use the In-Memory option : 1- Be sure that your database is running on version level 12.1.0 or higher: 2- Set the parameter INMEMORY_SIZE to any value at about 100M (minimum setting): 3- Restart the database: brunorsHi! I am Bruno, a Brazilian born…
[OCM 12C] Tuning SQL statements
DBMS_STATS.CREATE_EXTENDED_STATSDBMS_STATS.GATHER_TABLE_STATS PARAMETER OPTIMIZER_MODE Database SQL Tuning Guide -> 14 Influencing the Optimizer brunorsHi! I am Bruno, a Brazilian born and bred, and I am also a naturalized Swedish citizen. I am a former Oracle ACE and, to keep up with academic research, I am a Computer Scientist with an MSc in Data Science and another MSc in Software Engineering. I have over ten years of experience working with companies such as IBM, Epico Tech, and Playtech across three different countries (Brazil, Hungary, and Sweden), and I have joined projects remotely…
[OCM 12C] Managing SQL Plan Baselines
“SQL plan management is a preventative mechanism that enables the optimizer to automatically manage execution plans, ensuring that the database uses only known or verified plans.” SPM has three main components: • Plan Capture: OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = TRUE (Automatic Initial Plan Capture), OPTIMIZER_USE_SQL_PLAN_BASELINE=TRUE • Plan Selection • Plan Verification/Evolution dba_sql_plan_baselines DBA_SQL_PLAN_BASELINE Database Administration -> Database SQL Tuning Guide -> 23 Managing SQL Plan Baselines brunorsHi! I am Bruno, a Brazilian born and bred, and I am also a naturalized Swedish citizen. I am a former Oracle ACE and, to keep up…
[OCM 12C] Configuring the Resource Manager
Good to user with Enterprise Manager Cloud Control. “Oracle Database Resource Manager (the Resource Manager) enables you to manage multiple workloads within a database that are contending for system and database resources.” “You can examine these statistics using real-time SQL monitoring and the Resource Manager dynamic performance views (the V$RSRC_* views).” “Oracle Database provides a procedure (CREATE_SIMPLE_PLAN) that enables you to quickly create a simple resource plan.” Views: V$SESSION V$RSRC_* views) Steps to create a complex plan: Step 1: Create a pending area. Step 2: Create, modify, or delete consumer…
Retrieving a dropped table on Oracle 12c
Sometimes developers need to recover some tables and we as Oracle DBAs are asked to recover them. So in this post I am going to show you how do to that. I will be creating the table named TECHDATABASKET under the SH user using the dba_tables as example and afterwards I am going to use the syntax “FLASHBACK TABLE SH.TECHDATABASKET TO BEFORE DROP;” in order to recover this table. In the meantime, we need to execute a SELECT key on the table dba_recyclebin or simply the usage of the command…