Delete vs Truncate

Delete Command:

• It is DML command. 
• Command is for deleting the rows from the table.
• Can delete selective rows from the table using where clause.
• All rows can be deleted if no where clause is specified.
• It needs a commit or rollback command to complete the transaction or make it as permanent change.
• Delete will generate redo log entries.
• Delete command use the Undo tablespace to rollback the transaction.
• Delete operation is slow compared to truncate command as it generates redo log entries.
• Delete operation fires the delete triggers on that particular table.
• You can grant delete table privilege to a user.
• Delete never de-allocate the space. It needs a table reorganization to claim the free space.
• Delete doesn’t reset the high watermark.

Truncate Command:

• Truncate is DDL command. Obviously implicit commit is followed by truncate.
• Truncate command removes the entire rows from the table.
• Truncate command cannot be used for selective deletes.
• There is no rollback option with truncate command.
• There will not be any undo generation so this will not use undo tablespace.
• Truncate does not generate redo log entries.
• Truncate is faster compared to delete command.
• Underling triggers will not get fired for truncate command.
• You cannot grant truncate table privilege to a user.
• Truncate de-allocate the space.
• Truncate makes unusable index usable again.
• You cannot flashback truncate command.
• Truncate reset the high watermark.

Monthly database growth

select to_char(creation_time, 'YYYY Month') "Month",
sum(bytes)/1024/1024/1024 "Growth in GB"
from sys.v$datafile
where creation_time > SYSDATE-365*3 /* 3 years growth details */   
group by to_char(creation_time, 'YYYY Month') ORDER BY to_char(creation_time, 'YYYY Month')                      

ORA-29913: error in executing ODCIEXTTABLEOPEN callout;ORA-29400;KUP-00554;KUP-01005

Drop the table (if exists)
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE EXT_TEST PURGE';
EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF;
END;
Verify this does not already exist before running this
Create the "Directory" Object
BEGIN
CREATE OR REPLACE DIRECTORY FULL_SCHEMA_BACKUPS as '/mnt/dbbkup/Databkup/FULL_SCHEMA_BACKUPS'
END;
grant read,write on directory FULL_SCHEMA_BACKUPS to TEST_SUH;
Create the "External" table

SQL> CREATE TABLE EXT_TEST

  2  (
  3     TEST_Client VARCHAR2(30 BYTE)
  4     ,REQUEST_ID VARCHAR2(20 BYTE)
  5     ,Measure VARCHAR2(200 BYTE)
  6     ,FirstDate VARCHAR2(25 BYTE)
  7     ,LastDate VARCHAR2(25 BYTE)
  8     ,CHANGED_FIELD VARCHAR2(10 BYTE)
  9     ,OLD_VALUE VARCHAR2(100 BYTE)
 10     ,NEW_VALUE VARCHAR2(100 BYTE)
 11  )
 12  ORGANIZATION EXTERNAL
 13  (
 14    TYPE ORACLE_LOADER
 15    DEFAULT DIRECTORY FULL_SCHEMA_BACKUPS
   16  ACCESS PARAMETERS
  17   (
 18     SKIP 1
 19      FIELDS TERMINATED BY '|'
 20      RECORDS DELIMITED BY '\r\n'
 21      MISSING FIELD VALUES ARE NULL
  22     NOBADFILE
   23    NODISCARDFILE
   24    NOLOGFILE
 25    )
 26    LOCATION
  27   (
 28      'EXT_TEST.txt'
  ) 29
) 30
 31  REJECT LIMIT 0;

Table created.

SQL> select * from EXT_TEST;
select * from EXT_TEST
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-00554: error encountered while parsing access parameters
KUP-01005: syntax error: found "skip": expecting one of: "column, fields,
records"
KUP-01007: at line 1 column 1

Replaced my code as below and it worked. I am able to query from EXT_TEST table.

CREATE TABLE EXT_TEST
(
                HEDIS_Client VARCHAR2(30 BYTE)
                ,REQUEST_ID VARCHAR2(20 BYTE)
                ,Measure VARCHAR2(200 BYTE)
                ,FirstDate VARCHAR2(25 BYTE)
                ,LastDate VARCHAR2(25 BYTE)
                ,CHANGED_FIELD VARCHAR2(10 BYTE)
                ,OLD_VALUE VARCHAR2(100 BYTE)
                ,NEW_VALUE VARCHAR2(100 BYTE)
)
ORGANIZATION EXTERNAL 
(
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY FULL_SCHEMA_BACKUPS
  ACCESS PARAMETERS
  (
    RECORDS DELIMITED BY NEWLINE
    LOGFILE FULL_SCHEMA_BACKUPS:'EXT_TEST.log'
    BADFILE FULL_SCHEMA_BACKUPS:'EXT_TEST.bad' 
    SKIP 1
    FIELDS TERMINATED BY '|'
    MISSING FIELD VALUES ARE NULL
          )
  LOCATION
  (
    'EXT_TEST.txt'
  )
)

REJECT LIMIT 0;

and removed 
NOBADFILE
NODISCARDFILE
NOLOGFILE

Data Pump impdp/expdp : Extract DDL and DML from dump file using SQLFILE option

Using data pump IMPDP utility we can generate SQL or DDL/DML from the dump file using SQLFILE option. When you execute IMPDP with SQLFILE option it won’t import the data into the actual tables or into the schema. Suppose if you wanted to generate some particular DDL's from the database you can use this option. Please find the example below with all syntax's.

PARFILE

userid=system/pwd

directory=DATAPUMP

dumpfile=TEST.dmp

logfile=TEST.log

SCHEMAS=TEST

$ expdp parfile=TEST.par

;;;

Export: Release 11.2.0.3.0 - Production on Tue Dec 15 16:28:57 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

;;;

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SYSTEM"."SYS_EXPORT_SCHEMA_02":  system/******** parfile=TEST.par 

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 4.621 GB

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/COMMENT

Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC

Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION

Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE

Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC

Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION

Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/VIEW/VIEW

Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/TRIGGER

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

. . exported "TEST"."TEST_AAA"               153.7 MB 1644172 rows

. . exported "TEST"."TEST_BBB"               142.0 MB 1571767 rows

. . exported "TEST"."TEST_CCC"               116.7 MB 1107338 rows

. . exported "TEST"."TEST_DDD"               118.2 MB 1459331 rows

. . exported "TEST"."TEST_EEE"               75.05 MB  771091 rows

. . exported "TEST"."TEST_FFF"               50.99 MB  487267 rows

. . exported "TEST"."TEST_GGG"               51.08 MB  616332 rows

. . exported "TEST"."TEST_HHH"               49.66 MB  591722 rows

. . exported "TEST"."TEST_III"               45.65 MB  514947 rows

. . exported "TEST"."TEST_JJJ"               43.33 MB  577712 rows

. . exported "TEST"."TEST_KKK"               41.89 MB  563463 rows

. . exported "TEST"."TEST_LLL"               40.29 MB  498193 rows

. . exported "TEST"."TEST_MMM"               40.73 MB  470865 rows

. . exported "TEST"."TEST_NNN"               39.70 MB  428088 rows

. . exported "TEST"."TEST_OOO" 32.45 MB  313492 rows

. . exported "TEST"."TEST_PPP"               35.83 MB  450797 rows

. . exported "TEST"."TEST_QQQ"               33.22 MB  438805 rows

. . exported "TEST"."TEST_RRR"               32.91 MB  427978 rows

. . exported "TEST"."TEST_SSS"               32.46 MB  352338 rows

. . exported "TEST"."TEST_TTT"               31.09 MB  372510 rows

Master table "SYSTEM"."SYS_EXPORT_SCHEMA_02" successfully loaded/unloaded

******************************************************************************

Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_02 is:

  /media/mnt05/DATAPUMP/TEST.dmp

Job "SYSTEM"."SYS_EXPORT_SCHEMA_02" successfully completed at 16:36:46

Do the import with IMPDP utility using SQLFILE option.

$ impdp system/pwd dumpfile=TEST.dmp directory=DATAPUMP sqlfile=DDL.sql logfile=DDL.log

;;;

Import: Release 11.2.0.3.0 - Production on Thu Dec 17 15:57:49 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

;;;

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Master table "SYSTEM"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded

Starting "SYSTEM"."SYS_SQL_FILE_FULL_01":  system/******** dumpfile=TEST.dmp directory=DATAPUMP sqlfile=DDL.sql logfile=DDL.log

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/COMMENT

Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC

Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION

Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE

Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC

Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION

Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/VIEW/VIEW

Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/TRIGGER

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Job "SYSTEM"."SYS_SQL_FILE_FULL_01" successfully completed at 15:58:28

The output of the DDL.sql file would be 

-- CONNECT SYSTEM

ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';

ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';

ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';

ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';

ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';

ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';

-- new object type path: SCHEMA_EXPORT/USER

 CREATE USER "TEST" IDENTIFIED BY VALUES 'S:793F36205EF4B329188DDEE462889143B8A321D748374746A322B82EAE0F;1ACC9700A60AE94C'

      DEFAULT TABLESPACE "APP_DATA01"

      TEMPORARY TABLESPACE "TEMP002";

-- new object type path: SCHEMA_EXPORT/SYSTEM_GRANT

GRANT CREATE JOB TO "TEST";

GRANT CREATE PROCEDURE TO "TEST";

GRANT CREATE SEQUENCE TO "TEST";

GRANT CREATE VIEW TO "TEST";

GRANT CREATE TABLE TO "TEST";

GRANT UNLIMITED TABLESPACE TO "TEST";

GRANT CREATE SESSION TO "TEST";

CREATE TABLE "TEST"."TEST_AAA"

   (    "ID" NUMBER,

        "MEM_NBR" VARCHAR2(75 BYTE),

        "MEMBER_DATE" DATE,

        "COMPLIANCE" VARCHAR2(6 BYTE),

        "RNK" NUMBER,

        "RVAL" CHAR(12 BYTE)

   ) SEGMENT CREATION DEFERRED

  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

 NOCOMPRESS NOLOGGING

  TABLESPACE "APP_DATA01" ;

CREATE TABLE "TEST"."TEST_CCC"

   (    "ID" NUMBER,

        "MEM_NBR" VARCHAR2(75 BYTE),

        "MEMBER_DATE" DATE,

        "COMPLIANCE" VARCHAR2(6 BYTE),

        "RNK" NUMBER,

        "RVAL" CHAR(7 BYTE)

   ) SEGMENT CREATION DEFERRED

  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

 NOCOMPRESS NOLOGGING

  TABLESPACE "APP_DATA01" ;

CREATE TABLE "TEST"."TEST_BBB"

   (    "ID" NUMBER,

        "MEM_NBR" VARCHAR2(75 BYTE),

        "MEMBER_DATE" DATE,

        "COMPLIANCE" VARCHAR2(6 BYTE),

        "RNK" NUMBER,

        "RVAL" CHAR(7 BYTE)

   ) SEGMENT CREATION DEFERRED

  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

 NOCOMPRESS NOLOGGING

  TABLESPACE "APP_DATA01" ;

How to find out Linux System is 32-bit or 64-bit

$ arch
x86_64

$ getconf LONG_BIT

64

$ file /sbin/init

/sbin/init: ELF 64-bit LSB shared object, x86-64, version 1 (SYSV), dynamically linked (uses shared libs), for GNU/Linux 2.6.18, stripped

CRONTAB

# 1. Entry: Minute when the process will be started [0-60]
# 2. Entry: Hour when the process will be started [0-23]
# 3. Entry: Day of the month when the process will be started [1-28/29/30/31]
# 4. Entry: Month of the year when the process will be started [1-12]
# 5. Entry: Weekday when the process will be started [0-6] [0 is Sunday]

# Minute   Hour   Day of Month       Month          Day of Week        Command    
# (0-59)  (0-23)     (1-31)    (1-12 or Jan-Dec)  (0-6 or Sun-Sat)   
    0           2          12                   *                       *              /usr/bin/find


Use the command "crontab -e" to edit your crontab file.

Use the command "crontab -l" to list your crontab file.

IMPDP - import specific table from one schema to another schema

Source schema - UAT_2014_1
Target schema - TEST_SUH
Table to import - USERS

impdp system/password DIRECTORY=SAMPLE_BACKUPS dumpfile=UAT_2014_1_04092014_16.dmp logfile=TEST_SUH_IMPDP.log TABLES=UAT_2014_1.USERS REMAP_SCHEMA=UAT_2014_1:TEST_SUH

To more options

impdp help=y