Thursday, November 5, 2015

Searching a text in long datatype column

There is a situation that we have created a table with LONG datatype column and we wanted to search a string in that text.

We use LONG datatype columns to store characters of length upto 2 GB. This is also called big version of VARCHAR2.

The long datatype is deprecated in the  newer versions of the Oracle release. But they are still maintained in the old legacy systems. As per Oracle, the use of LONG datatype must be restricted to the below conditions:

The use of LONG values is subject to these restrictions:
  • A table can contain only one LONG column.
  • You cannot create an object type with a LONG attribute.
  • LONG columns cannot appear in WHERE clauses or in integrity constraints (except that they can appear in NULL and NOT NULL constraints).
  • LONG columns cannot be indexed.
  • LONG data cannot be specified in regular expressions.
  • A stored function cannot return a LONG value.
  • You can declare a variable or argument of a PL/SQL program unit using the LONG data type. However, you cannot then call the program unit from SQL.
  • Within a single SQL statement, all LONG columns, updated tables, and locked tables must be located on the same database.
  • LONG and LONG RAW columns cannot be used in distributed SQL statements and cannot be replicated.
  • If a table has both LONG and LOB columns, then you cannot bind more than 4000 bytes of data to both the LONG and LOB columns in the same SQL statement. However, you can bind more than 4000 bytes of data to either the LONG or the LOB column.
In addition, LONG columns cannot appear in these parts of SQL statements:
  • GROUP BY clauses, ORDER BY clauses, or CONNECT BY clauses or with the DISTINCT operator in SELECT statements
  • The UNIQUE operator of a SELECT statement
  • The column list of a CREATE CLUSTER statement
  • The CLUSTER clause of a CREATE MATERIALIZED VIEW statement
  • SQL built-in functions, expressions, or conditions
  • SELECT lists of queries containing GROUP BY clauses
  • SELECT lists of subqueries or queries combined by the UNION, INTERSECT, or MINUS set operators
  • SELECT lists of CREATE TABLE ... AS SELECT statements
  • ALTER TABLE ... MOVE statements
  • SELECT lists in subqueries in INSERT statements

So, here comes the big question, how to search a string in LONG datatype columns. So here, I have created sample scripts to understand this scenario better:

create table temp (x long, id number);

desc temp;

insert into temp values ('This is log text1',1);

insert into temp values ('This is log text2',2);

insert into temp values ('This is log text3',3);

insert into temp values ('This is text4',4);

select * from temp;

select * from temp where x like '%log%';

So this is the problem we are talking about.

How to resolve this? Convert the LONG column to CLOB and try your search operation there.

Still confused? Check the below code part as well:

create global temporary table temp_global (x clob, id number);

insert into temp_global select to_lob(x),id from temp;

select * from temp_global;

select * from temp_global where x like '%log%'; --Returns 3 rows

select * from temp_global where x not like '%log%'; --Returns 1 row. 

(check the insert script highlighted with green)

So this resolves all the problem with the LONG column. 

Conclusion: Try not creating LONG columns unless they are really needed. Use CLOB datatype instead.

Have you worked on LONG datatype?

Poll Maker

Rebuild unusable Index

The below is the query to rebuild unusable Partitioned and unpartitioned Indexes.

Run the 'Alter index' script when there are unusable indexes.

--Query to rebuild unusable Partitioned and Non Partitioned Indexes
SELECT owner,
       'ALTER index ' || owner || '.' || index_name || ' REBUILD;' stmt
  FROM (SELECT owner,
               NULL partition_name,
          FROM all_indexes --Non Partitioned tables
         WHERE status NOT IN ('VALID', 'N/A')
        SELECT owner,
          FROM all_indexes i, all_ind_partitions ip --Partitioned Tables
         WHERE     i.owner = ip.index_owner
               AND i.index_name = ip.index_name
               AND ip.status != 'USABLE');

If there are many database actions involved everyday, then we can think of creating a database job for this and running this every day after business hours.

Friday, October 23, 2015

Treesize Professional- Software review

In this topic , I am going to share the reviews of the software "Treesize Professional".

This tool is a powerful and flexible hard disk space manager for Windows 8/7/Vista/XP or Windows Server 2012/2008/2003 (32 or 64 Bit).

This software lists the size of all the files within the folder specified. This software displays the result in Gantt Chart as a easier visual representation. This folder also does recursive search to scan the folders inside the specified folder.

A variety of graphic reports allow you to get large amounts of info about your disk space at a glance.

The Chart tab, gives the pictorial representation of the sizes.

The 'Details' tab lists all the details of the folder within the search directory including the % of Parent(allocated)

The 'extension' tab lists the size of the files grouped by extensions. This will be helpful during analysis.

