Oracle External Table

Whit this feature you can access data in external sources as if it were a table in the database.
Prior to release 10g external tables were read-only (9i); from release 10g external tables can also be
written too.
To use the external tables feature you must have knowledge of the file format and record format of the datafiles on your platform if the ORACLE_LOADER access driver is used and the “source” is in text format.
When you create an external table you must specify :
o TYPE: ORACLE_LOADER (default). You can run only for loading data and the data must come from a file of text data. Loading from external tables to internal tables from the table is done by reading data files only external text.
o TYPE: ORACLE_DATAPUMP can perform both loads and unloads. The data must come from binary dump files. Uploads in internal tables from external tables are done by fetching from the binary dump files. Unloads from internal tables to external tables are done by populating the external tables binary file.

To do before create an external table

From a sqlplus session:

Sqlplus / as sysdba

1) CREATE DIRECTORY external_tab_dir AS ‘/usr/my/files’;
2) GRANT READ ON DIRECTORY external_tab_dir TO username;

Create an external table :
SQL> CREATE TABLE table_name
(column1 CHAR(5),
Column2 CHAR(20),
Column3 CHAR(15),
Column4 DATE)
ORGANIZATION EXTERNAL
( PARAMETERS
(RECORDS DELIMITED BY NEWLINE
FIELDS (field_col1 CHAR(2),
field_col2 CHAR(18),
field_col3 CHAR(11),
field_col4 CHAR(10) date_format DATE mask mm/dd/yyyy”
)
)
LOCATION (‘source.dat’)
);

Table created.

Creating Tablespaces in ASM

 

When ASM creates a datafile for a permanent tablespace, the datafile is set to auto-extensible with an unlimited maximum size and 100 MB default size. You can use the AUTOEXTEND clause to override this default extensibility and the SIZE clause to override the default size.

ASM applies attributes to the datafile, as specified in the system default template for a datafile as shown in the table in Managing disk group templates
If there is a disk group named DATA, you can create a tablespace TBSLAVORO in that disk group with the following SQL statement:

CREATE TABLESPACE TBSLAVORO DATAFILE ‘+DATA’;

The following example illustrates the usage of Oracle ASM with defaults. This example enables Oracle ASM to create and manage the tablespace data file for you, using Oracle supplied defaults that are adequate for most situations.

Assume the following initialization parameter setting:

DB_CREATE_FILE_DEST = +data

The following statement creates the tablespace and its data file:

CREATE TABLESPACE tblspace;

Problem affecting rman and redolog

 

I have worked on a db  were a rman configured to make a backup every 2 hour that means every two hours we found in the alert.log ALTER SYSTEM ARCHIVE LOG and the db suddenly slows.

The problem was the db has only 3 redolog.

When the backup started rman forces the ALTER SYSTEM ARCHIVE LOG and oracle needs to find a free redolog. it switched the redolog and as soon it fill the new redolog the archive must finish. if not oracle does not have a free redolog to switch, forced it to wait ( the db seems frozen ) the end of the backup.

the solution is to add more redolog, at least 8 to be sure one is always free to oracle’s disposal.

 

 

Search records and null

Find in a table, for any field of the table, how many records are NULL, and of these NULL how many separate by values of another field of the same table.

Example result:

TAB_NAME                      COL_NAME                      NUM_ROWS                   NUM_NULLS_TOT       NUM_NULLS_X_COLVAL               COLVAL_LE_ID

————————————————————————————-

S_REPORT                        S_TAX_BRACKET_CD                        7111600   7111599   2470            631

S_REPORT                        S_TAX_BRACKET_CD                        7111600   7111599   57859         FC222200

S_REPORT                        S_TAX_BRACKET_CD                        7111600   7111599   3038882   Z2030

S_REPORT                        S_TAX_BRACKET_CD                        7111600   7111599   4012388   C8101

 

The S_REPORT table has 7111600 rows and has a column name S_TAX_BRACKET_CD that has 7111599 NULL rows. The S_REPORT table also has a column name LE_ID which its value are 631, FC222200, Z2030 e C8101. The NULL rows are separated for column name LE_ID values in:

LE_ID=631 has 2470 NULL rows

LE_ID=FC222200 has 57859 NULL rows

LE_ID=Z2030 has 3038882 NULL rows

LE_ID=C8101 has 4012388 NULL rows

