Oracle jobs

Collecting some of the important view for Oracle jobs:

To check for the job, its schedule & others details:

select job,LAST_DATE,NEXT_DATE, BROKEN,what from dba_jobs;

To show details on job run:

select log_date, job_name, status, req_start_date, actual_start_date, run_duration from dba_scheduler_job_run_details;

To show job history:

select log_date , job_name, status from dba_scheduler_job_log;

To show running jobs:

select job_name, session_id, running_instance, elapsed_time, cpu_used from dba_scheduler_running_jobs;

show all schedules:

select schedule_name, schedule_type, start_date, repeat_interval from dba_scheduler_schedules;

show all jobs and their attributes:

select * from dba_scheduler_jobs;

show all program-arguments:

select * from dba_scheduler_program_args;

Oracle timezone: dbtimezone & sessiontimezone

Hi All,  I am trying to understand what is difference between dbtimezone & sessiontimezone.  

To start with Oracle has four datetime data types :

DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE (TSTZ), TIMESTAMP WITH LOCAL TIME ZONE (TSLTZ).

Also we need to know about V$TIMEZONE_NAMES. 

V$TIMEZONE_NAMES has two columns:

TZNAME :  The time zone region (for example, US/Pacific) 

TZABBREV: The corresponding daylight abbreviation (for example, PDT)

So, DBTIMEZONE is a function which returns the current value of Database Time Zone.  It can be checked as below query: 

SQL> select  dbtimezone from dual;

DBTIME

------

+00:00

Thus in need of day light saving, we must set default_timezone attribute to a regions name instead of absolute time zone offset, in order to ensure that daylight saving adjustments are being taken into the application account. 

Time zone is set during database creation or when using CREATE DATABASE. It can be altered using ALTER DATABASE command.

SQL> ALTER DATABASE SET TIME_ZONE='America/Menominee';

Database altered.

SQL> ALTER DATABASE SET TIME_ZONE='-06:00';

Database altered.

SQL> SELECT TZ_OFFSET('America/Menominee') FROM DUAL;

TZ_OFFS

--------

-06:00

Also I need to add when we set TIME ZONE database must be re-start.

DBTIMEZONE and SESSIONTIMEZONE are different in their operational scope. DBTIMEZONE shows the database time zone, while SESSIONTIMEZONE shows it for the session. This implies that if the time zone is altered at session level, only SESSIONTIMEZONE will change and not the DBTIMEZONE.

SQL> select sessiontimezone from dual;

SESSIONTIMEZONE

-------------------------------------------------------------

+05:30

SQL> ALTER SESSION SET TIME_ZONE = 'US/Pacific';

Session altered.

SQL> select sessiontimezone from dual;

SESSIONTIMEZONE

-------------------------------------------------------------

US/Pacific

SQL>

TNS_ADMIN effects

TNS_ADMIN effects to check listener.ora if not set properly.

pacdceblprdd1  /app/oracle/amsshome/10.2.0.4/network/admin > lsnrctl

LSNRCTL for Solaris: Version 10.2.0.4.0 - Production on 23-JAN-2012 12:11:15

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> status LISTENER_AMSS

TNS-01101: Could not find service name

LSNRCTL>

LSNRCTL> status LISTENER

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

STATUS of the LISTENER

------------------------

Alias                     LISTENER

Version                   TNSLSNR for Solaris: Version 10.2.0.4.0 - Production

Start Date                20-SEP-2011 13:14:34

Uptime                    124 days 22 hr. 57 min. 1 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /var/opt/oracle/listener.ora   -à Empty file

Listener Log File         /app/oracle/amsshome/10.2.0.4/network/log/listener.log

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=pacdceblprdd1)(PORT=1521)))

Services Summary...

Service "AMSS" has 1 instance(s).

  Instance "AMSS", status READY, has 1 handler(s) for this service...

Service "AMSS_pacdceblprdd1" has 1 instance(s).

  Instance "AMSS", status READY, has 1 handler(s) for this service...

Service "AMSS_pacdceblprdd1_XPT" has 1 instance(s).

  Instance "AMSS", status READY, has 1 handler(s) for this service...

The command completed successfully

LSNRCTL> exit

pacdceblprdd1  /app/oracle/amsshome/10.2.0.4/network/admin >

pacdceblprdd1  /app/oracle/amsshome/10.2.0.4/network/admin > echo $SHELL

