Archives For September 2013

autoincrementing columns

September 27, 2013 — Leave a comment

postgresql provides it, msssql (I think) provides it, too. and finally, with release 12c, you may have auto incrementing columns in oracle:

SQL> create table t1 ( a number generated always as identity, b number );
SQL> insert into t1 (b) values (1);

1 row created.

SQL> insert into t1 (b) values (2);

1 row created.

SQL> select * from t1;

	 A	    B
---------- ----------
	 1	    1
	 2	    2

what happens in the background is quite simple: oracle creates a sequence:

SQL> select sequence_name from user_sequences;

SEQUENCE_NAME
--------------------------------------------------------------------------------
ISEQ$$_19540

user/dba/all_tables has a new column to refect this:

SQL> select HAS_IDENTITY from user_tables where table_name = 'T1';

HAS
---
YES

but it is not possible to have more than one of these:

SQL> create table t2 ( a number generated always as identity, b number generated always as identity );
create table t2 ( a number generated always as identity, b number generated always as identity )
                                                                                               *
ERROR at line 1:
ORA-30669: table can have only one identity column

once the table is dropped, the sequence is gone, too:

SQL> drop table t1;

Table dropped.

SQL> select sequence_name from user_sequences;

no rows selected

SQL> 
Advertisements

moving data files online

September 18, 2013 — Leave a comment

oracle 12c introduced a nice feature: move data files online:

SQL*Plus: Release 12.1.0.1.0 Production on Fri Jun 28 16:48:29 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> create tablespace tbs1 datafile '/oradata/dbs300/dbf/tbs1_01.dbf' size 10m;

Tablespace created.

SQL> alter database move datafile '/oradata/dbs300/dbf/tbs1_01.dbf' to '/oradata/dbs300/dbf/tbs1_02.dbf';

Database altered.

SQL> 

the trace file generated gives some more details about the move operation:

Moving datafile /oradata/dbs300/dbf/tbs1_01.dbf (16) to /oradata/dbs300/dbf/tbs1_02.dbf
The secondary file /oradata/dbs300/dbf/tbs1_02.dbf is created
Blocks copied for file /oradata/dbs300/dbf/tbs1_02.dbf
Move operation committed for file /oradata/dbs300/dbf/tbs1_02.dbf
Move operation completed for file /oradata/dbs300/dbf/tbs1_02.dbf
Moving datafile /oradata/dbs300/dbf/tbs1_01.dbf (16) to /oradata/dbs300/dbf/tbs1_02.dbf

*** 2013-06-28 17:28:26.437
The secondary file /oradata/dbs300/dbf/tbs1_02.dbf is created

The number in brackets (16) is the file id:

SQL> select FILE_ID from dba_data_files where file_name = '/oradata/dbs300/dbf/tbs1_02.dbf';

   FILE_ID
----------
	16

SQL> 

on how to confuse people

September 10, 2013 — Leave a comment

wow: three releases, three different file namings:

em12 donwload page

in note 1169017.1 oracle announced that the setting of “similar” for the cursor_sharing parameter will be deprecated as of 12c: “The ability to set this will be removed in version 12 of the Oracle Database (the settings of EXACT and FORCE will remain available). Instead, we recommend the use of Adaptive Cursor Sharing in 11g.”

the current documentation is up to date and “similar” is not mentioned anymore, but:

SQL*Plus: Release 12.1.0.1.0 Production on Fri Jun 28 15:33:34 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> show parameter sharing

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing			     string	 EXACT
SQL> alter system set cursor_sharing='similar';

System altered.

SQL> show parameter sharing

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing			     string	 similar
SQL> 

so, the ability to set this is still there …

… just came across a nice option of tar while reading the man pages on how to exlude hidden files from being tared. there is another option “–exclude-vcs”:

tar --exclude-vcs -cvf something.tar somethingtotar

good to know.

this at least works with subversion. did not try any other vcs. btw, my version of tar is:

tar --version
tar (GNU tar) 1.26
Copyright (C) 2011 Free Software Foundation, Inc.