Find the Tablespace usage/freespace information

Tablespace size

Scripts for disabling and enabling constraints

Running the following statements in sqlplus will create two sql files with statements for disabling and enabling FK constraints in current schema.

set echo on
set verify on
set feedback off
set heading off
set pagesize 0
set linesize 1000
spool disable_constraints_script.sql
select 'alter table '||table_name||' disable constraint '||constraint_name||';' from user_constraints where constraint_type in ('R');

spool enable_constraints_script.sql
select 'alter table '||table_name||' modify constraint '||constraint_name||' enable novalidate;' from user_constraints where constraint_type in ('R');

Tips on day-to-day Communications & use of language for the same.

Useful tips on day-to-day grammer in communication / correspondence

Useful tips on day-to-day grammer in communication / correspondence

Counting rows in Partition with millions of rows

If the no. of rows in a partitioned table is in millions, use the following to minimize the time in returning the no. of rows

SQL> set timing on

SQL> select /*+ full(m) parallel(m,16) */ count(*) from MY_TABLE partition (MY_TABLE_PR108) M;
COUNT(*)---------- 209204953
Elapsed: 00:06:07.02

SQL> alter table MY_TABLE truncate partition MY_TABLE_PR108;
Table truncated.
Elapsed: 00:00:12.20

Parameter file for exp & imp

A ready reference to exp & imp parfile

$exp parfile=ExportParfile.par
$imp parfile=ImportParfile.par


ExportParfile.par
OWNER=VSTREFWORK
FEEDBACK=10000
COMPRESS=N
STATISTICS=NONE
RECORDLENGTH=65535
BUFFER=10248000
USERID=system/password
LOG=myexp.log
FILE=myexp.dmp


DIRECT=Y speeds up export for FULL=Y Cannot be used with QUERY


ImportParfile.par
FROMUSER=VSTREFWORK
TOUSER=QATREF18
file=my_exp_file.dmp
log=my_import_log.log
grants=n
commit=y
buffer=10240000
ignore=y
statistics=none
USERID=system/password

Use SHOW=Y for running a mock imp/exp i.e. only create a log file without actually importing / exporting.

Use help for further details
exp HELP=Y
imp HELP=Y