Oracle developer

Searching a text in long datatype column

noreply@blogger.com (Unknown) — Thu, 05 Nov 2015 14:56:00 +0000

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?

Rebuild unusable Index

noreply@blogger.com (Unknown) — Thu, 05 Nov 2015 14:12:00 +0000

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,

index_name,

'ALTER index ' || owner || '.' || index_name || ' REBUILD;' stmt

FROM (SELECT owner,

table_name,

index_name,

NULL partition_name,

index_type,

blevel,

leaf_blocks,

distinct_keys,

num_rows

FROM all_indexes --Non Partitioned tables

WHERE status NOT IN ('VALID', 'N/A')

UNION

SELECT owner,

table_name,

i.index_name,

partition_name,

index_type,

ip.blevel,

ip.leaf_blocks,

ip.distinct_keys,

ip.num_rows

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.

Treesize Professional- Software review

noreply@blogger.com (Unknown) — Fri, 23 Oct 2015 14:04:00 +0000

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.

Pros:

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.

Cons:

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

Download link: http://download.cnet.com/TreeSize-Professional/3000-2248_4-10008952.html

Have you used Treesize Professional before?

Oracle Partitions

noreply@blogger.com (Unknown) — Tue, 20 Oct 2015 15:55:00 +0000

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:

CREATE TABLE TBL_PERSONALINFORMATION

(

  PERSONID    VARCHAR2(20 CHAR)                 NOT NULL,

  FORENAME    VARCHAR2(1000 CHAR),

  LASTNAME    VARCHAR2(1000 CHAR)               NOT NULL,

  AGE         NUMBER,

  SEX         VARCHAR2(1 BYTE),

  ADDRESS     VARCHAR2(2000 CHAR),

  DATE_ADDED  DATE

)

PARTITION BY RANGE (DATE_ADDED)

(

  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')) ,  

  PARTITION
TBL_PI_201545_20151024 VALUES LESS THAN (TO_DATE(' 2015-11-07 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?

Oracle Tablespaces

noreply@blogger.com (Unknown) — Thu, 15 Oct 2015 14:33:00 +0000

About

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.

CREATE TABLESPACE

Tablespaces are created with the syntax 'CREATE TABLESPACE'.

Project Tablespace:

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

<PROJECT>_BIG_IDX:

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

<PROJECT>_SMALL_IDX:

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

<PROJECT>_BIG_TAB:

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

<PROJECT>_SMALL_TAB:

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:

Table:

ALTER TABLE <TABLE NAME to be moved> MOVE TABLESPACE
<destination TABLESPACE NAME>;

Index:

ALTER INDEX <INDEX_NAME> REBUILD TABLESPACE
<TABLESPACE_NAME>;

LOBSEGMENT:

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>);

LOBINDEX:

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

TABLE PARTITION:

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

INDEX PARTITION:

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

Reference: http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_7003.htm and my own project experience.

Oracle Locks and killing the blocking sessions

noreply@blogger.com (Unknown) — Wed, 14 Oct 2015 15:21:00 +0000

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
select
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
where
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,
DECODE(l.block,
0, 'Not Blocking',
1, 'Blocking',
2, 'Global') STATUS,
DECODE(v.locked_mode,
0, 'None',
1, 'Null',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share',
5, 'S/Row-X (SSX)',
6, 'Exclusive', TO_CHAR(lmode)
) MODE_HELD
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

SELECT *
FROM DBA_DML_LOCKS where owner=<owner_name>;

--Use this session id to find SERIAL# by using following SELECT statement
SELECT SID,SERIAL#
FROM V$SESSION
WHERE SID IN (SELECT SESSION_ID
FROM DBA_DML_LOCKS
where owner=<owner_name>);

--Use ALTER SYSTEM command to KILL SESSION and this will release the lock:
ALTER SYSTEM KILL SESSION '152,361';

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

noreply@blogger.com (Unknown) — Wed, 14 Oct 2015 14:47:00 +0000

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:

ISO-8859-1

windows-1252

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

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

SELECT XMLSerialize(DOCUMENT
XMLType('<BODY>äÄßÜüÜberwachung</BODY>') as BLOB ENCODING 'UTF-8'
VERSION '1.0' INDENT SIZE = 2)

  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:

SELECT REPLACE (

          REPLACE (

             REPLACE (

                REPLACE (

                   REPLACE (

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

                              
'Ü',

                              
'Ue'),

                      'Ö',

                      'Oe'),

                   'Ä',

                   'Ae'),

                'ä',

                'ae'),

             'ü',

             'ue'),

          'ö',

          'oe')

  FROM DUAL;

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:

ä -> ä
Ä -> Ä
ö -> ö
Ö -> Ö
ü -> ü
Ü -> Ü
ß -> ß
€ -> €
& -> &
< -> <
> -> >
„ -> "
© -> ©
• -> •
™ -> ™
® -> ®
§ -> §
| -> |

Function for converting BLOB to CLOB

noreply@blogger.com (Unknown) — Wed, 14 Oct 2015 14:21:00 +0000

The below is the function to convert BLOB to CLOB datatypes.

Before going to the function, lets understand what are those datatypes about:

BLOB data type

A BLOB (binary large object) is a varying-length binary string that can be up to 2,147,483,647 characters long. Like other binary types, BLOB strings are not associated with a code page. In addition, BLOB strings do not hold character data.
The length is given in bytes for BLOB unless one of the suffixes K, M, or G is given, relating to the multiples of 1024, 1024*1024, 1024*1024*1024 respectively.

Note: Length is specified in bytes for BLOB.

Syntax


{ BLOB | BINARY LARGE OBJECT } [ ( length [{K |M |G }] ) ]


CLOB data type


A CLOB (character large object) value can be up to 2,147,483,647 characters long. A CLOB is used to store unicode character-based data, such as large documents in any character set.

The length is given in number characters for both CLOB, unless one of the suffixes K, M, or G is given, relating to the multiples of 1024, 1024*1024, 1024*1024*1024 respectively.

Length is specified in characters (unicode) for CLOB.


Syntax
{CLOB |CHARACTER LARGE OBJECT} [ ( length [{K |M |G}] ) ]



FUNCTION:




CREATE OR REPLACE FUNCTION blob_to_clob (blob_in IN BLOB)

RETURN CLOB

AS

     v_clob    CLOB;

     v_varchar VARCHAR2(32767);

     v_start      PLS_INTEGER := 1;

     v_buffer  PLS_INTEGER := 32767;

BEGIN

    
DBMS_LOB.CREATETEMPORARY(v_clob, TRUE);

     

     FOR i IN
1..CEIL(DBMS_LOB.GETLENGTH(blob_in) / v_buffer)

     LOOP

          

        v_varchar :=
UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(blob_in, v_buffer, v_start));



          
DBMS_LOB.WRITEAPPEND(v_clob, LENGTH(v_varchar), v_varchar);



          v_start := v_start +
v_buffer;

     END LOOP;

     

   RETURN v_clob;

  

END blob_to_clob;

/

Toad Version Problem

noreply@blogger.com (Unknown) — Wed, 14 Oct 2015 14:11:00 +0000

I use Oracle 11.2. version and have been using Toad 12.6.0.53 version.

Oracle Version:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit
Production

PL/SQL Release 11.2.0.3.0 - Production

CORE        11.2.0.3.0        Production

TNS for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production

NLSRTL Version 11.2.0.3.0 - Production

Recently when I tried to export the DDL of a particular table from this menu:

Toad => Database => Export => Generate Schema script:

I got this error:

I tried to see how this error occurs: so I spooled sql into the screen and got this output:

Select table_name, column_name, data_type, data_type_mod,
data_type_owner,

       decode(data_type, 'CHAR',
char_length,

                        
'VARCHAR', char_length,

                        
'VARCHAR2', char_length,

                         'NCHAR',
char_length,

                        
'NVARCHAR', char_length,

                        
'NVARCHAR2', char_length,

                        
data_length) data_length,

       data_precision,
data_scale, nullable, char_used

       , virtual_column

       , identity_column,
column_id, hidden_column, default_on_null

FROM SYS.DBA_TAB_COLS c

WHERE OWNER = :own

AND   USER_GENERATED = 'YES'

and exists (select 'x'

            from  sys.DBA_ALL_TABLES t

            where t.table_name =
c.table_name

            and   t.owner = c.owner)

order by table_name, internal_column_id

I tried looking information about the column 'USER_GENERATED' in the table DBA_TAB_COLS in the Oracle Documentation and got the below information:

This column is a Oracle 12c column and my Toad tried to look for this column as I use the wrong version of Toad.

Resolution: Toad and Oracle versions are incompatible. So Installed Toad 11.6 version. This will resolve the version discrepancy issue.