Download - OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs
![Page 1: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/1.jpg)
Oracle Database 12c
The Best Oracle Database 12c New Features for Developers and
DBAs
Presented by: Alex Zaballa, Oracle DBA
![Page 2: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/2.jpg)
Alex Zaballa
http://alexzaballa.blogspot.com/
@alexzaballa206 and counting…https://www.linkedin.com/in/alexzaballa
![Page 3: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/3.jpg)
Worked for 7 years in Brazil as an Oracle Developer.2000 - 2007
Worked for 8 years in Angola as an Oracle DBAfor the Ministry of Finance.2007 - 2015
![Page 4: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/4.jpg)
![Page 5: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/5.jpg)
Oracle Database 12c
New Features for Developers and DBAs
![Page 6: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/6.jpg)
Oracle Official Documentation 12.1.0.2
• http://docs.oracle.com/database/121/NEWFT/chapter12102.htm
Oracle Learning Library (OLL)
• https://apexapps.oracle.com/pls/apex/f?p=44785:1:0
![Page 7: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/7.jpg)
Articles about 12c
• https://oracle-base.com/articles/12c/articles-12c
![Page 8: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/8.jpg)
“With more than 500 new features, Oracle Database 12c is designed to give Oracle customers exactly what they’ve told us they need for cloud computing, big data, security, and availability.”
![Page 9: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/9.jpg)
JSON
OTN Article by Alex Zaballa
http://www.oracle.com/technetwork/pt/articles/sql/json-oracle-database-12c-2378776-ptb.html
![Page 10: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/10.jpg)
JSON
• Oracle Database 12.1.0.2 has now native support for JSON.
• “JSON (JavaScript Object Notation) is a lightweight data-interchange format. It is easy for humans to read and write. It is easy for machines to parse and generate.”
Source: http://json.org/
![Page 11: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/11.jpg)
JSON
![Page 12: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/12.jpg)
JSON
![Page 13: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/13.jpg)
DEMO
![Page 14: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/14.jpg)
Data Redaction
OTN Article in English by Alex Zaballa
http://www.oracle.com/technetwork/articles/database/data-redaction-odb12c-2331480.html
![Page 15: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/15.jpg)
Data Redaction
• One of the new features introduced in Oracle Database 12c
• Part of the Advanced Security option• Enables the protection of data shown to the
user in real time, without requiring changes to the application
![Page 16: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/16.jpg)
Data Redaction
![Page 17: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/17.jpg)
Data Redaction
![Page 18: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/18.jpg)
DEMO
![Page 19: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/19.jpg)
SQL Query Row Limits and Offsets
![Page 20: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/20.jpg)
SQL Query Row Limits and Offsets
![Page 21: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/21.jpg)
SQL Query Row Limits and Offsets
Top-N Queries – Pré 12c
![Page 22: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/22.jpg)
SQL Query Row Limits and Offsets
![Page 23: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/23.jpg)
SQL Query Row Limits and Offsets
![Page 24: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/24.jpg)
SQL Query Row Limits and Offsets
![Page 25: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/25.jpg)
DEMO
![Page 26: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/26.jpg)
PL/SQL From SQLwith function Is_Number (x in varchar2) return varchar2 is Plsql_Num_Error exception; pragma exception_init(Plsql_Num_Error, -06502); begin if (To_Number(x) is NOT null) then return 'Y'; else return ''; end if; exception when Plsql_Num_Error then return 'N'; end Is_Number;select rownum, x, is_number(x) is_num from t;
![Page 27: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/27.jpg)
DEMO
![Page 28: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/28.jpg)
Session Level Sequences
Session level sequences are used to produce unique values in a session. Once the session ends, the sequence is reset. Generating Primary Keys for a Global Temporary Table would be a field where those kinds of sequences could be used.
![Page 29: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/29.jpg)
Session Level Sequences
CREATE SEQUENCE sequence_test START WITH 1 INCREMENT BY 1 SESSION/
![Page 30: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/30.jpg)
Session Level Sequences
ALTER SEQUENCE sequence_testSESSION;
ALTER SEQUENCE sequence_testGLOBAL;
![Page 31: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/31.jpg)
DEMO
![Page 32: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/32.jpg)
Temporary Undo
Global Temporary Tables (GTT) hold the data in a temporary tablespace. The data in GTTs are either deleted after commit or kept until the session is connected depending of the definition of the GTT.(ON COMMIT PRESERVE OR DELETE ROWS ).
DMLs in a Global Temporary Tables do not generate REDO, but generate UNDO and this will result in REDO generating.
![Page 33: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/33.jpg)
Temporary Undo
alter session set temp_undo_enabled=true;
**you can change for the session or for the database.
![Page 34: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/34.jpg)
DEMO
![Page 35: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/35.jpg)
Statistics During Loads
The ability to gather statistics automatically during bulk loads: - CREATE TABLE AS SELECT - INSERT INTO ... SELECT into an empty table using a direct path insert
![Page 36: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/36.jpg)
DEMO
![Page 37: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/37.jpg)
SQL*Loader Express
• You don't need to to write and test a SQL*Loader control file.
• The benefit main is the savings for time and effort.
![Page 38: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/38.jpg)
SQL*Loader Express
[oracle@oracle01 tmp]$ cat EMP_TEST.dat 1,Emp 12,Emp 23,Emp 34,Emp 45,Emp 56,Emp 67,Emp 78,Emp 89,Emp 9
![Page 39: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/39.jpg)
SQL*Loader Express
[oracle@oracle01 tmp]$ sqlldr teste/teste TABLE=EMP_TESTSQL*Loader: Release 12.1.0.1.0 - Production on Sat Jan 11 12:16:28 2014Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.Express Mode Load, Table: EMP_TESTPath used: External Table, DEGREE_OF_PARALLELISM=AUTO
Table EMP_TEST: 9 Rows successfully loaded.
Check the log files: EMP_TEST.log EMP_TEST_%p.log_xtfor more information about the load.
![Page 40: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/40.jpg)
DEMO
![Page 41: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/41.jpg)
Extended Data Types
SQL> create table table_test(column01 varchar2(4001));*ERROR at line 1:ORA-00910: specified length too long for its datatype
![Page 42: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/42.jpg)
Extended Data Types
- VARCHAR2 : 32767 bytes- NVARCHAR2 : 32767 bytes- RAW : 32767 bytes
![Page 43: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/43.jpg)
Extended Data Types
SHUTDOWN IMMEDIATE;STARTUP UPGRADE;ALTER SYSTEM SET max_string_size=extended;@?/rdbms/admin/utl32k.sqlSHUTDOWN IMMEDIATE;STARTUP;
**Once you switch to extended data types you can't switch back
![Page 44: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/44.jpg)
DEMO
![Page 45: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/45.jpg)
READ Object Privilege and READ ANY TABLE System Privilege
What is the difference to SELECT and SELECT ANY TABLE?
![Page 46: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/46.jpg)
READ Object Privilege and READ ANY TABLE System Privilege
SELECT and SELECT ANY TABLE provides the ability to lock rows:
LOCK TABLE table_name IN EXCLUSIVE MODE;SELECT ... FROM table_name FOR UPDATE;
![Page 47: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/47.jpg)
READ Object Privilege and READ ANY TABLE System Privilege
SQL> grant select on scott.emp to teste;Grant succeeded.
SQL> lock table scott.emp in exclusive mode;Table(s) Locked.
![Page 48: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/48.jpg)
READ Object Privilege and READ ANY TABLE System Privilege
SQL> grant read on scott.emp to teste;Grant succeeded.
SQL> lock table scott.emp in exclusive mode;lock table scott.emp in exclusive mode *ERROR at line 1:ORA-01031: insufficient privileges
![Page 49: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/49.jpg)
DEMO
![Page 50: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/50.jpg)
Invisible ColumnsCREATE TABLE table_test_inv( column1 NUMBER, column2 NUMBER, column3 NUMBER INVISIBLE, column4 NUMBER);
SQL> desc table_test_invName ----------------------------------------- COLUMN1 NUMBERCOLUMN2 NUMBERCOLUMN4 NUMBER
![Page 51: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/51.jpg)
Invisible Columns
INSERT INTO table_test_inv (column1,column2,column3,column4) VALUES (1,2,3,4);
INSERT INTO table_test_inv VALUES (1,2,4);
![Page 52: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/52.jpg)
Invisible Columns
SET COLINVISIBLE ON
SQL> desc table_test_invName ----------------------------------------- COLUMN1 NUMBERCOLUMN2 NUMBERCOLUMN4 NUMBERCOLUMN3 (INVISIBLE) NUMBER
![Page 53: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/53.jpg)
Invisible Columns
ALTER TABLE table_test_inv MODIFY column3 VISIBLE;
WHY ?
You are preparing the changes on the database, but the application is not prepared yet.
Select * from …...
Insert into TABLE VALUES (......, ....., .....)
![Page 54: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/54.jpg)
DEMO
![Page 55: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/55.jpg)
SQL Text Expansion
SQL> variable retorno clobSQL> begin dbms_utility.expand_sql_text( input_sql_text => 'select * from emp', output_sql_text=> :retorno );end;
![Page 56: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/56.jpg)
SQL Text Expansion
• Views• VPDs
![Page 57: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/57.jpg)
DEMO
![Page 58: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/58.jpg)
Multiple Indexes on the same set of Columns
Pre 12c:
ORA-01408: such column list already indexed error.
![Page 59: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/59.jpg)
Multiple Indexes on the same set of Columns
Is the ability to create more than one index on the same set of columns in 12c.
**Only one of these indexes can be visible at a time
![Page 60: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/60.jpg)
Multiple Indexes on the same set of Columns
Why would you want to do that?
• Unique versus nonunique
• B-tree versus bitmap
• Different partitioning strategies
![Page 61: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/61.jpg)
DEMO
![Page 62: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/62.jpg)
Partial Indexes for Partitioned Table
• You can create local and global indexes on a subset of the partitions of a table, enabling more flexibility in index creation.
• This feature is not supported for unique indexes, or for indexes used for enforcing unique constraints.
![Page 63: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/63.jpg)
Partial Indexes for Partitioned Table
![Page 64: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/64.jpg)
DEMO
![Page 65: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/65.jpg)
Truncate Cascade
SQL> truncate table scott.dept;truncate table scott.dept *ERROR at line 1:ORA-02266: unique/primary keys in table referenced by enabled foreign keys
![Page 66: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/66.jpg)
Truncate Cascade
SQL> truncate table scott.dept cascade;Table truncated.
The constraint should be ON DELETE CASCADE.
![Page 67: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/67.jpg)
DEMO
![Page 68: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/68.jpg)
Limit the PGA
SQL> show parameter pga NAME TYPE VALUE-------------------------- ------------- ----------------------pga_aggregate_limit big integer 2G
pga_aggregate_target ****
![Page 69: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/69.jpg)
Limit the PGA
PGA_AGGREGATE_LIMIT is set to the greater of: - 2 GB (default value)- 200% of PGA_AGGREGATE_TARGET- 3 MB times the PROCESSES parameter
![Page 70: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/70.jpg)
Full Database Caching
Can be used to cache the entire database in memory. It should be used when the buffer cache size of the database instance is greater than the whole database size.
![Page 71: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/71.jpg)
RMAN Table Recovery in 12c
RMAN enables you to recover one or more tables or table partitions to a specified point in time.
![Page 72: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/72.jpg)
RMAN Table Recovery in 12c
RMAN> RECOVER TABLE HR.REGIONS UNTIL TIME "TO_DATE('01/10/2013 09:33:39','DD/MM/RRRR HH24:MI:SS')"AUXILIARY DESTINATION '/tmp/backups'
![Page 73: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/73.jpg)
DDL LOGGING
![Page 74: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/74.jpg)
DDL LOGGING
/u01/app/oracle/diag/rdbms/orcl/orcl/log/ddl/log.xml
![Page 75: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/75.jpg)
Direct SQL statement execution in RMAN
Pre - 12c:RMAN> SQL ‘SELECT sysdate FROM dual’;
12c:RMAN> SELECT sysdate FROM dual;
![Page 76: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/76.jpg)
In-Database Archiving
SQL> create table table_test(column1 number) row archival;
insert into table_test values(1);insert into table_test values(2);insert into table_test values(3);
![Page 77: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/77.jpg)
In-Database Archiving
![Page 78: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/78.jpg)
In-Database Archiving
update table_testset ora_archive_state=DBMS_ILM.ARCHIVESTATENAME(1) where column1=3;
![Page 79: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/79.jpg)
In-Database Archiving
alter session set row archival visibility=all;
![Page 80: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/80.jpg)
Heat Map, Automatic Data Optimization and ILM
OTN Article in Portuguese by Daniel Da Meda and Alex Zaballa
http://www.oracle.com/technetwork/pt/articles/database-performance/ilm-e-automatic-data-optimization-2601873-ptb.html
![Page 81: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/81.jpg)
Heat Map, Automatic Data Optimization and ILM
• Heat Map: Oracle Database 12c feature that stores system-generated data usage statistics at the block and segment levels. Automatically tracks modification and query timestamps at the row and segment levels.
• Automatic Data Optimization (ADO): automatically moves and compresses data according to user-defined policies based on the information collected by Heat Map
• ILM: Heat Map and Automatic Data Optimization make Oracle Database 12c ideal for implementing ILM
![Page 82: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/82.jpg)
Heat Map, Automatic Data Optimization and ILM
Enabling Heat Map
SQL> alter system set heat_map = on;
![Page 83: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/83.jpg)
Heat Map, Automatic Data Optimization and ILM
Heat Map statistics can be viewed graphically through EM Cloud Control:
![Page 84: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/84.jpg)
Heat Map, Automatic Data Optimization and ILM
Creating ADO policies
Compress the tablespace USER_DATA and all its residing segments at OLTP level after 30 days of low access:
ALTER TABLESPACE USER_DATA ILM ADD POLICYROW STORE COMPRESS ADVANCEDSEGMENT AFTER 30 DAYS OF LOW ACCESS;
![Page 85: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/85.jpg)
Heat Map, Automatic Data Optimization and ILM
Creating ADO policies
Compress the table ORDER_ITEMS including any SecureFile LOBs at OLTP level after 90 days of no modification:
ALTER TABLE ORDER_ITEMS ILM ADD POLICYROW STORE COMPRESS ADVANCEDGROUP AFTER 90 DAYS OF NO MODIFICATION;
![Page 86: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/86.jpg)
Session private statistics for Global Temporary Tables
Pre 12c, statistics gathered for global temporary tables (GTTs) were common to all sessions.
![Page 87: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/87.jpg)
Session private statistics for Global Temporary Tables
On 12c, by default session-private statistics are enabled
SELECT DBMS_STATS.get_prefs('GLOBAL_TEMP_TABLE_STATS') FROM dual;
STATS------------------------------------------------------------------------------SESSION
![Page 88: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/88.jpg)
Session private statistics for Global Temporary Tables
How to change?
Behavior pre 12c:BEGIN DBMS_STATS.set_global_prefs ( pname => 'GLOBAL_TEMP_TABLE_STATS', pvalue => 'SHARED');END;/
Back to default on 12c:BEGIN DBMS_STATS.set_global_prefs ( pname => 'GLOBAL_TEMP_TABLE_STATS', pvalue => 'SESSION');END;/
![Page 89: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/89.jpg)
Session private statistics for Global Temporary Tables
How to change for one table?
BEGIN dbms_stats.set_table_prefs('SCOTT','GTT_TEST', 'GLOBAL_TEMP_TABLE_STATS','SHARED');END;
BEGIN dbms_stats.set_table_prefs('SCOTT','GTT_TEST', 'GLOBAL_TEMP_TABLE_STATS’,’SESSION');END;
![Page 90: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/90.jpg)
DEMO
![Page 91: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/91.jpg)
Identity Columns
CREATE TABLE tabela_teste ( id NUMBER GENERATED ALWAYS AS IDENTITY, coluna1 VARCHAR2(30));
![Page 92: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/92.jpg)
Identity Columns
CREATE TABLE tabela_teste ( id NUMBER GENERATED BY DEFAULT AS IDENTITY, coluna1 VARCHAR2(30));
![Page 93: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/93.jpg)
Multitenant
![Page 94: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/94.jpg)
Fonte: Oracle Documentation
![Page 95: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/95.jpg)
Multitenant
Fonte: https://blogs.oracle.com/UPGRADE/entry/non_cdb_architecture_of_oracle
![Page 96: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/96.jpg)
Multitenant
Fonte: https://blogs.oracle.com/UPGRADE/entry/non_cdb_architecture_of_oracle
![Page 97: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/97.jpg)
Multitenant
Fonte: https://blogs.oracle.com/UPGRADE/entry/non_cdb_architecture_of_oracle
![Page 98: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/98.jpg)
In-Memory
Fonte: Oracle Documentation
![Page 99: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/99.jpg)
SIMD Vector Processing
Fonte: http://www.oracle.com/technetwork/database/in-memory/overview/twp-oracle-database-in-memory-2245633.html
![Page 100: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/100.jpg)
In-Memory
In-Memory Area – a static pool in SGA
![Page 101: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/101.jpg)
In-Memory
Fonte: OracleBase.com
![Page 102: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/102.jpg)
In-MemoryAlter table hr.EMPLOYEES inmemory;
ALTER TABLE sales MODIFY PARTITION SALES_Q1_1998 INMEMORY;
ALTER TABLE sales INMEMORY NO INMEMORY(prod_id);
CREATE TABLESPACE tbs_test DATAFILE '+DG01 SIZE 100M DEFAULT INMEMORY;
![Page 103: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/103.jpg)
In-Memory
Fonte: http://www.oracle.com/technetwork/database/in-memory/overview/twp-oracle-database-in-memory-2245633.html
![Page 104: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/104.jpg)
SQLcl
http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html
![Page 105: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/105.jpg)
![Page 106: OOW16 - Oracle Database 12c - The Best Oracle Database 12c New Features for Developers and DBAs](https://reader035.vdocuments.us/reader035/viewer/2022062310/5872e7101a28abfa548b62ed/html5/thumbnails/106.jpg)
Thank You
Slides Available: http://www.slideshare.net/