Change column position

In Oracle, it is not straightforward to change the position of a column. Let alone one that contains data.

Without resolving to manually changing metadata tables, but using SQL, the following trick can be applied:

- Create a new table called "newtable" with the desired column order, based on "oldcolumn"

Note: generate DDL script for "oldtable" and only change the position of the columns. Do not apply constraints, defaults, primary keys, foreign keys, etc. They are named and the names cannot exists twice in a schema

- Insert all data from "oldtable" into "newtable" using a DML INSERT-statement
- Drop "oldtable"
- Rename "newtable" as "oldtable"
- Apply constraints, defaults, etc to "oldtable"

Note: if columns are deleted or newly added to "newtable", then this is not straightforward and each constraint, default, etc should be inspected before applying

Or you can use the "dbms_redefinition" package (bhaski on www.geekinterview.com):

But you should have these previleges -->
# execute access on the dbms_redefinition package
# create any table # alter any table
# drop any table
# lock any table
# select any table

create table tab1(col1 char,col3 number);

alter table tab1 add(col2 char);

create table tab2 as select col1,col2,col3 from tab1; exec

dbms_redefinition.can_redef_table('schema_name','tab1');

begin
dbms_redefinition.start_redef_table('schema_name','tab1','tab2','col1,col2,col3');
dbms_redefinition.finish_redef_table( 'schema_name', 'tab1', 'tab2' );
end;

select * from tab1;
col1 col2 col3

Change column data type

In Oracle, it is not straightforward to change the data type of a column that contains data. Without resolving to manually changing metadata tables, but using SQL, the following trick can be applied:

- Add a new column to the table called "new column" with the new data type
- Copy the values of "old column" to "new column" using a DML UPDATE statement
- Remove the "old column" from the table using a DDL ALTER statement

Most recognizable samples

Breakbeats
Al Green - I’m Glad You’re Mine (0:00)
[Eric B. & Rakim - Mahogany, The Notorious B.I.G. - I Got a Story to Tell, RZA - My Lovin' Is Digi]

Bad Bascomb – Black Grass (1:41)
[Doug E. Fresh - Everybody Got 2 Get Some, Monie Love - Monie In The Middle]

Billy Squier – Big Beat (0:00)
[Ice Cube – Jackin’ For Beats, Big Daddy Kane – Get Down, A Tribe Called Quest – We Can Get Down, EPMD – Get Wit This]

Booker T. & The M.G.'s - Born Under A Bad Sign
Booker T. Jones & William Bell - Born Under A Bad Sign

Bobby Byrd - Hot Pants (0:00)
[Sir Mix-a-Lot - One Time's Got No Case, Das EFX - Straight Out the Sewer, Ini Kamoze - Here Comes the Hotstepper, Run-DMC - What's It All About]

Chuck Brown and The Soul Searchers - Ashley’s Roach Clip (3:31)
[Eric B. & Rakim – Paid In Full, PM Dawn – Set Adrift On Memory Bliss, Nice & Smooth - Down the Line]

Dexter Wansel – Theme From The Planets (0:00)
[Eric B. & Rakim - I Ain't No Joke, Last Night by Kid 'N Play, Gutfest '89 by Digital Underground]

Donny Hathaway – Magnificent Sanctuary Band (0:00)
[Beastie Boys - Johnny Ryall]

Edwin Birdsong - Rapper Dapper Snapper (0:00)
[De La Soul - Me Myself and I, Gang Starr - Skills]

Fred Wesley and The J.B.'s - More Peas (7:22)
[Organized Konfusion - Fudge Pudge, Cypress Hill - The Phunky Feel One]

Funkadelic – Good Old Music (0:00)
[Young Black Male by 2Pac]

Kool & The Gang – Jungle Jazz (0:13)

Kool & The Gang - N.T.
[Jump by Kris Kross, Fakin' the Funk by Main Source]

Funk Inc. – Kool Is Back (1:48)
[Jeru the Damaja - Come Clean]

Isley Brothers – Footsteps In The Dark

James Brown - Blues and Pants (0:00)
[Ice-T - New Jack Hustler, Cypress Hill - The Phunky Feel One]

James Brown - Funky Drummer (5:35)

James Brown – Funky President (0:00)
Eric B. Is President by Eric B. & Rakim, Hey Ladies by Beastie Boys]

Jeff Beck – Come Dancing (0:00)
[Chino XL - It's All Bad, Gang Starr - New York Strait Talk]

Jimi Hendrix - Flashing (0:00)

Joe Tex – Papa Was Too (0:00)
[EPMD - Headbanger, Wu-Tang Clan - Wu-Tang Clan Ain't Nuthing Ta F*** Wit]

Lafayette Afro Rock Band - Hihache (0:00)

Led Zeppelin - When The Levee Breaks (0:00)
[Ice-T - Midnight, Eminem - Kim]

Little Feat - Fool Yourself (0:00)
[A Tribe Called Quest - Bonita Applebum, Organized Konfusion - Soundman]

Little Richard – The Rill Thing (0:00)

Lowell Fulsom - Tramp (0:00)
[House Of Pain - Jump Around, Cypress Hill - How I Could Just Kill A Man]

Lyn Collins - Think (About It) (1:23)

Manzel - Midnight Theme (0:00)
[Wreckx-N-Effect - Rump Shaker, Cypress Hill - How I Could Just Kill A Man]

Mountain - Long Red (Live) (0:00)

Melvin Bliss - Synthetic Substitution (0:00)
[Naughty By Nature - OPP, Ice-T - Original Gangster, The Pharcyde - Ya Mama]

Orange Crush – Action (0:25)
[Do You Want It... Do You Like It... by Puff Daddy, We Major by Kanye West]

Power of Zeus – Sorcerer of Isis (0:10)
[The Wiseguys - Face the Flames, Eminem - Amityville]

Q65 – Get Out Of My Life Woman
[Jeru the Damaja - Revenge of the Prophet (Part 5), The Wiseguys - We Be the Crew]

Roy Ayers – The Boogie Back (0:00)
[N.W.A - F*** Tha Police]

Silhouettes – Fonky First (0:00)

Skull Snaps – It’s A New Day (0:00)
[The Pharcyde - Passin' Me By, Digable Planets - For Corners]

Sly & the Family Stone - Sing a Simple Song (2:12)

The Headhunters – God Make Me Funky (0:09)
[Eric B. & Rakim - Beats for the Listeners]

The Honey Drippers - Impeach the President (0:00)

The Incredible Bongo Band - Apache (0:00)
[Sugarhill Gang - Apache, LL Cool J - You Can't Dance, Grandmaster Flash - Freelance]

The Meters – Here Comes The Meterman
[Big Daddy Kane - Long Live The Kane, Run-DMC - How'd Ya Do It Dee?]

The Meters - Look-Ka Py Py (0:50)
[Cypress Hill - The Phunky Feel One]

The Winstons - Amen, Brother (1:27)
[N.W.A. - Straight Out Of Compton, 3rd Bass - Portrait Of The Artist As A Hood, Brand Nubian - The Gods, Snow - Informer, Eric B. & Rakim - Casualties of War]

Young Holt Unlimited – Wah Wah Man

Zapp and Roger – More Bounce To The Ounce (0:00)
[EPMD – You Gots To Chill, Ice Cube – Jackin’ For Beats, Public Enemy – One Million Bottlebags, Ice Cube – Look Who’s Burnin’]

The Turtles – I’m Chief Kamanawanalea (0:00)
[Serious by Steady B feat. KRS-One, Say No Go by De La Soul, AmeriKKKa's Most Wanted by Ice Cube]

Grady Tate - Be Black Baby (0:00)
[Set It Off by Big Daddy Kane, Fakin' the Funk by Main Source, Stand Together by Beastie Boys]

Sounds
The J.B.'s - The Grunt (0:00)
Herbie Hancock - Wiggle Waggle (0:00)

Identifying keys

A key that is made up of one field is a simple key. All other keys are combined keys. There are two types of combined keys, compound and composite:

Simple key: key that consists of one field

Combined key: key that consists of more than one field

Compound key: combined key in which each field is itself a simple key (of another entity)

Composite key: combined key, that is not a compound key

A new term I have made up myself:

Concatenated key: a simple key, that is the concatenation of the fields in a combined key

Truncate tables that have a foreign key

A script to truncate tables that have foreign keys referencing them. It also keeps one record in the table, the one with ID=0. In this case, each table has this field as a primary key.

Finally, some tables are not to be truncated. The readible names of these tables are in a metadata table called TDWM_TABLES. The readible name is in the field VIEWNAME.

The process is:

- disable constraints on the selected tables, i.e. with names like 'TDWH_%'
- create a new intermediate table via CTAS that contains the records to keep, i.e. with ID=0
- truncate the table
- copy records back from the intermediate table
- drop intermediate table
- enable constraints on the selected tables, i.e. with names like 'TDWH_%'. Note: enable PK constraints before enabling the FK constraints!

Here is the script:

/* SCRIPT */

SET SERVEROUTPUT ON SIZE UNLIMITED

BEGIN

-- Disable constraints
DBMS_OUTPUT.PUT_LINE ('-> Disabling constraints');
FOR reg IN (SELECT 'ALTER TABLE ' || table_name || ' DISABLE CONSTRAINT ' || constraint_name || ' CASCADE' as ST1 FROM user_constraints WHERE table_name LIKE 'TDWH_%')
LOOP
DBMS_OUTPUT.PUT_LINE (reg.ST1); EXECUTE IMMEDIATE reg.ST1;
END LOOP;

-- Truncate tables
DBMS_OUTPUT.PUT_LINE ('-> Truncating tables');
FOR reg IN (
SELECT
'CREATE TABLE Z_' || TABLE_NAME || ' AS SELECT * FROM ' || TABLE_NAME || ' WHERE ID=0' AS ST1,
'TRUNCATE TABLE ' || TABLE_NAME AS ST2,
'INSERT INTO ' || TABLE_NAME || ' SELECT * FROM Z_' || TABLE_NAME AS ST3,
'DROP TABLE Z_' || TABLE_NAME AS ST4
FROM USER_TABLES t
INNER JOIN TDWM_TABLES b ON UPPER(t.TABLE_NAME) = UPPER(b.TABLENAME)
WHERE t.TABLE_NAME LIKE 'TDWH_%' AND
UPPER(b.VIEWNAME) NOT IN ('TDAYOFWEEK','TMONTHOFYEAR','TQUARTEROFYEAR')
)
LOOP
DBMS_OUTPUT.PUT_LINE (reg.ST1); EXECUTE IMMEDIATE reg.ST1;
DBMS_OUTPUT.PUT_LINE (reg.ST2); EXECUTE IMMEDIATE reg.ST2;
DBMS_OUTPUT.PUT_LINE (reg.ST3); EXECUTE IMMEDIATE reg.ST3;
DBMS_OUTPUT.PUT_LINE (reg.ST4); EXECUTE IMMEDIATE reg.ST4;
END LOOP;

-- Enable constraints
DBMS_OUTPUT.PUT_LINE ('-> Enabling constraints');
FOR reg IN (SELECT 'ALTER TABLE ' || table_name || ' ENABLE CONSTRAINT ' || constraint_name as ST1 FROM user_constraints WHERE table_name LIKE 'TDWH_%' ORDER BY CONSTRAINT_NAME DESC)
LOOP
DBMS_OUTPUT.PUT_LINE (reg.ST1); EXECUTE IMMEDIATE reg.ST1;
END LOOP;
END;

Unlimited login attempts on Oracle 11g

In Oracle 10g, by default, each user has unlimited login attempts before being blocked. In Oracle 11g this behaviour is changed to 10 attempts.

When a user/schema is created, it gets assigned to the default profile. The easiest way for unlimited login attempts for all users is to give the default profile the rights:

ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED;

Reconfigure OracleXE built-in webserver's port

OracleXE comes with a built-in webserver, running on port 8080. Since this is a wellknown port, chances are that it is already in use on your machine, and therefore conflicting with an already running webserver.

Check this blog to reconfigure it:

http://daust.blogspot.com/2006/01/xe-changing-default-http-port.html

Cannot load package & required privilege is not held by the client

How to start a filesystem dtsx package from sql agent, without a config file or without adding a package password to the command line:

- ssis: make note of the package owner, should be windows (domain) account
- ssis: package: encrypt with userkey
- os: sql agent service should run under LocalSystem account
- os: add package owner to local groups: SQLServer2005DTSUser$SERVER, SQLServer2005SQLAgent$SERVER
- ssms: create credential for package owner
- ssms: create proxy using created credential, add all principals, add all subsystems
- ssms: use the created proxy as "run as" user for the job step that starts the package

There is no easy option to specify the password for a package from sql agent, hence the "encrypt with userkey".

See also: http://support.microsoft.com/kb/918760

Background information
Principals have access to a proxy. A proxy is a mapping to a credential. A credential is a mapping to a Windows account.

1. SSMS / Security / Credential, create a credential. Set identity to an existing account that has the appropriate rights to run the job.
2. SQL Agent / Proxies / SSIS Package Execution, create a new proxy
2a. Proxy / General / Credential name: select the credential from step 1
2b. Proxy / General / Subsystems: select all
2c. Proxy / Principals / MSDB Role, add principals: sqlagentuserrole, sqlagentreaderrole, sqlagentoperatorrole. This are the principals that can access the proxy.
2d. The account must have "logon as a batch job" privileges (check with secpol.msc)
4. Set SQL Server Agent service account to LocalSystem
5. If needed, run a job step under another account. Typically: a SQL Server account based on a Windows account: SQL Server Agent / Jobs / job / Steps / Edit / Advanced / Run as user,
- select proxy
- type SSIS package
- package source File system

If the "A required privilege is not held by the client" error occurs (from Microsoft website):

1. Set the SQL Server Agent service account in SQL Server Configuration Manager to the LocalSystem account.
2. Stop and then start the SQL Server Agent service.
3. Reset the SQL Server Agent service account in SQL Server Configuration Manager back to the original account.
4.Stop and then start the SQL Server Agent service.

From Marc at Stackoverflow:

So it boils down to really just two accounts:

one account is needed to run the SQL Server Agent Windows service - this is a Windows account on your machine / server which needs to have enough permissions to run the service, start and stop it - either use LocalSystem, Network Service, or whatever other Windows account you have to run services with

The other account would be the account to run your SQL Server Agent steps under - that's typically a SQL Server account (which could be based on a Windows account), and it needs enough privileges inside SQL Server to do its job, e.g. it needs access to the database objects and all. I would strive to have just one account for each app that runs the SQL Server jobs - makes life a whole lot easier!

From Influent at SQLTeam:

1. set ProtectionLevel to DontSaveSensitive in package properties in Visual Studio (using SSPI anyway so not sure it mattered)
2. created configuration file (had to alter path to config file in dtsx file since it's different on servers than in dev environment)
3. instead of importing the package into MSDB, set the job step Package Source to "File system" and pointed to dtsx file on local drive of server
4. set the Configurations tab of the job step to point to the dtsConfig file created in step 2
5. made sure the AD user kicking off the job was part of local server groups: Administrators, SQLServer2005DTSUser$SERVER, and SQLServer2005SQLAgent$SERVER (I eventually removed Administrator permissions because I only needed it to log to Windows Event Log)
6. made the associated SQL user to the user in step 5 part of the SQLAgentUserRole in the msdb database
7. made sure the local user assigned to the SQL Agent service was part of local server groups: Administrators, SQLServer2005DTSUser$SERVER, and SQLServer2005SQLAgent$SERVER

From xprog.blogspot.com:

1) Executed as user: DOMAIN\user. The process could not be created for step 1 of job 0xB013D0354C8CBD46B79E948740EF5441 (reason: 1314). The step failed.
The error 1314 is "A required privilege is not held by the client".

This message indicates that the SQL Server Service account doesn't have the required rights to switch the security context to the proxy account.

To fix it verify:
1. The proxy account have the "Act as part of the operating system" privilege.
2. The SQL Server Account is in the group
SQLServer2005MSSQLUser$$
3. The Proxy account is in the group
SQLServer2005DTSLUser$$

SQL Server Agent Account must have 'Adjust memory quotas for a process' (or 'Increase quotas' in pre-Windows 2003) privilege on SQL Server.

From FriggleFragle at MSDN:

an issue i found was that sql server agent and ssis had been changed to not use the local system account. this caused me to continually get the dreaded --> "A required privilege is not held by the client" error. i fought with it all afternoon and eventually updated the services to use the local system account and it worked. perhaps this will save some other as much time as i wasted.

External tables

Data can be imported into and exported from Oracle 10g in different ways. E.g. by using the SQL*Loader tool for text files (sqlldr) or the Data Pump (impdp/expdp) tools for the propriety Oracle RAW format. For previous Oracle versions, the tools imp and are also available. Those import/export to external locations on the client machine, not the server machine.

Another way to get data in and out of Oracle is to define an external table. To do this you have to create a table in Oracle in the regular way using a CREATE TABLE statement, but with adding an additional ORGANIZATION EXTERNAL clause.

Within this clause the properties of the external (server side!) location can be specified.

EXAMPLE

CREATE TABLE TEST
(
  "CODE" VARCHAR2(17 BYTE)
)
ORGANIZATION EXTERNAL
(
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY DATA_PUMP_DIR
  ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE
    CHARACTERSET WE8MSWIN1252
    FIELDS TERMINATED BY 0X'09'
    MISSING FIELD VALUES ARE NULL
    REJECT ROWS WITH ALL NULL FIELDS
  )
  LOCATION ( DATA_PUMP_DIR: 'EXTFILE.TXT' )
)

FIELD LIST

The list of fields following the create table keywords cannot be inferred dynamically, but has to be specified explicitly. So it is not possible to "hook up" a text file and retrieving the list of fields automatically.

Trick: import the data first into a table using SQL Developer, TOAD, etc, and create the DDL script from it. Finally, copy and paste the field list from this script.

It is also possible to specify the "external" list of fields inside the organization external clause. This is, for example, similar to how it is done using Oracle Warehouse Builder (OWB).

TYPE

One of these properties is "TYPE", which specifies the access driver that Oracle uses. There are two values for this property:

• ORACLE_LOADER: only read access, from all kinds of text files (but not, for example, Excel .xls)
• ORACLE_DATAPUMP: read and write access, a propriety RAW format

The access driver is a wrapper Oracle uses to call the previously mentioned SQL*Loader or Data Pump functionality.

An error occurs when an insert statement is performed on a table is created with access driver ORACLE_LOADER:

SQL Error: ORA-30657: operation not supported on external organized table30657.0000 -  "operation not supported on external organized table"
*Cause:    User attempted on operation on an external table which is
           not supported.
*Action:   Don't do that!

The ORACLE_DATAPUMP type has to be used to export data from Oracle using an external table. Unfortunately, this data cannot be humanly read or imported into any other tool then an Oracle database.

DEFAULT DIRECTORY / LOCATION

A directory object has to be available in Oracle. If it is not already present, it has to be created. For example:

CREATE OR REPLACE DIRECTORY DATA_PUMP_DIR AS 'C:\'

Now the tag DATA_PUMP_DIR can be used in the organization external clause. The DEFAULT DICTIONARY property is mandatory and has to be used when setting the LOCATION.

A path specification is not allowed between the single-quotes.

This is not valid:

• LOCATION ( 'C:\EXTFILE.TXT' ) 

ACCESS PARAMETERS

The options are rather straightforward, except perhaps "FIELDS TERMINATED BY". To set the field delimiter a character can be specified between single-quotes. But it is also possible to specify special characters like tab or linefeed.

To specify a tab:

FIELDS TERMINATED BY 0X'09'

The 0X instructs Oracle that the value between the single-quotes should be interpreted as an ASCII value. In the example, ASCII character 9 is a tab.

OPTIONALLY ENCLOSED BY '"'

It is also possible to specify that some values have double-quotes around them. This however does not work in the following case:

Column 1 value - tab - tab - Column 3 value

Here a record has a value for columns 1 and 3, but not for column 2. It should be read as NULL, but is actually skipped. This results in the value of column 3 being wrongly inserted in column 2.

CHARACTER SET

Explicitly setting the character set of the external location prevents problems with interpreting the data and can improve performance.

From Odi's blog:

If you do not specify the CHARACTERSET parameter, the database character set is used to interpret the input file. As the input file is in WE8MSWIN1252, the ORACLE_LOADER driver gets confused trying to interpret single-byte WE8MSWIN1252 codes as multibyte AL32UTF8 codes.

The character set of the input file depends on the way it was created. Even on US Windows, you can create text files in different encodings. Notepad allows you to save the file in ANSI code page (=WE8MSWIN1252 on US Windows), Unicode (=AL16UTF16LE), Unicode big endian (=AL16UTF16), and UTF-8 (=AL32UTF8). The Command Prompt edit.exe editor saves the files in the OEM code page (=US8PC437 on US Windows).

-- Sergiusz

LOGGING

All errors are logged to a bad file - containing the violating records - and a log file - containing messages about the error that occured.

To disable logging, (some of) the following keywords can be added to the clause:

• NODISCARDFILE
• NOBADFILE
• NOLOGFILE

Oracle specification of the ORACLE_LOADER access driver:

http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10825/et_params.htm

SQLite enforces foreign key constraints

As of version 3.6.19, SQLite finally supports foreign key constraints that can be enforced.

http://sqlite.org/releaselog/3_6_19.html

A pragma statement has to be execute on the database to enable the enforcement:

PRAGMA foreign_keys = true;

Measuring disk I/O speed

Disk thruput tester is a handy tool to test the sequential read and write performance of storage media, like harddisks and SSD. It supports configurating custom block sizes:

http://www.objectso.nl/

Using this tool, testing the read/write performance of 8KB, 16KB, 32KB, etc, sized blocks is a breeze. The size and location of the test file is also configurable.

Note: on the website the tool is called "disk throughput tester".

Winload.exe missing or corrupt (0xc000000e)

Today we had a problem with Acronis Disk Doctor. We used it to try to resize a existing partition. The tool doesn't directly support dynamic disks - only after downloading additional drivers. These were downloaded, because we had those in our system, and the system had to be rebooted. No changes had been made to this point. But after the reboot, the system stopped almost immediately with a "black screen" reporting:

0xC000000E \WINDOWS\SYSTEM32\WINLOAD.EXE INFO: THE SELECTED ENTRY COULD NOT BE LOADED BECAUSE THE APPLICATION IS MISSING OR CORRUPT

What made this case different from all others that I could find on the internet is that the system partition was no longer accessible. It was not assigned the C drive letter.

So, the suggestions I could find did not work. They include, but are not limited to:

• Repair using the Vista installation disc
• From the Vista installation disc's command prompt option:
  bootrec /fixmbr -> completes succesfully, but didn't fix the issue
• From the Vista installation disc's command prompt option:
  bootrec /fixboot -> no system partition can be found
• From the Vista installation disc's command prompt option:
  bootrec /scanos -> no system partition can be found
• Perform the following three commands from the Vista installation disc's command prompt option:
  x:\windows\system32\bcdedit /set {default} device partition=c:
  x:\windows\system32\bcdedit /set {default} osdevice partition=c:
  x:\windows\system32\bcdedit /set {bootmgr} device partition=c:
  -> no system partition can be found

Bootrec is the "fdisk /mbr" for Windows Vista and Windows 7.

If you got here and you still have a C drive letter assigned and that drive is accessible, then the suggestions above might work. I suggest you try them first.

If you don't have a system drive letter, then the resolution is fairly simple:

Start the repair option from a Windows 7 installation disk.

It detected that there were issues with the current Vista installation and fixed them. We rebooted the system and all was well again. No data loss and still running Vista.

We used a Windows 7 Ultimate x64 disk to fix a Windows Vista Ultimate x32 OS.

Cursor variants in Oracle 10g

Examples of different types of cursors. Each has its own pros and cons. Example 1 is the most straightforward and easiest to understand. The variants in example 3 perform better. I've put them here for reference.

-- 1) implicit cursor

DECLARE l_old_tbs VARCHAR(100) := 'USERS';
BEGIN
FOR item IN (
SELECT OWNER, TABLE_NAME FROM ALL_TABLES WHERE TABLESPACE_NAME = l_old_tbs )
LOOP
dbms_output.put_line(item.TABLE_NAME);
END LOOP;
END;

-- 2) explicit cursor

DECLARE l_old_tbs VARCHAR(100);
CURSOR c1 IS (SELECT OWNER, TABLE_NAME FROM ALL_TABLES WHERE TABLESPACE_NAME = l_old_tbs);
BEGIN
l_old_tbs := 'USERS'; -- set variable here, otherwise it is NULL!
FOR item IN c1 LOOP
dbms_output.put_line(item.TABLE_NAME);
END LOOP;
END;

-- 3a) local collection variable with multiple fields using BULK COLLECT and FORALL

CREATE TABLE TEST1 (OWNER VARCHAR(30), TABLE_NAME VARCHAR(30));
DECLARE l_old_tbs VARCHAR(100);
TYPE r_tmp IS RECORD (OWNER ALL_TABLES.OWNER%TYPE, TABLE_NAME ALL_TABLES.TABLE_NAME%TYPE);
TYPE t_tmp IS TABLE OF r_tmp;
tmp t_tmp;
BEGIN l_old_tbs:= 'USERS';
-- fill local collection "tmp" with one bulk operation
SELECT OWNER, TABLE_NAME BULK COLLECT INTO tmp FROM ALL_TABLES WHERE TABLESPACE_NAME = l_old_tbs;
-- update/insert/delete with one bulk operation; select not permitted.
-- no "loop" keyword, because following statement is performed as one batch. Check dbms_output, this is only one line.
-- always use "commit" statement
FORALL i IN tmp.FIRST..tmp.LAST
INSERT INTO TEST1 VALUES tmp(i);
dbms_output.put_line('first: 'tmp.FIRST', last: 'tmp.LAST);
COMMIT;
END;

-- 3b) local collection variable with single field using BULK COLLECT and FORALL

DECLARE l_old_tbs VARCHAR(100);
TYPE t_tmp IS TABLE OF ALL_TABLES.TABLE_NAME%TYPE;
tmp t_tmp;
BEGIN
l_old_tbs:= 'USERS';
-- fill "tmp" with one bulk operation
SELECT TABLE_NAME BULK COLLECT INTO tmp FROM ALL_TABLES WHERE TABLESPACE_NAME = l_old_tbs;
-- update/insert/delete with one bulk operation; select not permitted.
-- no "loop" keyword, because following statement is performed as one batch.
-- always use "commit" statement
FORALL i IN tmp.FIRST..tmp.LAST
INSERT INTO TEST1 (TABLE_NAME) SELECT tmp(i) FROM DUAL;
COMMIT;
END;

-- 3c) local collection variable with multiple fields using BULK COLLECT and FOR

SET SERVEROUTPUT ON;
SET SERVEROUTPUT ON SIZE UNLIMITED;
DECLARE
  l_old_tbs VARCHAR2(100);
  TYPE r_tmp IS RECORD (OWNER ALL_TABLES.OWNER%TYPE, TABLE_NAME ALL_TABLES.TABLE_NAME%TYPE);
  TYPE t_tmp IS TABLE OF r_tmp;
  tmp t_tmp;
BEGIN
  l_old_tbs:= 'USERS';
  -- fill local collection "tmp" with one bulk operation
  SELECT OWNER, TABLE_NAME BULK COLLECT INTO tmp FROM ALL_TABLES WHERE TABLESPACE_NAME = l_old_tbs;
  -- update/insert/delete with one bulk operation; select not permitted.
  -- with "loop" statement. Check dbms_output, there are as much lines as tmp.LAST.
  -- always use "commit" statement
  FOR i IN 1..tmp.COUNT
  LOOP
    --INSERT INTO TEST1 VALUES tmp(i);
    dbms_output.put_line('tablename: '||tmp(i).TABLE_NAME||', last: '||tmp.LAST);
    COMMIT;
  END LOOP;
END;

-- 3d) local collection variable with single field using BULK COLLECT and FOR

SET SERVEROUTPUT ON;
SET SERVEROUTPUT ON SIZE UNLIMITED;
DECLARE
  i INT := 0;
  l_tabname VARCHAR2(100);
  TYPE r_tmp IS RECORD (TABLE_NAME USER_TABLES.TABLE_NAME%TYPE);
  TYPE t_tmp IS TABLE OF r_tmp;
  tmp t_tmp;
BEGIN
  -- fill local collection "tmp" with one bulk operation
  SELECT TABLE_NAME BULK COLLECT INTO tmp FROM USER_TABLES WHERE UPPER(TABLE_NAME) LIKE 'TTMP_%';
  -- update/insert/delete with one bulk operation; select not permitted.
  -- with "loop" statement. Check dbms_output, there are as much lines as tmp.LAST.
  -- always use "commit" statement
  FOR i IN 1..tmp.COUNT
    LOOP
      dbms_output.put_line(i);
      l_tabname := 'DROP TABLE ' || tmp(i).TABLE_NAME; 
      dbms_output.put_line(l_tabname);
      EXECUTE IMMEDIATE l_tabname;
      COMMIT;  
    END LOOP;
END;

-- 4) use a temp table

CREATE GLOBAL TEMPORARY TABLE tmp (OWNER VARCHAR(30), TABLE_NAME VARCHAR(30);

Create handwritten truetype font for free using fontcapture

Fontcapture (http://www.fontcapture.com/) is the only website I could find that offers a service to convert handwritten characters into a True Type (.ttf) font for free. It supplies you with a template to fill in each of the glyphs (characters) of the font. You can print this template, fill it by pen, and scan and upload it to the fontcapture website. The bitmaps are turned into vector paths in the background and the font is created almost instantly.

Another advantage is that if you own a tablet pc or a (Wacom) pentablet, you can use it to fill in the template digitally. No need to print and scan the template.

The mechanism can also be used to trace bitmaps, since the tool does not distinguish between characters or other designs. The result is a font with vectorized images.

Alternatives:

• Fontifier (http://www.fontifier.com/): almost identical to Fontcapture, but charges $9 per font
• Yourfonts (http://www.yourfonts.com/): almost identical to Fontcapture, but charges $9 per font
• Fontgrinder: (http://www.fontgrinder.com/): idem, but charges even more: $14.95
• Fontstruct (http://fontstruct.fontshop.com/): free, but doesn't support handwritten fonts. The website has an editor for constructing your own font from predefined building blocks. A sort of "lego-for-font" method.

UPDATE 20100106: Unfortunately, the site has been taken offline. The domain is now referring to yourfonts.com. No free alternative is available anymore.

UPDATE 20110727: There is a new website offering the same free funtionality: http://www.myscriptfont.com

Netsend

Windows Vista and Windos 7 are no longer shipped with the command line tool netsend. Fortunately, a freeware alternative is available at:

http://www.lantalk.net/netsend/

List of freeware tools

Explorer & OS

• Total Commander (shareware): http://www.ghisler.com/
• 7-Zip: http://www.7-zip.org/
• VirtualCloneDrive: http://www.slysoft.com/nl/virtual-clonedrive.html
• NTFS Undelete: http://www.ntfsundelete.com/
• CDBurnerXP: http://cdburnerxp.se/en/download
• Atto Disk Benchmark: http://www.attotech.com/products/product.php?sku=Disk_Benchmark

Editor

• Notepad2: http://www.flos-freeware.ch/notepad2.html
• ExifTool & ExifToolGUI: http://www.sno.phy.queensu.ca/~phil/exiftool

3D

• Now3d: http://www.now3d.it/Eng/
• Blender: http://www.blender.org/
• Sketchup: http://sketchup.google.com/

Graphics

• FastStone Image Viewer: http://www.faststone.org/
• Paint .Net: http://www.getpaint.net/
• IcoFX: http://www.icofx.ro/
• Screenhunter Free: http://wisdom-soft.com/products/screenhunter.htm
• Inkscape: http://www.inkscape.org/
• FontForge: http://fontforge.sourceforge.net/

Conversion

• Super: http://www.erightsoft.com/
• MediaCoder: http://mediacoder.sourceforge.net/download.htm
• PDFsam (split and merge): http://www.pdfsam.org/
• Bullzip PDF Printer: http://www.bullzip.com/products/pdf/info.php
• A-PDF extractor (PDF to text): http://www.a-pdf.com/text/index.htm

Media & audio/dsp

• Renoise: http://www.renoise.com/
• Zoom Player 5.02 (6+ is no longer free): http://download.cnet.com/Zoom-Player-Standard/3000-2139_4-10068896.html?tag=mncol
• Media Tagger (Ladislav Dufek): http://www.softpedia.com/get/Multimedia/Audio/Tag-Editors/Media-Tagger.shtml
• Subtitle Workshop: http://www.urusoft.net/home.php?lang=1
• Divx 7: http://www.divx.com/en/win
• Youwashock: http://youwashockvst.googlepages.com/
• Stomper: http://www.lysator.liu.se/~zap/stomper/index2.html
• MP3Val: http://mp3val.sourceforge.net
• T@B ZS4: http://www.zs4.net

Databases

• PostgreSQL (as a service): http:/www.postgresql.org
• SQL Server Compact 3.5 (in-process): http://www.microsoft.com/Sqlserver/2005/en/us/compact.aspx
• SQLite (in-process): http://www.sqlite.org/

Great directors and actors

Just a list for myself, because I keep forgetting names.

Directors

• Darren Aronofski: Pi, Requiem For A Dream, The Wrestler
• Christopher Nolan: Memento, Insomnia, Batman Begins, The Prestige, The Dark Knight
• Clint Eastwood: The Bridges Of Madison County, Mystic River, Million Dollar Baby, Letters From Iwo Jima, Changeling, Gran Torino
• David Fincher: Se7en, The Game, Fight Club, Zodiac, The Curious Case Of Benjamin Button

Actors

• Christian Bale: Empire Of The Sun, Equilibrium, The Machinist, Batman Begins, Harsh Times, The Prestige, 3:10 To Yuma, The Dark Knight
• Joseph Gordon-Levitt: Mysterious Skin, Brick, The Lookout

Oracle SQL Developer screen redraw glitch

Perform the following steps to fix the redraw, or screen refresh, bug that can occur in Oracle SQL Developer when used on Windows Vista and Windows 7. They occur for example when scrolling horizontally or vertically through the records of a table when the records don't fit the window.

Start with just the sqldeveloper.conf adjustment. If that doesn't fix it, then perform all steps.

Steps to solve the issue:

• Add or replace the attribute of the following config file.
  ..\sqldeveloper\bin\sqldeveloper.conf
  AddVMOption -Dsun.java2d.noddraw=true
• Install the most recent jdk.
  Last seen here: http://java.sun.com/javase/downloads/index.jsp
  Select the Java SE Development Kit (JDK). At this time, JDK 6 Update 16 is the most recent version. Install it in C:\Program Files\Java\jdk1.6.0_16
• Add or replace the attributes of the following config files.
  ..\ide\bin\jdk.conf
  SetJavaHome C:\Program Files\Java\jdk1.6.0_16
  ..\ide\bin\ide.conf
  AddVMOption -Xmx256M
  AddVMOption -Dsun.awt.keepWorkingSetOnMinimize=true

Remove visual rings / circles from pen tablet in Windows 7

When using a pen in stead of a mouse in Windows, rings or circles may appear when you click. This happens at least in Vista and Windows 7. I am using a Wacom Graphire pen and wanted to deactivate those. This is not as straightforward as it seems. The rings are part of the Tablet PC Input functionality of the OS - not of the Wacom drivers or application.

How to disable these rings in Windows 7?

• Disable the "Tablet PC Input Service".
  This solved the problem for me for a while... until the rings came back for no apparent reason. I suspect because of an automatic update or updated Wacom driver, that reactivated the tablet pc ring feature.
• Uninstall Tablet PC Components.
  Control panel -> Programs and Features -> Turn Windows features on or off -> uncheck Tablet PC Components. Windows needs to be restarted after this.
• Kill the wisptis.exe process, e.g. via taskmanager.
  Then 1) rename \windows\system32\wisptis.exe to wisptis.bak, 2) create a textfile called wisptis.exe with nothing in it. Alternatively: open properties for wisptis.exe, go to security tab and "deny full control" for the "everyone" account. If "everyone" is not visible, click edit, click add, type in "everyone" and click ok.

I am not using a tablet pc, so these actions might have a negative impact when using one.

UPDATE 20100305: The wisptis.exe process still comes up when running some .Net applications. Even when the above tip is applied. Most probably it is copied from the protected cache / version history Windows keeps. To prevent this, download the tool "process blocker":

http://www.processblocker.com/download.html

Configure it to block "wisptis.exe" and now the rings are REALLY gone forever.

UPDATE 20120718: There is a registry fix to disable the rings:

http://viziblr.com/news/2011/8/14/the-ultimate-guide-to-making-your-wacom-tablet-work-on-windo.html

Download the fix from step 5. Try this first and if it doesn't work, then use the process blocker application.

UPDATE 20130410: ... or disable it via a group policy:

1. Open Local Group Policy Editor:Run... gpedit.msc
2. Navigate to User Configuration - Administrative Templates - Windows Components - Tablet PC - Cursors
3. Enable the Turn off pen feedback setting.

Limited connectivity using wireless internet in Vista Home SP1

I've had an issue with "limited connectivity" on my Vista Home SP1 installation. It is a laptop that connects wirelessly (WiFi) to a router. The router is connected to the internet via DSL. The router is working fine and the connection to the internet is ok. However, after using the internet for some time, sometimes half an hour, sometimes minutes, the wireless connection displays a yellow exclamation mark with the message "limited connectivity". It is not the router, since rebooting the laptop fixes the issue... for some time, and then it happens again. After that, trying to open network connections, or any other application related to the network, hangs that application.

I've collected the following possible remedies. I'm not sure which one solved it in the end. Obvious remedies like "put the laptop wifi switch to ON", "put in a correct WEP key" (or any other encryption key) or "check if the router is turned on" are left out:

• (20090618) I put this upfront, because all the tips that follow didn't resolve the issue for me, so I finally bought a new Wifi USB dongle. After buying a new laptop to add to my wireless network I noticed that the WEP encryption I was using didn't work for the new laptop. So in the end I changed protocols from WEP to the stronger WPA. After that, all PC's and even the Wii worked flawlessly. Even after switching back to the original wifi NIC on the laptop that first had the "limited connectivity" message, that laptop hasn't had the problem. So in short: try changing encryption protocols. Start with none (unsecured) to see if the problem is gone and then move on to stronger ones.
• Update drivers. There is a great free tool to update almost all drivers on your system, called DriverMax. Just register and it can be used legitimately for 30 days. I am not sure why it is "freeware" if it states that you can "use it freely for 30 days". It is based around a community of users uploading drivers. It the tool detects a new driver, you can then update it. This is a bit tedious, but always better then scouring the internet manually. The current version of the driver that is going to be updated is uploaded to DriverMax first. It is stored in their driver base for other users.
• Reset TCPIP stack. Execute the following statement at the command prompt:
  NETSH INT IP RESET
• Reset Winsock. Execute the following statement at the command prompt:
  NETSH WINSOCK RESET. Or remove both winsock registry keys, reboot, and reinstall TCP/IP protocol: http://support.microsoft.com/kb/811259
• Disable auto-tuning. Execute the following statement at the command prompt:
  NETSH INTERFACE TCP SHOW GLOBAL. Check if "Receive Window Auto-Tuning Level" is disabled. If not, then disable it:
  NETSH INTERFACE TCP SET GLOBAL AUTOTUNINGLEVEL=DISABLED. Re-enable with "=NORMAL".
• Disable IP6 protocol. Uncheck IPv6 protocol on the network adapters and disable in the registry. HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip6\Parameters, add or change DWORD key "DisabledComponents", set to hex "FF".
• Disable other protocols. Additionaly uncheck QoS Packet Scheduler, Link-Layer Topology Discovery Mapper I/O Driver, and Link-Layer Topology Discovery Responder.
• Disable power management on NIC. Right click the wireless network adapter in Device Manager, select properties, tab "Power Management" and uncheck the box "Allow the computer to turn of this device to save power". In my case it was turned back on again at a later point in time, but that could be due to installing a new driver afterwards.
• Change wireless channel. Try different channel numbers for the NIC. Each channel number (mostly between 1 and 13) corresponds to a different frequency. The property can be set on the advanced tab of the driver for the NIC in device manager. Set the adhoc channel accordingly in the wireless router configuration.
• Disable and re-enable network adapter. Apparently, this sometimes fixes issues.
• Reboot in Safe Mode. A solution like the previous one. Reboot the system, press F8, select "safe mode". After it has fully restarted, reboot again. Windows starts normally again.
• Run the System File Checker utility. Execute the following statement at the command prompt:
  SFC /SCANNOW. This is not of much of use to the majority of laptop owners, since they often do not own a copy of their Windows OS installation disk. When SFC finds a component (e.g. DLL) on the system, that is different from the one that was installed during the original installation, then it needs to get that original one from the installation disk.
• Check eventviewer. I have error messages concerning a NTIOMIN service that cannot start, TCPIP.SYS signing issues, and Kerberos related messages (can supposedly be ignored when the laptop is not part of a domain). Still haven't figured out the first two. I'm also getting "informational" events about the NETw5v32 wireless driver. This is related to an incorrect driver.
• Run registry or system cleaners: nice tools to run are HijackThis and CCleaner. The first can be used to remove questionable or corrupt services, browser helper objects, protocols, etc. The latter cleans the registry, removing orphanaged keys.
• Repair wireless. Right click the wireless network icon in the notification area and select "diagnose and repair". Alternative method. Execute the following statement at the command prompt:
  %windir%\system32\rundll32.exe ndfapi,NdfRunDllDiagnoseIncident. Do this before "limited connectivity" message pops up. In my case it displayed a message: "TCP/IP settings not optimized", and it optimized them. Running it again didn't display the message again, so I guess it did something the first time.
• Run Intel PROSet/Wireless diagnostics. If using Intel on-board wireless (I am using 3945ABG), then you can run the diagnostics utility. Execute the following statement at the command prompt:
  %windir%\system32\iPROSet.cpl. Run "diagnose" to check hardware, driver, radio, etc. Run "statistics" to check beacons, transmit errors, and power and power levels.
• Downgrade driver. Sometimes the most recent driver is not the best one for your device. Some drivers available are: NETw3v32, NETw4v32 (11.5.0.34), NETw5v32 (12.2.0.11). In my case, the NETw4v32 driver gave 100% signal quality, while a NETw3v32 driver gave only 60%. The NETw5v32 filled my eventlog with messages (see remark elsewhere).
• Set router to G-Only: put the router in network mode "G-Only". This way, the older B-mode is not supported, but throughput is improved.
• (Run Network Diagnostics tool. Execute the following statement at the command prompt: NETSH DIAG GUI. Check all scanning options and start. This should work in Vista when the command prompt is started with admin rights, but I couldn't get it to work)
• (NIC settings. If supported, try putting the adapter in full-duplex mode, or if it is already, in half-duplex mode. Experiment what works best. I cannot do this myself and wonder if this is even possible for a wireless adapter?)
• (Run netdiag tool. Unfortunately, this tool is only availabe in Windows XP as part of "Microsoft Windows XP Support Tools". So, only for those using Windows XP, execute the following statement at the command prompt:
  NETDIAG /TEST:WINSOCK /V. Use the outcome of the test for further analysis.)

Links: http://ask-leo.com/what_is_limited_connectivity_and_how_do_i_fix_it.html