Server Administration :: Create An Index Using Parallelism?

Feb 17, 2011

We are trying to create an index using parallelism. The table contains 24 Million row. But It takes very long time. Also we are unable to see any sessions confirming that the index creation is using parallelism. The creation goes on for more than 3 hours. SQL statement executed :

CREATE INDEX ANT.ANT_OM_TRAN ON APPS.ANT_OM_TRAN_INDX (last_update_date) PARALLEL 4 TABLESPACE APPS_TS_IDX;

I found out the session using the query

select sid,serial#,program,module,osuser,username,machine,inst_id,sql_id
from gv$session where type not in ( 'BACKGROUND') and machine='APCUSDE'
and osuser='applmgr' and program like 'sqlplus%' order by logon_time ;
select * from gv$sqltext where sql_id='akrr2ww1ukq5z';

The given queries were used to check if it was using parallelism, but i was unable to find anything.

SELECT QCSID, SID, INST_ID "Inst", SERVER_GROUP "Group", SERVER_SET "Set",
NAME "Stat Name", VALUE
FROM GV$PX_SESSTAT A, V$STATNAME B
WHERE A.STATISTIC# = B.STATISTIC# AND NAME LIKE 'PHYSICAL READS'
AND VALUE > 0 ORDER BY QCSID, QCINST_ID, SERVER_GROUP, SERVER_SET;
SELECT * FROM V$PX_PROCESS;

how can i make sure that, it is using parallelism.

View 4 Replies


ADVERTISEMENT

Server Administration :: Convert Global Index To Local Index

Jun 23, 2011

I have a global index and I want to convert it to local index.Is there a way to recreate local index with out dropping the global index.

I can create a local index first and then drop the global index. But is there a way to create it with out dropping the global index, just convert it.

View 5 Replies View Related

Server Utilities :: Data Pump Worker Parallelism

Jun 17, 2010

While using impdp I had set parallel to 16. Currently parallelism is 16 but there are only two workers with worker parallism 1.

1st worker is executing and 2nd worker is waiting.

Is my impdp running parallely? What does work parallelism mean?

View 1 Replies View Related

Server Administration :: While Creating Index ORA-00603 / ORACLE Server Session Terminated By Fatal Error

Jul 26, 2010

While creating the index we are getting the error "ORA-00603: ORACLE server session terminated by fatal error".

We have the space in tablespaces and also in the file systesm.

View 13 Replies View Related

Server Administration :: How To Get Block_number Of Index

Jan 4, 2012

If i know a rowid of a row,how to get the block_number of index?

SQL> select rowid from hxl.tb_test where id=1;

ROWID
------------------
AAAMo/AAFAAAAAOAAA

View 7 Replies View Related

Server Administration :: Rebuilding Of Index

Oct 30, 2010

we have 25 GB free space in Index tablespace, to avoid space issue i have added 10GB of space. Next shift DBA, rebuild INDEX of the from INDEX tablespace. Now i can check, only 27GB free space in INDEX tablespace...

Rebuilding of INDEX takes extra space.. ?

View 7 Replies View Related

Server Administration :: How To Compress Index

Oct 23, 2011

How to compress a index?

SQL> Create Table tb_compress_test
2 As
3 Select * From dba_objects;

Table created.

SQL> Create Index idx_object_id On tb_compress_test(object_id);

Index created.

SQL> Alter Index idx_object_id Move Compress;
Alter Index idx_object_id Move Compress
*
ERROR at line 1:
ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option

View 9 Replies View Related

Server Administration :: Density Of Index?

Nov 29, 2010

What exactly mean density of the index in db meanings ? I mean when you configure rebuilt of the index and how do you determine the value of density? for e.g when I have density 40 what does it mean ? next question : how shall I determine which tables are included in index ?

View 5 Replies View Related

Server Administration :: Drop Index Hangs On SAP?

Apr 14, 2010

On a SAP system, am trying to drop six indexes, largest is 300MB and smallest is 50MB.

I tried running drop index sapusername.index_name on the 50MB index via SQL*Plus and it seems to be taking forever. anything I can check on the database on why it is taking such a long time?

