For good DBAs, everything are same except the database name, while for bad DBAs, everything are different except the database name. Of cause we have good DBAs, but we still hit some problems. There are a lot of...
When posting scripts or query result to blog (Many DBAs have personal blogs), I always use an UltraEdit macro to format it, it will give us good text align. The macro defination is very simple, first step is...
There are lots of database (including Oracle, Sybase, and all the SQL Servers etc) running on Windows platform, and there are a lots of DBA tools wrote in Perl language. To use them on Windows, you need to...
By DBMS_LOB PL/SQL package I cannot load client OS files to LOB columns, you can either use lobs utility, or use Oracle's SQL Loader (sqlldr) to do this job. I have the following OS files in...
Perl is very popular programming language for DBA scripts and tools, following are the code of how to connect to different database with DBI. There is something special for the DBD-Sybase driver. DBI->connect('DBI:Oracle:tnsname', $user, $pass); DBI->connect('DBI:mysql:database=;host=;port=', $user, $pass);...
To get the maximum performance of Oracle SQL Loader (sqlldr), take the following into account. Use fixed width column text file such as following. 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON ...
There are lots of database running on Windows, and lot's of useful utilities developed for Windows. Sometime DBAs need to schedule a utility to run at specific time. For example, extract data to text file for data warehouse...
One of our batch job get the following error when calling sqlldr to load text file into database. % 9205/bin/sqlldr *****/*****@***** control=testsqlldr.ctl data=..... SQL*Loader: Release 9.2.0.5.0 - Production on Tue Aug 7 18:00:35 2007 Copyright (c) 1982, 2002,...
DBVerify (dbv) is an utility in standard Oracle binary to check whether there is any block corruption in the data file. I use it often in the following case. After building standby, dbv all the files...
I get this question from oracle-l groups about how to put "Sep 17 2007 7:00:00:020AM" (values from Sybase database) into an Oracle date column.. After several retries, I am be able to load it now. I create a...
SQL Server and Sybase provide a system procedure to do this job, while Oracle hasn't it. And now I receive a task to move few tables (totally 80gb) from one schema to another schema. I tested this using...
When recovery with AUL (MyDUL) in text mode (default mode), it will generate three files for each table (Recovery Example), then we need to execute the SQL file to create the table, and then load the data by...
Some old databases are created with US7ASCII character set, when the client NLS_LANG setting was the same with database character set, Oracle will not do any conversion, so the client was able to store Chinese into the database....
Standby is a good way for disaster recovery, usually we create at least two standbys for each production database, one is locally with minimum time delay to the primary, and another is remotely in different city with 8...
I heard a bad news from someone that one of his colleagues dropped an Oracle schema by mistake in Oracle Enterprise Manager (OEM), because the OEM seems in hang status, so they did some random mouse clicks or...
There is a "query" option in Oracle exp utility, which enables you export filtered rows of a table by providing a where clause with this option. However this option is not widely used, many people do not clearly...
There is something wrong in my mind about the "REPLACE" mode of Oracle SQL Loader, I have thought that sqlldr will use a merge logic, to replace matched rows if the target table has a primary key. But...
The steps of context install on Oracle 10g. 1, create tablespace drsys 2, run @?/ctx/admin/catctx.sql ctxsys drsys temp01 nolock 3, run @?/ctx/admin/defaults/drdefus.sql 4, grant execute on ctxsys.ctx_ddl to public The steps of context uninstall (remove) on...
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...
We moved some Oracle data files from OS file system to Veritas file system, to get the better performance, we need to convert them to Veritas Quick-IO file, then Oracle will treat them as raw devices. $> ls...
We cannot use following command to move Oracle context index to another tablespace, because it's a special kind of index. ALTER INDEX ... REBUILD TABLESPACE CTXDATA; You need create new storage setting or modify existing storage...
I wrote some OCI program and compiled them into 32 bit executable on 64 bit Solaris machine. So I need to set the proper Oracle library path to get it work. I wrote a shell script to set...
I am moving some data by ociuldr with the following field and record option. ociuldr ... field=0x07 record=0x06 ... I think it will work well, but actually not, there are rows rejected when loading with sqlldr....
On Linux/Unix we can use gcc to compile OCI source code. Use the following command to compile the text unload utility as 64 bit binary. gcc -m64 -D_LARGEFILE64_SOURCE -D_FILE_OFFSET_BITS=64 -I${ORACLE_HOME}/rdbms/public -I${ORACLE_HOME}/rdbms/demo -L${ORACLE_HOME}/lib -lclntsh -o ociuldr.bin ociuldr.c ...
We really like RMAN's data file copy feature, and we like to automate the standby build job with our own script. When we use RMAN script to copy the data file, we got few problems, first is a...
When you use old version of exp to export tables with LOB column from Oracle 9.2.0.5 or higher version, you will get an error "EXP-00003 : no storage definition found for segment .....", actually this is an Oracle...
I am not so familiar with the DBMS_STATS procedure, so I always use the "ANALYZE" command. One day I got the following problem. Let's start a demo, first step is creating demo partitioned table. SQL> CREATE TABLE T_PARTDEMO(...
OCI is a very effective program interface to access Oracle database, I have wrote several DBA utilities with OCI interface. On Windows the compiled binary can run under Oracle 8i/9i/10g client environment. But on Linux/Unix, it cannot, because...
I create the following table in an UTF8 character set Oracle database. create table t_charset(col1 varchar2(10), col2 varchar2(20)); The I insert two rows under different NLS_LANG settings. The first column is the client NLS_LANG setting, the...
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...
The following connection format method is supported by 8i/9i/10g: C:\>sqlplus /nolog SQL*Plus: Release 10.2.0.1.0 - Production on Tue Dec 5 17:40:22 2006 Copyright (c) 1982, 2005, Oracle. All rights reserved. SQL> conn anysql/anysql@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=localhost)(Port=1521))(CONNECT_DATA=(SID=TEST))) Connected. SQL> Yong Huang,...
When creating or rebuilding indexes on hot tables, we will use parallel option to make the it quickly, but please remember to disable the parallel after creating or rebuilding, else it will make the relative SQL run in...
I have a table named "T_OBJECTS" and one index on it named "T_OBJECTS_IX1". They are not analyzed now: TABLE_NAME NUM_ROWS LAST_ANALYZED -------------- ---------- --------------- T_OBJECTS T_OBJECTS_IX1 Now I receive a task to create a second index...
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...
Few days ago, I told one of my friends that the XMLTYPE column equals to CLOB type, that is not true, it's an object type. Later he ask me how to modify the cache property of XMLTYPE column,...
In Pro*C or OCI, we can use an host array as a bind value to submit array operations, it will reduce the network round trips, user calls and execute count, it can greatly improve the performance. In Java...
I create a composite index (DEPTNO and ENAME columns) on table SCOTT.EMP. Then I run the following SQL. SQL> SELECT /*+ first_rows no_expand */ * FROM EMP 2 WHERE deptNO=10 AND (ENAME IS NULL OR ENAME >...
I got a task to run the following two SQLs in 20 site databases, the table is very big, about 100GB size in every database. So it not a easy task. SELECT /*+ FULL(T) PARALLEL(T,4) */ COUNT(*) FROM BIGTABLE...
My notebook (Dell D630, 2GB memory) runs very well in company, but when I bring it to home, the windows explore runs slowly, and always hang for about one to three seconds. While the other application did not...
A lot of earlier created Oracle database did not configure the character set correctly in Asia, they choose US7ASCII, WE8DEC, WE8ISO8859P1 as the database character set. By default they just keep the NLS_LANG the same as database setting,...
A few databases got corrupted recently. Such as, disk holding the system tablespace data files got crashed, tables got dropped by mistakes, schema got dropped by mistakes, tablespace got dropped by mistakes (data files still exist). These are...
I saw two case recently about losing one of the data files for a tablespace, first case was losing a data file totally, second case is one of the data files has no recent backup, so it's equal...
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...
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...
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...
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,...
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...
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...
If you are on database release 10.1.x or 10.2.x the XDB Feature is Mandatory in order to use any of the member functions of the XMLTYPE. This is true even if you are not using the repository ,...
A lot of people complain the speed of Oracle exp/imp utility, seems the only thing we can do is to specify large buffer size. Actually there are some other methods to improve the performance a little. For export...
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...
When installing Oracle RAC on VMWare hosts, I don't want to install any desktop management system (both KDE and GNOME) for Oracle installer or other Java based configuration tools, it will make the VMWare host too big and...
I created a 4GB disk for the first node, choose the minimum installation of OS (RedHat Advanced Server 4 Update 5), and then installed the development packages and X-Window packages. But for the second node, I just created...
When installing Oracle RAC or database software, you need to make some changes to the Linux kernel parameters. # For Oracle 10g RAC kernel.sem=250 32000 100 128 kernel.shmmax=1048576000 kernel.shmmni=4096 kernel.shmall=2097152 fs.file-max=65536 net.core.rmem_default=262144 net.core.rmem_max=262144 net.core.wmem_default=262144 net.core.wmem_max=262144 net.ipv4.ip_local_port_range=1024 65000 Just...
I were doing some Oracle tuning jobs in last two weeks, and got a SQL like following : INSERT /*+ APPEND */ INTO B SELECT /*+ PARALLEL(A,4) FULL(A) */ SEQ_B.NEXTVAL, A.* FROM A;...
I am writing some DBA scripts with perl, one of them is the archive log file related. I have to implement this feature for more works. The first version is implemented by UNIX find command. # # Get...
When managing a lot of databases, finding a good policy to manage the archive log files is very important to make the work easy. Usually archive log files are placed on dedicate volumns, purge by space will help...
The SQLULDR2 improved the support of unloading in delimited and fix length text file. There are some difference for the SQL*Loader (sqlldr) control files. Here is a sample control file for delimited format. LOAD DATA INFILE 'uldrdata.txt' "STR...
I did a performance comparation of Oracle datapump, export and SQLULDR2 on a linux machine, the CPUs' frequency is 3G HZ. When running exp and SQLULDR2, the relative process take almost 100% of one CPU, so for data...
It's quite easy to display database data with WebChart utility. After loading the oramon performance data into database, I create a sample WebChart page to show the performance data, following is the CPU utilization chart. We...
Oracle give use lots of performance views, not only for database only, but also some important data about the applications. As a DBA we should analyze those data, and show the data to others. For example, the SQL...
Oracle give use lots of performance views, not only for database only, but also some important data about the applications. As a DBA we should analyze those data, and show the data to others. For example, how to...
I was changing the code of oramon in recent few weeks, keeping on adding more useful performance data and removing useless performance data, it took me one month to finally release the new version of oramon, and deploy...
For critical online transaction process database, it's very important to find out an effective way to monitor the database performance and alert the database performance problem. Because their are many concurrent sessions, any small problem can make the...
Now, we have more performance alert point than previously, we just had performance alert based on the load average and active sessions. I were wondering that we will receive too much performance alert and it may bother our...
Usually our application are bound to one physical database, for example, we always read our data from specific Oracle database or MySQL database, or write business data to specific Oracle database or MySQL database. The physical database is...