/bin/bash

pacdceblprdd1  /app/oracle/amsshome/10.2.0.4/network/admin >

pacdceblprdd1  /app/oracle/amsshome/10.2.0.4/network/admin >

pacdceblprdd1  /app/oracle/amsshome/10.2.0.4/network/admin > echo $TNS_ADMIN

pacdceblprdd1  /app/oracle/amsshome/10.2.0.4/network/admin >

pacdceblprdd1  /app/oracle/amsshome/10.2.0.4/network/admin >

pacdceblprdd1  /app/oracle/amsshome/10.2.0.4/network/admin > export TNS_ADMIN=/app/oracle/amsshome/10.2.0.4/network/admin

pacdceblprdd1  /app/oracle/amsshome/10.2.0.4/network/admin > echo $TNS_ADMIN

/app/oracle/amsshome/10.2.0.4/network/admin

pacdceblprdd1  /app/oracle/amsshome/10.2.0.4/network/admin >

pacdceblprdd1  /app/oracle/amsshome/10.2.0.4/network/admin >

pacdceblprdd1  /app/oracle/amsshome/10.2.0.4/network/admin > lsnrctl

LSNRCTL for Solaris: Version 10.2.0.4.0 - Production on 23-JAN-2012 12:46:26

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> exit

pacdceblprdd1  /app/oracle/amsshome/10.2.0.4/network/admin > lsnrctl status LISTENER_AMSS

LSNRCTL for Solaris: Version 10.2.0.4.0 - Production on 23-JAN-2012 12:46:48

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=24.40.26.83)(PORT=1521)))

STATUS of the LISTENER

------------------------

Alias                     LISTENER_AMSS

Version                   TNSLSNR for Solaris: Version 10.2.0.4.0 - Production

Start Date                02-SEP-2011 20:56:59

Uptime                    142 days 15 hr. 49 min. 49 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /app/oracle/amsshome/10.2.0.4/network/admin/listener.ora  à listener.ora file from $ORACLE_HOME/network/admin directory

Listener Log File         /app/oracle/amsshome/10.2.0.4/network/log/listener_amss.log

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=24.40.26.83)(PORT=1521)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))

Services Summary...

Service "AMSS" has 1 instance(s).

  Instance "AMSS", status UNKNOWN, has 1 handler(s) for this service...

Service "PLSExtProc" has 1 instance(s).

  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully

pacdceblprdd1  /app/oracle/amsshome/10.2.0.4/network/admin > ls -lart listener.ora

-rw-r--r--   1 oracle   dba          513 Jul 16  2009 listener.ora

pacdceblprdd1  /app/oracle/amsshome/10.2.0.4/network/admin >

If TNS_ADMIN is not set, it will be search in below sequence:

1)/home/tnsnames.ora(listener.ora)

2)/etc/tnsnames.ora (listener.ora)

3) $ORACLE_HOME/network/admin/tnsmaes.ora (listener.ora)

how to install ASM in few mins (windows)

Introduction:

This document describes the steps for creating ASM instance on Windows. We need to install only oracle binaries (From 11g R2, Grid Binary need to be installed).  After binary installation we can proceed to ASM instance creation manually.

Step 1:   Install oracle binaries

Oracle binary needs to be installed. Check for asmtool.exe and localconfig.bat files available on $ORACLE_HOME/bin. These are two important files which is required for creating disk and creating CSS respectively.

 

Step 2:   Creating disks using asmtool

Create a directory and create all the disks under that folder.

C:\>mkdir asm_disks

C:\>cd asm_disks

C:\asm_disks>asmtool -create c:\asm_disks\disk1 256

C:\asm_disks>asmtool -create c:\asm_disks\disk2 256

C:\asm_disks>asmtool -create c:\asm_disks\disk3 256

Here we have created 3 disks of size 256M.

NOTE: asmtool is the GUI tool for creating the disks. When you use this, You should have already partition the disk and use those disks in ASM. The partition should be NTFS.

 

Step 3:   Configuring Oracle CSS 

CSS is installed as part of CRS in RAC environment. This would be installed on CRS_HOME in case of RAC. On NON-RAC environment, it is installed in ORACLE_HOME.

Command to check the OCSS status:

C:\asm_disks>crsctl check cssd

Failure 1 contacting Cluster Synchronization Services daemon

 

