Chacterset issue with SQL loader and external table

Just had an issue with different contents for tables loaded with sqlldr and external table for a same input data file.

The issue is mainly caused by the different default behavior on the characterset of these 2 methods.

For external table, if character set is not specified, oracle assumes the data file has the same character set with the database. The DB characterset can be found with the following query:

SQL> select value from nls_database_parameters where parameter='NLS_CHARACTERSET';

For sqlldr, if characterset is not specified, it will try to use the value found in your client side NLS_LANG. If NLS_LANG is not set, it uses the default value, which is AMERICAN_AMERICA.US7ASCII .

Step by step Installation guide: Linkedin Azkaban as a webapp on redhat linux tomcat

Before you start, make sure you have tomcat installed and running with "service status tomcat5".

• download the latest azkaban tarball to /opt at http://github.com/downloads/azkaban/azkaban/azkaban-0.04.tar.gz
• tar -xvf azkaban-0.04.tar.gz
• copy azkaban-0.04/dist/war/azkaban.war /var/lib/tomcat5/webapps
• stop tomcat service: service tomcat5 stop
• append "AZKABAN_HOME="/opt/azkaban-0.04" into /etc/sysconfig/tomcat5
• start tomcat service: service tomcat

wait for a couple of minus, you should get azkaban running at http://your_host_name:port/azkaban/

Query to get tablespace contiguous spaces

Ever getting ORA-01659: unable to allocate MINEXTENTS beyond 8 in tablespace XYZ?

I got this last weekend when creating a table with several GB level partitions.

The error indicates that it can not find contiguous spaces to create initial extents * minextents.

How do we know how many contiguous spaces we have on the tablespace, I tried to search the web but only got lengthy complex PL/SQL scripts. It ends with that I have to write my own. I post it here hoping someday it may help somebody.

SELECT
tablespace_name,
file_id,
round(sum(bytes)/1024/1024/1024, 3) contiguous_free_space_gb,
sum(blocks) no_of_blocks,
min(block_id) as starting_block,
round((sum(sum(bytes)) over ())/1024/1024/1024, 3) total_ts_free_space_gb
FROM
(
SELECT
tablespace_name,
file_id,
block_id,
bytes,
blocks,
nvl(block_id - min(block_id) over (partition by file_id) - sum(blocks) over (partition by file_id order by block_id rows between unbounded preceding and 1 preceding), 0) gap_blocks
FROM dba_free_space
WHERE tablespace_name = '&ts_name'
)
GROUP BY gap_blocks, tablespace_name, file_id
ORDER BY sum(bytes) desc

やられた

It was 5:00PM and I was asked to write a function which needs to parse a comma delimited string. I was lazy and trying to avoid the lengthy substr/instr loop. I remember I read somewhere that there is builtin function exactly does the same thing.
After a search, I found out the function dbms_utility.comma_to_table and that's when the nightmare started.

After I finished the function and tested with 'abc,efg', it works well.
When I put it into real use
This is what I got:
ORA-00931: missing identifier
ORA-06512: at "SYS.DBMS_UTILITY", line 125
ORA-06512: at "SYS.DBMS_UTILITY", line 160
ORA-06512: at "SYS.DBMS_UTILITY", line 202
ORA-06512: at line 7
00931. 00000 - "missing identifier"


http://www.dbforums.com/oracle/887432-dbms_utility-comma_to_table-giving-problem.html
"Unfortunately dbms_utility.comma_to_table is NOT a general purpose utility as its name would suggest. It only works with comma-separated names that are valid database object names, i.e. up to 30 chars beginning with a letter"