Features which can help us
Row limiting : Oracle 12c onwards introduces sql syntax for row limiting. This makes it easier to retrieve records in sets for display or processing.
Example :-
create table employee (id integer primary key, name varchar2(10));
insert into employee values (1,'Adam');
insert into employee values (2,'Ben');
insert into employee values (3,'Colin');
insert into employee values (4,'Dean');
insert into employee values (5,'Evan');
insert into employee values (6,'Frank');
insert into employee values (7,'Greg');
insert into employee values (8,'Hank');
insert into employee values (9,'Ian');
insert into employee values (10,'Jack');
commit;
SQL> select * from employee order by id fetch first 3 rows only;
ID NAME
---------- ----------
1 Adam
2 Ben
3 Colin
SQL> select * from employee order by id offset 3 rows fetch next 3 rows only;
ID NAME
---------- ----------
4 Dean
5 Evan
6 Frank
SQL> select * from employee order by id fetch first 50 percent rows only;
ID NAME
---------- ----------
1 Adam
2 Ben
3 Colin
4 Dean
5 Evan
Extended Datatypes VARCHAR2 32767 bytes
it is now possible to create VARCHAR2, NVARCHAR2, and RAW attributes of size 32767 bytes.
Limitations to RESOURCE, SELECT ANY DICTIONARY
Dictionary tables containing password hashes (DEFAULT_PWD$, ENC$, LINK$, USER$, USER_HISTORY$, XS$VERIFIERS) are no longer included in the SELECT ANY DICTIONARY system privilege. This makes it safer to give developers access to dictionary tables for tuning and debugging, without giving them the chance to run brute force attacks ...
Data Redaction via DBMS_REDACT SECURE SENSTIVE DATA(MASKING) which I shared previously
some bulk transactions like "create table as" and "insert into select from" will automatically collect statistics. No need to analyze
Identity columns
In previous versions of Oracle there was no implicit relationship between a primary key and the sequence that maintained it's value, so developers needed to implement this relationship via insert triggers or application code. Oracle 12c removes that requirement for custom coding by implementing the relationship in the CREATE TABLE statement using the IDENTITY clause.
SQL> CREATE TABLE TEST_TABLE (ID NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1) PRIMARY KEY, TEXT VARCHAR2(100));
Table created.
When we insert into the table the ID column is automatically populated.
SQL> insert into test_table (text) values ('aaa');
1 row created.
SQL>commit;
Commit complete.
SQL> select * from test_table;
ID TEXT
------- ----------
1 aaa
When we created the table a sequence was also created.
SQL> select object_name, object_type from dba_objects where owner = 'TEST';
OBJECT_NAME OBJECT_TYPE
--------------- ------------------------------
SYS_C009854 INDEX
ISEQ$$_91602 SEQUENCE
TEST_TABLE TABLE
sequence.nextval as default and identity columns
The new Oracle 12c now allows to define a table with the sequence.nextval directly in the in-line column definition:
SQL> create table bar (
2 id number generated as identity,
3 foo varchar2(50) not null,
4 constraint bar_pk primary key (id)
5 );
Table created.
SQL> insert into bar (foo) values ('baz');
1 row created.
SQL> insert into bar (foo) values ('test');
1 row created.
SQL> insert into bar (foo) values ('whoo');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from bar;
ID FOO
---------- ------------------------------
1 baz
2 test
3 whoo
Concurrent Execution of UNION and UNION ALL Branches Oracle Database 12C release 1
UNION or UNION ALL consists of many queries(branches) that in pre 12C releases were executed one by one.
<BRANCH1>
UNION
<BRANCH2>
<BRANCH1>
UNION ALL
<BRANCH2>
So in pre 12C first is processed BRANCH1 then BRANCH2. Of course each individual query(branch) can be processed in serial or in parallel but only one branch at time.
Oracle 12C allows to run branches(statement) of UNION or UNION ALL concurrently. It means that BRANCH2 can be processed together with BRANCH1 :) . It can even return data faster than BRANCH1.
This feature is turned on automatically and entire UNION or UNION ALL is processed in parallel
Enhancement allows PLSQL declarations in the WITH clause
https://oracle-base.com/
JSON compatiabilty
Already shared
SQL Plus Last Login Time
select USERNAME,LAST_LOGIN from DBA_USERS where LAST_LOGIN is not null;
sequence reset
alter sequence ldbo.FWDBROKUNIQUENUMBER restart start with 1;
SELECT ldbo.FWDBROKUNIQUENUMBER.
default on null
There is new feature: default on null. For example:
CREATE TABLE tab1 (
col1 NUMBER DEFAULT 5,
col2 NUMBER DEFAULT ON NULL 7,
description VARCHAR2(30)
);
So when you try to INSERT null in col2, this will automatically be 7.
Oracle Default User
select * from dba_users where oracle_maintained='Y';
READ ANY TABLE
GRANT READ ANY TABLE TO psmith;
As with the READ object privilege, the READ ANY TABLE system privilege does not enable users to lock tables in exclusive mode nor select tables for update operations. Conversely, the SELECT ANY TABLE system privilege enables users to lock the rows of a table, or lock the entire table, through a SELECT ... FOR UPDATE statement, in addition to querying any table.
PRIVATE TEMPORARY TABLES
Private temporary tables enables Oracle 19c users to create temporary database objects that are automatically dropped at the end of a transaction or a session.
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 the end of the transaction (just like GLOBAL TEMPORARY TABLES created with ON COMMIT DELETE ROWS clause)
– DEFINITION is automatically dropped at the end of the transaction (ON COMMIT DELETE DEFINITION) or at the end of the session (ON COMMIT PRESERVE DEFINITION)
This feature is useful for developers as well as DBAs, because it could reduce the number of temporary or “test” tables created in the database.
Example:
CREATE PRIVATE TEMPORARY TABLE ora$ptt_testptt (testcol NUMBER)
ON COMMIT DROP DEFINITION;
SQL CANCELLING
A new command is introduced for DBAs to cancel blocking or too expensive statements instead of kill the originating session.
ALTER SYSTEM CANCEL SQL ‘sid,serial#’;
ALTER SYSTEM CANCEL SQL ‘sid,serial#,sqlid’;
ALTER SYSTEM CANCEL SQL ‘sid,serial#,@inst#’;
ALTER SYSTEM CANCEL SQL ‘sid,serial#,@inst#,sqlid’;
APPROX_COUNT(), APPROX_SUM() and APPROX_RANK() are introduced with Oracle 18c, which can calculate 99% accurate results in very high speed.
select oowncode, approx_count(*)
from sauda
group by oowncode
having approx_rank(partition by oowncode order by approx_count(*) desc) <= 1
order by 1;
DBMS_LOCK.SLEEP is depreciated in Oracle 18c and DBMS_SESSION.SLEEP is introduced, and is available with no additional grants needed.