Creating the OCSS Service:

C:\asm_disks>localconfig add

Step 1:  creating new OCR repository

Successfully accumulated necessary OCR keys.

Creating OCR keys for user 'administrator', privgrp ''..

Operation successful.

Step 2:  creating new CSS service

successfully created local CSS service

successfully added CSS to home

 

Now you can see the OracleCSService created and running on Windows services.

Start->Run->services.msc

C:\asm_disks>crsctl check cssd

Cluster Synchronization Services appears healthy

 

 

Step 4:   Creating ASM init file.

Create INIT+ASM.ora  file on $ORACLE_HOME/database directory.  Use the below minimum parameters

INSTANCE_TYPE= ASM

DB_UNIQUE_NAME= +ASM

LARGE_POOL_SIZE= 8M

ASM_DISKSTRING= 'c:\asm_disks\*'

_ASM_ALLOW_ONLY_RAW_DISKS= FALSE

 

We use “_ASM_ALLOW_ONLY_RAW_DISKS” parameter so that during the process of disk discovery, the disks what we have created above will be identified.  Else you won’t see any disks from ASM.

 

Step 5:   Starting up ASM instances

Create password file the service before you start the ASM instance. 

C:\>orapwd file= C:\app\administrator\product\11.1.0\db_1\database\PWD+ASM.ora password=asm

C:\>oradim –NEW –ASMSID +ASM

Now set ORACLE_HOME & ORACLE_SID

C:\> set ORACLE_HOME= C:\app\administrator\product\11.1.0\db_1

C:\>set ORACLE_SID=+ASM

C:\>sqlplus “/as sysdba”

SQL> startup

ASM instance started

Total System Global Area  535662592 bytes

Fixed Size                  1334380 bytes

Variable Size             509162388 bytes

ASM Cache                  25165824 bytes

ORA-15110: no diskgroups mounted

SQL>

 

Now we have created ASM instance. Next we will proceed with Diskgroup creation.

 

Step 6:   Creating Diskgroup.

Create diskgroup by using the existing disks.

SQL> col path for a30

SQL> select path, header_status from v$asm_disk;

PATH                           HEADER_STATU

------------------------------ ------------

C:\ASM_DISKS\DISK3             CANDIDATE

C:\ASM_DISKS\DISK1             CANDIDATE

C:\ASM_DISKS\DISK2             CANDIDATE

SQL> create diskgroup data external redundancy disk 'c:\asm_disks\disk*';

Diskgroup created.

SQL> select name, total_mb, free_mb from v$asm_diskgroup;

NAME                           File Size (MB)    FREE_MB

------------------------------ -------------- ----------

DATA                                      256        230

 

 

Step 7:   Creating spfile and setting ASM_DISKGROUPS.

SQL> show parameter pfile

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

spfile                               string

 

We are not using spfile, Hence trying to create the same here.

SQL> create spfile from pfile;

File created.

 

Now, Startup force will bring the instance and start it again. When the database is brought up, it will use the SPFILE as it was create on $ORACLE_HOME/database.

SQL> startup force;

ASM instance started

Total System Global Area  535662592 bytes

Fixed Size                  1334380 bytes

Variable Size             509162388 bytes

ASM Cache                  25165824 bytes

ORA-15110: no diskgroups mounted

 

We get “no diskgroups mounted” error during start up because, we have not specified any values in ASM_DISKGROUPS parameter. Now the database has been started using spfile.

Set the value for asm_diskgroups in spfile and restart the instance.

SQL> alter system set asm_diskgroups=data scope=spfile;

System altered.

SQL> startup force;

ASM instance started

Total System Global Area  535662592 bytes

Fixed Size                  1334380 bytes

Variable Size             509162388 bytes

ASM Cache                  25165824 bytes

ASM diskgroups mounted

SQL> select * from v$asm_diskgroup;

 

Conclusion: Here ends creation of asm on windows. You can install this on any system for having better hands on ASM.

MAX_DUMP_FILE_SIZE

When choosing to use to parameter, be sure to use suffix (M or K). 

If you set max_dump_file_size=10000. 

This represents 10000 x block_size of ur server, e.g block_size for solaris=512bytes. HPUX=1024bytes, Windows/nt=512bytes.

If want to use value as 10M then use in below format: 

ALTER SYSTEM SET MAX_DUMP_FILE_SIZE='10M' scope=both;