'Age of Files' tab has the details about the files grouped by the creation date.

'Top 100 files' tab lists the top 100 files grouped by size.


  • Easier to understand the tool with the pictorial representation
  • Professional Interface
  • Graphical display
  • Can be integrated with other apps like Scheduler to gather the stats every week.
  • Does faster scan.
  • Require no big Technical knowledge to use this tool.
  • Support most of the 32/64 bit Windows Server.
  • Helpful for weekly Server maintenance.


  • Overpriced
  • Crowded Interface
  • Seems to freeze up with large volumes of data. (1 terabyte).

Have you used Treesize Professional before?

Quiz Maker

Tuesday, October 20, 2015

Oracle Partitions

Need for partition:

Oracle tables which are very huge say(> 2GB) , which inserts data everyday and that have historical and important details for reference, requires Partitions.

Partitions are nothing but smaller segments within the tables for easier maintenance of the tables.

When the content of a table need to be distributed across different types of storage devices, then we need to create partitions.

And finally for performance issue, we need partition. We prefer losing a weekly data instead of the whole table contents :)

Partitioned Index:

In addition to partitioning not only the tables, the Primary keys of the big tables are partitioned as well:

The reasons for partitioning the index are:
  • Avoid rebuilding the entire index when data is removed.
  • Perform maintenance on parts of the data without invalidating the entire index.
  • Reduce the impact of index skew caused by an index on a column with a monotonically increasing value.

Partitions can be created weekly or monthly basis depending of the data inflow:

We can create a dbms_job for creating the weekly(or monthly) partitions. The sample statement for creating partition name is :

We create the below table to show the demonstration about partition:

This sql will create the name of the partition in the format "201545_20151107". This means, that the partition has been created for the week no 45 of the year 2015 and the week ending 07.11.2015. This way you can create a job to create 3 partition names every week. This job can be scheduled to run every Sunday.

Once the names of the partition are created,then we need to create partition in the big tables with the created names.

For example if the table name is TBL_PERSONINFORMATION then partition names can be created as "TBL_PI_201545_20151107". 

