Home » RDBMS Server » Performance Tuning » Table creation with nologging clause is stopped in between (10g, 10.2.0.4, hp unix)
Table creation with nologging clause is stopped in between [message #625864] Wed, 15 October 2014 02:43 Go to next message
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 #625867 is a reply to message #625864] Wed, 15 October 2014 02:56 Go to previous messageGo to next message
John Watson
Messages: 7219
Registered: January 2010
Location: Global Village
Senior Member
What does this mean,
Quote:
stop the scheduler in between the process

and have you enabled force logging mode?
Re: Table creation with nologging clause is stopped in between [message #625868 is a reply to message #625864] Wed, 15 October 2014 03:05 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

In addition, as ODS has different meanings, tell us what it is in this case.

Re: Table creation with nologging clause is stopped in between [message #625873 is a reply to message #625868] Wed, 15 October 2014 04:13 Go to previous messageGo to next message
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 #625890 is a reply to message #625873] Wed, 15 October 2014 09:08 Go to previous messageGo to next message
John Watson
Messages: 7219
Registered: January 2010
Location: Global Village
Senior Member
FORCE LOGGING means that any NOLOGGING instructions are ignored. So everything that happens at the primary will go to your standbys.

You can see this easily enough: just log on, and see if the table exists.

I still do not know what this ODS is.
Re: Table creation with nologging clause is stopped in between [message #625891 is a reply to message #625890] Wed, 15 October 2014 09:18 Go to previous messageGo to next message
gazzag
Messages: 907
Registered: November 2010
Location: Bristol, UK
Senior Member
ODS can mean Operational Data Store, which would fit in with his apparent data-warehouse / standby database model, I guess.

@OP, how is this table-creation job run and how was it stopped before completion?
Re: Table creation with nologging clause is stopped in between [message #625921 is a reply to message #625891] Thu, 16 October 2014 03:55 Go to previous messageGo to next message
ashishkumarmahanta80
Messages: 231
Registered: October 2006
Location: kolkatta
Senior Member
Dear Mr. John,

Thanks. It means force logging ignore nologging clause in data-guard. I will check by login in ODS database.

Dear Mr. gazzag,

After having discussion, came to know that table was created manually(not through scheduler) and stopped in between.

Thank you @all
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 Go to previous message
gazzag
Messages: 907
Registered: November 2010
Location: Bristol, 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
Previous Topic: Materialized view refresh thrashing DBWR
Next Topic: SQL tuning advisor being retarted
Goto Forum:
  


Current Time: Fri Jan 19 16:38:09 CST 2018

Total time taken to generate the page: 0.01462 seconds