Default value to this Parameter is unlimited.

Moving Datafile with Physical Dataguard Environment

On Primary :

SQL> select name,db_unique_name,database_role,switchover_status from v$database;

NAME      DB_UNIQUE_NAME                 DATABASE_ROLE    SWITCHOVER_STATUS

——— —————————— —————- ——————–

ORCL      orcl                           PRIMARY          TO STANDBY

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /home/oracle/app/oracle/flash_recovery_area/orcl/

Oldest online log sequence     524

Next log sequence to archive   526

Current log sequence           526

SQL> select database_role,switchover_status from v$database;

DATABASE_ROLE    SWITCHOVER_STATUS

—————- ——————–

PRIMARY          TO STANDBY

SQL> sho parameter STANDBY_FILE_MANAGEMENT

NAME                                 TYPE        VALUE

———————————— ———– ——————————

standby_file_management              string      AUTO

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;

System altered.

SQL> sho parameter STANDBY_FILE_MANAGEMENT

NAME                                 TYPE        VALUE

———————————— ———– ——————————

standby_file_management              string      MANUAL

On Standby :

SQL> select name,db_unique_name,database_role,switchover_status from v$database;

NAME      DB_UNIQUE_NAME                 DATABASE_ROLE    SWITCHOVER_STATUS

——— —————————— —————- ——————–

ORCL      sbyorcl                        PHYSICAL STANDBY NOT ALLOWED

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /home/oracle/app/oracle/flash_recovery_area/sbyorcl/

Oldest online log sequence     524

Next log sequence to archive   0

Current log sequence           526

SQL> select database_role,switchover_status from v$database;

DATABASE_ROLE    SWITCHOVER_STATUS

—————- ——————–

PHYSICAL STANDBY NOT ALLOWED

SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

SEQUENCE# APPLIED

———- ———

517 YES

518 YES

519 YES

520 YES

521 YES

522 YES

523 YES

524 YES

525 IN-MEMORY

9 rows selected.

SQL> sho parameter STANDBY_FILE_MANAGEMENT

NAME                                 TYPE        VALUE

———————————— ———– ——————————

standby_file_management              string      AUTO

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;

System altered.

SQL> sho parameter STANDBY_FILE_MANAGEMENT

NAME                                 TYPE        VALUE

———————————— ———– ——————————

standby_file_management              string      MANUAL

SQL>

On Primary :

SQL> select file_name from dba_data_files where tablespace_name=’EXAMPLE’;

FILE_NAME

——————————————————————————–

/home/oracle/app/oracle/oradata/orcl/example01.dbf

SQL> alter tablespace example offline;

Tablespace altered.

[oracle@dgaskmpri01 OPatch]$ mv /home/oracle/app/oracle/oradata/orcl/example01.dbf  /tmp/askm/example01_temp.dbf

[oracle@dgaskmpri01 OPatch]$ sqlplus ‘/as sysdba’

SQL*Plus: Release 11.2.0.2.0 Production on Tue Nov 22 05:47:17 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – Production

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

SQL> alter tablespace example rename datafile ‘/home/oracle/app/oracle/oradata/orcl/example01.dbf’ to ‘/tmp/askm/example01_temp.dbf’;

Tablespace altered.

SQL> alter tablespace example online;

Tablespace altered.

SQL> select file_name from dba_data_files where tablespace_name=’EXAMPLE’;

FILE_NAME

——————————————————————————–

/tmp/askm/example01_temp.dbf

SQL>

On Standby :

SQL> select ts#,name from v$tablespace where name=’EXAMPLE’;

TS# NAME

———- ——————————

6 EXAMPLE

SQL> select name from v$datafile where ts#=6;

NAME

——————————————————————————–

/home/oracle/app/oracle/oradata/sbyorcl/example01.dbf

SQL>

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SQL> shut immediate

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL>

[oracle@dgaskmsby01 askm]$ mv /home/oracle/app/oracle/oradata/sbyorcl/example01.dbf /tmp/askm/example01_temp.dbf

[oracle@dgaskmsby01 askm]$ sqlplus ‘/as sysdba’

SQL*Plus: Release 11.2.0.2.0 Production on Tue Nov 22 05:53:49 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup mount

ORACLE instance started.

Total System Global Area  456146944 bytes

Fixed Size                  1344840 bytes

Variable Size             343935672 bytes

