Learning is not a spectator sport
Connor McDonald on SQL and the Oracle Database
Tag: ddl
DDL for constraints – subtle things
The DBMS_METADATA package is very cool. I remember the days of either hand-crafting DDL statements based on queries to the data dictionary, or many a DBA will be familiar with running “imp show=y” or “imp indexfile=…” in order to then laboriously extract the DDL…
Parsing freeform data in flat files
SQL loader is a very cool utility that has existed for a long time within Oracle to load flat files into the database. However sometimes people find the control file syntax quite cryptic, and when it comes to passing very complicated structures, this can…
LOBs and tiny typos
This one caught me out – I was racing around looking for bugs, or parameter files, or hidden settings that stopped SECUREFILE lobs from being created. Here was my incredibly simple test case – create a securefile LOB, and then make sure it’s a…
Please keep your foreign keys
I came across an interesting blog post the other day about whether databases should be (declaratively) enforcing the foreign key relationships between tables. The blog post discussed the issue of foreign keys being temporarily disabled to perform data loading, and then encountering the problem…
Better to be safe than sorry…
I’ve always been worried about taking a script that is fine to run in my non-production environments (in particular a DROP script) and accidentally running it in a Production environment, shortly followed by the typing up of a fresh resume to look for a…
DDL triggers – interesting results
This question came in on AskTom, yielding a very interesting result when it comes to DDL triggers. To set the scene, I’ll first create a table called T which is just a copy of SCOTT.EMP SQL> create table scott.t as select * from scott.emp;…
Duplicate constraints are impossible right ?
Here’s a very simple example of a table called PARENT being a (surprise surprise) parent in a referential integrity relationship to a (drum roll for my choice of name) CHILD table SQL> create table parent ( p int, constraint PAR_PK primary key (p) );…
12c ddl logging
In new releases, its often the little things that really catch my attention. Like this one…. I love it SQL> alter system set enable_ddl_logging = true; System altered. SQL> create table t ( x int ); Table created. SQL> drop table t purge; Table…
