| Table creation with nologging clause is stopped in between [message #625864] |
Wed, 15 October 2014 02:43  |
ashishkumarmahanta80
Messages: 231
Registered: October 2006
Location: kolkatta
|
Senior Member |
|
|
Dear All,
Here, We have logical data -guard. Through scheduler, table is created with nologging clause. But need to know whether any impact will happen if we stop the scheduler in between the process. Yesterday, due to high records, it was keep on executing for long time and lastly, user has stopped it. Also want to know, whether it will impact to ODS or not, since we have mention it "NO-LOGGING".
Below are the script for the creation of table -
create table dw_table_data_12102014 nologging as select * from acc_general_ledger where dat_voucher_date between '01-sep-2014' and '30-sep-2014'
SQL> desc acc_general_ledger
Name Null? Type
----------------------------------------- -------- ---------------
NUM_OFFICE_CD NOT NULL NUMBER(8)
TXT_LEDGER_ACCOUNT_CD NOT NULL VARCHAR2(16)
NUM_FIN_YEAR NOT NULL NUMBER(4)
NUM_FIN_PERIOD NOT NULL NUMBER(2)
NUM_REPORTING_YEAR NOT NULL NUMBER(4)
NUM_REPORTING_PERIOD NOT NULL NUMBER(2)
NUM_TAX_YEAR NOT NULL NUMBER(4)
NUM_TAX_PERIOD NOT NULL NUMBER(2)
DAT_VOUCHER_DATE NOT NULL DATE
DAT_TRANSACTION_DATE NOT NULL DATE
DAT_POSTING_DATE NOT NULL DATE
TXT_TRANSACTION_CATEGORY_CD NOT NULL VARCHAR2(5)
NUM_TRANSACTION_CONTROL_NO NOT NULL NUMBER(20)
TXT_TRANSACTION_TYPE_CD NOT NULL VARCHAR2(5)
NUM_TRANSACTION_DOCUMENT_NO NOT NULL NUMBER(10)
NUM_LINE_NO NOT NULL NUMBER(10)
TXT_EXPENSE_TYPE_CD VARCHAR2(6)
NUM_DEPARTMENT_CD NUMBER(3)
NUM_REFERENCE_NO NUMBER(15)
DAT_REFERENCE_DATE DATE
NUM_POLICY_NO NUMBER(10)
NUM_ENDORSEMENT_NO NUMBER(10)
NUM_CLAIM_NO NUMBER(20)
NUM_RI_TREATY_NO NUMBER(15)
TXT_DR_CR NOT NULL VARCHAR2(2)
NUM_AMOUNT NOT NULL NUMBER(15,2)
TXT_DIMENSION_1_VALUE_CD VARCHAR2(20)
TXT_DIMENSION_2_VALUE_CD VARCHAR2(20)
TXT_DIMENSION_3_VALUE_CD VARCHAR2(20)
TXT_DIMENSION_4_VALUE_CD VARCHAR2(20)
TXT_DIMENSION_5_VALUE_CD VARCHAR2(20)
TXT_DIMENSION_6_VALUE_CD VARCHAR2(20)
TXT_DIMENSION_7_VALUE_CD VARCHAR2(20)
TXT_DIMENSION_8_VALUE_CD VARCHAR2(20)
TXT_DIMENSION_9_VALUE_CD VARCHAR2(20)
TXT_REMARKS VARCHAR2(200)
NUM_TRANS_ID NUMBER(15)
DAT_TRANS_DATE DATE
NUM_SCROLL_NO NUMBER(8)
DAT_SCROLL_DATE DATE
TXT_PAYMENT_MODE_CD VARCHAR2(2)
NUM_INSTRUMENT_TYPE_CD NUMBER(2)
TXT_INSTRUMENT_NO VARCHAR2(30)
DAT_INSTRUMENT_DATE DATE
DAT_INSTRUMENT_RCVD_DATE DATE
NUM_DRAWEE_BANK_CD NUMBER(8)
TXT_DRAWEE_BANK_BRANCH VARCHAR2(200)
NUM_HOUSE_BANK_BRANCH_CD NUMBER(8)
TXT_BANK_ACCOUNT_NO VARCHAR2(80)
NUM_PAYIN_SLIP_NO NUMBER(20)
DAT_PAYIN_SLIP_DATE DATE
NUM_GL_EXTRACTION_NO NUMBER(10)
TXT_STATUS VARCHAR2(50)
NUM_INDEX_NO NUMBER(10)
NUM_COLLECTION_OFFICE_CD NUMBER(8)
NUM_INSERT_TRANS_ID NUMBER(15)
NUM_MODIFY_TRANS_ID NUMBER(15)
DAT_INSERT_DATE DATE
DAT_MODIFY_DATE DATE
Regards,
Ashish Kumar Mahanta
|
|
|
|
|
|
|
|
| Re: Table creation with nologging clause is stopped in between [message #625873 is a reply to message #625868] |
Wed, 15 October 2014 04:13  |
ashishkumarmahanta80
Messages: 231
Registered: October 2006
Location: kolkatta
|
Senior Member |
|
|
Dear Mr. John,
Yes. Forced logging is enabled during the configuration itself. User has scheduled one job and inside the script create table syntax was written.
When it took long time to complete it, they stopped the job.
Dear Mr. Michel,
This is happening in primary side. So want to know whether it will impact at logical standby side also?
Thanks,
Ashish Kumar Mahanta
|
|
|
|
|
|
|
|
|
|
| Re: Table creation with nologging clause is stopped in between [message #625922 is a reply to message #625921] |
Thu, 16 October 2014 04:09 |
gazzag
Messages: 1119
Registered: November 2010
Location: Bedwas, UK
|
Senior Member |
|
|
You still don't say how the table creation was stopped, however:
SQL> create table test as select * from all_objects;
^C
E:\oracle\scripts>
E:\oracle\scripts>sqlplus test/test
SQL*Plus: Release 11.2.0.1.0 Production on Thu Oct 16 10:02:00 2014
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> desc test;
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER NOT NULL VARCHAR2(30)
OBJECT_NAME NOT NULL VARCHAR2(30)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NOT NULL NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED NOT NULL DATE
LAST_DDL_TIME NOT NULL DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
SQL> select count(*) from test;
COUNT(*)
----------
50560
SQL> select count(*) from all_objects;
COUNT(*)
----------
50560
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