Database Buffers          104857600 bytes

Redo Buffers                6008832 bytes

Database mounted.

SQL> alter database rename file ‘/home/oracle/app/oracle/oradata/sbyorcl/example01.dbf’ to ‘/tmp/askm/example01_temp.dbf’;

Database altered.

SQL> select name from v$datafile where ts#=6;

NAME

——————————————————————————–

/tmp/askm/example01_temp.dbf

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Database altered.

On Primary :

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

System altered.

On Standby :

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

System altered.

interrupt/stop any oracle session

Many times, we have request from development or testing team to cancel their ongoing queries. To deal with such request, we (DBA) have to find session and kill them.

Now where is the way to stop the ongoing sql query for any session. All you should have the SID and SERIAL# of the session you want to interrupt.  

SQL> conn jay/jay

Connected.

SQL>

SQL> create table test23 ( x int, constraint t_pk primary key(x) );

Table created.

SQL> insert into test23 values (1);

1 row created.

SQL> insert into test23 values (2);

1 row created.

SQL> insert into test23 values (9999999999);

1 row created.

SQL> commit;

Commit complete.

SQL> begin

  2  for i in 2 .. 999999

  3  loop

  4  delete from test23 where x = i;

  5  commit;

  6  insert into test23 values (i+1);

  7  commit;

  8  end loop;

  9  end;

 10  /

begin

*

ERROR at line 1:

ORA-01013: user requested cancel of current operation

ORA-06512: at line 6

From other terminal, I checked the sid & serial id for required session and executed the below session.

SQL> select sid,serial# from v$session where schemaname='JAY';

       SID    SERIAL#

---------- ----------

       137         11

SQL> exec sys.dbms_system.set_ev(137,11, 10237, 1, '');

PL/SQL procedure successfully completed.

Once executed, it cancel in the ongoing query. We can see ongoing pl-sql procedure is cancel with ORA-01013 error. As long as the event is set, any further statements the session attempts to execute will immediately terminate with the same error.

SQL> insert into test23 values (3);

insert into test23 values (3)

*

ERROR at line 1:

ORA-01013: user requested cancel of current operation

To deactivate the event, make the same call with the fourth parameter set to "0". The session will then be able to execute statements again.

SQL> exec sys.dbms_system.set_ev(137,11, 10237, 0, '');

PL/SQL procedure successfully completed.

SQL>

You can see I can start further operation without re-connecting.

SQL> insert into test23 values (3);

1 row created.

SQL> rollback;

Rollback complete.

SQL>

You should have sysdba priviledge to execute dbms_system or can be granted to any other oracle user. 

grant execute on  dbms_system to my_user;

Thanks,

Jay vardhan 

GET DDL

Hi All,

To get DDL for any object, we have DBMS_METADATA. Let say we have to get DDL for a table named JAY_TEST which is owned by SCOTT schema.

Query would be : select DBMS_METADATA.get_ddl('TABLE','JAY_TEST','SCOTT') txt from dual;

More of the time, new DBA’s don’t like to use this DBMS’s ..and like to use traditional EXP/IMP.  Well this can also be used to get DDL for any object. Let see how.

padwsdpr pacdcorwprdd1 /home/oracle/amddba/dba/local/bin > exp / rows=n compress=n tables=SCOTT.JAY_TEST file=exp.dmp log=exp.log   buffer=10240000

Export: Release 11.1.0.7.0 - Production on Wed Feb 15 07:06:24 2012

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

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

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

Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set

server uses WE8MSWIN1252 character set (possible charset conversion)

Note: table data (rows) will not be exported

About to export specified tables via Conventional Path ...

Current user changed to SCOTT

. . exporting table                       JAY_TEST

EXP-00091: Exporting questionable statistics.

EXP-00091: Exporting questionable statistics.

EXP-00091: Exporting questionable statistics.

Export terminated successfully with warnings.

padwsdpr pacdcorwprdd1 /home/oracle/amddba/dba/local/bin > imp / rows=n full=y  ignore=y show=y file=exp.dmp log=exp.log  buffer=10240000

Import: Release 11.1.0.7.0 - Production on Wed Feb 15 07:07:03 2012

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

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

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

Export file created by EXPORT:V11.01.00 via conventional path

import done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set

import server uses WE8MSWIN1252 character set (possible charset conversion)