Below the SQL script code that create two tables (T_NUM_NULLS and T_NUM_NULLS_X_COLVAL), and one view (V_NUM_NULLS_X_COLVAL) where find the result after script run. Important: before the script run in your DB, modify the “nametab” and “namecol” default variable value in the script according to your research.

 

/* 

/* Procedure that find, for every column name in the tables specified in nametab variable, how many records are NULL,                  */

/* grouped by column value specified in namecol variable.                                                                              */

/* The result of the procedure are in: V_NUM_NULLS_X_COLVAL view, T_NUM_NULLS and T_NUM_NULLS_X_COLVAL tables                          */

/* Modify how would you prefer the default nametab and namecol values                                                                  */

/*                                                                                                                                     */

/* NOTICES:                                                                                                                            */

/* 1) The column name contents in namecol variable must be present in all tables where you execute the research of NULL values.        */

/* 2) The procedure has locked the data type dimension in the EXECUTE IMMEDIATE strings – this could be cause run errors.              */

/* 3) Please rispect the standard naming rules of Oracle database objects for the content of the nametab and namecol variables,        */

/*    otherwise the procedure will return an error.                                                                                    */

DECLARE

cnt INTEGER;– Variabile 0 o 1 numero record trovati – 0 significa che la tabella T_NUM_NULLS non esiste

nametab VARCHAR2(30)default’S_REPORT%’;– Name/s table/s string variable

namecol VARCHAR2(30)default’LE_ID’;– Column name string variable that contains the categories to distinguish NULLS

numtabtot INTEGER;

numtabok INTEGER;

BEGIN

 

– Variables values check

nametab :=REPLACE(nametab,’ ‘,”);

namecol :=REPLACE(namecol,’ ‘,”);

namecol :=REPLACE(namecol,’%’,”);

 

– Check if exists the T_NUM_NULLS table. If exists, drop and create it, otherwise create it.

SELECT count(*)INTO cnt FROM USER_TABLES WHERE table_name =’T_NUM_NULLS’;

 

IF cnt =0

THEN

EXECUTEIMMEDIATE’create table T_NUM_NULLS (TAB_NAME VARCHAR2(30), COL_NAME VARCHAR2(30), NUM_ROWS NUMBER, NUM_NULLS NUMBER)’;

ELSE

EXECUTEIMMEDIATE’drop table T_NUM_NULLS’;

EXECUTEIMMEDIATE’create table T_NUM_NULLS (TAB_NAME VARCHAR2(30), COL_NAME VARCHAR2(30), NUM_ROWS NUMBER, NUM_NULLS NUMBER)’;

ENDIF;

– Check if exists the T_NUM_NULLS_X_COLVAL table. If exists, drop and create it, otherwise create it.

SELECT count(*)INTO cnt FROM USER_TABLES WHERE table_name =’T_NUM_NULLS_X_COLVAL’;

 

IF cnt =0

THEN

EXECUTEIMMEDIATE’create table T_NUM_NULLS_X_COLVAL (TAB_NAME VARCHAR2(30), COL_NAME VARCHAR2(30), NUM_NULLS NUMBER, COLVAL_’ || namecol || ‘ VARCHAR2(8))’;

ELSE

EXECUTEIMMEDIATE’drop table T_NUM_NULLS_X_COLVAL’;

EXECUTEIMMEDIATE’create table T_NUM_NULLS_X_COLVAL (TAB_NAME VARCHAR2(30), COL_NAME VARCHAR2(30), NUM_NULLS NUMBER, COLVAL_’ || namecol || ‘ VARCHAR2(8))’;

ENDIF;

– Fills numtabok and numtabtot variables to check that the column name content in namecol variable, exists in all tables where we doing the research of NULLS, in order to avoid below query errors.

SELECT COUNT(*)INTO numtabok

FROM

(

SELECTDISTINCT(table_name)

FROM ALL_TAB_COLUMNS

WHERE1=1

AND TABLE_NAME LIKE” || nametab || ”

)

;

SELECT COUNT(*)INTO numtabtot

FROM

(

SELECTDISTINCT(t2.table_name), t2.COLUMN_NAME

FROM

(

SELECTDISTINCT(table_name), COLUMN_NAME

FROM ALL_TAB_COLUMNS

WHERE1=1

AND table_name LIKE” || nametab || ”

) t1

INNERJOIN

(

SELECTDISTINCT(table_name),COLUMN_NAME

FROM ALL_TAB_COLUMNS

WHERE1=1

AND table_name LIKE” || nametab || ”

AND COLUMN_NAME =” || namecol || ”

) t2

ON(t1.table_name = t2.table_name)

)

