DBA Tools

Get Oracle DDL Script

    A lot of DBAs are extracting DDL (table create) script with Toad and PL/SQL Developer utilities. But a lot of database are running on Linux or Unix hosts, and connections directly from desktop are not allowed, then it...

Posted on December 24, 2007 7:26 PM

Get Oracle space usage information in AnySQL

    You can get the table and it's indexes' or lob segments' size by "ORA SIZE" command easily. ASQL> ora size sh.t_lobtest OWNER SEGMENT_NAME              SEGMENT_TYPE SIZE_MB INIEXT MAXEXT ----- ------------------------- ------------ ------- ------ ------ SH    T_LOBTEST                 TABLE         0.0625  65536 SH    SYS_IL0000012006C00001$$  LOBINDEX      0.0625  65536 SH    SYS_LOB0000012006C00002$$ LOBSEGMENT    0.0625  65536...

Posted on December 24, 2007 7:07 PM

Drop 4TB table on dictionary managed tablespace

    I got a chance to drop a 4TB size table on dictionary managed tablespace (DMT), this table comes from a very old design, and the partition design is not good for data purge, and delete cannot catch up...

Posted on December 22, 2007 5:30 PM

Describe tables with indexes structure information

    When describe a talbe to get the structure information, I also hope to display the indexes information. In AnySQL, I wrote the DESCRIBE command with this feature. It really save me a lot of time to write SQLs...

Posted on December 20, 2007 10:09 PM

What's Oracle 11G's compress table support for OLTP system?

    Before Oracle 11g, the data will not be compressed by normal insert statement even if the table is created with compress option. If you want to compress a tables data, you need to move table as compressed, or...

Posted on November 16, 2007 6:43 PM

ALTER TABLE ... MODIFY DEFAULT ATTRIBUTES ...

    When doing transportable tablespace, we get the following errors when run the TTS set check procedure. SQL> EXECUTE dbms_tts.transport_set_check('USERS', TRUE, TRUE); PL/SQL procedure successfully completed. VIOLATIONS -------------------------------------------------------------------- Default Partition (Table) Tablespace USERS for ... not contained ... Default...

Posted on November 2, 2007 4:40 PM

Oracle Compress Table Block Format (3)

    The compress block format seems not so much complex as I expected. After spent few hours, I can get the same dump information from AUL as dump datafile command do. Left side comes from Oracle dump datafile command,...

Posted on November 1, 2007 3:19 PM

Oracle Compress Table Block Format (2)

    In previous test, I just create table with two columns, how about for a table with a lot of columns? The basic block format does not change, the key is how to extract the rows. I get one...

Posted on October 30, 2007 10:38 PM

Oracle Compress Table Block Format (1)

    Oracle compress table is an new feature since Oracle 9i, it could save you a lot of storage, I have seen this new feature used in data warehouse. In oracle 9i, compress table have some bugs, for example...

Posted on October 30, 2007 9:26 PM

Poor SUN CPU speed for Oracle compress table

    We are thinking to use Oracle compress table for data archiving via TTS, by creating a single table for every month, and then moving it to a cheaper storage. Since the history data will not be modified any...

Posted on October 30, 2007 11:28 AM

Compare index structure difference between tables

    I will introduce you how to compare index structure between tables with the compare any column utility (Overview / Download). I fixed the table structure difference found in previous case, and create the following indexes for demo. CREATE...

Posted on October 23, 2007 5:44 PM

Compare column for different tables on different databases.

    I will introduce you how to compare structure for different tables in different databases (ALL type) with the compare any column utility (Overview / Download). I create the following tables in three different databases for this demo. --...

Posted on October 19, 2007 8:12 PM

Compare column for the same table on different databases.

    I will introduce you how to compare structure for the same table in different database (TABLE type) with the compare any column utility (Overview / Download). I create the "SALES" table in three different databases for this demo....

Posted on October 16, 2007 8:25 PM

Compare column for different tables on the same database.

    I will introduce you how to compare structure for different tables in the same database (HOST type) with the compare any column utility (Overview / Download). I create the three tables in SH schema for this demo. create...

Posted on October 16, 2007 5:32 PM

Compare column script for tables on different databases.

    The ocidiff utility is just a peer-to-peer compare utility, it cannot get all of our problem resolved. For some big tables, for example the sales or the bill invoice tables, we may store the data in separate tables,...

Posted on October 15, 2007 8:03 PM

How my DBDiff (ocidiff) utility works for different objects?

    ocidiff utility is one of my own version of DBDiff utilities. There are several key tasks in this utility, let's see how it works?     How to get the SQLs to create the missing tables? In this...

Posted on October 15, 2007 7:29 PM

How to choose the SIZE and HASHKEYS of hash cluster table?

    Hash cluster table can get much performance improve for equal access, however proper SIZE and HASHKEYS option is required. In OLTP, we always have some tables, the query use equal condition and with unique or very effective index...

Posted on October 10, 2007 5:19 PM

The steps of Oracle table online redefination

    Online redefinition can be used to reduce the database down time in OLTP system. The first step is to check whether we can redefine it. SQL>  exec dbms_redefinition.can_redef_table( USER, 'T_DEF1') ; PL/SQL procedure successfully completed.     Then we...

Posted on October 10, 2007 3:28 PM

Got the "Table is full" error for MySQL memory engine table

    After using MySQL memory engine table as middle cache table, we got this error when application inserting the data to the table. Seems there is some limits on the table. After searching on Google, we know that we...

Posted on September 25, 2007 7:46 PM

Compare and repair table structure between two schemas

    I have been maintaining some QA environments which require all the tables' structure should be same with the core QA environments. So we are required to add column on all the environments when make any table structure changes....

Posted on September 18, 2007 6:05 PM

Data compare tool needed, compare rows between tables.

    Sometimes we need to compare rows for two tables. I have saw many times people were asking this kind of tools, I want such a tool too. I did put some time in designing such a tool, but...

Posted on September 7, 2007 11:23 PM