On deferred segment creation and truncate

One year ago, I wrote about a side effect of deferred segment creation and drop tablespace :
on deferred segment creation

Today I discoved one more side effect :

In the good old days (I read once that you are old as soon as you start talking about the good old days) and according to the doc :
You cannot truncate the parent table of an enabled foreign key constraint. You must disable the constraint before truncating the table


SQL> alter session set deferred_segment_creation=false;

Session altered.

SQL> create table t1(x number primary key);

Table created.

SQL> create table t2(x number references t1);

Table created.

SQL> truncate table t1;
truncate table t1
               *
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys

This however does not apply if you have deferred segment creation and empty tables


SQL> alter session set deferred_segment_creation=true;

Session altered.

SQL> create table t1(x number primary key);

Table created.

SQL> create table t2(x number references t1);

Table created.

SQL> truncate table t1;

Table truncated.

Table truncated. Ok, what’s the point in truncating an empty table …

Check mount option in linux

I did not find a clean way to check the mount option in Linux.

For instance wsize=32768

On AIX, I simply type “mount” and see the mount option…

For some reasons, my Linux does not show me the complete mount options !


$ mount
precision:/nfsserver on /nfsclient type nfs (rw,bg,addr=127.0.0.2)
$ grep nfsclient /proc/mounts
precision:/nfsserver /nfsclient nfs rw,vers=3,rsize=32768,wsize=32768,hard,proto=tcp,timeo=600,retrans=2,sec=sys,addr=precision 0 0

To lookup my NFS mount was using the mount option specified by Oracle for creating a tablespace that uses NFS, I had to grep in /proc… unreal!

What does # mean in sqlplus?

The script used to be


shutdown abort

it has been replaced by


#shutdown abort
shutdown immediate

Let’s try !


SQL> #shutdown abort
ORACLE instance shut down.
SQL> shutdown immediate
ORA-01012: not logged on

sqlplus just silently ignored the # symbol and executed the first statement.

Thanks to Maxim comment, here is a new case to explain the sql prefix #


SQL> sho sqlpre
sqlprefix "#" (hex 23)
SQL> select
  2  #prompt hello world
hello world
  2  * from dual;

D
-
X

While within (or outside of) an sqlplus block, you can tell sqlplus to immediately run a sqlplus statement

The correct syntaxes to put comment are documented in Placing Comments in Scripts


SQL> remark shutdown abort
SQL> rem shutdown abort
SQL> -- shu abort
SQL> /* shutdown abort */