LOB datatypes in Oracle

LOBs support storing large, unstructured objects such as audio, video, picture, text, etc.

Before the LOB, there was LONG and LONG RAW datatypes which also were storing large objects. Oracle still supports it, but strongly recommends using LOBs instead of them. LONG and LONG raw have many restrictions and have less opportunity than LOBs. So let’s discuss their differences:

  1. Table can store multiple LOBs, while you are restricted to use only one LONG column per table.
  2. A LOB can store maximum 4GB in Oracle 8, 8TB in Oracle 9i/10g, 128TB in Oracle 11g. While LONG column can store maximum 2GB.
  3. Table containing LOBs can be partitioned, while table with LONG column cannot be partitioned.
  4. When you are using LOBs you are able to access its data randomly, while you must sequentially read LONG type data from beginning to end.
  5. LOBs can be used in user defined data types (except NCLOB), while LONG cannot be used.

LOB datatypes can be stored inline (within a table) or out-of line (within a tablespace, using a LOB locator) or as an external file (BFILE).

BLOB

BLOBs are binary objects, used to store binary, raw data. BLOBs participate into transaction and can be rolled back or committed.

CLOB

CLOBs are character LOBs. Used to store single byte character set (large texts, xml…). They also participate into transactions.

NCLOB

NCLOBs are used to store multi-byte character data, which corresponds to the national character set defined into the database. They also are participating into transactions.

Three of them are types of Internal LOBs; there also exists external LOB called BFILE. It is the single type, which is external.

BFILE

BFILE is short for Binary File, which is stored outside the database. It is stored into the Operating System as a file. It doesn’t participate into transactions. The amount of file is also limited by OS. BFILEs are read only. They only support random reads, means not sequential read. The changes to the BFILEs can be done through OS.

 

Advertisements

Moving Tables with LOB columns to a diff. tablespace

In the previous post I discussed how to move table and its indexes to a different tablespace, and I also mentioned that moving tables with LOB columns to a different tablespace is performed by the different way. So here it is how to do this.

First of all, let’s create a test table with LOB column.

CREATE TABLE TST_TABLE(
COL_ID          NUMBER CONSTRAINT TEST_PK PRIMARY KEY,
COL_CLOB   CLOB
);

–Insert one row into this table

INSERT INTO TST_TABLE
VALUES(1,’FIRST ROW’);
COMMIT;

–Query it

SELECT * FROM TST_TABLE

–Result

COL_ID   | COL_CLOB
——————————-
1              | <CLOB>

Now let’s check table’s current tablespace.

SELECT TABLE_NAME
,TABLESPACE_NAME
FROM DBA_TABLES
WHERE TABLE_NAME=’TST_TABLE’

–Result

TABLE_NAME  | TABLESPACE_NAME
—————————————————
TST_TABLE     | SYSTEM

 

Assume that we have already created a test tablespace, let’s say it is called TST_TBS.

–Move table to TST_TBS tablespace

ALTER TABLE TST_TABLE MOVE TABLESPACE TST_TBS

–Let’s check the result

SELECT TABLE_NAME
,TABLESPACE_NAME
FROM DBA_TABLES
WHERE TABLE_NAME=’TST_TABLE’

–Result

TABLE_NAME  | TABLESPACE_NAME
————————————————–
TST_TABLE     | TST_TBS

When you create a table with LOB column, oracle automatically creates LOB segment and LOB index. If you do not indicate their(LOB segment and LOB index) names and where they  should be kept, oracle names them itself and creates in the same tablespace as the table is.

By default, segment name starts with SYS_LOB… and index name starts with SYS_IL…

Let’s check what indexes are already created for our table.

SELECT INDEX_NAME                 AS INDX_NM
,INDEX_TYPE                 AS  INDX_TP
,TABLESPACE_NAME   AS TBS_NM
,STATUS
FROM DBA_INDEXES
WHERE TABLE_NAME=’TST_TABLE’

–Result

INDX_NM                                      | INDX_TP | TBS_NM | STATUS
—————————————————————————————–
SYS_IL0000055912C00002$$| LOB         | SYSTEM  | VALID
TEST_PK                                      | NORMAL | SYSTEM  | UNUSABLE

Or you can query the following to identify segment name and index name.

SELECT COLUMN_NAME           AS CLN_NM
,SEGMENT_NAME        AS SEGMENT
,TABLESPACE_NAME  AS TBS_NM
,INDEX_NAME                AS INDX_NM
FROM USER_LOBS
WHERE TABLE_NAME=’TST_TABLE’

–Result

CLN_NM     |SEGMENT                                         | TBS_NM | INDX_NM
———————————————————————————————————————-
COL_CLOB|SYS_LOB0000055912C00002$$| SYSTEM |SYS_IL0000055912C00002$$

Because of, by default LOB column is stored outside of the table it is still located in the SYSTEM tablespace(was not moved).

In order to move LOB column to a different tablespace, you should run the following command.

