Tag: dba

A simple 12c query with a cool result …

Its not immediately obvious the significance of this query…but trust me…you’ll love it 🙂 SQL> select table_name, column_name from dba_tab_cols 2 where column_name like ‘%\_VC’ escape ‘\’ 3 and owner = ‘SYS’ 4 order by 1,2; TABLE_NAME COLUMN_NAME —————————— ———————- ALL_CONSTRAINTS SEARCH_CONDITION_VC ALL_VIEWS TEXT_VC…

No Hakan factor for IOT

Sadly there seems to be no concept of the Hakan factor for an IOT. I have an application which merges into an IOT, the merge incrementally populating a swag of initially null columns, hence growing the rows in size. Some simple benchmarking shows the…

An accusatory error message

I found this hilarious SQL> startup ORACLE instance started. Total System Global Area 1469792256 bytes Fixed Size 2402776 bytes Variable Size 536872488 bytes Database Buffers 922746880 bytes Redo Buffers 7770112 bytes Database mounted. ORA-19821: an intentionally corrupt log file was found Really ? I…

Pluggable database and restricted sessions

Once you get into pluggable database territory, you might need to check your usage of “alter system enable restricted session”, because unless you’ve patched, there’s a little bug which lets you enable restricted session, but wont let you get out of it ! 🙂…

Cloning databases

My friend Kyle wrote a blog piece a while back http://www.kylehailey.com/what-delphix-does-in-1-minute-22-seconds/ Now, not to criticise Delphix (in fact,the opposite – its a very very cool product, and you should read some of Kyle’s great blog content on it), but if you have not got…

Once you’ve done your I/O…there’s still more to do !

The world is obsessed with I/O nowadays…. This is understandable – we’re in the middle of a pioneering period for I/O – flash, SSD, MLC, SLC, with ever more sophisticated transport mechanisms – infiniband, and the like. But don’t forget, that once you get…

Compressed partitions are not compressed tables

So…you have got a big table and you’re lucky enough to have a partitioning license. So far, so good. Then you read about using compression to make your older, static data faster and smaller and you thought … “Cool!” Let’s see that in action…

How big will my index be ?

You’ve got a big table…you want to add an index. Will you have enough space ? Well, its not perfect but you can a reasonable approximation using DBMS_SPACE SQL> drop table T purge; Table dropped. SQL> create table T nologging as 2 select d.*…

12c invisible columns

As several bloggers have already pointed out, column order changes when you start playing with invisible columns, for example SQL> create table T ( c1 int, c2 int, c3 int ); Table created. SQL> desc T Name Null? Type —————————————– ——– ————— C1 NUMBER(38)…

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…

Feeling raw about raw…

Its been well documented for a while that Oracle were not going to allow raw devices in 12….but this still seems brutal. 8.1.12 Desupport for Raw Storage Devices Starting with Oracle Database 12c, block file storage on raw devices is not supported. You must…

can you lock part of a table ?

I should stress that this is all well covered in the documentation, but it was something I stumbled upon just recently. Its common knowledge that you can lock a table: SQL> create table T 2 ( x int ) 3 partition by range (…