cesardbaLeave a Comment on Novidades Oracle Database 23c Free Developer Edition

Novidades Oracle Database 23c Free Developer Edition

Dia 03/04/2023 foi disponibilizado a versão free do Oracle Database para desenvolvedores, pensando nos desenvolvedores e nas empresas, a Oracle lançou a edição Developer da versão 23c totalmente gratuita. Hoje iremos testar algumas novidades.

Versão do Database:

SQL> set lines 200
SQL> select BANNER_FULL from gv$version;

BANNER_FULL
----------------------------------------------------------------------------
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0

1 – Utilização do IF[NOT]EXISTS em operações de DDL:

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 FREEPDB1                       READ WRITE NO

SQL> ALTER SESSION SET CONTAINER=FREEPDB1;

Session altered.

SQL> show con_name

CON_NAME
------------------------------
FREEPDB1

-- IF CREATE TABLE
SQL> CREATE TABLE IF NOT EXISTS TB_TEST(id NUMBER);

Table created.

– Verificando tabela criada:

SET LINESIZE 200 VERIFY OFF
COLUMN owner FORMAT A20
COLUMN object_name FORMAT A20
COLUMN edition_name FORMAT A15
SELECT owner,
       object_name,
       object_type,
       TO_CHAR(created, 'DD-MON-YYYY HH24:MI:SS') AS created,
       TO_CHAR(last_ddl_time, 'DD-MON-YYYY HH24:MI:SS') AS last_ddl_time,
       timestamp,
       status
FROM   dba_objects
WHERE object_name='TB_TEST'
ORDER BY owner, object_name;
SET VERIFY ON
OWNER                OBJECT_NAME          OBJECT_TYPE             CREATED                       LAST_DDL_TIME                 TIMESTAMP           STATUS
-------------------- -------------------- ----------------------- ----------------------------- ----------------------------- ------------------- -------
SYS                  TB_TEST              TABLE                   04-APR-2023 19:49:53          04-APR-2023 19:49:53          2023-04-04:19:49:53 VALID

– Drop da tabela TB_TEST com IF[NOT]EXISTS:

-- IF DROP TABLE
SQL> DROP TABLE IF EXISTS TB_TEST;

Table dropped.

2 – Insert multiplos:

SQL> insert into TB_DEPT values (1,'HR'), (2,'IT'),(3,'MANUFACTURING');
commit;
3 rows created.

SQL>

Commit complete.

SQL> SELECT id_dept,name FROM TB_DEPT;

   ID_DEPT NAME
---------- --------------------
         1 HR
         2 IT
         3 MANUFACTURING


SQL> insert into TB_EMPLOYEE values (1,1,'CESAR'), (2,3,'CARLOS'),(3,3,'ANTONIO');
commit;
3 rows created.

SQL>

Commit complete.


SQL> SELECT id_employee,id_dept,name FROM TB_EMPLOYEE;

ID_EMPLOYEE    ID_DEPT NAME
----------- ---------- --------------------
          1          1 CESAR
          2          3 CARLOS
          3          3 ANTONIO

3 – Update join:

SQL> SELECT EE.ID_EMPLOYEE,DEP.NAME,EE.NAME 
FROM TB_EMPLOYEE EE
INNER JOIN TB_DEPT DEP
ON DEP.ID_DEPT=EE.ID_DEPT;

ID_EMPLOYEE NAME                 NAME
----------- -------------------- --------------------
          1 HR                   CESAR 
          2 MANUFACTURING        CARLOS
          3 MANUFACTURING        ANTONIO
    
-- UPDATE IT DEPARTMENT =2
UPDATE TB_EMPLOYEE EE
	SET EE.ID_DEPT = 2
FROM TB_DEPT DEPT
WHERE DEPT.ID_DEPT=EE.ID_DEPT
AND EE.ID_EMPLOYEE IN (1,2,3);
COMMIT;

3 rows updated.

SQL> commit;


SQL> SELECT EE.ID_EMPLOYEE,DEP.NAME,EE.NAME 
FROM TB_EMPLOYEE EE
INNER JOIN TB_DEPT DEP
ON DEP.ID_DEPT=EE.ID_DEPT;

ID_EMPLOYEE NAME                 NAME
----------- -------------------- --------------------
          1 IT                   CESAR
          2 IT                   CARLOS
          3 IT                   ANTONIO

4 – Tipo de dados boolean:

SQL> CREATE TABLE IF NOT EXISTS TB_EMPLOYEE(id_employee NUMBER,id_dept NUMBER, name VARCHAR(20),active BOOLEAN);

Table created.

SQL> insert into TB_EMPLOYEE values (1,1,'CESAR',true), (2,3,'CARLOS',false),(3,3,'ANTONIO',false);
commit;
3 rows created.

SQL>

Commit complete.

SQL> SELECT * FROM TB_EMPLOYEE;

ID_EMPLOYEE    ID_DEPT NAME                 ACTIVE
----------- ---------- -------------------- -----------
          1          1 CESAR                TRUE
          2          3 CARLOS               FALSE
          3          3 ANTONIO              FALSE


-- RETURN FALSE
SQL> SELECT * FROM TB_EMPLOYEE WHERE NOT active; 

ID_EMPLOYEE    ID_DEPT NAME                 ACTIVE
----------- ---------- -------------------- -----------
          2          3 CARLOS               FALSE
          3          3 ANTONIO              FALSE
          

-- RETURN TRUE
SQL> SELECT * FROM TB_EMPLOYEE WHERE active;

ID_EMPLOYEE    ID_DEPT NAME                 ACTIVE
----------- ---------- -------------------- -----------
          1          1 CESAR                TRUE