ALTER TABLE TST_TABLE MOVE LOB(COL_CLOB)
STORE AS (TABLESPACE TST_TBS);

–Check it

SELECT COLUMN_NAME         AS CLN_NM
,SEGMENT_NAME       AS SEGMENT
,TABLESPACE_NAME AS TBS_NM
,INDEX_NAME               AS INDX_NM
FROM USER_LOBS
WHERE TABLE_NAME=’TST_TABLE’

–Result

CLN_NM     |SEGMENT                                         |TBS_NM  | INDX_NM
———————————————————————————————————————–
COL_CLOB|SYS_LOB0000055912C00002$$|TST_TBS |SYS_IL0000055912C00002$$

Let’s query the following:

SELECT INDEX_NAME               AS INDX_NM
,INDEX_TYPE                AS INDX_TP
,TABLESPACE_NAME AS TBS_NM
,STATUS
FROM DBA_INDEXES
WHERE TABLE_NAME=’TST_TABLE’

–Result

INDX_NM                                       | INDX_TP | TBS_NM | STATUS
——————————————————————————————
SYS_IL0000055912C00002$$ | LOB         | TST_TBS |  VALID
TEST_PK                                       | NORMAL | SYSTEM  | UNUSABLE

As you can see, LOB index was automatically moved to the new tablespace, but normal index still is in the same tablespace. To move it to a different tablespace, run the following:

ALTER INDEX TEST_PK REBUILD TABLESPACE TST_TBS

–Check it

SELECT INDEX_NAME               AS INDX_NM
,INDEX_TYPE                AS INDX_TP
,TABLESPACE_NAME AS TBS_NM
,STATUS
FROM DBA_INDEXES
WHERE TABLE_NAME=’TST_TABLE’

–Result

INDX_NM                                       | INDX_TP | TBS_NM | STATUS
——————————————————————————————
SYS_IL0000055912C00002$$ | LOB         | TST_TBS |  VALID
TEST_PK                                       | NORMAL | TST_TBS |  VALID

 

After rebuilding an index, it became valid. As I also said in the previous post , if at least one index of the table is not valid DML operations will fail.

Moving tables and indexes to a different tablespace

Moving table to a different tablespace can be done by several ways:

  1. Using Export/Import.
  2. Or by the following clause:

ALTER TABLE schemaName.tableName REBUILD TABLESPACE tablespaceName;

I will discuss the second variant now.

–Let’s create a test table.

CREATE TABLE TESTTABLE(
TESTCOLUMN NUMBER CONSTRAINT TEST_PK PRIMARY KEY
);

–Insert one row into that table

INSERT INTO TESTTABLE
VALUES(1);
COMMIT;

–Let’s see rowid value

SELECT ROWID,TESTCOLUMN
FROM TESTTABLE;

–Result

ROWID                                | TESTCOLUMN
——————————————————–
AAANoRAABAAAPVSAAA  | 1

Before you move table to a different tablespace, you must have created  this tablespace before.

–Now let’s create a test tablespace.

CREATE TABLESPACE testTablespace
DATAFILE   ‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\testTablespace01.DBF’
SIZE 5242880
AUTOEXTEND ON NEXT 1310720 MAXSIZE 32767M
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO

–Let’s see table’s current tablespace

SELECT TABLE_NAME,TABLESPACE_NAME
FROM DBA_TABLES
WHERE TABLE_NAME=’TESTTABLE’;

–Result

TABLE_NAME  | TABLESPACE_NAME
—————————————————-
TESTTABLE     | SYSTEM

–Move this table to a test tablespace

ALTER TABLE TESTTABLE MOVE TABLESPACE TESTTABLESPACE;

–See again table’s current tablespace

SELECT TABLE_NAME,TABLESPACE_NAME
FROM DBA_TABLES
WHERE TABLE_NAME=’TESTTABLE’;

–Result

TABLE_NAME  | TABLESPACE_NAME
—————————————————-
TESTTABLE     | TESTTABLESPACE

–Let’s see again rowid value

SELECT ROWID,TESTCOLUMN
FROM TESTTABLE;

–Result

ROWID                                | TESTCOLUMN
——————————————————–
AAANoSAAHAAAAAMAAA | 1

As you can see rowid was changed. It makes indexes of this table unusable. To check it, run the following query.

SELECT INDEX_NAME,TABLE_NAME,STATUS,TABLESPACE_NAME
FROM DBA_INDEXES
WHERE TABLE_NAME=’TESTTABLE’;

–Result

INDEX_NAME | TABLE_NAME | STATUS       | TABLESPACE_NAME
—————————————————————————————–
TEST_PK         | TESTTABLE    | UNUSABLE | SYSTEM

Any DML operation on this table will cause an error, because an index is not valid. Let’s check it:

INSERT INTO TESTTABLE
VALUES(2);
COMMIT;

–Error

ORA-01502: index ‘SYS.TEST_PK’ or partition of such index is in unusable state

This happens because you have moved test table to another tablespace, and this operation changed rowid value. The index still refers to the old rowid value. To solve this, you must rebuild the index.

