Tag: 12c

Optimizer curiosity in 12.1.0.2

For almost as long as I can remember, the optimizer has had a nifty little trick when you (in effect) try to combine two different usage models within a single SQL.ย  To explain that, I’m referring to the common scenario of: “If a bind…

temporary undo in 12c

This feature seems a no-brainer once youโ€™re on 12c. After all, why would you want your global temporary tables to be hammering away at your redo logs. With that in mind, my initial tinkering with the feature had me getting ready for a โ€œblog…

In-memory – can you REALLY drop those indexes ?

Its not a hard sell to convince anyone that a whopping huge great chunk of compressed column-based memory is going to assist with those analytic-style queries. The In-memory option seems a natural fit, especially for those people where the bottleneck in their infrastructure is…

GROUP BY – wrong results in 12.1.0.2

I encountered this during some testing – the optimizer can be a little overzealous when it encounters nested group-by’s SQL> create table T ( ts number not null, c char(10)); Table created. SQL> insert into T 2 select 100+dbms_random.value(1,50),’x’ 3 from ( select 1…

truncated ddl in 12c (and 11.2.0.4)

Just curious if anyone else is seeing this behaviour.. SQL> set long 500000 SQL> @pt “select * from v$sql where sql_id = ‘1km492z723vpu'” SQL_TEXT : alter table scott.emp SQL_FULLTEXT : alter table scott.emp SQL_ID : 1km492z723vpu … Suffice to say, there’s a lot more…

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…

12c – Nested tables vs Associative arrays

This was going to the be the immediate follow up to my previous post, but 12.1.0.2 came out and I got all excited about that and forgot to post this one ๐Ÿ™‚ Anyway, the previous post showed how easy it is to convert between…

12.1.0.2 security grrr…

One of my favourite security “tricks” used to be the following: SQL> [create|alter] user MY_USER identified by values ‘impossible’; Looks odd, but by setting the encrypted value of someone’s password to something that it is impossible to encrypt to, means you’ll never be able…

12c nasty with remote query optimization

We have a fairly common query process, where we run a MERGE command to compare a remote table to a local copy of it, as “poor mans” Golden Gate to bring that table up to date on a regular basis. [Editors note: Writing MERGE’s…

Upgrade to 12c … credentials

We did a “real” upgrade to 12c this weekend, where “real” means a production system, as opposed to my laptop, a play VM etc etc ๐Ÿ™‚ It all went relatively smoothly except for one interesting thing, that I can’t 100% say was caused by…

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 ! ๐Ÿ™‚…

views in 12c

Observed an interesting idiosyncracy in the creation of views in 12c (this is in a pluggable database, but not confirmed whether this is related or not). This database was upgraded from 11.2 without incident…until we came to replace one of the existing views. SQL>…