Using DUMP, CHR, and REPLACE character functions

task#7 The set of sentences that will be analysed consist of character strings with the following pattern:

Number#1 Word#2 Number#2 Word#3 Number#3 Word#4 Number#4 Word#5 Number#5

Substrings Number#1, Number#2, Number#3, Number#4, Number#5 need to be extracted from the text and saved into separate table columns as numbers in order to be used as parameters in SQL aggregate functions for further analysis.

The length of each sentence is unknown as well as the format of Number# and Word# substrings.

step#1 – Start the SQL Developer

Start SQL Developer

Start SQL Developer

step#2 – The collected set of sentences is saved into the table DEMO.TEXTS. Each sentence is represented as a string of VARCHAR2(256) in the column WORD_SET.

As the table TEXTS is too long , I selected 6 rows that represent a typical characters sets for this task.

The Set Of Strings

The Set Of Strings

What is strange with this set of strings is that they appear to be of the similar sizes, but their exact lengths are different. The shortest string is 29 characters long, and the longest one is 166 characters long. Obviously, the longer strings contain some other ASCII non-printable characters. How to see them?

step#3 – Apply SQL DUMP function to reveal the content of strings.

Apply the DUMP Function

Apply the DUMP Function

The Result Set shows that our strings contain ASCII characters:

CHR(10) or Line feed, ‘\n’

CHR(13) or Carriage return, ‘\r’

CHR(32) or Space

step#4 – With the simple update statement and REPLACE function, characters chr(10), chr(13), and chr(32) will be removed from all strings:

The UPDATE Statement with REPLACE Functions

The UPDATE Statement with REPLACE Functions

step#5 – All strings are clean and contain only “0-9”, “a-z”, and “A-Z” characters.

The Clean Strings

The Clean Strings

step#6 – The typical string looks like

5,219Spring28.5KSummer44.1KAutumn2,557Winter6

The following procedure called proc_extract will extract substrings with numbers and save them in separate columns as NUMBER data type.

Those columns are table TEXTS columns named

TOTAL for Number#1,

SPRING for Number#2,

SUMMER for Number#3,

AUTUMN for Number#4, and

WINTER for Number#5 substrings.

create or replace procedure proc_extract

authid current_user

is

  type type_id is table of DEMO.texts.word_id%TYPE;

  l_my_id type_id := type_id();

  type t_stats is table of DEMO.texts.word_set%TYPE;

  l_my_list t_stats := t_stats();

  l_last_id number := 0;

  l_len1 number;

  l_len2 number;

  l_my_str varchar2(256);

  l_my_str2 varchar2(256);

  l_my_i DEMO.texts.word_id%TYPE;

begin

  select word_id, word_set bulk collect into l_my_id, l_my_list

  from texts

  where word_set <> 'X';

  dbms_output.put_line(l_my_list.COUNT);

  for i in l_my_id.FIRST..l_my_id.COUNT

  loop

    l_my_i := l_my_id(i);

    l_my_str := l_my_list(i);

    l_len2 := length (l_my_str);

    if (instr(l_my_str,'Winter', 1, 1) <> 0 ) then

      l_len1 := instr(l_my_str,'Winter', 1, 1);

      l_my_str2 := substr(l_my_str, l_len1, l_len2 - l_len1 + 1);

      l_my_str2 := funct_format_num(replace(l_my_str2, 'Winter') );

      l_my_str := substr(l_my_str, 1, l_len1 - 1);

      dbms_output.put_line(l_my_str2);

      dbms_output.put_line(l_my_str);

      update texts set winter = to_number(l_my_str2) where word_id = l_my_i;

    end if;

    l_len2 := length (l_my_str);

    if (instr(l_my_str,'Autumn',1,1) <> 0 ) then

      l_len1 := instr(l_my_str, 'Autumn', 1, 1);

      l_my_str2:= substr(l_my_str, l_len1, l_len2 - l_len1 +1);

      l_my_str2 := funct_format_num(replace(l_my_str2,'Autumn'));

      l_my_str := substr(l_my_str, 1, l_len1 - 1);

      dbms_output.put_line(l_my_str2);

      dbms_output.put_line(l_my_str);

      update texts set autumn = to_number(l_my_str2) where word_id = l_my_i;

    end if;

    l_len2 := length (l_my_str);

    if (instr(l_my_str,'Summer',1,1) <> 0 ) then

      l_len1 := instr(l_my_str,'Summer',1,1);

      l_my_str2:= substr(l_my_str, l_len1 , l_len2 - l_len1 + 1);

      l_my_str2 := funct_format_num(replace(l_my_str2,'Summer'));

      l_my_str := substr(l_my_str, 1, l_len1 - 1);

      dbms_output.put_line(l_my_str2);

      dbms_output.put_line(l_my_str);

      update texts set summer = to_number(l_my_str2) where word_id = l_my_i;

    end if;

    l_len2 := length (l_my_str);

    if (instr(l_my_str,'Spring',1,1) <> 0 ) then

      l_len1 := instr(l_my_str,'Spring',1,1);

      l_my_str2:= substr(l_my_str, l_len1 , l_len2 - l_len1 + 1);

      l_my_str2 := funct_format_num(replace(l_my_str2,'Spring'));

      l_my_str := substr(l_my_str, 1, l_len1 - 1);

      dbms_output.put_line(l_my_str2);

      dbms_output.put_line(l_my_str);

      update texts set spring = to_number(l_my_str2) where word_id = l_my_i;

    end if;

    l_len2 := length (l_my_str);

    if l_len2 > 0 then

      l_my_str := funct_format_num(l_my_str);

      dbms_output.put_line(l_my_str);

      update texts set total = to_number(l_my_str) where word_id = l_my_i;

    end if;
 
  end loop;

  commit;

exception

when others then

  dbms_output.put_line(SQLERRM);

end;

The function funct_format_num will transform the existing number format “9,999”, “99.9K”, “999K”, and “99.9M” into simple “9999999” format that is easy to use.

create or replace function funct_format_num (p_in_str varchar2)

return varchar2

authid current_user

as

  l_s varchar2(256);

begin

  l_s := p_in_str;

  if (instr(p_in_str,'.') <> 0 AND instr(p_in_str,'K') <> 0 ) then

    l_s := replace(replace(l_s,'.'),'K');

    l_s := l_s || '00';

  elsif instr(p_in_str,'K') <> 0 then

    l_s := replace(l_s,'K');

    l_s := l_s || '000';

  elsif ( instr(p_in_str,'.') <> 0 AND instr(p_in_str,'M')<> 0 ) then

    l_s:= replace(replace(l_s,'.'),'M');

    l_s:= l_s || '00000';

  elsif instr(p_in_str,',') <> 0 then

    l_s := replace(l_s,',');

  end if;

  return l_s;

exception

when others then

  raise;

end;

step#7 – After execution of the procedure proc_extract, the table is populated with correct values.

The Table DEMO.TEXTS

The Table DEMO.TEXTS

step#8 – Now, it is easy to execute any SQL aggregate function on TEXTS columns

Apply Aggregate Functions

Apply Aggregate Functions

Summary

Sometimes, we do not need to write long procedures with FOR loops and analyze each table column that contains character strings. Instead, the rule “Use SQL statement first.” is the best choice. Simple UPDATE statement combined with CHR and REPLACE functions will save the day and our time and the helpful DUMP function gives us the evidence what non-printable characters are inside our character strings.

The other story is when the once clean strings that we have, should be divided to several substrings. Then, SQL functions SUBSTR, INSTR, and LENGTH should be used and appropriate subprograms should be written as well to finish the task.

Reminder

DUMP (expr, return_fmt, start_position, length)

returns a VARCHAR2 value containing the data type code, length in bytes, and internal representation of expr. The returned result is always in the database character set. [ref2 ]

CHR (n USING NCHAR_CS)

returns the character having the binary equivalent to n as a VARCHAR2 value in either the database character set or, if you specify USING NCHAR_CS, the national character set. [ref2 ]

REPLACE (char, search_string, replacement_string)

returns char with every occurrence of search_string replaced with replacement_string. If replacement_string is omitted or null, then all occurrences of search_string are removed. If search_string is null, then char is returned. [ref2 ]

Reference

[ref2] Oracle® Database SQL Language Reference 12c Release 1 (12.1) E41329-09

76% increase of developer’s productivity with Oracle EM12c, Enterprise Manager Database Express 12c

Oracle Enterprise Manager Database Express, also referred to as EM Express, is a web-based tool for managing Oracle Database 12c. Built inside the database server, it offers support for basic administrative tasks such as storage and user management, and provides comprehensive solutions for performance diagnostics and tuning.