ALTER INDEX TEST_PK REBUILD TABLESPACE TESTTABLESPACE;

This command will rebuild the index and also moves it to TESTTABLESPACE tablespace. If you want to rebuild index without moving it to another tablespace. Simply, run the following command:

ALTER INDEX TEST_PK REBUILD;

–Let’s check its status

SELECT INDEX_NAME,TABLE_NAME,STATUS
FROM DBA_INDEXES
WHERE TABLE_NAME=’TESTTABLE’

–Result

INDEX_NAME | TABLE_NAME | STATUS  | TABLESPACE_NAME
—————————————————————————————
TEST_PK         | TESTTABLE    | VALID       | TESTTABLESPACE

You must do this action for each index, to make DML operation resumable on this table.

Note that, if table contains LOB columns this variant doesn’t work. Moving tables with LOB columns to another tablespace is discussed here.

How to Load JavaScript Contents Dynamically

This post is related to my previous post in terms of performance of loading page. Compressing and Combining .js files is very good thing to do. But not all .js files are essential for page functionality in the very first seconds after site is loaded. Almost in all web applications we are facing that situation. Application has many users with different privileges and roles. Not all list of menu is accessible for every user and in most cases user will not open all menus in one session. So, it has no purpose to load all .js files for the first time, because most of them will not be executed at all. Let’s make our architecture so that a .js file was loaded only after demand on function that is defined there. To explain better I’ll give very clear example. For example I have a web application with menu items (Let’s call them applications or subapplications ):

  • Menu 1
  • Menu 2
  • Menu 3

After the first load of site it is enough to load just those contents that displays my menu. And after click on Menu 1 firstly, let’s download that .js files where are defined functionality of Menu 1 and secondly, execute the functions that creates and displays the layout of Menu 1. I think I’m clear what I want to do. To approach the goal we have to create a singleton class that handles application content loading staff. All .js content will be downloaded by this class except essential files for displaying initial layout of web application. And this is the class that handlers dynamic loading:

(function(){
  var appManager, loadScript, head, App;
  appManager = {};
  appManager.cache = {}; //App instances will be cached here
  /**
  * Loads JavaScript from specified URL in DOM
  * @param {string} src
  *     URL for JavaScript file
  * @param {function} callBack
  *     function that will be called after file downloading finishes.
  *     Usually callBack function will call one of the function that
  *     is defined in newly downloaded file.
  */
  loadScript = function(src, callBack){
    var script;
    //get and cache head of document
    head && (head = document.getElementsByTagName("head")[0]);
    script = document.createElement('script'); //create script tag
    script.type = 'text/javascript';
    //listen to moment when downloading finishes (for IE)
    script.onreadystatechange= function() {
      if (this.readyState == 'complete') callBack();
    };
    //listen to moment when downloading finishes (for others than IE)
    script.onload = callBack;
    script.src = src; //assing src property to script tag
    head.appendChild(script); //append to head to begin download.
  };
  /**
  * Application class that has properties:
  *  name - name of subapplication
  *  src - URL of application's .js file
  *  callBack - function that will be executed after file loads,
  *  status - weather application file is downloaded or not.
  */
  App = function(name, callBack){
    this.name = name;
    /**
    * .js file name is considered to be
    * dirName + "_min.js" in directory
    * named dirName.
    */
    this.src = name + "/" + name + "_min.js";
    this.callBack = callBack;
    this.status = 'instantiated';
    /**
    * Cache application because not to
    * create and download more than once
    * the same application.
    */
    appManager.cache[name] = this;
  };
  /**
  * Method of App class that downloads its
  * .js file and executes callback function
  */
  App.prototype.load = function(){
    var that;
    this.status = 'loading';
    that = this;
    loadFinished = function(){
      that.callBack();
      that.status = 'loaded';
    };
    loadScript(that.src, loadFinished);
  };
  /**
  * By this function will be loaded applications
  * from global scope.
  * @param {string} appName
  *       Name of application, typically name of application
  *       and the name of directory under which application
  *       .js files are.
  * @param {function} callBack
  *        Function that will be executed after application
  *        loads.
  */
  appManager.load = function(appName, callBack){
    var app;
    /**
    * Create an application or get it from cache if it was created
    * before.
    */
    app = this.cache[appName] || new App(appName, callBack);
    if(app.isLoaded()){
      /**
      * If application is taken from cache and its file is already
      * downloaded then directly execute callBack.
      */
      callBack();
    }else if(app.isLoading()){
      /**
      * If application is taken from cache and the process of
      * downloading is in progress do nothing.
      */
      return;
    }else{
      /**
      *  Application is created for the first time and load it.
      */
      app.load();
    }
  };
  //make appManager global.
  window.appManager = appManager;
})();

This code is useful for understanding an idea and to run demos. But in real application, when there are many changes at runtime, this class also needs some additional functionalities such as: dependences on applications, some special files to be downloaded except core file and so on…

Now we have got dynamically loaded JavaScript content architecture.