;

 

– Creating view that contains the procedure result.

EXECUTEIMMEDIATE’CREATE OR REPLACE VIEW V_NUM_NULLS_X_COLVAL AS SELECT t1.TAB_NAME, t1.COL_NAME, t1.NUM_ROWS, t1.NUM_NULLS NUM_NULLS_TOT, t2.NUM_NULLS NUM_NULLS_X_COLVAL, t2.COLVAL_’ || namecol || ‘ FROM T_NUM_NULLS t1 LEFT OUTER JOIN T_NUM_NULLS_X_COLVAL t2 ON t1.TAB_NAME = t2.TAB_NAME AND t1.COL_NAME = t2.COL_NAME ‘;

IF numtabtot = numtabok

THEN

FOR someone IN

(

select t1.TABLE_NAME, t2.COLUMN_NAME, t1.NUM_ROWS, t2.NUM_NULLS

from

(

select TABLE_NAME, NUM_ROWS

from USER_TABLES

where1=1

AND table_name IN

(

SELECTDISTINCT(table_name)

FROM ALL_TAB_COLUMNS

WHERE1=1

AND table_name LIKE” || nametab || ”

)

) t1

INNERJOIN

(

select TABLE_NAME, COLUMN_NAME, NUM_NULLS

from ALL_TAB_COL_STATISTICS

where1=1

AND table_name IN

(

SELECTDISTINCT(table_name)

FROM ALL_TAB_COLUMNS

WHERE1=1

AND table_name LIKE” || nametab || ”

)

) t2

on t1.TABLE_NAME = t2.TABLE_NAME

)

LOOP

EXECUTEIMMEDIATE’insert into T_NUM_NULLS (TAB_NAME, COL_NAME, NUM_ROWS, NUM_NULLS) values (”’ || someone.TABLE_NAME || ”’, ”’ || someone.COLUMN_NAME || ”’, ”’ || someone.NUM_ROWS || ”’, ”’ || someone.NUM_NULLS || ”’)’;

EXECUTEIMMEDIATE’insert into T_NUM_NULLS_X_COLVAL (TAB_NAME, COL_NAME, NUM_NULLS, COLVAL_’ || namecol || ‘) ‘ || ‘select ”’ || someone.TABLE_NAME || ”’ TABLE_NAME, ”’ || someone.COLUMN_NAME || ”’ COLUMN_NAME, count(*) NUM_NULLS, ‘ || namecol || ‘ from ‘ || someone.TABLE_NAME || ‘ where ‘ || someone.COLUMN_NAME || ‘ is null group by ‘ || namecol || ”;

ENDLOOP;

ELSE

FOR someone IN

(

select t1.TABLE_NAME, t2.COLUMN_NAME, t1.NUM_ROWS, t2.NUM_NULLS

from

(

select TABLE_NAME, NUM_ROWS

from USER_TABLES

where1=1

AND table_name IN

(

SELECTDISTINCT(table_name)

FROM ALL_TAB_COLUMNS

WHERE1=1

AND table_name LIKE” || nametab || ”

)

) t1

INNERJOIN

(

select TABLE_NAME, COLUMN_NAME, NUM_NULLS

from ALL_TAB_COL_STATISTICS

where1=1

AND table_name IN

(

SELECTDISTINCT(table_name)

FROM ALL_TAB_COLUMNS

WHERE1=1

AND table_name LIKE” || nametab || ”

)

) t2

on t1.TABLE_NAME = t2.TABLE_NAME

)

LOOP

EXECUTEIMMEDIATE’insert into T_NUM_NULLS (TAB_NAME, COL_NAME, NUM_ROWS, NUM_NULLS) values (”’ || someone.TABLE_NAME || ”’, ”’ || someone.COLUMN_NAME || ”’, ”’ || someone.NUM_ROWS || ”’, ”’ || someone.NUM_NULLS || ”’)’;

/* Compared to the previous FOR LOOP, isn’t executed the “insert into T_NUM_NULLS_X_COLVAL” because at least one of tables of research doesn’t have the search topic column name. */

ENDLOOP;

ENDIF;

END;

/

 

 

Visit Us On TwitterVisit Us On Google PlusVisit Us On LinkedinCheck Our Feed