Index instantaneous

[Dulima-SP]

Environment information:
* Oracle version: 10g
* Operating system: RH4

Srs ..

I need to create Some indices in production, but if I do this during the day I'll lock the table !!

Someone knows if there is someone parameter that I can change in my session to create these indices almost instantly ??

grateful

[diegolenhardt]

When it is Rebuild has the online, but I do not know if this parameter works together with CREATE INDEX ... online;

makes a test aí ...

[Dulima-SP]

So follows the error !!!

Select all

ORA-00439: feature not enabled: Online Index Build 

What do I need to activate?

Select all

 
SYS@stddb > select parameter, value from v$option; 
 
PARAMETER                                                        VALUE 
---------------------------------------------------------------- ---------------------------------------------------------------- 
Partitioning                                                     FALSE 
Objects                                                          TRUE 
Real Application Clusters                                        FALSE 
Advanced replication                                             FALSE 
Bit-mapped indexes                                               FALSE 
Connection multiplexing                                          TRUE 
Connection pooling                                               TRUE 
Database queuing                                                 TRUE 
Incremental backup and recovery                                  TRUE 
Instead-of triggers                                              TRUE 
Parallel backup and recovery                                     FALSE 
Parallel execution                                               FALSE 
Parallel load                                                    TRUE 
Point-in-time tablespace recovery                                FALSE 
Fine-grained access control                                      FALSE 
Proxy authentication/authorization                               TRUE 
Change Data Capture                                              FALSE 
Plan Stability                                                   TRUE 
Online Index Build                                               FALSE 
Coalesce Index                                                   FALSE 
Managed Standby                                                  FALSE 
Materialized view rewrite                                        FALSE 
Materialized view warehouse refresh                              FALSE 
Database resource manager                                        FALSE 
Spatial                                                          FALSE 
Visual Information Retrieval                                     FALSE 
Export transportable tablespaces                                 FALSE 
Transparent Application Failover                                 TRUE 
Fast-Start Fault Recovery                                        FALSE 
Sample Scan                                                      TRUE 
Duplexed backups                                                 FALSE 
Java                                                             TRUE 
OLAP Window Functions                                            TRUE 
Block Media Recovery                                             FALSE 
Fine-grained Auditing                                            FALSE 
Application Role                                                 FALSE 
Enterprise User Security                                         FALSE 
Oracle Data Guard                                                FALSE 
Oracle Label Security                                            FALSE 
OLAP                                                             FALSE 
Table compression                                                FALSE 
Join index                                                       FALSE 
Trial Recovery                                                   FALSE 
Data Mining                                                      FALSE 
Online Redefinition                                              FALSE 
Streams Capture                                                  FALSE 
File Mapping                                                     FALSE 
Block Change Tracking                                            FALSE 
Flashback Table                                                  FALSE 
Flashback Database                                               FALSE 
Data Mining Scoring Engine                                       FALSE 
Transparent Data Encryption                                      FALSE 
Backup Encryption                                                FALSE 
Unused Block Compression                                         FALSE 
Oracle Database Vault                                            FALSE 
Real Application Testing                                         FALSE 
 

[diegolenhardt]

Online is only for Enterprise banks, your should be Standart ..

= /

[Dulima-SP]

This ....

any light ????

[diegolenhardt]

Putz face, without affecting will be complicated,

What you should do for a faster creation is to create the index with nologging not to generate Red / Archive,

] Link with documentation http://download.oracle.com/docs/cd/B120 ... ndexes.htm

[Dulima-SP]

Show ....

More does not have an impact on the table and the lock will go on p ....

I will have to do with the analyst .....

Vlw Diego !!

[pauloaleo]

See the CPU number you have on the machine, and do a CREATE INDEX with Parallel Degree = CPU number of them.

Then changes the index to Noparallel.

goes much faster (this if your machine is not a windows of life)

[diegolenhardt]

I think the biggest bottleneck would be in I / O ..

[Dulima-SP]

I'll test an evening !!!

The locks were on top !!!!


Impossible to run now !!!

[diegolenhardt]

If they are many, then run a query in the User_indexes
to see if it does not have any with status = 'unusable' depending on the skip_indexes parameter can generate errors in the queries if you have index not available,;)

[Dulima-SP]

Po leave !!!

put the result tomorrow

grateful srs.