Table creation script: 

  PERSONID    VARCHAR2(20 CHAR)                 NOT NULL,
  LASTNAME    VARCHAR2(1000 CHAR)               NOT NULL,
  AGE         NUMBER,
  SEX         VARCHAR2(1 BYTE),
  PARTITION TBL_PI_201543_20151024 VALUES LESS THAN (TO_DATE(' 2015-10-25 00:00:00', 'YYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) , 
  PARTITION TBL_PI_201544_20151024 VALUES LESS THAN (TO_DATE(' 2015-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) , 

Inserting data into the table:

insert into TBL_PERSONALINFORMATION partition (TBL_PI_201543_20151024) values ('10','AA','BB',19,'F','AAA BBB St.',sysdate);

After inserting into the table at the appropriate partition, the table looks like below:

Each partition has sufficient number of rows for our demo.

Before deleting Partition:

Few important points to consider before deleting the partitions:

  • All the FKs must be disabled before deleting
  • Delete the partitions using 'ALTER TABLE.. DROP PARTITION' syntax
  • Enable the FKs back
  • Rebuild the indexes of the partitioned table
  • Delete the data from the referential tables to maintain consistency.

Deleting the Partition:

This way, I have deleted the partition "TBL_PI_201543_20151024".

Quiz Time

Do you know why we disable Foreign Keys before deleting Partitions?

To maintain referential integrity with other tables
As per the syntax
To avoid deleting referential data
online polls

Thursday, October 15, 2015

Oracle Tablespaces


Tablespace is created in oracle for allocating space for holding database objects.

There are different types of tablespaces:
  •  PERMANENT TABLESPACE: This tablespace contains persistent schema objects.Objects in permanent tablespaces are stored in datafiles. 
  • An UNDO tablespace is a type of permanent tablespace used by Oracle Database to manage undo data if you are running your database in automatic undo management mode. Oracle strongly recommends that you use automatic undo management mode rather than using rollback segments for undo.
  • A TEMPORARY TABLESPACE contains schema objects only for the duration of a session. Objects in temporary tablespaces are stored in tempfiles.


Tablespaces are created with the syntax 'CREATE TABLESPACE'.

Project Tablespace:

In any oracle related project, tablespaces can be created as below:

All the small Indexes, Index Partitions of size greater than 4 MB are grouped here. INDEXPARTITITON comes under this.

All the small Indexes, Index Partitions of size lesser than 5 MB are grouped here. INDEXPARTITITON comes under this.

All the database objects like LOBINDEX, LOBSEGMENT, TABLE, TABLEPARTITON etc. of size greater than 5 MB are grouped here.

All the database objects like LOBINDEX, LOBSEGMENT, TABLE, TABLEPARTITON etc. of size lesser than 5 MB are grouped here.

Re-allocating the database objects in proper tablespaces:

There are cases where we need to reallocate database objects like Tables, Index in the proper Tablespaces on weekly or on monthly basis.

These reallocation will be done based on num_rows, avg_row_len and initial_extent of the Table objects. 

Manual Tablespace reallocation scripts:



In Order to move CLOB column to different tablespace, use the below command. This will move LOGSEGMENT and LOBINDEX to the new tablespace.

ALTER TABLE <Table_Name> MOVE LOB(<CLOB_Column>) STORE AS (TABLESPACE <Tablespace_name>);

You cannot specify a tablespace for the LOBINDEX -- it is automatically created and moved with the LOBSEGMENT.


alter table <table_name> move partition <Table_partition_name> tablespace <tablespace_name>;


ALTER INDEX <Index_name> REBUILD PARITION <Partition_Name> TABLESPACE  <Tablespace_name>;

Reference: and my own project experience.

Wednesday, October 14, 2015

Oracle Locks and killing the blocking sessions

The below queries will identify the locked sessions, the owner of the locked objects.

This also explains how to kill the blocking locked sessions.

--Active sessions
       substr(a.spid,1,9) pid,
       substr(b.sid,1,5) sid,
       substr(b.serial#,1,5) ser#,
       substr(b.machine,1,6) box,
       substr(b.username,1,10) username,
--     b.server,
       substr(b.osuser,1,8) os_user,
       substr(b.program,1,30) program
from v$session b, v$process a
b.paddr = a.addr
and type='USER'
order by os_user,username;

-- Query to identify the locks
SELECT username U_NAME, owner OBJ_OWNER,
object_name, object_type, s.osuser,
  0, 'Not Blocking',
  1, 'Blocking',
  2, 'Global') STATUS,
    0, 'None',
    1, 'Null',
    2, 'Row-S (SS)',
    3, 'Row-X (SX)',
    4, 'Share',
    5, 'S/Row-X (SSX)',
    6, 'Exclusive', TO_CHAR(lmode)
FROM gv$locked_object v, dba_objects d,
gv$lock l, gv$session s
WHERE v.object_id = d.object_id
AND (v.object_id = l.id1)
AND v.session_id = s.sid
ORDER BY username, session_id;

--Identify the locks of the owner
FROM DBA_DML_LOCKS where owner=<owner_name>;

--Use this session id to find SERIAL# by using following SELECT statement
 where owner=<owner_name>);

--Use ALTER SYSTEM command to KILL SESSION and this will release the lock:

In order to execute this, you may need Oracle DBA previlieges. Check with your DBA to execute this step.

Dealing with German Characters in XML/Oracle

There are couple of ways to handle German characters like Ü,Ä,Ö,ä,ö,ü,ß in XML and Oracle queries.

In case of XML, we use the XMLserialize function with encoding options.

The most common encoding options for printing German characters are:

The commonly used encoding option UTF-8  does not support German characters.

The below are the programming way of explaining these encoding options:


  AS xmlserialize_doc FROM DUAL 
As you see the UTF-8 encoding option does not support German characters.

Lets see the other options.

SELECT XMLSerialize(DOCUMENT XMLType('<BODY>äÄßÜüÜberwachung</BODY>') as BLOB ENCODING 'ISO-8859-1' VERSION '1.0' INDENT SIZE = 2)
  AS xmlserialize_doc FROM DUAL;

This encoding is working :)

  SELECT XMLSerialize(DOCUMENT XMLType('<BODY>äÄßÜüÜberwachung</BODY>') as BLOB ENCODING 'windows-1252' VERSION '1.0' INDENT SIZE = 2)
  AS xmlserialize_doc FROM DUAL;

This option is working as well.

The only problem with this approach is that output will be in BLOB format and we need a function to convert that into CLOB to use it.

We can use the below approach as well to use it in the oracle objects like package or function:

          REPLACE (
             REPLACE (
                REPLACE (
                   REPLACE (
                      REPLACE (REPLACE ('ÜäÖößü', 'ß', 'ss'),

This will replace the German characters into the English equivalent.

This approach will be useful if the encoding option is not supported by systems like Interfaces.

The below are the HTML notation for the German characters:

  • ä -> &auml;
  • Ä -> &Auml;
  • ö -> &ouml;
  • Ö -> &Ouml;
  • ü -> &uuml;
  • Ü -> &Uuml;
  • ß -> &szlig;
  • € -> &euro;
  • & -> &amp;
  • < -> &lt;
  • > -> &gt;
  • „ -> &quot;
  • © -> &copy;
  • • -> &bull;
  • ™ -> &trade;
  • ® -> &reg;
  • § -> &sect;
  • | -> |