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.

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?

Yes

No

Quiz Maker

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: 

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?

To maintain referential integrity with other tables

As per the syntax

To avoid deleting referential data

online polls

Oracle Tablespaces

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

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

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:

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

Function for converting BLOB to CLOB

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

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.