. importing OPS$AMDDBA's objects into OPS$AMDDBA

. importing SCOTT's objects into SCOTT

 "ALTER SESSION SET CURRENT_SCHEMA= "SCOTT""

 "CREATE TABLE "JAY_TEST" ("EVENT_DW_ID" NUMBER NOT NULL ENABLE, "CORP_ID" NU"

 "MBER NOT NULL ENABLE, "ROYALTY_AMOUNT" NUMBER, "ROYALTY_FACTOR_ID" NUMBER N"

 "OT NULL ENABLE, "CHANNEL_ID" NUMBER NOT NULL ENABLE, "EVENT_DISTRIBUTOR_DW_"

 "ID" NUMBER NOT NULL ENABLE, "EVENT_RATING_ID" NUMBER NOT NULL ENABLE, "CORP"

 "_EVENT_SUBJECT_DW_ID" NUMBER NOT NULL ENABLE, "CORP_STUDIO_DW_ID" NUMBER NO"

 "T NULL ENABLE, "EVENT_TYPE_ID" NUMBER NOT NULL ENABLE, "EVENT_STATUS_ID" NU"

 "MBER NOT NULL ENABLE, "EVENT_DESC" VARCHAR2(20), "FIRST_ORDER_DATE" DATE NO"

 "T NULL ENABLE, "FIRST_ORDER_TIME" VARCHAR2(12), "LAST_ORDER_DATE" DATE NOT "

 "NULL ENABLE, "LAST_ORDER_TIME" VARCHAR2(12), "DATE_ID" DATE NOT NULL ENABLE"

 ", "HOUR_ID" NUMBER NOT NULL ENABLE, "START_TIME" VARCHAR2(12), "STOP_DATE" "

 "DATE NOT NULL ENABLE, "STOP_TIME" VARCHAR2(12), "FREE_MINUTES" NUMBER, "EVE"

 "NT_DISCOUNT_ID" NUMBER NOT NULL ENABLE, "EVENT_NO_ORDER_FLAG" NUMBER NOT NU"

 "LL ENABLE, "SS_EVENT_ID" NUMBER NOT NULL ENABLE, "SS_SHOW_ID" NUMBER, "SS_S"

 "TATUS" NUMBER, "EVENT_CNT" NUMBER, "EVENT_LEVEL_1_ID" NUMBER, "EVENT_EFF_DA"

 "TE" TIMESTAMP (6) NOT NULL ENABLE, "EVENT_END_DATE" TIMESTAMP (6) NOT NULL "

 "ENABLE, "DW_ADD_DATE" TIMESTAMP (6) NOT NULL ENABLE, "DW_UPDATE_DATE" TIMES"

 "TAMP (6) NOT NULL ENABLE, "BASE_EVENT" VARCHAR2(5))  PCTFREE 10 PCTUSED 40 "

 "INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FRE"

 "ELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "SGDATA" LOGGING"

 " NOCOMPRESS PARALLEL ( DEGREE DEFAULT INSTANCES DEFAULT)"

 " ALTER TABLE "JAY_TEST" MODIFY ("ROYALTY_AMOUNT" DEFAULT 0)"

 " ALTER TABLE "JAY_TEST" MODIFY ("FREE_MINUTES" DEFAULT 0)"

 " ALTER TABLE "JAY_TEST" MODIFY ("SS_SHOW_ID" DEFAULT 0)"

 " ALTER TABLE "JAY_TEST" MODIFY ("EVENT_CNT" DEFAULT 1)"

 " ALTER TABLE "JAY_TEST" MODIFY ("EVENT_LEVEL_1_ID" DEFAULT 0)"

 "GRANT DELETE ON "JAY_TEST" TO "SCOTT_READ_WRITE_ROLE""

 "GRANT INSERT ON "JAY_TEST" TO "SCOTT_READ_WRITE_ROLE""

 "GRANT SELECT ON "JAY_TEST" TO "SCOTT_READ_WRITE_ROLE""

 "GRANT UPDATE ON "JAY_TEST" TO "SCOTT_READ_WRITE_ROLE""

 "GRANT SELECT ON "JAY_TEST" TO "AMITAGA""

 "CREATE UNIQUE INDEX "JAY_TEST_PK" ON "JAY_TEST" ("EVENT_DW_ID" )  PCTFREE 1"

 "0 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 F"

 "REELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "SGDATA" LOGGI"

 "NG PARALLEL ( DEGREE 12 INSTANCES 1)"

 "CREATE INDEX "JAY_TEST_IXN1" ON "JAY_TEST" ("SS_EVENT_ID" , "CORP_ID" )  PC"

 "TFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTE"

 "NTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "SGINDE"

 "X" LOGGING"

 "ALTER SESSION SET CURRENT_SCHEMA= "SCOTT""

 "ALTER TABLE "JAY_TEST" ADD  CONSTRAINT "JAY_TEST_PK" PRIMARY KEY ("EVENT_DW"

 "_ID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 "

 "NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT"

 ") TABLESPACE "SGDATA" LOGGING ENABLE "

 "ALTER TABLE "JAY_TEST" ADD CONSTRAINT "JAY_TEST_FK8" FOREIGN KEY ("EVENT_TY"

 "PE_ID") REFERENCES "JAY_TEST_TYPE" ("EVENT_TYPE_ID") ENABLE NOVALIDATE"

 "ALTER TABLE "JAY_TEST" ADD CONSTRAINT "JAY_TEST_FK9" FOREIGN KEY ("EVENT_ST"

 "ATUS_ID") REFERENCES "JAY_TEST_STATUS" ("EVENT_STATUS_ID") ENABLE NOVALIDAT"

 "E"

 "ALTER TABLE "JAY_TEST" ADD CONSTRAINT "JAY_TEST_FK4" FOREIGN KEY ("EVENT_DI"

 "STRIBUTOR_DW_ID") REFERENCES "JAY_TEST_DISTRIBUTOR" ("EVENT_DISTRIBUTOR_DW_"

 "ID") ENABLE NOVALIDATE"

 "ALTER TABLE "JAY_TEST" ADD CONSTRAINT "JAY_TEST_FK10" FOREIGN KEY ("EVENT_D"

 "ISCOUNT_ID") REFERENCES "JAY_TEST_DISCOUNT" ("EVENT_DISCOUNT_ID") ENABLE NO"

 "VALIDATE"

 "ALTER TABLE "JAY_TEST" ADD CONSTRAINT "JAY_TEST_FK7" FOREIGN KEY ("CORP_STU"

 "DIO_DW_ID") REFERENCES "LU_CORP_STUDIO" ("CORP_STUDIO_DW_ID") ENABLE NOVALI"

 "DATE"

 "ALTER TABLE "JAY_TEST" ADD CONSTRAINT "JAY_TEST_FK6" FOREIGN KEY ("CORP_EVE"

 "NT_SUBJECT_DW_ID") REFERENCES "LU_CORP_EVENT_SUBJECT" ("CORP_EVENT_SUBJECT_"

 "DW_ID") ENABLE NOVALIDATE"

 "ALTER TABLE "JAY_TEST" ADD CONSTRAINT "JAY_TEST_FK1" FOREIGN KEY ("CORP_ID""

 ") REFERENCES "LU_CORP" ("CORP_ID") ENABLE NOVALIDATE"

 "ALTER TABLE "JAY_TEST" ADD CONSTRAINT "JAY_TEST_FK3" FOREIGN KEY ("CHANNEL_"

 "ID") REFERENCES "LU_CHANNEL" ("CHANNEL_ID") ENABLE NOVALIDATE"

 "ALTER TABLE "JAY_TEST" ENABLE CONSTRAINT "JAY_TEST_FK8""

 "ALTER TABLE "JAY_TEST" ENABLE CONSTRAINT "JAY_TEST_FK9""

 "ALTER TABLE "JAY_TEST" ENABLE CONSTRAINT "JAY_TEST_FK4""

 "ALTER TABLE "JAY_TEST" ENABLE CONSTRAINT "JAY_TEST_FK10""

 "ALTER TABLE "JAY_TEST" ENABLE CONSTRAINT "JAY_TEST_FK7""

 "ALTER TABLE "JAY_TEST" ENABLE CONSTRAINT "JAY_TEST_FK6""

 "ALTER TABLE "JAY_TEST" ENABLE CONSTRAINT "JAY_TEST_FK1""

 "ALTER TABLE "JAY_TEST" ENABLE CONSTRAINT "JAY_TEST_FK3""

Import terminated successfully without warnings.

padwsdpr pacdcorwprdd1 /home/oracle/amddba/dba/local/bin >