I can leave it to run overnight but worried that when I come back the next day, it will still be hanged. Is there any quick way of dropping the index, .i.e. drop immediate ...

Am not using SAP's BRTOOLs as it is also hanging from there and the SAP-ADMIN had approved for the DBA to drop it from our end instead.

View 4 Replies View Related

Server Administration :: Validate Structure Of Index

Jan 5, 2012

I am working on Index issue and need to validate the structure of the index. Does validation lock the Index ?

Do we need downtime or we can do it in normal working hours.

alter index index_name validate structure;

View 9 Replies View Related

Server Administration :: Index Tablespace Is Not Growing?

Apr 26, 2012

In our production database server Index tablespace is not growing now... On average index tablespace increases 200 MB to 250 MB per day. Last two weeks index tablespace does not increase..

I am giving you all a statistics of our production db Tablespace usage..

Date Globusdataxml(MB)Globusindexxml(MB)
23-4-2012 26321025 1581.94
24-4-2012 24633.31 1584.25
25-4-2012 23410.88 1576.25
26-4-2012 22186.19 1573.25

View 9 Replies View Related

Server Administration :: Materialized Aggregate View Index?

Jun 27, 2012

When you create a MAV, you automatically get a hidden column and an index. Here's an example,drop user jon cascade;

grant dba to jon identified by jon;
conn jon/jon
create table emp as select * from scott.emp;
create materialized view mv1 enable query rewrite as
select deptno,sum(sal) from emp group by deptno;
select object_name,object_type from user_objects;
select index_name,column_name from user_ind_columns where table_name='MV1';
select column_name,hidden_column from user_tab_cols where table_name='MV1';
select deptno,"SUM(SAL)",sys_nc00003$ from mv1;

View 2 Replies View Related

Server Administration :: Identify Condition To Rebuild Index

May 27, 2012

In which condition it is required to rebuild the index and how can identified it?

View 12 Replies View Related

Server Administration :: Index And Table Rebuild Method

Jan 17, 2012

The best way to rebuild index and table and also give the reason as why we need to use this method?

View 10 Replies View Related

Server Administration :: Recover Space After Index Rebuild?

Apr 26, 2011

We have separate tablespaces for Tables and Indexes. Also Temp is in different temporary tablespace and UNDO also in UNDO tablespace and Index tablespace contains only Indexes. The tablespace usage for tables is 80% and Index is 91%.There is not enough disk space to allocate for the datafile on our system. I ran a rebuild on one of the Index but now notice the Index tablespace is 98% used soon after the rebuild finished.

How can i free up space for Index tablespace and why does the size of Index tablespace increased after the rebuild.

View 8 Replies View Related

Server Administration :: ORA-01654 - Unable To Extend Index?

Aug 14, 2013

We met unable to extend index exception.I have executed below mentioned query.
ERROR:ORA-01654: index
(128, tablespace USERS) You can not extend the OMS_SG_IT.TRN_BD_MNTHLY_OCF_SLT_PKC.

QUERY:
select db.FILE_NAME,
db.AUTOEXTENSIBLE,
db.INCREMENT_BY,
db.USER_BLOCKS,

[code]...

RESULT:
/u01/app/oracle/oradata/SCSKTRA/users01.dbf
YES
5
3872
65536
(null)
LOCAL

ANOTHER QUERY:
select tablespace_name,file_name,bytes/1024/1024 from dba_data_files where tablespace_name='USERS';

RESULT:
USERS
/u01/app/oracle/oradata/SCSKTRA/users01.dbf
31.25

Now what i have to do...

View 5 Replies View Related

Server Administration :: How To Find Which Table Or Index Needs To Be Reorg

Apr 21, 2009

How to find which table or Index needs to be reorg..??

IS there any Query!!!.

View 10 Replies View Related

SQL & PL/SQL :: Create Non-cluster Index On A Clustered Index?

Jul 11, 2012

Can we create non-cluster index on a clustered index?

View 5 Replies View Related

Server Administration :: Index Rebuild Grows Database Size

Oct 29, 2010

Our database size is 100GB and i removed few records from a table and rebuild the index.The size of Index reduced considerably after Index rebuild but now i see our database size increased to 115GB.I know online rebuild creates second index which is also removed after the build is finished then why the increase in database size?Is there a way so it shows up more space ?