5 – O from DUAL é opcional:

– Antes:

SQL> alter session set nls_date_format = 'DD/MM/YYYY HH24:MI:SS';

Session altered.

SQL> SELECT SYSDATE FROM DUAL;

SYSDATE
-------------------
04/04/2023 20:20:09

– Atualmente na versão 23c:

SQL> alter session set nls_date_format = 'DD/MM/YYYY HH24:MI:SS';

Session altered.

SQL> SELECT SYSDATE;

SYSDATE
-------------------
04/04/2023 20:20:48


SQL> SELECT 'Oracle Database 23c Edition'|| SYSDATE TT;

TT
----------------------------------------------
Oracle Database 23c Edition04/04/2023 20:22:51


SQL> SELECT 20+20 T;

         T
----------
        40
        
  SQL> SELECT 1-10 TT,20+30 T;

        TT          T
---------- ----------
        -9         50

6 – Anotações dos metadados com possíveis descrições ou informações importantes de campos ou da própria tabela:

SQL> ALTER SESSION SET CONTAINER=FREEPDB1;

Session altered.

SQL> show con_name

CON_NAME
------------------------------
FREEPDB1

SQL> CREATE TABLE IF NOT EXISTS TB_EMPLOYEE(id_employee NUMBER,id_dept NUMBER, name VARCHAR(20),active BOOLEAN annotations (active 'active employee'))
annotations (display 'Table to store employees');


select object_name,
       object_type,
       column_name,
       domain_name,
       domain_owner,
       annotation_name,
       annotation_value
from   dba_annotations_usage
where object_name='TB_EMPLOYEE'
group by object_name,
       object_type,
       column_name,
       domain_name,
       domain_owner,
       annotation_name,
       annotation_value
order by annotation_name, annotation_value;


OBJECT_NAME  OBJECT_TYPE  COLUMN_NAME  DOMAIN_NAME  DOMAIN_OWNER ANNOTATION_NAM ANNOTATION_VALUE
------------ ------------ ------------ ------------ ------------ -------------- --------------------------------------------------
TB_EMPLOYEE  TABLE        ACTIVE                                 ACTIVE         active employee
TB_EMPLOYEE  TABLE                                               DISPLAY        Table to store employees

7 – Pensando em facilitar a vida dos desenvolvedores, a Oracle criou a role especial DB_DEVELOPER_ROLE, esta role facilita e agiliza a vida do DBA e desenvolvedor.

– Iremos criar o usuário CESAR_DEV e verificar os grants ao aplicar a role DB_DEVELOPER_ROLE:

SQL> ALTER SESSION SET CONTAINER=FREEPDB1;

Session altered.

SQL> show con_name

CON_NAME
------------------------------
FREEPDB1

SQL>
SQL> CREATE USER CESAR_DEV IDENTIFIED BY CESAR;

User created.

SQL> grant create session to CESAR_DBA;

Grant succeeded.

SQL> GRANT DB_DEVELOPER_ROLE TO CESAR_DEV;

Grant succeeded.


[oracle@srv23dev ~]$ sqlplus CESAR_DEV/CESAR@localhost:1521/FREEPDB1

SQL*Plus: Release 23.0.0.0.0 - Developer-Release on Wed Apr 5 07:16:31 2023
Version 23.2.0.0.0

Copyright (c) 1982, 2023, Oracle.  All rights reserved.

Last Successful login time: Tue Apr 04 2023 21:18:28 -03:00

Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0

SQL> select * from session_privs order by privilege;

PRIVILEGE
----------------------------------------
CREATE ANALYTIC VIEW
CREATE ATTRIBUTE DIMENSION
CREATE CUBE
CREATE CUBE BUILD PROCESS
CREATE CUBE DIMENSION
CREATE DIMENSION
CREATE DOMAIN
CREATE HIERARCHY
CREATE JOB
CREATE MATERIALIZED VIEW
CREATE MINING MODEL
CREATE MLE
CREATE PROCEDURE
CREATE SEQUENCE
CREATE SESSION
CREATE SYNONYM
CREATE TABLE
CREATE TRIGGER
CREATE TYPE
CREATE VIEW
DEBUG CONNECT SESSION
EXECUTE DYNAMIC MLE
FORCE TRANSACTION
ON COMMIT REFRESH

24 rows selected.

SQL>

8 – Grant para leitura de todas as tabelas do esquema CESAR_DBA para o usuário CESAR_DEV:

SQL> ALTER SESSION SET CONTAINER=FREEPDB1;

Session altered

SQL> show con_name

CON_NAME
------------------------------
FREEPDB1

SQL> GRANT SELECT ANY TABLE ON SCHEMA CESAR_DBA  TO CESAR_DEV;

Grant succeeded.

[oracle@srv23dev ~]$ sqlplus CESAR_DEV/CESAR@localhost:1521/FREEPDB1

SQL*Plus: Release 23.0.0.0.0 - Developer-Release on Tue Apr 4 20:44:38 2023
Version 23.2.0.0.0

Copyright (c) 1982, 2023, Oracle.  All rights reserved.


Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0

SQL> show user;
USER is "CESAR_DEV"
SQL>
SQL> SELECT * FROM CESAR_DBA.TB_EMPLOYEE;

ID_EMPLOYEE    ID_DEPT NAME                 ACTIVE
----------- ---------- -------------------- -----------
          1          1 CESAR                TRUE
          2          3 CARLOS               FALSE
          3          3 ANTONIO              FALSE

SQL>

Leave a Reply

Your email address will not be published. Required fields are marked *

search previous next tag category expand menu location phone mail time cart zoom edit close