Friday, October 14, 2011

Adding Software Oracle Linux 5

I downloaded the one of the Oracle Developer Days VM and was trying to install subversion via the Add/Remove Software dialog (which is basically a GUI for yum). It turns out the VM you download points to Oracles internal yum servers. To fix this follow these steps (as root, the default password is oracle):
# cd /etc/yum.repos.d
# mv ULN-Base.repo to ULN-Base.repo.old
# wget http://public-yum.oracle.com/public-yum-el5.repo

Open the public-yum-el5.repo file in a text-editor and change the enabled flag for the first repository to enabled=1

Thursday, August 4, 2011

Foreign Keys with missing Indexes

The following script will show any Foreign Keys that do not have a corresponding Index:

select fk.owner, fk.table_name, fk.constraint_name, fkc.column_name
from all_constraints fk
, all_cons_columns fkc
where fk.owner = :owner
and fk.constraint_type = 'R'
and not exists ( select null
from all_indexes ind
where ind.index_name = fk.constraint_name
and ind.owner = fk.owner )
and fkc.owner = fk.owner
and fkc.table_name = fk.table_name
and fkc.constraint_name = fk.constraint_name
and not exists ( select null
from all_constraints pk
, all_cons_columns pkc
where pk.constraint_type = 'P'
and pk.owner = fk.owner
and pk.table_name = fk.table_name
and pkc.owner = pk.owner
and pkc.table_name = pk.table_name
and pkc.constraint_name = pk.constraint_name
and fkc.column_name = pkc.column_name )
order by owner, table_name, constraint_name;

Thursday, June 16, 2011

CTAS (Create Table As Select)and NOT NULL Constraints

Stumbled across YAOF (Yet Another Oracle Feature) today; When performing a CTAS (Create Table As Select) NOT NULL Constraints are usually created, for example:
CREATE TABLE ctas_test
( id NUMBER NOT NULL
, name VARCHAR2(30) NULL );

CREATE TABLE ctas_test_copy AS
SELECT * FROM ctas_test;

SELECT table_name, column_name, nullable
FROM all_tab_columns
WHERE table_name LIKE 'CTAS_TEST%'
ORDER BY table_name, column_name;

As you can see the Not Null Constraints have been successfully replicated to the CTAS_TEST_COPY table. However if I now execute:

CREATE TABLE ctas_test_minus AS
SELECT * FROM ctas_test
MINUS
SELECT * FROM ctas_test;

SELECT table_name, column_name, nullable
FROM all_tab_columns
WHERE table_name LIKE 'CTAS_TEST%'
ORDER BY table_name, column_name;

The Not Null Constraints are missing from the CTAS_TEST_MINUS table.