select sum(bytes) from dba_segments where owner='abc' and segment_name='abc_index_1';
8GB

ALTER INDEX abc_index_1 REBUILD ONLINE ;

select sum(bytes) from dba_segments where owner='abc' and segment_name='abc_index_1';
2GB

View 6 Replies View Related

Server Administration :: Start Using Data And Index Compression In 11g Database

Jun 27, 2012

We have a requirement from the customer to start using data and index compression in our 11g database.. Is this something available in Oracle 10g,11g without any additional costs? We are not sure if this will work with our application so we will have to test it in-house, is it possible to compress the existing table data/index to test it out?

View 3 Replies View Related

Server Administration :: How To Verify If Rebuild Index Required In Database

Mar 26, 2012

How to verify if rebuild an index is required in database.

View 9 Replies View Related

Server Administration :: Move Indexes (around 300) From Data To Index Tablespace

May 18, 2011

I need to move my indexes (around 300) from data tablespace to index tablespace. What's the best way to do it?

View 9 Replies View Related

Server Administration :: Create Tablespace For Administration

Nov 29, 2010

i'm a student currently learning database administration security.

I need to create a tablespace for administration of database but i don't know what datafile settings are best suited for admin usage.

I have attached the schema that was given to me for this assignment.

View 12 Replies View Related

Server Administration :: Possible To Run SHRINK SPACE Against Table With Function Based Index

Jun 27, 2013

It is not possible to run SHRINK SPACE against a table with a function based index. This is documented, and I've tested on the current release. I've reverse engineered it a bit, and the issue is in fact that you cannot SHRINK SPACE if there is an index on a virtual column:SQL> SQL> create table t1(c1 number, c2 as (c1*2)) segment creation immediate;

Table created.
SQL> alter table t1 enable row movement;
Table altered.
SQL> alter table t1 shrink space;
Table altered.
SQL> create index i1 on t1(c2);
Index created.

SQL> alter table t1 shrink space;
alter table t1 shrink space

ERROR at line 1: ORA-10631: SHRINK clause should not be specified for this object.

View 2 Replies View Related

Server Administration :: Multi-block Disk Reads For Index Range Scans?

Aug 31, 2010

My understanding of DB_FILE_MULTIBLOCK_READ_COUNT parameter is that it affects only Full Table Scans and Fast Full Index Scans - all other disk retrieval is single block.If so, then maybe I'm reading this trace incorrectly:

select /*+ first_rows */ pk
from test_join_tgt
where pk >= 0
and rownum > 1

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 21.48 27.77 22368 22134 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 21.49 27.78 22368 22134 0 0
[code]...

What the heck is going on with the Multi-block reads in the Range Scan?

View 3 Replies View Related

Server Administration :: Splitting Table Partition Without Making Primary Key Index Unusable?

Apr 8, 2013

splitting a table partition without making its primary key index ar any other indexes unusable.

I think it is possible to do so 10g onwards.

DB Details:
Oracle RDBMS 11.2.0.3, HP-Ux B.11.31, OLTP

View 2 Replies View Related

Server Administration :: Create Multiple Database In Single Server

Apr 18, 2011

I have database in single server. I already have ORACLE_SID=stagedb. So i want to create new sid and named as proddb.Is it possible have multiple db in single server? This server not running as RAC. It run as single only.

View 1 Replies View Related

Server Administration :: How To Use LV To Create ASM Disk

Jul 27, 2012

How can use the LV to create a ASM disk in Red Hat Linux As 5

#lvcreate -n lv01 -L 200g myvg

View 3 Replies View Related

Server Administration :: How To Create Db_link

Mar 16, 2012

if i set the global_names to True, how to create a db link to another machine?

SQL> select * from global_name;

GLOBAL_NAME
------------------------------------------------------
LNK_62

SQL> show parameters global_name;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
global_names boolean TRUE

View 2 Replies View Related

Server Administration :: How To Create ASM Instance Using DBCA

Jul 15, 2010

I am learning to create an ASM (Automatic Storage Management) instance using dbca.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved