No question at this time
DBA Top 10
1 M. Cadot 26200
2 A. Kavsek 16900
3 B. Vroman 11000
4 M. Hidayathullah ... 10500
5 T. Boles 7600
6 F. Diaz 6700
7 P. Wisse 6400
8 J. Schnackenberg 4700
9 A. Hudspith 4500
10 P. Knibbs 2500
About
DBA-Village
Download PLATO
The free tool for auditing and tuning your database
Version 55 now available
Sep 02, 2016
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users47898
Total active users1919
Act. users last 24h13
Act. users last hour1
Registered user hits last week245
Registered user hits last month1316
Go up

Bulk-Load in a Local-Index-Table
Next thread: Oracle sqlplus scrolling interruption
Prev thread: auditing to the OS and Event Logs in Windows

Message Score Author Date
Hello, is there easy way with bulk-load to load...... U U Feb 08, 2017, 18:12
You have not to care about how many partitions/s...... Michel Cadot Feb 08, 2017, 20:35
Hello Michael, To create to local index is need t...... U U Feb 08, 2017, 21:14
No, you don't need to know, Oracle knows it for ...... Score: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 Pts Michel Cadot Feb 08, 2017, 21:30
Great thanks a lot Michael.... U U Feb 09, 2017, 09:41

Follow up by mail Click here


Subject: Bulk-Load in a Local-Index-Table
Author: U U, Germany
Date: Feb 08, 2017, 18:12, 42 days ago
Oracle info: Oracle 11
Error info: ORA-26002
Message: Hello,

is there easy way with bulk-load to load in a index partitioned table.

The structure of the table is:
Intervall-Partition and Subpartition and over the (Sub)-partiton there is a Local-index

Progress to load the Table:

Before-SQL: DROP INDEX idx;
Main Process: Bulk-Load is running successfull
AFter-SQL: create the local index again

But the problem is located in After-SQL, because I dont know how many partition, and subpartiton was created in the Main-Process (bulk-load)

I found many examples, but based on classical insert statement and the INDEX was in UNUSUAL-Status.
After the Insert the Index was rebuilded.

Thank you in advance for your help
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Bulk-Load in a Local-Index-Table
Author: Michel Cadot, France
Date: Feb 08, 2017, 20:35, 42 days ago
Message:
You have not to care about how many partitions/subpartitions have been created if you create a local index unless you want to specify storage clause for each partition or subpartition.

Regards
Michel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Bulk-Load in a Local-Index-Table
Author: U U, Germany
Date: Feb 08, 2017, 21:14, 42 days ago
Message: Hello Michael,
To create to local index is need the amount of the partition. So the local index match to the partitions.
Is there a generic way during the runtime to estimate this?
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Bulk-Load in a Local-Index-Table
Author: Michel Cadot, France
Date: Feb 08, 2017, 21:30, 42 days ago
Score:   Score: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 Pts
Message:
No, you don't need to know, Oracle knows it for you.
Example:
SQL>  CREATE TABLE "PARTITIONED_TABLE"

2 ( "A" NUMBER(*,0),
3 "B" NUMBER(*,0)
4 ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
5 PARTITION BY RANGE ("A")
6 (PARTITION "PART_1" VALUES LESS THAN (2),
7 PARTITION "PART_2" VALUES LESS THAN (3),
8 PARTITION "PART_3" VALUES LESS THAN (4) )
9 /

Table created.

SQL> select partitioned, partition_count from user_tables t, user_part_tables p
2 where t.table_name='PARTITIONED_TABLE' and p.table_name=t.table_name
3 /
PAR PARTITION_COUNT
--- ---------------
YES 3

1 row selected.

SQL> create index PARTITIONED_TABLE_IDX on PARTITIONED_TABLE (a) local
2 /

Index created.

SQL> select partitioned, partition_count from user_indexes i, user_part_indexes p
2 where p.index_name = i.index_name and p.table_name = i.table_name
3 and i.table_name = 'PARTITIONED_TABLE'
4 /
PAR PARTITION_COUNT
--- ---------------
YES 3

1 row selected.


Regards
Michel


Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Bulk-Load in a Local-Index-Table
Author: U U, Germany
Date: Feb 09, 2017, 09:41, 42 days ago
Message: Great thanks a lot Michael.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here