External Tables

 

External tables are created using the SQL CREATE TABLE...ORGANIZATION EXTERNAL statement.

TYPE — specifies the type of external table. Each type of external table is supported by its own access driver.

ORACLE_LOADER — this is the default access driver. It loads data from external tables to internal tables. The data must come from text data files.

ORACLE_DATAPUMP — this access driver can perform both loads and unloads. The data must come from binary dump files.

• ORACLE_HIVE — extracts data stored in Apache HIVE.

SQL> CREATE TABLE emp_load

  2    (employee_number      CHAR(5),

  3     employee_dob         CHAR(20),

  4     employee_last_name   CHAR(20),

  5     employee_first_name  CHAR(15),

  6     employee_middle_name CHAR(15),

  7     employee_hire_date   DATE)

  8  ORGANIZATION EXTERNAL

  9    (TYPE ORACLE_LOADER

 10     DEFAULT DIRECTORY def_dir1

 11     ACCESS PARAMETERS

 12       (RECORDS DELIMITED BY NEWLINE

 13        FIELDS (employee_number      CHAR(2),

 14                employee_dob         CHAR(20),

 15                employee_last_name   CHAR(18),

 16                employee_first_name  CHAR(11),

 17                employee_middle_name CHAR(11),

 18                employee_hire_date   CHAR(10) date_format DATE mask "mm/dd/yyyy"

 19               )

 20       )

 21     LOCATION ('info.dat')

 22    );

 

Table created.

SQL> CREATE TABLE inventories_EXT

2 ORGANIZATION EXTERNAL

3 (

4 TYPE ORACLE_DATAPUMP

5 DEFAULT DIRECTORY def_dir1

6 LOCATION ('inv_xt.dmp')

7 )

8 AS SELECT * FROM inventories;

Table created.

LISTAGG Function

 https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/LISTAGG.html

As an Aggregate Function

SELECT department_id "Dept.", LISTAGG(last_name, '; ' ON OVERFLOW TRUNCATE '...') WITHIN GROUP (ORDER BY hire_date) "Employees" FROM employees GROUP BY department_id ORDER BY department_id;

Dept. Employees ------ ------------------------------------------------------------ 10 Whalen 20 Hartstein; Fay 30 Raphaely; Khoo; Tobias; Baida; Himuro; Colmenares 40 Mavris 50 Kaufling; Ladwig; Rajs; Sarchand; Bell; Mallin; Weiss; Davie s; Marlow; Bull; Everett; Fripp; Chung; Nayer; Dilly; Bissot ; Vollman; Stiles; Atkinson; Taylor; Seo; Fleaur; ... (23) 70 Baer . . .

As an Analytic Function

SELECT department_id "Dept", hire_date "Date", last_name "Name", LISTAGG(last_name, '; ') WITHIN GROUP (ORDER BY hire_date, last_name) OVER (PARTITION BY department_id) as "Emp_list" FROM employees WHERE hire_date < '01-SEP-2003' ORDER BY "Dept", "Date", "Name"; Dept Date Name Emp_list ----- --------- --------------- --------------------------------------------- 30 07-DEC-02 Raphaely Raphaely; Khoo 30 18-MAY-03 Khoo Raphaely; Khoo

Bulk Insertion - INSERT ALL

 

ALTER SESSION SET CURSOR_SHARING = FORCE;

1. INSERT ALL
2. INTO my_table (column1, column2, column_n) VALUES (expr1, expr2, expr_n)
3. INTO my_table (column1, column2, column_n) VALUES (expr1, expr2, expr_n)
4. INTO my_table2 (column1, column2, column_n) VALUES (expr1, expr2, expr_n)
5. INTO my_table3 (column1, column2, column_n) VALUES (expr1, expr2, expr_n)
6. WHEN CONDITION THEN
7. INTO my_table4 (column1, column2, column_n) VALUES (expr1, expr2, expr_n)
8. SELECT * FROM dual;

DBMS_APPLICATION_INFO

 DBMS_APPLICATION_INFO.set_module
(
module_name => g_module_name||'.'||g_block_name,
action_name => 'Initializing ...'
);

DBMS_APPLICATION_INFO.read_module(l_prev_module_block,l_prev_action);

DBMS_APPLICATION_INFO.set_client_info
        (
        client_info =>g_module_name||'.'||g_block_name
        );

DBMS_APPLICATION_INFO.read_client_info (l_curr_module_block);

DBMS_REDEFINITION- DeFragamentation

 Defragamentation of Tables  in database

https://oracle-base.com/articles/11g/online-table-redefinition-enhancements-11gr1

https://medium.com/@dmitry.romanoff/how-to-check-fragmentation-in-an-oracle-database-a575b8a6e2ea

Calculate Fragmented Space in Tables

Check Db_Block_Size  parameter , here it is 8 KB.

SELECT      
  table_name,     
  avg_row_len, num_rows,    
  ROUND(((blocks*8/1024)),2) || 'MB' AS "TOTAL_SIZE",     
  ROUND((num_rows*avg_row_len/1024/1024),2) || 'MB' AS "ACTUAL_SIZE",     
  ROUND(((blocks*8/1024) - (num_rows*avg_row_len/1024/1024)),2) || 'MB' AS "FRAGMENTED_SPACE",     
  (ROUND(((blocks*8/1024) - (num_rows*avg_row_len/1024/1024)),2) / ROUND(((blocks*8/1024)),2)) * 100 AS "PERCENTAGE" 
FROM user_tables  
WHERE ROUND(((blocks*8/1024)),2) > 0 
ORDER BY 6 DESC;

Once you’ve identified fragmentation, consider the following methods to address it:

ALTER TABLE YOUR_TABLE_NAME MOVE;

ALTER INDEX YOUR_INDEX_NAME REBUILD;

For large tables, consider using online redefinition to minimize downtime:

BEGIN
   DBMS_REDEFINITION.START_REDEF_TABLE (
      uname => 'COMMON', orig_table => 'CITY', int_table => 'CITY_TMP');
END;
EXEC DBMS_REDEFINITION.sync_interim_table('COMMON', 'CITY', 'CITY_TMP'); 
ALTER TABLE COMMON.CITY_TMP ADD (CONSTRAINT CITY_pk2 PRIMARY KEY (CITY_ID));
EXEC DBMS_REDEFINITION.finish_redef_table('COMMON', 'CITY', 'CITY_TMP'); 

Online Table Redefinition Including Dependents (COPY_TABLE_DEPENDENTS)

The above process drops the dependent Trigger , indexes on the table.

So we use dbms_redefinition.copy_table_dependents   to copy dependent objects like triggers, indexes, constraints, grants and optionally its statistics and materialized view logs.

-- Copy dependents.

SET SERVEROUTPUT ON

DECLARE
  l_num_errors PLS_INTEGER;
BEGIN
  DBMS_REDEFINITION.copy_table_dependents(
    uname               => 'TEST',
    orig_table          => 'REDEF_TAB',
    int_table           => 'REDEF_TAB2',
    copy_indexes        => 1,             -- Default
    copy_triggers       => TRUE,          -- Default
    copy_constraints    => TRUE,          -- Default
    copy_privileges     => TRUE,          -- Default
    ignore_errors       => FALSE,         -- Default
    num_errors          => l_num_errors,
    copy_statistics     => FALSE,         -- Default
    copy_mvlog          => FALSE);        -- Default
    
  DBMS_OUTPUT.put_line('num_errors=' || l_num_errors); 
END;
/