task#6 Use Enterprise Manager Database Express 12c ( EM12c) and alter the state of the pluggable database PDBORCL from open to close, and again from close to open.
step#1 – Connect to EM 12c. Open the browser with “localhost:5500/em” and enter SYS username/password.

The Enterprise Manager Start Page

The Enterprise Manager Start Page

Then open the window that shows pluggable databases PDBs state.

The List of Oracle 12c Pluggable Databases

The List of Oracle 12c Pluggable Databases

The pluggable database PDBORCL is open in Read Write mode, it has been running for 6 hours and 31 minutes, and its size is 2GB.
step#2 – Open the List of possible Actions on pluggable databases.

The Action List

The Action List

The pluggable database PDBORCL could be:
-Cloned
-Plugged or Unplugged
-Dropped
-Closed

step#3 – Click on the Close option to close the pluggable database PDBORCL

The Pluggable Database is Closed

The Pluggable Database is Closed

The pluggable database pdborcl is now closed.
step#4 – Open the pluggable database pdborcl in Read Write mode.

The Pluggable Database

The Pluggable Database

step#5 – Open SQL window and see the SQL statements that will be executed to open the pluggable database pdborcl in Read Write mode.

SQL statements that will be executed

SQL statements that will be executed

step#6 – See the confirmation message that the pluggable database pdborcl is successfully opened.

The Pluggable Database is OPEN

The Pluggable Database is OPEN

Summary
The Enterprise Manager Database Express 12c allows the DBAs and Oracle Developers clean, fast, and effortless control over the root CDB and pluggable PDBs.

Reference

Oracle 12c documentation

EM12c

High Efficiency with SQL Developer 12c

Oracle SQL Developer is a graphical version of SQL*Plus that gives database developers a convenient way to perform basic tasks. You can browse, create, edit, and delete (drop) database objects; run SQL statements and scripts; edit and debug PL/SQL code; manipulate and export (unload) data; and view and create reports.

You can connect to any target Oracle database schema using standard Oracle database authentication. Once connected, you can perform operations on objects in the database.

You can connect to schemas for selected third-party (non-Oracle) databases, such as MySQL, Microsoft SQL Server, Sybase Adaptive Server, Microsoft Access, and IBM DB2, as well as view metadata and data in these databases; also you can migrate third-party databases to Oracle

task#5 Use SQL Developer and do the following: without any SQL or PL/SQL code writing, alter the state of the pluggable database PDBORCL from open to close, then change its state again from close to open.

step#1 – Start SQL Developer, open the View menu DBA, and click on the Option Container Database. That option will allow the management of the Container Database CDB and the Pluggable Databases PDBs. Then, open the window with the status of the pluggable database PDBORCL.

Start SQL Developer

Start SQL Developer

Open View Menu and Option DBA

Open View Menu and Option DBA

Open Option Container Database

Open Option Container Database

step#2 – Open the list of actions on PDBORCL

Modify PDB state

Modify PDB state

step#3 – Close the pluggable database PDBORCL

Apply Modify PDB State to Close

Apply Modify PDB State to Close

Successful State Modification To Close

Successful State Modification To Close

PDB is Closed

PDB is Closed

step#4 – Open the pluggable database PDBORCL in Read Write mode

Modify PDB state From Close To Open

Modify PDB state From Close To Open

Apply Change From Close To Open

Apply Change From Close To Open

PDB is successfully OPENed

PDB is successfully Opened

PDB status is OPEN in Read Write mode

PDB status is OPEN in Read Write mode

Summary

SQL Developer offers to DBAs and Oracle Developers efficient control over root CDB and pluggable PDBs. Productivity of DBAs and Oracle Developers is increased more than ever with Oracle Database 12c Tools.

References

Oracle 12c documentation

[ref7] Oracle® SQL Developer User’s Guide Release 3.2 E35117-06

Upgrade to Oracle Database 12c

task#1 Check the version of your Oracle database and if it is not 12c, notify that it should be upgraded to the Oracle Database 12c.

step#1 – Connect to the Oracle Database as SYSDBA

c:TEMP>sqlplus / as sysdba
SYS@orcl > spool task#1.ext append

step#2 – Check the database version

SYS@orcl > select value from v$parameter where name='optimizer_features_enable';
VALUE
-----------------------------------------------------------
12.1.0.2
SYS@orcl > select value from v$parameter where name='db_unique_name';
VALUE
---------
orcl

The other way to know the Oracle Database version is the following:

--Oracle Database 10.2 
SQL> begin 
2 dbms_output.put_line(DBMS_DB_VERSION.VERSION || '.' || DBMS_DB_VERSION.RELEASE); 
3 end; 
4 / 
10.2
--Oracle Database 12.1
SYS@orcl > begin
  2  dbms_output.put_line(DBMS_DB_VERSION.VERSION || '.' || DBMS_DB_VERSION.RELEASE);
  3  end;
  4  /
12.1


step#3 – If the database is not Oracle Database 12c, show the following message ” current_version should be updated to Oracle database 12c. Go to the www.oracle.com ” , and go to step#8.

SYS@orcl > select value current_version,
case substr(value,1,2) 
  when '12' then 'The current version is ' || value
  else value || ' should be updated to Oracle Database 12c. Go to www.oracle.com'
end as status
from v$parameter
where name='optimizer_features_enable';
/
VERSION       STATUS
------------- -----------------------------------------------
12.1.0.2      The current version is 12.1.0.2


step#4 – Else, if it is Oracle Database 12c, show CDB/PDBs details

SYS@orcl > select con_id, name, open_mode, restricted from v$containers;
CON_ID     NAME         OPEN_MODE  RES
------     -----------  --------  ---
1          CDB$ROOT     READ WRITE NO
2          PDB$SEED     READ ONLY  NO
3          PDBORCL      MOUNTED    NO

step#5 – If any PDB is not opened, set its mode to READ ONLY or READ WRITE accordingly

SYS@orcl > select con_id, name
from v$containers
where open_mode not IN ('READ WRITE', 'READ ONLY');
CON_ID     NAME
---------- ------------------------------
3          PDBORCL
SYS@orcl > alter pluggable database pdborcl open read write;
Pluggable database altered.
SYS@orcl > select con_id, name, open_mode, restricted 
from v$containers 
where name='PDBORCL';
/
CON_ID     NAME                           OPEN_MODE  RES
---------- ------------------------------ ---------- ---
3          PDBORCL                        READ WRITE NO


step#6 – Connect to the last PDB that is opened in READ WRITE mode.

SYS@orcl > alter session set container=pdborcl;
Session altered.

step#7 – Show the container ID of the PDB

SYS@orcl > sho con_id
CON_ID
------------------------------
3
SYS@orcl > sho parameter plsql
NAME                   TYPE        VALUE
-------------------    ----------- ------------------------------
plsql_ccflags          string
plsql_code_type        string      INTERPRETED
plsql_debug            boolean     FALSE
plsql_optimize_level   integer     2
plsql_v2_compatibility boolean     FALSE
plsql_warnings         string      DISABLE:ALL

step#8 – Disconnect from the database.

SYS@orcl > exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
c:TEMP>type task#1.ext | more

task#2 Automate task#1. Create a standalone procedure that will return the same result as task#1.

create or replace function funct_db_version
  return number
  authid current_user
is
  l_intval number;
  l_strval varchar2(2000);
  l_type number;
begin
  l_type := dbms_utility.get_parameter_value ('optimizer_features_enable', l_intval, l_strval);
  return to_number(substr(l_strval,1,2));
exception
  when others then
    dbms_output.put_line(SQLERRM);
end;
/
--Oracle Database 10.2
select funct_db_version from dual;

FUNCT_DB_VERSION
----------------
              10
--Oracle Database 12.1
SYS@orcl > select funct_db_version from dual;

FUNCT_DB_VERSION
----------------
              12
create or replace procedure proc_upgrade_to_12c
  authid current_user
is
  l_stmt varchar2(500);
$IF DBMS_DB_VERSION.VERSION >= 12 $THEN
  CURSOR cur_id_name
  is 
  select con_id, name from v$containers where open_mode not IN ('READ WRITE', 'READ ONLY');
  TYPE t_id is table of v$containers.con_id%TYPE;
  TYPE t_name is table of v$containers.name%TYPE;
  l_list_id t_id;
  l_list_name t_name;
  l_con_id varchar2(256);
  l_con_name varchar2(256);
$END
begin
  if not funct_db_version = 12 then
    dbms_output.put_line(' The database ' ||DBMS_DB_VERSION.VERSION || '.' || DBMS_DB_VERSION.RELEASE || ' should be updated to Oracle Database 12c. Go to www.oracle.com' );
    return;
  end if;
$IF DBMS_DB_VERSION.VERSION >= 12 $THEN
  if not cur_id_name%ISOPEN then
    open cur_id_name;
  end if; 
  fetch cur_id_name BULK COLLECT into l_list_id, l_list_name;
  if l_list_id.COUNT > 0 then
    for i in 1..l_list_id.COUNT
    loop
      l_stmt := 'alter pluggable database ' ||lower( l_list_name(i) )|| ' open read write';
      execute immediate l_stmt;
      dbms_output.put_line('Container ' || lower( l_list_name(i) )|| ' open READ WRITE');
    end loop;
  end if; 
  select sys_context('USERENV','CON_ID') into l_con_id from dual;
  select sys_context('USERENV','CON_NAME') into l_con_name from dual;
  dbms_output.put_line('Current container ' || l_con_name || ' has ID ' || l_con_id);
  dbms_output.put_line('$$PLSQL_CODE_TYPE = ' || $$PLSQL_CODE_TYPE);
  dbms_output.put_line('$$PLSQL_OPTIMIZE_LEVEL = ' || $$PLSQL_OPTIMIZE_LEVEL);
  dbms_output.put_line('$$PLSCOPE_SETTINGS = ' || $$PLSCOPE_SETTINGS);
  if cur_id_name%ISOPEN then
    close cur_id_name;
  end if;
$END
exception
  when others then
$IF DBMS_DB_VERSION.VERSION >= 12 $THEN
  if cur_id_name%ISOPEN then
    close cur_id_name;
  end if;
$END
  dbms_output.put_line(SQLERRM);
end proc_upgrade_to_12c;
/
--Oracle Database 10.2
SQL>exec proc_upgrade_to_12c;

The database 10.2 should be updated to Oracle Database 12c. Go to www.oracle.com

PL/SQL procedure successfully completed.
--Oracle Database 12.1
SYS@orcl > exec proc_upgrade_to_12c;
Current container CDB$ROOT has ID 1
$$PLSQL_CODE_TYPE = INTERPRETED
$$PLSQL_OPTIMIZE_LEVEL = 2
$$PLSCOPE_SETTINGS = IDENTIFIERS:NONE

PL/SQL procedure successfully completed.

task#3 Create a standalone procedure that shows details of CDB/PDBs such as container id, name, and open mode.


create or replace procedure proc_show_details_12c
  authid current_user
  is
$IF DBMS_DB_VERSION.VERSION >= 12 $THEN
  CURSOR cur_id_name_mode 
  is 
  select con_id, name, open_mode from v$containers;
  TYPE t_id is table of v$containers.con_id%TYPE;
  TYPE t_name is table of v$containers.name%TYPE;
  TYPE t_mode is table of v$containers.open_mode%TYPE;
  l_list_id t_id;
  l_list_name t_name;
  l_list_mode t_mode;
  l_con_id varchar2(256);
  l_con_name varchar2(256);
  l_stmt varchar2(500);
$END
begin
$IF DBMS_DB_VERSION.VERSION >= 12 $THEN
  if funct_db_version = 12 then
    if not cur_id_name_mode%ISOPEN then
      open cur_id_name_mode;
    end if; 
    fetch cur_id_name_mode BULK COLLECT into l_list_id, l_list_name, l_list_mode;
    if l_list_id.COUNT > 0 then
      for i in 1..l_list_id.COUNT
      loop
        if l_list_name(i) = 'CDB$ROOT' then
          dbms_output.put_line('Root ' || l_list_id(i) || ', ' || l_list_name(i) || ' open as ' || l_list_mode(i) );
        else
          dbms_output.put_line('Pluggable DB ' || l_list_id(i) || ', ' || l_list_name(i) || ' open as ' || l_list_mode(i) );
        end if;
      end loop; 
    end if;
    if cur_id_name_mode%ISOPEN then
      close cur_id_name_mode;
    end if; 
  end if;
$ELSE
  dbms_output.put_line('Version '|| DBMS_DB_VERSION.VERSION || '.' || DBMS_DB_VERSION.RELEASE || 
  ' does not support this code. Upgrade to 12c.');
$END
exception
  when others then
$IF DBMS_DB_VERSION.VERSION >= 12 $THEN
  if cur_id_name_mode%ISOPEN then
    close cur_id_name_mode;
  end if;
$END
  dbms_output.put_line(SQLERRM);
end proc_show_details_12c;
/
--Oracle Database 10.2
SQL> exec proc_show_details_12c;
Version 10.2 does not support this code. Upgrade to 12c.

PL/SQL procedure successfully completed.
--Oracle Database 12.1
SYS@orcl > exec proc_show_details_12c;
Root 1, CDB$ROOT open as READ WRITE
Pluggable DB 2, PDB$SEED open as READ ONLY
Pluggable DB 3, PDBORCL open as READ WRITE

PL/SQL procedure successfully completed.

Or, you can see all the above actions in a SQL*Plus Command Line Window:

Alter The Mode Of The Pluggable Database

Alter The Mode Of The Pluggable Database

task#4 Create a package with procedures that will return the same result as task#2 and task#3.


create or replace package pkg_checker
is
  function funct_db_version return number;
  procedure proc_show_details_12c;
  procedure proc_upgrade_to_12c;
end;
/


create or replace package body pkg_checker
is
  function funct_db_version
  return number
  is
    l_intval number;
    l_strval varchar2(2000);
    l_type number;
  begin
    l_type := dbms_utility.get_parameter_value ('optimizer_features_enable', l_intval, l_strval);
    return to_number(substr(l_strval,1,2));
  exception
    when others then
      dbms_output.put_line(SQLERRM);
  end;

  procedure proc_show_details_12c
  is
$IF DBMS_DB_VERSION.VERSION >= 12 $THEN
    CURSOR cur_id_name_mode
    is 
    select con_id, name, open_mode from v$containers;
    TYPE t_id is table of v$containers.con_id%TYPE;
    TYPE t_name is table of v$containers.name%TYPE;
    TYPE t_mode is table of v$containers.open_mode%TYPE;
    l_list_id t_id;
    l_list_name t_name;
    l_list_mode t_mode;
    l_con_id varchar2(256);
    l_con_name varchar2(256);
    l_stmt varchar2(500);
$END
  begin
    if not funct_db_version = 12 then
      dbms_output.put_line( 'The database ' || DBMS_DB_VERSION.VERSION || '.' || DBMS_DB_VERSION.RELEASE || ' should be updated to Oracle Database 12c. Go to www.oracle.com' );
      return;
    end if;
$IF DBMS_DB_VERSION.VERSION >= 12 $THEN
    if not cur_id_name_mode%ISOPEN then
      open cur_id_name_mode;
    end if; 
    fetch cur_id_name_mode BULK COLLECT into l_list_id, l_list_name, l_list_mode;
    if l_list_id.COUNT > 0 then
      for i in 1..l_list_id.COUNT
      loop
        if l_list_name(i) = 'CDB$ROOT' then
          dbms_output.put_line('Root ' || l_list_id(i) || ', ' || l_list_name(i) || ' open as ' || l_list_mode(i) );
        else
          dbms_output.put_line('Pluggable DB ' || l_list_id(i) || ', ' || l_list_name(i) || ' open as ' || l_list_mode(i) );
        end if;
      end loop; 
    end if;
    if cur_id_name_mode%ISOPEN then
      close cur_id_name_mode;
    end if; 
$END
  exception
    when others then
$IF DBMS_DB_VERSION.VERSION >= 12 $THEN
    if cur_id_name_mode%ISOPEN then
      close cur_id_name_mode;
    end if;
$END
    dbms_output.put_line(SQLERRM);
  end;

  procedure proc_upgrade_to_12c
  is
    l_stmt varchar2(500);
$IF DBMS_DB_VERSION.VERSION >= 12 $THEN
    CURSOR cur_id_name
    is 
    select con_id, name from v$containers where open_mode not IN ('READ WRITE', 'READ ONLY');
    TYPE t_id is table of v$containers.con_id%TYPE;
    TYPE t_name is table of v$containers.name%TYPE;
    l_list_id t_id;
    l_list_name t_name;
    l_con_id varchar2(256);
    l_con_name varchar2(256);
$END
  begin
    if not funct_db_version = 12 then
      dbms_output.put_line('The database ' || DBMS_DB_VERSION.VERSION || '.' || DBMS_DB_VERSION.RELEASE || ' should be updated to Oracle Database 12c. Go to www.oracle.com' );
      return;
    end if;
$IF DBMS_DB_VERSION.VERSION >= 12 $THEN
    if not cur_id_name%ISOPEN then
      open cur_id_name;
    end if; 
    fetch cur_id_name BULK COLLECT into l_list_id, l_list_name;
    if l_list_id.COUNT > 0 then
      for i in 1..l_list_id.COUNT
      loop
        l_stmt := 'alter pluggable database ' ||lower( l_list_name(i) )|| ' open read write';
        execute immediate l_stmt;
        dbms_output.put_line('Container ' || lower( l_list_name(i) )|| ' open READ WRITE' ) ;
      end loop;
    end if;
    select sys_context('USERENV','CON_ID') into l_con_id from dual;
    select sys_context('USERENV','CON_NAME') into l_con_name from dual;
    dbms_output.put_line('Container ' || l_con_name || ' has ID ' || l_con_id);
    dbms_output.put_line('$$PLSQL_CODE_TYPE = ' || $$PLSQL_CODE_TYPE);
    dbms_output.put_line('$$PLSQL_OPTIMIZE_LEVEL = ' || $$PLSQL_OPTIMIZE_LEVEL);
    dbms_output.put_line('$$PLSCOPE_SETTINGS = ' || $$PLSCOPE_SETTINGS);
    if cur_id_name%ISOPEN then
      close cur_id_name;
    end if;
$END
  exception
    when others then
$IF DBMS_DB_VERSION.VERSION >= 12 $THEN
    if cur_id_name%ISOPEN then
      close cur_id_name;
    end if;
$END
    dbms_output.put_line(SQLERRM);
  end;
end;
/
--Oracle Database 10.2
SQL> exec pkg_checker.proc_show_details_12c;
The database 10.2 should be updated to Oracle Database 12c. Go to www.oracle.com

PL/SQL procedure successfully completed.
--Oracle Database 12.1
SYS@orcl > exec pkg_checker.proc_show_details_12c;
Root 1, CDB$ROOT open as READ WRITE
Pluggable DB 2, PDB$SEED open as READ ONLY
Pluggable DB 3, PDBORCL open as READ WRITE

PL/SQL procedure successfully completed.
--Oracle Database 10.2
SQL> exec pkg_checker.proc_upgrade_to_12c;
The database 10.2 should be updated to Oracle Database 12c. Go to www.oracle.com

PL/SQL procedure successfully completed.
--Oracle Database 12.1
SYS@orcl > exec pkg_checker.proc_upgrade_to_12c;
Container CDB$ROOT has ID 1
$$PLSQL_CODE_TYPE = INTERPRETED
$$PLSQL_OPTIMIZE_LEVEL = 2
$$PLSCOPE_SETTINGS = IDENTIFIERS:NONE

PL/SQL procedure successfully completed.

Now, you can repeat the process over and over again, see the SQL*Plus Command Line Window:

Repeat the Action Over and Over Again

Repeat the Action Over and Over Again

So far, the following schema objects were created:

--Oracle Database 10.2
SYS$SQL> 
select substr(object_name,1,20), substr(object_type,1,20)
from user_objects  
where created > sysdate - 1;

