Showing posts with label oracle 19c features. Show all posts
Showing posts with label oracle 19c features. Show all posts

Saturday, July 4, 2020

Oracle 19c Features for Developer and DBA

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/articles/12c/with-clause-enhancements-12cr1

 

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.NEXTVAL FROM dual;

 

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.

 

 

Followers