Oracle 18c New Features – Classified!
New Features – Oracle Database 18C
Cancel SQL statement Oracle Database 18C
In latest version of database 18C
instead of killing user session you can cancel its currently running or opened
SQL statement using the ALTER SYSTEM
CANCEL SQL statement.
ALTER SYSTEM CANCEL SQL 'SID, SERIAL, @INST_ID,
SQL_ID';
Required parameters
§
SID – Session ID
§
SERIAL – Session serial number
Optional parameters
§
INST_ID – Instance ID
§
SQL_ID – SQL ID of the SQL statement
Example
Canceling a SQL statement having the session identifier of 15,
session serial number of 20, instance 4
ALTER SYSTEM CANCEL SQL '15,20,@4';
Canceling a SQL statement having the session identifier of 20,
session serial number of 51142, and SQL ID of 8vu7s907prbgr:
ALTER SYSTEM CANCEL SQL '20, 51142, 8vu7s907prbgr';
It means you can just cancel one of many opened cursors in a
given session – it’s great option.
NOTE: When you cancel a DML statement, the statement is rolled
back
Scalable
sequences Oracle Database 18C
Sequence is commonly known oracle object that is used to
generate numbers in specified orders. In Oracle 18C new extra words can be
defined for sequence creation:
§
SCALE | NOSCALE(default)
§
EXTEND | NOEXTEND(default)
SCALE – forces to build extra 6 digit number
as prefix to normal sequence number
scalable sequence number = 6
digit number || normal sequence number
According to oracle documentation 6 digit scalable sequence offset number = 3 digits from instance_id and 3
digits from session_id using following pattern [(instance_id % 100) + 100] || [session_id % 1000].
So it means it’s
constant in a session and changes once you reconnect.
CREATE SEQUENCE scale_seq START WITH 1 MAXVALUE
9999999 SCALE;
SELECT sequence_name, min_value, max_value,
scale_flag, extend_flag
FROM user_sequences;
SEQUENCE_NAME
MIN_VALUE MAX_VALUE S E
-------------- ---------- ---------- - -
SCALE_SEQ 1 9999999 Y N
In above example as default sequence scale_seq
was created with NOEXTEND. This has impact on MAXVALUE that can be
generated. In this case MAXVALUE was defined with 7 digits (9999999) but first
6 are consumed by 6 digit scalable sequence offset
number. So in reality you
can generate here [999999]9 only numbers 1..9 for last position.
SELECT sys_context('USERENV', 'INSTANCE') inst_id,
sys_context('USERENV', 'SID') sid FROM dual;
INST_ID SID
-------- ----
1 132
--you can see below how first six digits are build
based on
--instance_id number and sid value
SELECT scale_seq.nextval FROM dual;
NEXTVAL
----------
1011321
SELECT scale_seq.nextval FROM dual;
NEXTVAL
----------
1011322
...
--9th last possible iteration for NOEXTEND
SELECT scale_seq.nextval FROM dual;
NEXTVAL
----------
1011329
--10th try raises error
SELECT scale_seq.nextval FROM dual;
ORA-64603: NEXTVAL cannot be instantiated for
SCALE_SEQ.
Widen the sequence by 1 digits or alter sequence
with SCALE EXTEND.
Following example with
this issue
CREATE SEQUENCE scale_err_seq START WITH 1
MAXVALUE 999 SCALE;
SELECT scale_err_seq.nextval FROM dual;
ORA-64603: NEXTVAL cannot be instantiated for
SCALE_ERR_SEQ.
Widen the sequence by 4 digits or alter sequence
with SCALE EXTEND.
To fix this problem EXTEND word can be used so 6 digit scalable sequence offset number will not consume first 6 digits from
MAXVALUE definition.
CREATE SEQUENCE scale_ext_seq START WITH 9
MAXVALUE 999 SCALE EXTEND;
SELECT sequence_name, min_value, max_value,
scale_flag, extend_flag
FROM user_sequences
ORDER BY sequence_name;
SEQUENCE_NAME
MIN_VALUE MAX_VALUE S E
-------------- ---------- ---------- - -
SCALE_ERR_SEQ 1 999 Y N
SCALE_EXT_SEQ 1 999 Y Y
SCALE_SEQ 1 9999999 Y N
SELECT scale_ext_seq.nextval FROM dual;
NEXTVAL
----------
101132009
SELECT scale_ext_seq.nextval FROM dual;
NEXTVAL
----------
101132010
It’s possible to change sequence to be SCALE EXTEND with
ALTER SEQUENCE scale_seq SCALE
EXTEND;
ALTER SEQUENCE scale_err_seq SCALE
EXTEND;
or NOSCALE
ALTER SEQUENCE scale_seq NOSCALE;
ALTER SEQUENCE scale_err_seq NOSCALE;
Schema Only Accounts Oracle
Database 18C
New type of schema can
be created in 18C where user has no password.
CREATE USER username NO AUTHENTICATION;
Schema user_noauth
creation without password
CREATE USER user_noauth NO
AUTHENTICATION;
GRANT CREATE SESSION TO user_noauth;
To connect to this
type of schema you need to grant proxy authentication to other user.
CREATE USER tomasz IDENTIFIED BY tomasz;
GRANT CREATE SESSION TO tomasz;
ALTER USER user_noauth GRANT
CONNECT THROUGH tomasz;
New column AUTHENTICATION_TYPE in dictionary can be found in
DBA|ALL|USER_USERS for this feature
SELECT username, account_status,
authentication_type
FROM dba_users
WHERE username IN ('TOMASZ', 'USER_NOAUTH');
USERNAME
ACCOUNT_STATUS
AUTHENTICATION_TYPE
------------- --------------- --------------------
USER_NOAUTH
OPEN NONE
TOMASZ
OPEN PASSWORD
Connect to no
authentication account via proxy authentication
SQL> CONNECT tomasz[user_noauth]\tomasz
Connected.
SQL> show user
USER is "USER_NOAUTH"
Limitations
§
Schema only accounts can be used for both administrator and
non-administrator accounts.
§
Schema only accounts can be created on the database instance
only, not in Oracle Automatic Storage Management (ASM) environments.
§
You can grant system privileges (such as CREATE ANY TABLE) and administrator roles (such as DBA) to schema only accounts. Schema only accounts can create
objects such as tables or procedures, assuming they have had to correct
privileges granted to them.
§
You cannot grant the SYSDBA, SYSOPER, SYSBACKUP, SYSKM, SYSASM, SYSRAC, and SYSDG administrative
privileges to schema only accounts.
§
You can configure schema only accounts to be used as client
users in a proxy authentication in a single session proxy. This is because in a
single session proxy, only the credentials of the proxy user are verified, not
the credentials of the client user. Therefore, a schema only account can be a
client user. However, you cannot configure schema only accounts for a two-proxy
scenario, because the client credentials must be verified. Hence, the
authentication for a schema only account will fail.
§
Schema only accounts cannot connect through database links,
either with connected user links, fixed user links, or current user links.
§
Private Temporary Tables
Oracle Database 18C
New type of temporary tables appeared in 18C
called Private Temporary Tables. They are temporary database objects that are
dropped at the end of a transaction or session. Private temporary tables are
stored in memory and each one is visible only to the session that created it.
CREATE PRIVATE TEMPORARY
TABLE .... ON COMMIT
DROP DEFINITION
or
CREATE PRIVATE TEMPORARY
TABLE .... ON COMMIT
PRESERVE DEFINITION
DROP DEFINITION
|
This creates a private temporary table that is transaction
specific. All data in the table is lost, and the table is dropped at the end
of transaction.
|
PRESERVE DEFINITION
|
This creates a private temporary table that is session
specific. All data in the table is lost, and the table is dropped at the end
of the session that created the table.
|
Private temporary table name must start with prefix defined by
instance parameter private_temp_table_prefix
SQL>show parameter private
NAME TYPE
VALUE
-------------------------- ------- ---------
private_temp_table_prefix string
ORA$PTT_
Following statement
raises exception because of wrong prefix in table name
CREATE PRIVATE TEMPORARY
TABLE
test_tbl_session
(
time_id
DATE,
amount_sold
NUMBER(10,2)
)
ON COMMIT PRESERVE DEFINITION;
CREATE PRIVATE TEMPORARY TABLE test_tbl_session
*
ERROR at line 1:
ORA-00903: invalid table name
Following example shows howto create private
temporary table that exists till first COMMIT in session. Important note –
there are dedicated views that shows detailed information about private
temporary tables DBA|USER_PRIVATE_TEMP_TABLES.
SQL> connect tomasz/tomasz
CREATE PRIVATE TEMPORARY
TABLE
ORA$PTT_test_tbl_session
(
time_id
DATE,
amount_sold
NUMBER(10,2)
)
ON COMMIT DROP DEFINITION;
SELECT sid, serial#, owner, table_name, duration
FROM user_private_temp_tables;
SID SERIAL# OWNER
TABLE_NAME DURATION
--- ------- ------ ------------------------
-----------
132 55609 TOMASZ ORA$PTT_TEST_TBL_SESSION TRANSACTION
What is interesting
you can create the same table name for the same user in another concurrent
session
SQL> connect tomasz/tomasz
CREATE PRIVATE TEMPORARY
TABLE
ORA$PTT_test_tbl_session
(
time_id
DATE,
amount_sold
NUMBER(10,2)
)
ON COMMIT DROP DEFINITION;
SELECT sid, serial#, owner, table_name, duration
FROM user_private_temp_tables;
SID SERIAL# OWNER
TABLE_NAME DURATION
--- ------- ------ ------------------------
-----------
11 37471 TOMASZ
ORA$PTT_TEST_TBL_SESSION TRANSACTION
SELECT sid, serial#, owner, table_name, duration
FROM dba_private_temp_tables;
SID SERIAL# OWNER
TABLE_NAME DURATION
--- ------- ------ ------------------------
-----------
132 55609 TOMASZ ORA$PTT_TEST_TBL_SESSION TRANSACTION
11 37471
TOMASZ
ORA$PTT_TEST_TBL_SESSION TRANSACTION
Time to load and
commit data
--load data
INSERT INTO ORA$PTT_test_tbl_session
VALUES(sysdate, 10);
1 row created.
--check data
SELECT count(*) FROM ORA$PTT_test_tbl_session;
COUNT(*)
----------
1
--commit data and check table is dropped
COMMIT;
Commit complete.
SELECT count(*) FROM ORA$PTT_test_tbl_session;
ORA-00942: table or view does
not exist
Another type of private temporary table is ON COMMIT PRESERVE DEFINITION. As long as user stays connected table exists.
After re connection
table is gone.
SQL> connect tomasz/tomasz
CREATE PRIVATE TEMPORARY
TABLE
ORA$PTT_test_tbl_commit
(
time_id
DATE,
amount_sold NUMBER(10,2)
)
ON COMMIT PRESERVE DEFINITION;
INSERT INTO ORA$PTT_test_tbl_commit
VALUES(sysdate, 10);
1 row created.
COMMIT;
Commit complete.
INSERT INTO ORA$PTT_test_tbl_commit
VALUES(sysdate, 10);
1 row created.
COMMIT;
Commit complete.
SELECT sid, serial#, owner, table_name, duration
FROM dba_private_temp_tables
WHERE
table_name = 'ORA$PTT_TEST_TBL_COMMIT';
SID SERIAL# OWNER
TABLE_NAME DURATION
--- ------- ------ ------------------------
-----------
11
37471 TOMASZ
ORA$PTT_TEST_TBL_COMMIT SESSION
SQL> connect tomasz/tomasz
SELECT sid, serial#, owner, table_name, duration
FROM dba_private_temp_tables
WHERE
table_name = 'ORA$PTT_TEST_TBL_COMMIT';
no rows selected
Qualified expressions for
PL/SQL Oracle Database 18C
In Oracle Database 18C type
initialization is simplified with qualified expressions for Record type and
Associative Array Type. It can help simplify coding.
Record
rec_var := (field_name => some_value, .., field_name => some_value)
Associative Array
rec_var := (1 => some_value, .., 10 => some_value)
RECORD type example
DECLARE
TYPE l_rec_type IS RECORD
(
username VARCHAR2(30),
phone VARCHAR2(30),
town VARCHAR2(30)
);
l_user l_rec_type;
BEGIN
--works for version 12C
l_user := l_rec_type('Tomasz', NULL, 'Warsaw');
--new 18C
l_user := l_rec_type
(
username => 'Tomasz',
phone => 'Warsaw'
);
END;
/
ASSOCIATIVE ARRAY example
DECLARE
TYPE l_rec_idx_type IS TABLE OF VARCHAR(30) INDEX BY BINARY_INTEGER;
TYPE l_rec_var_type IS TABLE OF VARCHAR(30) INDEX BY VARCHAR2(30);
l_idx l_rec_idx_type;
l_var l_rec_var_type;
BEGIN
--works for version 12C
l_idx(1) := 'Tomasz';
l_idx(2) := 'Adam';
l_idx(4) := 'Eve';
--new 18C
l_idx := l_rec_idx_type
(
1 => 'Tomasz',
2 => 'Adam',
4 => 'Eve'
);
--works for version 18C
l_var('Tomasz') := 'Lesinski';
l_var('Adam') := 'First';
l_var('Eve') := 'Second';
--new 18C
l_var := l_rec_var_type
(
'Tomasz' => 'Lesinski',
'Adam' => 'First',
'Eve' => 'Second'
);
END;
/
Post a Comment: