Create user

CREATE USER TEST_SUH IDENTIFIED BY whoami
DEFAULT TABLESPACE users_tbs
TEMPORARY TABLESPACE temp
QUOTA 100M ON users_tbs
QUOTA UNLIMITED ON data_tbs;

Create tablespace: Temporary Tablespace

CREATE TABLESPACE temp_tbs TEMPFILE '/opt/oracle/mydbs/data/mydbs_temp_tbs_01.tmp' SIZE 100m;

Create tablespace: Undo Tablespace

CREATE TABLESPACE undo_tbs TEMPFILE '/opt/oracle/mydbs/data/mydbs_undo_tbs_01.tmp' SIZE 1g RETENTION GUARANTEE;

Create tablespace: Permanent Tablespace

CREATE TABLESPACE data_tbs DATAFILE '/opt/oracle/mydbs/data/mydbs_data_tbs_01.dbf' SIZE 100m;
CREATE TABLESPACE data_tbs DATAFILE '/opt/oracle/mydbs/data/mydbs_data_tbs_01.dbf' SIZE 100m FORCE LOGGING BLOCKSIZE 8k;
CREATE TABLESPACE data_tbs DATAFILE '/opt/oracle/mydbs/data/mydbs_data_tbs_01.dbf' SIZE 100m NOLOGGING DEFAULT COMPRESS EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
CREATE TABLESPACE data_tbs DATAFILE '/opt/oracle/mydbs/data/mydbs_data_tbs_01.dbf' SIZE 100m NOLOGGING
DEFAULT COMPRESS EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;
CREATE BIGFILE TABLESPACE data_tbs DATAFILE '/opt/oracle/mydbs/data/mydbs_data_tbs_01.dbf' SIZE 10G;

Create spfile

CREATE SPFILE FROM PFILE;
CREATE SPFILE='/opt/oracle/admin/mydb/pfile/spfilemybd.ora' FROM PFILE='/opt/oracle/admin/mydb/pfile/initmybd.ora';

Create profile

CREATE PROFILE development_profile
LIMIT
SESSIONS_PER_USER 2 CONNECT_TIME 100000 IDLE_TIME 100000
LOGICAL_READS_PER_SESSION 1000000
PRIVATE_SGA 10m
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LIFE_TIME 60
PASSWORD_REUSE_TIME 365
PASSWORD_REUSE_MAX 3
PASSWORD_LOCK_TIME 30
PASSWORD_GRACE_TIME 5;

Create pfile

CREATE PFILE FROM SPFILE;
CREATE PFILE='/opt/oracle/admin/mydb/pfile/initmybd.ora' FROM SPFILE='/opt/oracle/admin/mydb/pfile/spfilemybd.ora';

Create/Delete database link

CREATE:
CREATE DATABASE LINK my_db_link CONNECT TO current_user USING 'my_db';
CREATE PUBLIC DATABASE LINK my_db_link CONNECT TO remote_user IDENTIFIED BY psicorp
USING 'my_db';
DROP:
DROP DATABASE LINK my_db_link;

DROP PUBLIC DATABASE LINK my_db_link;

Create directory

CREATE OR REPLACE DIRECTORY mydir AS '/opt/oracle/admin/directories/mydir';

Create database

CREATE DATABASE prodb
MAXINSTANCES 1 MAXLOGHISTORY 1
MAXLOGFILES 5 MAXLOGMEMBERS 3
MAXDATAFILES 100
DATAFILE 'C:\oracle\ora92010\prodb\system01.dbf'
SIZE 250M REUSE AUTOEXTEND ON NEXT 10240K
MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL DEFAULT
TEMPORARY TABLESPACE TEMP
TEMPFILE 'C:\oracle\ora92010\prodb\temp01.dbf'
SIZE 40M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
SYSAUX TABLESPACE
DATAFILE 'C:\oracle\ora92010\prodb\sysauxtbs01.dbf'
SIZE 300M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
UNDO TABLESPACE "UNDOTBS1"
DATAFILE 'C:\oracle\ora92010\prodb\undotbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
CHARACTER SET WE8MSWIN1252
NATIONAL CHARACTER SET AL16UTF16
LOGFILE
GROUP 1 ('C:\oracle\ora92010\prodb\redo01.log') SIZE 102400K,
GROUP 2 ('C:\oracle\ora92010\prodb\redo02.log') SIZE 102400K,
GROUP 3 ('C:\oracle\ora92010\prodb\redo03.log') SIZE 102400K;

Create control file

CREATE CONTROLFILE REUSE DATABASE "mydb" 
NORESETLOGS NOARCHIVELOG
MAXLOGFILES 32 MAXLOGMEMBERS 3
MAXDATAFILES 200 MAXINSTANCES 1
MAXLOGHISTORY 1000
LOGFILE
GROUP 1 ('/ora01/oracle/mydb/mydb_redo1a.rdo',
              '/ora02/oracle/mydb/mydb_redo1b.rdo') SIZE 500K,
GROUP 2 ('/ora01/oracle/mydb/mydb_redo2a.rdo',
              '/ora01/oracle/mydb/mydb_redo2b.rdo') SIZE 500K
DATAFILE
'/ora01/oracle/mydb/mydb_system_01.dbf ',
'/ora01/oracle/mydb/mydb_users_01.dbf ',
'/ora01/oracle/mydb/mydb_undo_01.dbf ',
'/ora01/oracle/mydb/mydb_sysaux_01.dbf ',
'/ora01/oracle/mydb/mydb_alldata_01.dbf ';

Alter user: Password and Account Management

ALTER USER olduser PASSWORD EXPIRE;
ALTER USER olduser ACCOUNT LOCK;
ALTER USER olduser ACCOUNT UNLOCK;

Alter trigger

ALTER TRIGGER tr_my_trigger DISABLE;
ALTER TRIGGER tr_my_trigger ENABLE;
ALTER TRIGGER tr_my_trigger RENAME TO tr_new_my_trigger;
ALTER TRIGGER tr_my_trigger COMPILE;

Alter user: Change Password

ALTER USER olduser IDENTIFIED BY newpassword;
ALTER USER olduser IDENTIFIED EXTERNALLY;

Alter tablespace: Tablespace Management

ALTER TABLESPACE my_data_tbs DEFAULT STORAGE (INITIAL 100m NEXT 100m FREELISTS 3);
ALTER TABLESPACE my_data_tbs MINIMUM EXTENT 500k;
ALTER TABLESPACE my_data_tbs RESIZE 100m;
ALTER TABLESPACE my_data_tbs COALESCE;
ALTER TABLESPACE my_data_tbs OFFLINE;
ALTER TABLESPACE my_data_tbs ONLINE;
ALTER TABLESPACE mytbs READ ONLY;
ALTER TABLESPACE mytbs READ WRITE;
ALTER TABLESPACE mytbs FORCE LOGGING;
ALTER TABLESPACE mytbs NOLOGGING;
ALTER TABLESPACE mytbs FLASHBACK ON;
ALTER TABLESPACE mytbs FLASHBACK OFF;
ALTER TABLESPACE mytbs RETENTION GUARANTEE;
ALTER TABLESPACE mytbs RETENTION NOGUARANTEE;

Alter tablespace: Rename

ALTER TABLESPACE my_data_tbs RENAME TO my_newdata_tbs;

Alter tablespace: Data Files and Tempfiles

ALTER TABLESPACE mytbs ADD DATAFILE /ora100/oracle/mydb/mydb_mytbs_01.dbf' SIZE 100M;
ALTER TABLESPACE mytemp ADD TEMPFILE '/ora100/oracle/mydb/mydb_mytemp_01.dbf' SIZE 100M;
ALTER TABLESPACE mytemp AUTOEXTEND OFF;
ALTER TABLESPACE mytemp AUTOEXTEND ON NEXT 100m MAXSIZE 1G;

Alter tablespace: Backups

ALTER TABLESPACE my_data_tbs BEGIN BACKUP;
ALTER TABLESPACE my_data_tbs END BACKUP;

Alter system: System Management

ALTER SYSTEM CHECKPOINT GLOBAL;
ALTER SYSTEM KILL SESSION '145,334';
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM DISABLE RESTRICTED SESSION;
ALTER SYSTEM SUSPEND;
ALTER SYSTEM QUIESCE RESTRICTED;
ALTER SYSTEM UNQUIESCE;
ALTER SYSTEM RESUME;
ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SYSTEM FLUSH BUFFER_CACHE;

Alter system: Set System Parameters

ALTER SYSTEM SET db_cache_size=325M COMMENT='This change is to add more memory to the system' SCOPE=BOTH;

Alter session: Enable and Disable Parallel Operations

ALTER SESSION ENABLE PARALLEL DML PARALLEL 3;
ALTER SESSION ENABLE PARALLEL DDL;
ALTER SESSION DISABLE PARALLEL QUERY;

Alter session: Set Session Parameters

ALTER SESSION SET nls_date_format='MM/DD/YYYY HH24:MI:SS';
ALTER SESSION SET sort_area_size=10000000;
ALTER SESSION SET query_rewrite_enabled=TRUE;
ALTER SESSION SET resumable_timeout=3600;
ALTER SESSION SET skip_unusable_indexes=TRUE;
ALTER SESSION SET SQL_TRACE=TRUE;

Alter profile: Modify Limits (Resource)

ALTER PROFILE my_profile LIMIT SESSIONS_PER_CPU=10;
ALTER PROFILE my_profile LIMIT CONNECT_TIME=1000;
ALTER PROFILE my_profile LIMIT IDLE_TIME=60;
ALTER PROFILE my_profile LIMIT PRIVATE_SGA=1000000;

Alter profile: Modify Limits (Password)

ALTER PROFILE my_profile LIMIT FAILED_LOGIN_ATTEMPTS=3;
ALTER PROFILE my_profile LIMIT PASSWORD_LOCK_TIME=2/24;
ALTER PROFILE my_profile LIMIT PASSWORD_GRACE_TIME=5;
ALTER PROFILE my_profile LIMIT PASSWORD_LIFETIME=60;
ALTER PROFILE my_profile LIMIT PASSWORD_REUSE_TIME=365 PASSWORD_REUSE_MAX=3;

Alter function: Recompile a Function

ALTER FUNCTION my_function COMPILE;

Alter database: Open the Database Read-Only

ALTER DATABASE OPEN READ ONLY;

Alter database: Open the Database with resetlogs

ALTER DATABASE OPEN RESETLOGS;

Alter database: Move or Rename a Database File or Online Redo Log

NOTE - The database must be mounted to rename or move online redo logs. The database must be mounted or the data files taken offline to move database data files.
ALTER DATABASE RENAME FILE '/ora/datafile/oldfile.dbf' TO '/ora/datafile/newfile.dbf';

Alter database: Mount and Open the Database

ALTER DATABASE MOUNT;
ALTER DATABASE OPEN;

Alter database: Create a Data File

ALTER DATABASE CREATE DATAFILE '/opt/oracle/datafile/users01.dbf' AS '/opt/oracle/datafile/users01.dbf';
ALTER DATABASE CREATE DATAFILE 4 AS '/opt/oracle/datafile/users01.dbf';
ALTER DATABASE CREATE DATAFILE '/opt/oracle/datafile/users01.dbf' AS NEW;

Alter database: Logfile Commands

ALTER DATABASE ADD LOGFILE GROUP 2 ('/opt/oracle/logfiles/redo02a.rdo', '/opt/oracle/logfiles/redo02b.rdo') SIZE 300M REUSE;
ALTER DATABASE ADD LOGFILE MEMBER '/opt/oracle/logfiles/redo02c.rdo' to GROUP 2;
ALTER DATABASE ADD LOGFILE thread 3 GROUP 2 ('/opt/oracle/logfiles/redo02a.rdo', '/opt/oracle/logfiles/redo02b.rdo') SIZE 300M REUSE;
ALTER DATABASE DROP LOGFILE GROUP 3;
ALTER DATABASE DROP LOGFILE MEMBER '/opt/oracle/logfiles/redo02b.rdo';

Alter database: Control File Operations

ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS /opt/oracle/logfile_backup/backup_logfile.trc' REUSE RESETLOGS;
ALTER DATABASE BACKUP CONTROLFILE TO '/opt/oracle/logfile_backup/backup_logfile.ctl';

Alter database: Alter a Tempfile

ALTER DATABASE TEMPFILE 4 RESIZE 100M;
ALTER DATABASE TEMPFILE 4
AUTOEXTEND ON NEXT 100M MAXSIZE 1000M;
ALTER DATABASE TEMPFILE 4 DROP INCLUDING DATAFILES;
ALTER DATABASE TEMPFILE 4 OFFLINE;

Alter database: Alter a Data File

ALTER DATABASE DATAFILE 4 OFFLINE;
ALTER DATABASE DATAFILE '/opt/oracle/datafile/users01.dbf' OFFLINE;
ALTER DATABASE DATAFILE '/opt/oracle/datafile/users01.dbf' RESIZE 100m;
ALTER DATABASE DATAFILE '/opt/oracle/datafile/users01.dbf' AUTOEXTEND ON NEXT 100M MAXSIZE 1000M;
ALTER DATABASE DATAFILE 4 END BACKUP;

Database Copying/Cloning

A database cloning procedure is especially useful for the DBA who wants to give his developers a full-sized TEST and DEV instance by cloning the PROD instance into the development server areas.

This Oracle clone procedure can be use to quickly migrate a system from one UNIX server to another.  It clones the Oracle database and this Oracle cloning procedures is often the fastest way to copy a Oracle database.

STEP 1: On the old system, go into SQL*Plus, sign on as SYSDBA and issue: “alter database backup controlfile to trace”. This will put the create database syntax in the trace file directory. The trace keyword tells oracle to generate a script containing a create controlfile command and store it in the trace directory identified in the user_dump_dest parameter of the init.ora file. It will look something like this:

    STARTUP NOMOUNT

    CREATE CONTROLFILE REUSE DATABASE "OLDLSQ" NORESETLOGS

    NOARCHIVELOG

    MAXLOGFILES 16

    MAXLOGMEMBERS 2

    MAXDATAFILES 240

    MAXINSTANCES 1

    MAXLOGHISTORY 113

    LOGFILE

    GROUP 1 ('/u03/oradata/oldlsq/log1a.dbf',

    '/u03/oradata/olslsq/log1b.dbf') SIZE 30M,

    GROUP 2 ('/u04/oradata/oldlsq/log2a.dbf',

    '/u04/oradata/oldlsq/log2b.dbf') SIZE 30M

    DATAFILE

    '/u01/oradata/oldlsq/system01.dbf',

    '/u01/oradata/oldlsq/mydatabase.dbf'

    ;

    # Recovery is required if any of the datafiles are restored

    # backups, or if the last shutdown was not normal or immediate.

    RECOVER DATABASE

    # Database can now be opened normally.

    ALTER DATABASE OPEN;

 STEP 2: Shutdown the old database

 STEP 3: Copy all data files into the new directories on the new server. You may change the file names if you want, but you must edit the controlfile to reflect the new data files names on the new server.

    rcp /u01/oradata/oldlsq/* newhost:/u01/oradata/newlsq

    rcp /u01/oradata/oldlsq/* newhost:/u01/oradata/newlsq

    rcp /u03/oradata/oldlsq/* newhost:/u03/oradata/newlsq

    rcp /u04/oradata/oldlsq/* newhost:/u04/oradata/newlsq

 STEP 4: Copy and Edit the Control file – Using the output syntax from STEP 1, modify the controlfile creation script by changing the following:

 Old:

     CREATE CONTROLFILE REUSE DATABASE "OLDLSQ" NORESETLOGS

 New:

     CREATE CONTROLFILE SET DATABASE "NEWLSQ" RESETLOGS

 STEP 5: Remove the “recover database” and “alter database open” syntax

    # Recovery is required if any of the datafiles are restored

    # backups, or if the last shutdown was not normal or immediate.

    RECOVER DATABASE

    # Database can now be opened normally.

    ALTER DATABASE OPEN;

STEP 6: Re-names of the data files names that have changed.

Save as db_create_controlfile.sql.

Old:

    DATAFILE

    '/u01/oradata/oldlsq/system01.dbf',

    '/u01/oradata/oldlsq/mydatabase.dbf'

New:

    DATAFILE

    '/u01/oradata/newlsq/system01.dbf',

    '/u01/oradata/newlsq/mydatabase.dbf'

STEP 7: Create the bdump, udump and cdump directories

    cd $DBA/admin

    mkdir newlsq

    cd newlsq

    mkdir bdump

    mkdir udump

    mkdir cdump

    mkdir pfile

 STEP 8: Copy-over the old init.ora file

     rcp $DBA/admin/olslsq/pfile/*.ora newhost:/u01/oracle/admin/newlsq/pfile

 STEP 9: Start the new database

     @db_create_controlfile.sql

 STEP 10: Place the new database in archivelog mode

Database present status

SELECT SYSDATE-logon_time "Days", (SYSDATE-logon_time)*24 "Hours", status
from  sys.v_$session where  sid=1

SELECT DECODE(TRUNC(SYSDATE - LOGON_TIME), 0, NULL, TRUNC(SYSDATE - LOGON_TIME) || ' Days' || ' + ') || TO_CHAR(TO_DATE(TRUNC(MOD(SYSDATE-LOGON_TIME,1) * 86400), 'SSSSS'),'HH24:MI:SS') UPTIME FROM v$session WHERE SID = 1;

ORA-02030 - Grants on v$ views

Today I have faced an issue while giving GRANT permission on a v$ view. Received 'ORA-02030: can only select from fixed tables/views' error.

SQL> grant select on v$session to TEST_SUH;
grant select on v$session to TEST_SUH
                *
ERROR at line 1:
ORA-02030: can only select from fixed tables/views

SQL>

Solution:

Here the problem is caused because of trying to give select privilege on a synonym, Oracle v$ views are named V_$VIEWNAME and they have synonyms in format V$VIEWNAME and we can't give any privilege on a synonym.

If you want to give permission to a V$ views, Follow below way,,

SQL> grant select on v_$session to TEST_SUH;

Grant succeeded.

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

Export tables with the expdp command and a parfile

Parfile name export.par
userid=system/password
SCHEMAS=UAT_2016_XXX
DIRECTORY=FULL_BACKUPS
DUMPFILE=UAT_2016_XXX_%U.dmp
LOGFILE=UAT_2016_XXX.log
INCLUDE=TABLE:"IN('AAA','BBB','CCC','DDD','EEE','FFF')"

COMPRESSION=ALL

PARALLEL=8

Command
expdp parfile=export.par

To see more options
expdp help=y