When export oracle data to different database platform, text file is always a good choice and may be the only choice, if they cannot connect directly. And some time I was required to provide some rows in excel...
Migrate data may be a regular job for a DBA, sometime from this Oracle server to another Oracle server, and sometime migrate from Oracle to MySQL or other database, or other database to Oracle. With good utility it...
Performance is critical for OLTP system, asyncdata should have good performance for the data replication, else it may not be able to catch up with the source table. Following is the key points to get the best performance....
Oracle materialized view log is good for capture the DML change, however it's not flexible, for example, the table must have a physical primary key defined for asyncdata, and you have no wait to skip the change capture...
Of cause, MySQL does not have materialized view log now as Oracle has. But we can implement the same feature with a log table and a trigger, since MySQL version 5 does support the trigger. In MySQL we...
ociuldr (Source) is a free utility used to unload Oracle rows to text file, which can be used to prepare data for data warehouse, or do the data migration from Oracle to other databases, or moving data between...
Oracle's SQL Loader (sqlldr) can be used to load rows in text file into database tables. But sqlldr need a control file to specify how to interpret the text file format, including the column (field) separator, the record...
Oracle CLOB and BLOB data types can be used to store long text or large images, provide better performance compare to the LONG and LONG RAW data types for row accessing. However it's not easy to manipulate them...
After several times under production environments (with thousands of concurrent connections), I did hit some critical bugs for this script. For example, lot's of unique constraint errors, it's hard to explain the reason here. I just want to...
About half years ago, someone told me to add this, because he want to get more control of ociuldr utility in his shell script. I was afraid of change any code at that time, so just left him...
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....
I made few changes to this free utility, a new option "mode=" is introduced to change the load mode, there are four different modes in sqlldr. INSERT: Load data into empty table, this is the default mode. APPEND:...
I spent some time in researching the Oracle log file format. Previous research was done about two years ago for Oracle 9i. Fortunately I did get something for these days research. And wrote a tool called "MyLOG" (8i/9i,10g/11g)...
I got some requests of data recovery by drop operation, including drop tables and drop tablespaces and drop schema. For this kind of recovery, the most complex step is to map the table with the data object id...
Now you can use ociuldr to move data with LONG RAW type. Just the same as LONG type, the "long=" option define the maximum length ociuldr will read. You may need to set the proper value of this...
How to get the first SCN, next SCN, first time, next time of archived log? the first way should be check rows from V$LOG_HISTORY or V$ARCHIVED_LOG, but these view just keep information of few latest archived logs, it...
I saw somebody were asking how to change the character set id in the Oracle export file. I did once in 1998, we had a export file from a US7ASCII database, and wanted to import it into a...
When tuning the performance of Oracle SQL loader (sqlldr), the read size and bind size option are very critical. When ociuldr utility generate the SQL loader control file for you, the default read size and bind size are...
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...
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,...
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...
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....
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. --...
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...
Few days ago, somebody (they wrote a MYjDUL utility) post a message on a public forum said that AUL/MyDUL is copied from jDUL open source project. This is not true, when I started to write AUL/MyDUL utility in...
Oracle's system level statistics is very useful for performance track. Oracle keep on add new statistics from Oracle 8, 8i, 9i, 10g to 11g. The key is how to display the statistics value. SQL> SELECT name, value FROM...
Free Oracle text unload utility (ociuldr) was used to extract Oracle data from OLTP system and then load them into their OLAP system by alibaba company (Taobao). They get a really fast unload speed these days. 0 rows...
When hit database load problems, I always want to know what the active sessions are doing and waiting. On 10g we can get these information from V$SESSION view, on 8i and 9i, we can join V$SESSION and V$SESSION_WAIT....
AnySQL is a command line database interface tools wrote in Java, just like Oracle's SQL*Plus, but connect to the database via JDBC. There are 3 connect methods available. The default method is Oracle JDBC Thin driver,...
When adding column in out system, I always get resource busy (ORA-00054) errors. If I cannot add it after a lot of retries (we have script to do this). I will try to find out the lock owner,...
Just found a file sharing service named file.io, and then decide to upload some of my tools to anysql folder for sharing. You can download AnySQL utility (Search ...) here. anysql-jre.zip, AnySQL with JRE for Windows,...
When I start to learn MySQL database, I feel Oracle SQL*Plus should learn from MySQL to display the result as form style. It's not implemented in Oracle 11g's SQL*Plus, but you are still lucky because I implement it...
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...
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...
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...
As an Oracle DBA, I feel no good command line tools when facing SQL Server or Sybase database, not comfirtable with osql or sqlcmd because too much differences with SQL*Plus. AnySQL is Java & JDBC based, it can...
I took few hours to make some changes to rsync's source code, to fit for copy contents in raw devices to remote host's raw devices. I am an Oracle DBA, and some of our databases are using raw...
WebChart is a Java servlet based platform for simple business data display in tabular or chart mode, it was initially wroted in 2003, but I improved it these days. And I created database performance management pages on it...
I will show you how WebChart works, take a look at the following picture furst, it's WebChart's techniqual framework. WebChart is a three-tier applications, you can view data in HTML or XML or PDF format with...
After introduce of the WebChart features and the the WebChart framwork, somebody told me that they need to test WebChart, but don't know how to install it after download. After reading this article, you should know how to...
WebChart just have limited chart features compared to Microsoft Excel, But we can export data to an Excel file and do more analyze. It require you download Apache POI packages and put it to the tomcat common library...
WebChart has it's own connection pool manager. There were several Java Servlet container such as Tomcat and Resin, both of them were very popular, but they have differnet database connection pool implementaion. In order to tranparently running on...
In the WebChart features overview page, I show you seven demo, the first three are telling you how to display data in table format pages, then next four pages are telling you how to display data in different...
In the WebChart features overview page, I show you seven demo, the first three pages are telling you how to display data in table format pages, then next four pages are telling you how to display data in...
WebChart read page defination from WCML files, short term for WebChart Marker Lanuage. No secret for WCML files, it's just a text file, and can be edited by any text editor, with a lot of properties defination in...
The WebChart Framework tells you what techniques it used, if you want to get well known of WebChart, you need to know the WebChart input and output map. WebChart can display data in four formats for...
Sqlldr is much faster for fixed legnth text file, so someone suggest me to add this feature in ociuldr utiltiy, now it supported. When you use a space char as the field separator, it will generate fixed-length text...
Usually the data displayed in WebChart is very confidential, only few people can access and view it. So WebChart must has some security settings to control the access of the WCML pages. You can use four properties to...
WebChart is running as an assistant of performance monitor, business monitor in my working company, it plays a very important role now, as may people are watching the WebChart pages. I get the database server's one...
User Defined Parameters. Parameter=Value ...... Security related properties. WEBCHART.SECURITY={TRUE|FALSE} WEBCHART.DEFAULTACCESS={ALLOW|DENY} WEBCHART.ALLOW=User or role list, split by vertical line(|) WEBCHART.DENY=User or role list, split by vertical line(|) Global settings. WEBCHART.DB_CHARSET=character set of database WEBCHART.PARAMETER_CHARSET=web parameter...
The format we dispaly the data is always different from the format we store the data. For example, we store the monthly trade summary in row based format, one row for each month, but when we display it,...
I added a simple online editor into WebChart program, to provide better online support or perform online development, else you must login to the WebChart application server and replace the updated WCML files, it's not so flexable to...
Data security is very important for every company or people, we need try our best to improve the software security. In previous WebChart release, the password is not encrypted in both database connection configuration and the user table,...
I compared the unload speed of ociuldr and exp utilities, it seemd that ociuldr just has 50% speed compare to exp conventional mehtod, and had no idea of tuning it. After I introduced a buffer layer into it,...
With Java JDBC technology, WebChart can connect to many different types of database system, such as MySQL. Now I will show you some examples pages on MySQL. First, create the demo table with the following script in MySQL....
Since ociuldr get 25% speed improve, I want to know the performance difference with oracle export utility. I tested them on my notebook, oracle server and client in the same host, 8 columns (including VARCHAR, NUMBER and DATE...
Ociuldr is a good utility for text unloading, and many people are running it, to avoid compatibilities. I named the new version of text unloading utility as SQL*UnLoader (sqluldr), new version is 25% faster than the old version....
There are lots of command line options for squldr utility, it's not convenient for us to specify lots options value in command line, so I intorduced a new command line option "parfile" to save options value in a...
One of my friends need to unload CLOB values to flat file, he tried OCI 7 based ociuldr and sqluldr, both failed. For LOB columns, OCI 7 does have some prolems, it reports "ORA-32255" errors when retrieving NULL...
You can customize almost all the colors of the chart generated by WebChart platform now. In previous release, it's not possible to define the line color or bar color, it can only use the pre-defined colors. In latest...
SQLULDR2 can run as fast as Oracle exp utility with default command line options. If you increase the buffer to 1 megabyte for exp, it's still 15% faster than SQLULDR2. And may be much faster in direct mode...
What's STDOUT, it's the standard output device for a computer, usually it meas the screen. Somebody suggested me to add this feature to ociuldr few months ago, but I did not understand the requirements at that time. Now...
SQLULDR2 is not full free for extrading rows to text file from Oracle database. The ociuldr is still free, but SQLULDR2 is stronger than ociuldr, both in performance and features. If you want to extract huge table (tens...
Some DBAs set some alert value for Oracle performance tracking, but that's not enough. For database (not only Oracle) performance, we need gather the performance data, display and analyze the data. DBAs are the critical resource for database...
As a DBA, sometime we will get high load alert, usually a sudden load spike. After you take several minutes to login to the system, you will see that the load is quiet, when you want to find...
oramon contains just one executable binary, no installation is required, just download and extract it. Usually we run this utility on database server side, so it can get the database server's OS load information, which can help you...
The "ANYSQL_OMON_RAW.log" of oramon generated files recorded some performance data for chart type display, but we need to load them into database first. You should create a table with the following structure. CREATE TABLE DATABASE_PERF_STATISTICS ( SID ...
Data compress can save a lot of storage for data archiving, GZIP is a good choise between the compress ratio and compress speed, many utilities support GZIP feature, such as rsync with "-z" option. I spent some days...
WebChart plays more and more important role in my team, now all of the production DBAs are able to create pages on it. And they gave some good suggestions to make it better. It's easier to specify multiple...
Lot's of DBAs may know the ociuldr utility, to extract rows from Oracle to text file. Now I rewrite ociuldr with OCI 8, and rename it to SQLULDR2 with better performance (almost double) and some good new features,...
HTML links are very useful for navigation, when we display some summary data in WebChart, we also want to display a href link to display the detail data of relative quater. QUATER COUNT 2008Q1 7363835 2008Q2 9146220 2008Q3...
Somebody need to change the date type format (default, "yyyy-mm-dd hh24:mi:ss"), and change the numeric characters. For Oracle, you can run some "alter session set ..." commands to make the changes, so I add a new command line...
It's possible to get ORA-24345 error when you unload data with SQLULDR2, this error is due to no enough memory buffer allocated for column binding, the column data returned exceed the maximum length declared. After a full code...
After define HTML links for each columns, we need to change the old XSL template to show links in the pages. We can use "href" HTML tag to show it, change the XSL template file as following. <xsl:choose>...
We start to introduce MySQL and PostgreSQL into our business system, as a replacement of Oracle, to save the license cost. First we need to move some data from Oracle to MySQL or PostgreSQL for performance testing or...
Some departments need to extract rows from the production daily for busines analyze, so we decide to open the standby in read only mode for data extracting with database link. The database link can protect the database user...
More and more people are moving non-important data out of Oracle to MySQL for lower Oracle license fee. For DBAs, we need to know how to migrate data from Oracle to MySQL quickly. I will introduce you how...
Some people like to run WebChart demo application on Oracle database, while some other people may like to run WebChart demo application on MySQL database. Seems I have to write two WebChart demo applications, one for Oracle, another...
Supposed that we are creating a report of the top 3 highest salary employees for every department (check the Oracle demo table : SCOTT.EMP). If the table is stored in Oracle database, we could generate the report with...
We always use Oracle exp and imp utilities to move data from one oracle database to another, but the Oracle imp utility is really slow, because it does not support direct path load, and we must prepare lot's...
I want to introduce a new utility for data replication solution based on materialized view log or user defined triggers, I named it as DataSync, suppose that we want to replicate table (SCOTT.EMP)'s data from DB1 to DB2...
Supposed that we have few handerds of data stored in US7ASCII database, and we need to switch the system to an UTF8 database, how could we get the system switched in just few minutes without long period outage...
If we want to process huge tables (few hunderds of gigabytes), it will take long time for a single SQLULDR2 or DataCopy process, single process can makde single CPU exhausted. To speedup the process, we need maually partitioned...
We can write a Perl or shell script to start multiple datacopy process with TabSplit utility, now you don't need to write such a Perl or shell script, I have combined the code of TabSplit and DataCopy, we...
SQLULDR2 is a fast and flexible Oracle text unload utility, but it did not have a good GUI interface, that's because I am not good at writing Microsoft MFC program to create GUI interface. But many and many...
Supposed that we want to unload a huge table (100gb+) to text file, how to do it quickly? The answer is parallel unloading. I will suggest you perform a manual parallel by split the big table into multiple...
SQLULDR2 is a DBA utility to extract Oracle table's data to flat text file, it's based on OCI with excellent performance. Since most of uses like GUI interface, so I released a GUI version of SQLULDR2, let's call...