SUBSTR(OBJECT_NAME,1 SUBSTR(OBJECT_TYPE,
-------------------- -------------------
PROC_UPGRADE_TO_12C  PROCEDURE
PROC_SHOW_DETAILS_12 PROCEDURE
PKG_CHECKER          PACKAGE
PKG_CHECKER          PACKAGE BODY
FUNCT_DB_VERSION     FUNCTION
--Oracle Database 12.1
SYS@orcl > select lpad(substr(object_type,1,20),20) type, substr(object_name,1,20) name, created --12.1
  2  from user_objects
  3  where object_name like 'FUNCT%'
  4  or object_name like 'PROC%'
  5  or object_name like 'PKG%';

TYPE                 NAME                 CREATED
-------------------- -------------------- ---------
             PACKAGE PKG_CHECKER          21-AUG-15
        PACKAGE BODY PKG_CHECKER          21-AUG-15
           PROCEDURE PROC_SHOW_DETAILS_12 21-AUG-15
           PROCEDURE PROC_UPGRADE_TO_12C  21-AUG-15
            FUNCTION FUNCT_DB_VERSION     21-AUG-15

10 rows selected.

Final Task# Drop all objects from the database that were created in the above examples!

SYS@orcl > drop function funct_db_version;
SYS@orcl > drop procedure proc_upgrade_to_12c;
SYS@orcl > drop procedure proc_show_details_12c;
SYS@orcl > drop package pkg_checker;

Instead of Summary

The purpose of the task#1 is to show how many solutions you can create for the one task, in this case it was to find out the version and the release of the Oracle Database. This can also be done easily with this select statement:

c:TEMP>sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Sep 10 18:14:23 2015

Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SYS@orcl > COL PRODUCT FORMAT A40
SYS@orcl > COL VERSION FORMAT A15
SYS@orcl > COL STATUS FORMAT A15
SYS@orcl > SELECT * FROM PRODUCT_COMPONENT_VERSION;

PRODUCT                               VERSION    STATUS
------------------------------------ ----------  ------------
NLSRTL                               12.1.0.2.0  Production
OracleDatabase12c Enterprise Edition 12.1.0.2.0  64bit Production
PL/SQL                               12.1.0.2.0  Production
TNS for 64-bit Windows:              12.1.0.2.0  Production

SYS@orcl >

Dictionary
sqlplus = “SQL*Plus is an interactive and batch query tool that is installed with every Oracle Database installation. It has a command-line user interface.”, [ref2]
spool = SQL*Plus command that stores query results in a file, or optionally sends the file to a printer, [ref2]
.ext = the extension of the file where command spool stores the query results, [ref2]
append = The option that adds the contents of the buffer to the end of the file you specify in the spool command, [ref2]
CDB = a multitenant container database,
PDB = a pluggable database,
v$parameter = The dynamic performance view that displays information about the initialization parameters that are currently in effect for the session, [ref3]
v$containers = The dynamic performance view that displays information about PDBs and the root associated with the current instance, [ref3]
select = SQL statement that retrieves data from one or more tables, object tables, views, object views, or materialized views, [ref1]
CDB$ROOT = Oracle Database 12c root container, [ref4]
PDB$SEED = Oracle Database 12c seed PDB, [ref4]
PDBORCL = Oracle Database 12c pluggable database, a user-created entity that contains the data and code required for a specific set of features, [ref4]
READ ONLY = A database that is available for queries only and cannot be modified, [ref4]
READ WRITE = A database that is available for queries and that can be modified, [ref4]
MOUNTED = An database instance that is started and has the database control file open, [ref4]
alter pluggable database = SQL statement that modifies a pluggable database (PDB), [ref1]
alter session = SQL statement that sets or modifies any of the conditions or parameters that affect your connection to the database. The statement stays in effect until you disconnect from the database, [ref1]
show user = SQL*Plus command that shows the username you are currently using to access SQL*Plus, [ref2]
show con_id = SQL*Plus command that displays the id of the Container to which you are connected when connected to a Consolidated Database. If issued when connected to a non-Consolidated Database, this command returns 0, [ref2]
exit = SQL*Plus command that let you exit SQL*Plus, [ref2]
case = SQL expressions that let you use IF … THEN … ELSE logic in SQL statements without having to invoke procedures, [ref1]

Further reading
If you want to be familiar with Oracle SQL, please see [ref1].
If you want to know what kind of the CLI (Command Line Interface) SQL*Plus is, please see [ref2].
if you want to know details of the Oracle Database meta data, please see [ref3].
If you want to understand the Oracle Database architecture, please see [ref4].
If you want to know details of PL/SQL (Oracle extension of SQL), please see [ref5].

References
[ref1] SQL*Plus® User’s Guide and Reference Release 12.1 E18404-12
[ref2] Oracle® Database SQL Language Reference 12c Release 1 (12.1) E41329-09
[ref3] Oracle® Database Reference 12c Release 1 (12.1) E41527-15
[ref4] Oracle® Database Concepts 12c Release 1 (12.1) E41396-12
[ref5] Oracle® Database PL/SQL Language Reference 12c Release 1 (12.1) E50727-04
[ref6] Oracle® Database PL/SQL Packages and Types Reference 12c Release 1 (12.1)
E41829-05

Using Static Data Dictionary Views

All that we do on the database is recorded inside the static data dictionary tables. Data dictionary tables are not directly accessible, and we query them through data dictionary views. It takes time to know the details of each view but in the end it gives you the freedom to keep under the control the following:

  • schema objects
  • schema objects VALID status
  • the amount of the consumed resources by the schema objects
  • the dependencies between schema objects
  • the granted privileges on schema objects

Many data dictionary tables have three corresponding views:

  • ALL_ view
  • DBA_ view
  • USER_ view

An ALL_ view displays all the information accessible to the current user, which includes information from the current user’s schema, information from objects in other schemas, if the access to those objects is granted through privileges or roles to the current user.

A DBA_ view displays all information in the entire database. DBA_ views are accessible only by administrators.
They can be accessed by less privileged users if they are granted the SELECT ANY TABLE privilege.
This privilege is assigned to the DBA role when the system is initially installed.

A USER_ view displays all the information from the schema of the current user. No special privileges are required to query these views.

The columns of the ALL_, DBA_, and USER_ views corresponding to a single data dictionary table are nearly identical.

Each user should query the view DICTIONARY to know what views are available to him.

In my current Oracle Database 11g2, the DBA can access 2551 views:

sys@XE> select count(*) from dictionary;

  COUNT(*)
----------
      2551

If I create a new schema ANA

sys@XE> create user ana identified by swdev
  2  default tablespace USERS
  3  temporary tablespace TEMP;

User created.

sys@XE> alter user ana identified by swdev account unlock;

User altered.

sys@XE> grant connect, resource to ana;

Grant succeeded.

and if I connect to the database as the user ANA/SWDEV, I can access 804 views.

ana@XE> select count(*) from dictionary;

  COUNT(*)
----------
       804

ana@XE> desc dictionary
 Name              Null?    Type
 ----------------- -------- ----------------
 TABLE_NAME                 VARCHAR2(30)
 COMMENTS                   VARCHAR2(4000)

Only 689 views have a comment.

ana@XE> select count(comments) from dictionary;

COUNT(COMMENTS)
---------------
            689

How many of ALL_ , DBA_, and USER_ views can I access?

ana@XE> select count(*) from dictionary where table_name like 'ALL%';

  COUNT(*)
----------
       339

ana@XE> select count(*) from dictionary where table_name like 'DBA%';

  COUNT(*)
----------
         0

ana@XE> select count(*) from dictionary where table_name like 'USER%';

  COUNT(*)
----------
       361

I can not access DBA_ views, but I can access 339 ALL_ views and 361 USER_ views.

What USER_TAB_ views can I access?


ana@XE> select * 
from dictionary 
where table_name like 'USER_TAB%' 
and comments is not null
and rownum  < 6
order by 1;

TABLE_NAME
------------------------------
COMMENTS
------------------------------
USER_TABLES
Description of the user's own relational tables

USER_TABLESPACES
Description of accessible tablespaces

USER_TAB_COLS
Columns of user's tables, views and clusters

USER_TAB_COLUMNS
Columns of user's tables, views and clusters

USER_TAB_COL_STATISTICS
Columns of user's tables, views and clusters



The list of USER_ views that I usually query during the day is presented in the table of the post published on July, 21 2015.
If you want to know more about static dictionary views, the Oracle Database Reference Guide is listed under the reference [ref13].

WHY and HOW do I use static data dictionary views?

I want to know what schema objects are already in my schema.

ana@XE> select object_name
  2      , object_type
  3      , created
  4      , status
  5      , temporary
  6      , namespace
  7      from user_objects;

no rows selected

As I have been a new user to the database, there is no schema objects in my schema ANA.

I can copy create statements from the existing schema and add some objects to my schema.

ana@XE> select object_type, count(*) total
  2  from user_objects
  3  group by object_type
  4  order by 1;

OBJECT_TYPE              TOTAL
-------------------      ----------
FUNCTION                     1
LOB                          2
SEQUENCE                     1
TABLE                        1
TRIGGER                      1

I created 1 function, 2 LOB objects, 1 sequence, 1 table, and 1 trigger.

What are their names?


ana@XE> select object_name
  2      , object_type
  3      , created
  4      , status
  5      , temporary
  6      , namespace
  7      from user_objects;

OBJECT_NAME
--------------------------------------------------------
OBJECT_TYPE              CREATED   STATUS    T  NAMESPACE
-------------------      -----     ------    -  ----------
FUNCT_GET_DDL
FUNCTION                 23-JUL-15   VALID   N          1

TWEETS
TABLE                    23-JUL-15   VALID   N          1

SYS_LOB0000020438C00003$$
LOB                      23-JUL-15   VALID   N          8

SYS_LOB0000020438C00002$$
LOB                      23-JUL-15   VALID   N          8

TWEETS_SEQ
SEQUENCE                 23-JUL-15   VALID   N          1

TWEETS_TR_INSERT
TRIGGER                  23-JUL-15   VALID   N          3


6 rows selected.

From here, I can easily query USER_ views and learn more about existing schema objects.

ana@XE> select * from user_tables;

TABLE_NAME                     TABLESPACE_NAME                CLUSTER_NAME
------------------------------ ------------------------------ ------------------------------
IOT_NAME                       STATUS     PCT_FREE   PCT_USED  INI_TRANS  MAX_TRANS INITIAL_EXTENT
------------------------------ -------- ---------- ---------- ---------- ---------- --------------
NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE  FREELISTS FREELIST_GROUPS LOG B   NUM_ROWS
----------- ----------- ----------- ------------ ---------- --------------- --- - ----------
    BLOCKS EMPTY_BLOCKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN AVG_SPACE_FREELIST_BLOCKS
---------- ------------ ---------- ---------- ----------- -------------------------
NUM_FREELIST_BLOCKS DEGREE
------------------- ----------------------------------------
INSTANCES                                CACHE                TABLE_LO SAMPLE_SIZE LAST_ANAL PAR
---------------------------------------- -------------------- -------- ----------- --------- ---
IOT_TYPE     T S NES BUFFER_ FLASH_C CELL_FL ROW_MOVE GLO USE DURATION        SKIP_COR MON
------------ - - --- ------- ------- ------- -------- --- --- --------------- -------- ---
CLUSTER_OWNER                  DEPENDEN COMPRESS COMPRESS_FOR DRO REA SEG RESULT_
------------------------------ -------- -------- ------------ --- --- --- -------
TWEETS                         USERS
                               VALID            10                     1        255          65536
    1048576           1  2147483645                                         YES N

                             1
         1                                   N                ENABLED                        NO
             N N NO  DEFAULT DEFAULT DEFAULT DISABLED NO  NO                  DISABLED YES
                               DISABLED DISABLED              NO  NO  YES DEFAULT

The table Tweets is created in the tablespace USERS, it does not belong to any cluster, and it is VALID.


ana@XE> select table_name, comments from user_tab_comments;

TABLE_NAME                    
COMMENTS
------------------------------------------------------
TWEETS                         
Tweets table contains tweets and their attached photos

ana@XE> select * from user_col_comments;

TABLE_NAME                     COLUMN_NAME
------------------------------ -----------------
COMMENTS
------------------------------------------------
TWEETS                         ID
The ID is the identification number of the tweet.

TWEETS                         TEXT
The content of the tweet.

TWEETS                         PHOTO
The photo attached to the tweet.

Tweets table has 3 columns, ID, TEXT, and PHOTO.


ana@XE> select table_name, column_name, substr(data_type, 1, 8) data_type from user_tab_cols;

TABLE_NAME        COLUMN_NAME     DATA_TYPE
----------        -----------     -------------
TWEETS            ID              NUMBER
TWEETS            TEXT            CLOB
TWEETS            PHOTO           BLOB


Tweets table columns are of CLOB and BLOB data types.


ana@XE> select * from user_sequences;

SEQUENCE_NAME MIN  MAX        INC C O CACHE_SIZE LAST_NUMBER
------------  ---- ---------- --- - - ---------- ----------- 
TWEETS_SEQ    1    1.0000E+28   1 N N          0          1

ana@XE> select * from user_triggers;

TRIGGER_NAME                   TRIGGER_TYPE
------------------------------ ----------------
TRIGGERING_EVENT
----------------------------------------------------------------------------------------------------
TABLE_OWNER                    BASE_OBJECT_TYPE TABLE_NAME
------------------------------ ---------------- ------------------------------
COLUMN_NAME
----------------------------------------------------------------------------------------------------
REFERENCING_NAMES
----------------------------------------------------------------------------------------------------
WHEN_CLAUSE
----------------------------------------------------------------------------------------------------
STATUS
--------
DESCRIPTION
----------------------------------------------------------------------------------------------------
ACTION_TYPE TRIGGER_BODY                                                                     CROSSED
----------- -------------------------------------------------------------------------------- -------
BEF BEF AFT AFT INS FIR APP
--- --- --- --- --- --- ---
TWEETS_TR_INSERT               BEFORE EACH ROW
INSERT
ANA                            TABLE            TWEETS

REFERENCING NEW AS NEW OLD AS OLD

ENABLED
"ANA"."TWEETS_TR_INSERT"
BEFORE INSERT ON tweets
FOR EACH ROW
PL/SQL      BEGIN                                                                            NO
            SELECT tweets_seq.nextval INTO :new.ID FROM DUAL;
            END;
NO  NO  NO  NO  NO  YES NO

The trigger TWEETS_TR_INSERT will fire on insert on the table TWEETS for each row, it is valid, and its body is listed in the column TRIGGER_BODY.


ana@XE> select * from user_procedures;

OBJECT_NAME
----------------------------------------------------------------------------------------------------
PROCEDURE_NAME                  OBJECT_ID SUBPROGRAM_ID OVERLOAD
------------------------------ ---------- ------------- ----------------------------------------
OBJECT_TYPE         AGG PIP IMPLTYPEOWNER                  IMPLTYPENAME                   PAR INT
------------------- --- --- ------------------------------ ------------------------------ --- ---
DET AUTHID
--- ------------
FUNCT_GET_DDL
                                    20434             1
FUNCTION            NO  NO                                                                NO  NO
NO  CURRENT_USER

TWEETS_TR_INSERT
                                    20444             1
TRIGGER             NO  NO                                                                NO  NO
NO  DEFINER

My schema contains two PL/SQL subprograms, one function FUNCT_GET_DDL, and one trigger TWEETS_TR_INSERT.

Below, the source code of both PL/SQL subprograms is listed.

ana@XE> select text from all_source where owner = 'ANA';

TEXT
----------------------------------------------------------------------------------------------------
FUNCTION       "FUNCT_GET_DDL"
  (  p_in_type VARCHAR2
   , p_in_schema VARCHAR2
   , p_in_name VARCHAR2 )
RETURN CLOB
AUTHID CURRENT_USER
IS

-- Define local variables.

  l_h NUMBER; --handle returned by OPEN
  l_th NUMBER; -- handle returned by ADD_TRANSFORM
  l_doc CLOB; --returned sql ddl statement

BEGIN

-- Specify the object type.

  l_h := DBMS_METADATA.OPEN(p_in_type);

-- Use filters to specify the particular object desired.

  DBMS_METADATA.SET_FILTER(l_h,'SCHEMA',p_in_schema);
  DBMS_METADATA.SET_FILTER(l_h,'NAME',p_in_name);

-- Request that the metadata be transformed into creation DDL.

  l_th := DBMS_METADATA.ADD_TRANSFORM(l_h,'DDL');

-- Fetch the object.

  l_doc := DBMS_METADATA.FETCH_CLOB(l_h);

-- Release resources.

  DBMS_METADATA.CLOSE(l_h);

-- Return the result

  RETURN l_doc;

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;


TRIGGER "ANA"."TWEETS_TR_INSERT"
BEFORE INSERT ON tweets
FOR EACH ROW
BEGIN
SELECT tweets_seq.nextval INTO :new.ID FROM DUAL;
END;

51 rows selected.

There are other useful USER_ views about table columns. From the USER_UPDATABLE_COLUMNS we can see what columns we can UPDATE.


ana@XE> select * from user_updatable_columns;

OWNER     TABLE_NAME      COLUMN_NAME    UPD INS  DEL
--------- -----------     -----------    --- ---  ---
ANA       TWEETS          ID             YES YES  YES
ANA       TWEETS          TEXT           YES YES  YES
ANA       TWEETS          PHOTO          YES YES  YES

The view USER_TRIGGER_COLS tells us that the trigger TWEETS_TR_INSERT inserts new value to the column TWEETS.ID.

ana@XE> select * from user_trigger_cols;

TRIGGER_OWNER                  TRIGGER_NAME                   TABLE_OWNER
------------------------------ ------------------------------ ------------------------------
TABLE_NAME
------------------------------
COLUMN_NAME
----------------------------------------------------------------------------------------------------
COL COLUMN_USAGE
--- -----------------
ANA                            TWEETS_TR_INSERT               ANA
TWEETS
ID
NO  NEW OUT

If we wanted to know what resources our table consumes, there are two useful views, USER_TABLESPACES and USER_SEGMENTS.

ana@XE> select * from user_tablespaces;

TABLESPACE_NAME                BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS
------------------------------ ---------- -------------- ----------- ----------- -----------
  MAX_SIZE PCT_INCREASE MIN_EXTLEN STATUS    CONTENTS  LOGGING   FOR EXTENT_MAN ALLOCATIO SEGMEN
---------- ------------ ---------- --------- --------- --------- --- ---------- --------- ------
DEF_TAB_ RETENTION   BIG PREDICA ENC COMPRESS_FOR
-------- ----------- --- ------- --- ------------
SYSTEM                               8192          65536                       1  2147483645
2147483645                   65536 ONLINE    PERMANENT LOGGING   NO  LOCAL      SYSTEM    MANUAL
DISABLED NOT APPLY   NO  HOST    NO

SYSAUX                               8192          65536                       1  2147483645
2147483645                   65536 ONLINE    PERMANENT LOGGING   NO  LOCAL      SYSTEM    AUTO
DISABLED NOT APPLY   NO  HOST    NO

UNDOTBS1                             8192          65536                       1  2147483645
2147483645                   65536 ONLINE    UNDO      LOGGING   NO  LOCAL      SYSTEM    MANUAL
DISABLED NOGUARANTEE NO  HOST    NO

TEMP                                 8192        1048576     1048576           1
2147483645            0    1048576 ONLINE    TEMPORARY NOLOGGING NO  LOCAL      UNIFORM   MANUAL
DISABLED NOT APPLY   NO  HOST    NO

USERS                                8192          65536                       1  2147483645
2147483645                   65536 ONLINE    PERMANENT LOGGING   NO  LOCAL      SYSTEM    AUTO
DISABLED NOT APPLY   NO  HOST    NO


ana@XE> select * from user_segments;

SEGMENT_NAME
---------------------------------------------------------------------------------
PARTITION_NAME                 SEGMENT_TYPE       SEGMENT_SU TABLESPACE_NAME
------------------------------ ------------------ ---------- ------------------------------
     BYTES     BLOCKS    EXTENTS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS   MAX_SIZE
---------- ---------- ---------- -------------- ----------- ----------- ----------- ----------
RETENTI MINRETENTION PCT_INCREASE  FREELISTS FREELIST_GROUPS BUFFER_ FLASH_C CELL_FL
------- ------------ ------------ ---------- --------------- ------- ------- -------
TWEETS
                               TABLE              ASSM       USERS
     65536          8          1          65536     1048576           1  2147483645 2147483645
                                                             DEFAULT DEFAULT DEFAULT

SYS_IL0000020438C00002$$
                               LOBINDEX           ASSM       USERS
     65536          8          1          65536     1048576           1  2147483645 2147483645
                                                             DEFAULT DEFAULT DEFAULT

SYS_IL0000020438C00003$$
                               LOBINDEX           ASSM       USERS
     65536          8          1          65536     1048576           1  2147483645 2147483645
                                                             DEFAULT DEFAULT DEFAULT

SYS_LOB0000020438C00002$$
                               LOBSEGMENT         ASSM       USERS
     65536          8          1          65536     1048576           1  2147483645 2147483645
                                                             DEFAULT DEFAULT DEFAULT

SYS_LOB0000020438C00003$$
                               LOBSEGMENT         ASSM       USERS
     65536          8          1          65536     1048576           1  2147483645 2147483645
                                                             DEFAULT DEFAULT DEFAULT

From the view USER_SEGMENTS, we can see that our table TWEETS is inside the segment TWEETS, and the segment belongs to the tablespace USERS.
The SQL statement CREATE TABLE TWEETS created two LOBSEGMENT segments, one for the column TEXT, and the other for the column PHOTO, and also it created two LOBINDEX segments, one for the column TEXT, and the other for the column PHOTO.

If we need more information about each column of the table TWEETS, we will query the view USER_TAB_COLS or USER_TAB_COLUMNS.


ana@XE> select * from user_tab_columns where COLUMN_NAME = 'TEXT';

TABLE_NAME                     COLUMN_NAME
------------------------------ ------------------------------
DATA_TYPE
----------------------------------------------------------------------------------------------------
DAT
---
DATA_TYPE_OWNER
----------------------------------------------------------------------------------------------------
DATA_LENGTH DATA_PRECISION DATA_SCALE N  COLUMN_ID DEFAULT_LENGTH
----------- -------------- ---------- - ---------- --------------
DATA_DEFAULT                                                                     NUM_DISTINCT
-------------------------------------------------------------------------------- ------------
LOW_VALUE
----------------------------------------------------------------
HIGH_VALUE                                                          DENSITY  NUM_NULLS NUM_BUCKETS
---------------------------------------------------------------- ---------- ---------- -----------
LAST_ANAL SAMPLE_SIZE CHARACTER_SET_NAME                           CHAR_COL_DECL_LENGTH GLO USE
--------- ----------- -------------------------------------------- -------------------- --- ---
AVG_COL_LEN CHAR_LENGTH C V80 DAT HISTOGRAM
----------- ----------- - --- --- ---------------
TWEETS                         TEXT
CLOB
       4000                           Y          2
                      CHAR_CS                                                      4000 NO  NO
                      0   NO  YES NONE

ana@XE> select * from user_tab_columns where COLUMN_NAME = 'PHOTO';

TABLE_NAME                     COLUMN_NAME
------------------------------ ------------------------------
DATA_TYPE
----------------------------------------------------------------------------------------------------
DAT
---
DATA_TYPE_OWNER
----------------------------------------------------------------------------------------------------
DATA_LENGTH DATA_PRECISION DATA_SCALE N  COLUMN_ID DEFAULT_LENGTH
----------- -------------- ---------- - ---------- --------------
DATA_DEFAULT                                                                     NUM_DISTINCT
-------------------------------------------------------------------------------- ------------
LOW_VALUE
----------------------------------------------------------------
HIGH_VALUE                                                          DENSITY  NUM_NULLS NUM_BUCKETS
---------------------------------------------------------------- ---------- ---------- -----------
LAST_ANAL SAMPLE_SIZE CHARACTER_SET_NAME                           CHAR_COL_DECL_LENGTH GLO USE
--------- ----------- -------------------------------------------- -------------------- --- ---
AVG_COL_LEN CHAR_LENGTH C V80 DAT HISTOGRAM
----------- ----------- - --- --- ---------------
TWEETS                         PHOTO
BLOB
       4000                           Y          3
                                                                                        NO  NO
                      0   NO  YES NONE

The CREATE TABLE TWEETS statement also created indexes for both LOB columns.

ana@XE> select * from user_indexes;

INDEX_NAME                     INDEX_TYPE                  TABLE_OWNER
------------------------------ --------------------------- ------------------------------
TABLE_NAME                     TABLE_TYPE  UNIQUENES COMPRESS PREFIX_LENGTH
------------------------------ ----------- --------- -------- -------------
TABLESPACE_NAME                 INI_TRANS  MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS
------------------------------ ---------- ---------- -------------- ----------- -----------
MAX_EXTENTS PCT_INCREASE PCT_THRESHOLD INCLUDE_COLUMN  FREELISTS FREELIST_GROUPS   PCT_FREE LOG
----------- ------------ ------------- -------------- ---------- --------------- ---------- ---
    BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY
---------- ----------- ------------- ----------------------- -----------------------
CLUSTERING_FACTOR STATUS     NUM_ROWS SAMPLE_SIZE LAST_ANAL DEGREE
----------------- -------- ---------- ----------- --------- ----------------------------------------
INSTANCES                                PAR T G S BUFFER_ FLASH_C CELL_FL USE DURATION
---------------------------------------- --- - - - ------- ------- ------- --- ---------------
PCT_DIRECT_ACCESS ITYP_OWNER                     ITYP_NAME
----------------- ------------------------------ ------------------------------
PARAMETERS
----------------------------------------------------------------------------------------------------
GLO DOMIDX_STATU DOMIDX FUNCIDX_ JOI IOT DRO VISIBILIT DOMIDX_MANAGEM SEG
--- ------------ ------ -------- --- --- --- --------- -------------- ---
SYS_IL0000020438C00002$$       LOB                         ANA
TWEETS                         TABLE       UNIQUE    DISABLED
USERS                                   2        255          65536     1048576           1
 2147483645                                                                              10 YES

                  VALID                                     0
0                                        NO  N Y N DEFAULT DEFAULT DEFAULT NO


NO                               NO  NO  NO  VISIBLE                  YES

SYS_IL0000020438C00003$$       LOB                         ANA
TWEETS                         TABLE       UNIQUE    DISABLED
USERS                                   2        255          65536     1048576           1
 2147483645                                                                              10 YES

                  VALID                                     0
0                                        NO  N Y N DEFAULT DEFAULT DEFAULT NO


NO                               NO  NO  NO  VISIBLE                  YES


The view USER_LOBS shows all details about table TWEETS LOB columns TEXT and PHOTO.

ana@XE> select * from user_lobs;

TABLE_NAME
------------------------------
COLUMN_NAME
----------------------------------------------------------------------------------------------------
SEGMENT_NAME                   TABLESPACE_NAME                INDEX_NAME
------------------------------ ------------------------------ ------------------------------
     CHUNK PCTVERSION  RETENTION  FREEPOOLS CACHE      LOGGING ENCR COMPRE DEDUPLICATION   IN_
---------- ---------- ---------- ---------- ---------- ------- ---- ------ --------------- ---
FORMAT          PAR SEC SEG RETENTI RETENTION_VALUE
--------------- --- --- --- ------- ---------------
TWEETS
TEXT
SYS_LOB0000020438C00002$$      USERS                          SYS_IL0000020438C00002$$
      8192                   900            NO         YES     NONE NONE   NONE            YES
ENDIAN NEUTRAL  NO  NO  YES YES

TWEETS
PHOTO
SYS_LOB0000020438C00003$$      USERS                          SYS_IL0000020438C00003$$
      8192                   900            NO         YES     NONE NONE   NONE            YES
NOT APPLICABLE  NO  NO  YES YES

If we query USER_LOBS for the specific information about LOBs, such as

  • if it is a BASICFILE or SECUREFILE
  • whether or not the LOB is encrypted
    • for SecureFiles: YES or NO
    • for BasicFiles: NONE – Not applicable
  • the level of compression used for the LOB
    • for SecureFiles: LOW, MEDIUM, HIGH, NO
    • for BasicFiles: NONE – Not applicable
  • kind of deduplication used for this LOB
    • for SecureFiles: LOB – Deduplicate, NO – Keep duplicates
    • for BasicFiles: NONE – Not applicable

we will do it with the following statement:


ana@XE> select table_name
  2  , column_name
  3  , segment_name
  4  , tablespace_name
  5  , index_name
  6  , cache
  7  , logging
  8 , securefile
  9  , compression
 10  , deduplication
 11  , encrypt
 12  from user_lobs;
TABLE_NAME
------------------------------
COLUMN_NAME
----------------------------------------------------------------------------------------------------
SEGMENT_NAME                   TABLESPACE_NAME                INDEX_NAME
------------------------------ ------------------------------ ------------------------------
CACHE      LOGGING SEC COMPRE DEDUPLICATION   ENCR
---------- ------- --- ------ --------------- ----
TWEETS
TEXT
SYS_LOB0000020438C00002$$      USERS                          SYS_IL0000020438C00002$$
NO         YES     NO  NONE   NONE            NONE

TWEETS
PHOTO
SYS_LOB0000020438C00003$$      USERS                          SYS_IL0000020438C00003$$
NO         YES     NO  NONE   NONE            NONE


Both LOBs, TEXT and PHOTO, are BASICFILEs, they are not compressed, deduplicated or encrypted.

And finally, we should know what schema objects are dependent on.

The function FUNCT_GET_DDL depends on the packages STANDARD, DBMS_METADATA, DBMS_OUTPUT, SYS_STUB_FOR_PURITY_ANALYSIS, and DBMS_LOB.

The trigger TWEETS_TR_INSERT depends on the package STANDARD, the tables DUAL and TWEETS, and the sequence TWEETS_SEQ.

ana@XE> select * from user_dependencies;

NAME                           TYPE               REFERENCED_OWNER
------------------------------ ------------------ ------------------------------
REFERENCED_NAME                                                  REFERENCED_TYPE
---------------------------------------------------------------- ------------------
REFERENCED_LINK_NAME
----------------------------------------------------------------------------------------------------
  SCHEMAID DEPE
---------- ----
FUNCT_GET_DDL                  FUNCTION           SYS
STANDARD                                                         PACKAGE
        49 HARD

FUNCT_GET_DDL                  FUNCTION           PUBLIC
DBMS_METADATA                                                    SYNONYM
        49 HARD

FUNCT_GET_DDL                  FUNCTION           PUBLIC
DBMS_OUTPUT                                                      SYNONYM
        49 HARD

FUNCT_GET_DDL                  FUNCTION           SYS
SYS_STUB_FOR_PURITY_ANALYSIS                                     PACKAGE
        49 HARD

FUNCT_GET_DDL                  FUNCTION           SYS
DBMS_LOB                                                         PACKAGE
        49 HARD

TWEETS_TR_INSERT               TRIGGER            SYS
STANDARD                                                         PACKAGE
        49 HARD

TWEETS_TR_INSERT               TRIGGER            PUBLIC
DUAL                                                             SYNONYM
        49 HARD

TWEETS_TR_INSERT               TRIGGER            ANA
TWEETS                                                           TABLE
        49 HARD

TWEETS_TR_INSERT               TRIGGER            ANA
TWEETS_SEQ                                                       SEQUENCE
        49 HARD

9 rows selected.

SUMMARY

The Oracle Database is a complex system that contains many information inside itself.

The static data dictionary views collect all actions on the schema objects and it is necessary to know them better as that knowledge increases a developer productivity in everyday work.

REFERENCE

[ref13]Oracle® Database Reference 11g Release 2 (11.2) E25513-04

I hope this will be useful for anyone who would like to learn more about Oracle Database.

Thank you for visiting my blog!

Have a nice day!

P.S. Keep climbing Mount Olympus ( I mean keep learning and make things happen ) !

Static Data Dictionary Views

This is the list of the static data dictionary views that are very useful whatever you do with the schema objects and which I will refer in the posts that are coming.

VIEW NAME DESCRIPTION
USER_ALL_TABLES

describes the object tables and relational tables owned by the current user.

USER_ARGUMENTS

lists the arguments of the functions and procedures that are owned by the current user.

USER_CATALOG

lists tables, views, clusters, synonyms, and sequences owned by the current user.

USER_CLUSTERS

describes all the clusters owned by the current user.

USER_COL_COMMENTS

displays comments on the columns of the tables and views owned by the current user.

USER_COL_PENDING_STATS

describes the pending statistics of the columns owned by the current user.

USER_COL_PRIVS

describes the column object grants for which the current user is the object owner, grantor, or grantee.

USER_COL_PRIVS_MADE

describes the column object grants for which the current user is the object owner.

USER_COL_PRIVS_RECD

describes the column object grants for which the current user is the grantee.

USER_COLL_TYPES

describes named collection types (VARRAYs, nested tables, object tables, and so on) in the current user’s schema.

USER_CONS_COLUMNS

describes columns that are owned by the current user and that are specified in constraint definitions.

USER_CONS_OBJ_COLUMNS displays information about the types that object columns (or attributes) or collection elements have been constrained to, in the tables owned by the
current user.

USER_CONSTRAINTS

describes all constraint definitions on tables owned by the current user.

USER_CREDENTIALS

displays credentials owned by the current user.

USER_ENCRYPTED_COLUMNS

maintains encryption algorithm information for all encrypted columns in all tables in the user’s schema.

USER_ERRORS

describes the current errors on the stored objects owned by the current user.

USER_EXTENTS

describes the extents comprising the segments owned by the current user’s objects.

USER_EXTERNAL_LOCATIONS

describes the locations (data sources) of the external tables owned by the current user.

USER_EXTERNAL_TABLES

describes the external tables owned by the current user.

USER_FREE_SPACE

describes the free extents in the tablespaces accessible to the current user.

USER_IDENTIFIERS

displays information about the identifiers in the stored objects owned by the current user.

USER_INDEXES

describes indexes owned by the current user. To gather statistics for this view, use the DBMS_STATS package. This view supports parallel partitioned index scans.

USER_INDEXTYPES

describes the indextypes owned by the current user.

USER_INTERNAL_TRIGGERS

describes the internal triggers on all tables owned by the current user.

USER_LIBRARIES

describes the libraries owned by the current user

USER_LOB_PARTITIONS

displays the LOB partitions contained in the tables owned by the current user.

USER_LOB_SUBPARTITIONS

displays partition-level attributes of the LOB data subpartitions owned by the current user.

USER_LOBS

displays the user’s CLOBs and BLOBs contained in the user’s tables. BFILEs are stored outside the database, so they are not described by this view

USER_NESTED_TABLE_COLS

describes the columns of the nested tables owned by the current user.

USER_NESTED_TABLES

describes the nested tables in tables owned by the current user.

USER_OBJECT_TABLES

describes the object tables owned by the current user.

USER_OBJECTS

describes all objects owned by the current user.

USER_OPERATORS

describes all operators owned by the current user.

USER_PASSWORD_LIMITS

describes the password profile parameters that are assigned to the user.

USER_PLSQL_COLL_TYPES

describes the user’s own named PL/SQL collection types.

USER_PLSQL_TYPES

describes the user’s own PL/SQL types.

USER_POLICIES

describes all Oracle Virtual Private Database (VPD) security policies associated with objects owned by the current user.

USER_POLICY_ATTRIBUTES

lists the attribute associations {Namespaces, Attributes} of all context-sensitive and shared-context sensitive Oracle Virtual Private Database
(VPD) policies for synonyms, tables, or views owned by the user.

USER_POLICY_CONTEXTS

describes the driving contexts defined for the synonyms, tables, and views owned by the current user.

USER_POLICY_GROUPS

describes the policy groups defined for the synonyms, tables, and views owned by the current user.

USER_PROCEDURES

lists all functions and procedures that are owned by the current user, along with their associated properties.

USER_ROLE_PRIVS

describes the roles granted to the current user.

USER_SEGMENTS

describes the storage allocated for the segments owned by the current user’s objects.

USER_SEQUENCES

describes all sequences owned by the current user.

USER_SOURCE

describes the text source of the stored objects owned by the current user.

USER_SQLSET

displays information about the SQL tuning sets owned by the current user.

USER_SQLSET_BINDS

displays the bind values associated with the SQL tuning sets owned by the current user.

USER_STORED_SETTINGS

lists information about the persistent parameter settings for stored PL/SQL units, but only shows information about PL/SQL units owned by the
current user.

USER_SYNONYMS

describes the private synonyms (synonyms owned by the current user).

USER_SYS_PRIVS

describes system privileges granted to the current user.

USER_TAB_COLS

describes the columns of the tables, views, and clusters owned by the
current user. This view differs from “USER_TAB_COLUMNS” in that system-generated
hidden columns and invisible columns, which are user-generated
hidden columns, are not filtered out.

USER_TAB_COLUMNS

describes the columns of the tables, views, and clusters owned by
the current user. The USER_TAB_COLS view can display system-generated hidden columns and invisible
columns, which are user-generated hidden columns.

USER_TAB_COMMENTS

displays comments on the tables and views owned by the current user.

USER_TAB_PRIVS

describes the object grants for which the current user is the object owner, grantor, or grantee.

USER_TAB_PRIVS_MADE

describes the object grants for which the current user is the object owner.

USER_TAB_PRIVS_RECD

describes the object grants for which the current user is the grantee.

USER_TAB_STAT_PREFS

displays information about statistics preferences for the tables owned by the current user.

USER_TAB_SUBPARTITIONS

describes, for each table subpartition owned by the current user, the subpartition name, name of the table and partition to which it belongs, and its
storage attributes.

USER_TABLES

describes the relational tables owned by the current user.

USER_TABLESPACES

describes the tablespaces accessible to the current user.

USER_TRIGGER_COLS

describes the use of columns in the triggers owned by the current user and in triggers on tables owned by the current user.

USER_TRIGGER_ORDERING

describes the triggers owned by the current user that have FOLLOWS or PRECEDES ordering.

USER_TRIGGERS

describes the triggers owned by the current user.

USER_TYPES

describes the object types owned by the current user.

USER_UNUSED_COL_TABS

describes the tables owned by the current user that contain unused columns.

USER_UPDATABLE_COLUMNS

describes columns in a join view that can be updated by the current user, subject to appropriate privileges.

USER_USERS

describes the current user.

USER_VARRAYS

describes the varrays owned by the current user.

USER_VIEWS

describes the views owned by the current user.

USER_WALLET_ACES describes the status of access control entries for the current user to access wallets through PL/SQL network utility packages. Its columns (except for ACE_ORDER, START_DATE, END_DATE, GRANT_TYPE, INVERTED_PRINCIPAL, PRINCIPAL, PRINCIPAL_TYPE, and STATUS) are the same as those in DBA_WALLET_ACES.

I hope this will be helpful for anyone who would like to learn more about Oracle Database.

Thank you for visiting my blog!

Have a nice day!

P.S. Keep climbing Mount Olympus ( I mean keep learning and make things happen ) !