better than CTAS

SQL> create table t1(x number primary key); Table created. SQL> desc t1 Name                    Null?    Type ———————– ——– —————- X                       NOT NULL NUMBER SQL> create table t2 as select * from t1; Table created. SQL> desc t2 Name                    Null?    Type ———————– ——– —————- X                                NUMBER The table T2 has the column X, but not the constraint (primary key / not null). […]

import into UTF8 database

A common error when you import single-byte characters (e.g. iso8859p1 or mswin1252) into multi-bytes databases (e.g. utf8) is ORA-12899: value too large for column. The root cause is the default semantics in a database being BYTE SQL> select VALUE, ISDEFAULT   from v$parameter   where NAME='nls_length_semantics' VALUE   ISDEFAULT ——- ——— BYTE    TRUE It means, one char equals one […]

Transport tablespace over db links

You do not want to export the metadata from the source database, but rather use a database link to get this. As prerequisite, you have made a set of self-contained tablespaces in read-only mode and you have copied the datafiles. SQL>  create tablespace test datafile '/u02/oradata/db01/test01.dbf' size 10m; Tablespace created. SQL> create table scott.x(x number) tablespace […]

Datapump : table like ‘FOO%’ or like ‘BAR%’

Today I tried to put two like condition in an INCLUDE clause of datapump. I have the following tables SQL> select table_name from user_tables order by 1; TABLE_NAME —————————— AAA BAR1   *** BAR2   *** BLA FOO    *** FOO1   *** GOZ and I want tables like BAR% and tables likes FOO% First try : $ expdp scott/tiger […]