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.. ?
I have a table in my application that has the following structure
Create table EVENT (project_id number, DISC_CMPLNT_TX clob, MAINT_ACT_TX CLOB,LGBK_CMT_TX VARCHAR2(1000 CHAR))
PARTITION BY RANGE (project_id)
(PARTITION pmax VALUES LESS THAN (MAXVALUE));
I have created the below preference on the three columns of the table.
begin
ctx_ddl.create_preference ('skipJoinLexer', 'BASIC_LEXER');
ctx_ddl.set_attribute ('skipJoinLexer', 'skipjoins', ';:.,()<>*"''#-');
ctx_ddl.create_preference ('logbookTextDS', 'MULTI_COLUMN_DATASTORE');
ctx_ddl.set_attribute ('logbookTextDS', 'COLUMNS', 'DISC_CMPLNT_TX, MAINT_ACT_TX,LGBK_CMT_TX');
end;
I have created a local partitioned index with the parameter NOPOPULATE create index lb_text_idx on ac_maint (DISC_CMPLNT_TX) indextype is ctxsys.context local parameters ('lexer skipJoinLexer datastore logbookTextDS NOPOPULATE');
When an new value of project_id is inserted into table1 , I create a partition using the following command where prjId is the new value of the project_id ALTER TABLE EVENT split PARTITION pmax AT ('||prjId||') INTO ( PARTITION p_' || prjId || ', PARTITION pmax)
Now I run a huge data load into the EVENT table for a project Id say 1 and insert millions of records.I want to rebuild the lb_text_idx for this partition alone so that data is available for searching by the application. I did a rebuild using the command " alter index lb_text_idx rebuild partition p_1".
However when I run the below sql, I get a count of 0. I know that the text MAINT is definitely present in the table.
SELECT COUNT(1) FROM EVENT WHERE PROJECT_ID = 1 and contains (DISC_CMPLNT_TX, 'MAINT' ) > 0
When I run the below sql select parameters,status from user_ind_partitions where partition_name = 'P_1' , I get parameters as null and status as usable.Am I getting 0 records because the parameters are set to null? How do I rebuild with the same parameters for the specific partition alone.
I've read your article - you mentioned about MOSC recommendations. Actually they changed their mind and do not recommend rebuilding regularly - it is written on MOSC in 'Index Rebuild, the Need vs the Implications [ID 989093.1]'. What is your approach? Do you rebuild indexes regularly?
View 1 Replies View RelatedI 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.
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.
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
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
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 RelatedOn 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.
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;
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
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.
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;
In which condition it is required to rebuild the index and how can identified it?
View 12 Replies View RelatedThe best way to rebuild index and table and also give the reason as why we need to use this method?
View 10 Replies View RelatedWe 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.
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...
How to find which table or Index needs to be reorg..??
IS there any Query!!!.
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
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 RelatedHow to verify if rebuild an index is required in database.
View 9 Replies View RelatedI 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 RelatedIt 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.
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?
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
We deleted millions of records from a table.
1.Is it necessary to reorganize a table and index after the deletion of records from table ? Because i see some change in table size after table and index reorganization.
2.Will re org table and index improve the database performance ?
I have created a table like below-
PROMPT CREATE TABLE tst_fetch_vendor_data
CREATE TABLE tst_fetch_vendor_data (
vendor_data_seq_no NUMBER NOT NULL,
study_seq_no NUMBER NOT NULL,
vendor_record_seq_no NUMBER NOT NULL,
control_column_seq_no NUMBER NOT NULL,
resolved_value VARCHAR2(4000) NULL,
original_value VARCHAR2(4000) NULL,
transaction_user VARCHAR2(30) NOT NULL,
[code]....
Its just a temporary table, in which data comes and goes. I am using this in middle of a process.I am using it in a process like below--
--EXECUTE IMMEDIATE 'TRUNCATE TABLE TST_FETCH_VENDOR_DATA DROP STORAGE';
insert /*+ append */ into tst_fetch_vendor_data
(select * from vendor_data vd
where vd.control_column_seq_no in
(select control_column_seq_no from temp_control_column));
dbms_stats.gather_table_stats('EPDSYSREP','TST_FETCH_VENDOR_DATA',ESTIMATE_PERCENT=>100,
METHOD_OPT=>'for all indexed columns size auto',CASCADE=>True);
code to use that table..This table can contain data from 0 to 108000000 records.Now my questions are-
1. How much should I select sampling size (currently its 100%)Can I use dbms_stats.auto_sample_size, what will be the effect?
2. dbms_stats is good approach or should I use dynamic sampling.
3. what about the approach using CTAS instead of inserting data through insert.
4. What about pl/sql table with index or with clause query.
5. Do I need to rebuild index after inserting data into table.
I am on 11.2.0.3 Enterprise Edition. We are using the new feature "Composite Domain Index" for a Domain index on a very large table (>250.000.000 rows). It really works with mixed queries. We added two number columns using FILTER BY.We have lots of DML on this table. Therefore, we are executing synchronize and optimize once the week. The synch behaves pretty normal. But "optimize_index" takes a very very long time to complete. I have switsched on 'logging' for the optimize process. The $I table takes some time but is finished normally. But the optimization of the $S table (that is the table created for the CDI feature) is running over 12 hours now - and far from being finished. From the logfile, I can see that it optimizes 1000 rows every 20 minutes. Here is the output of the logfile:
Oracle Text, 11.2.0.3.0
14:33:05 06/26/12 begin logging
14:33:05 06/26/12 event
14:33:05 06/26/12 process $N for optimize: SEQDEV.GEN_GES_DESCRIPTION_CTX_I
14:33:16 06/26/12
14:33:16 06/26/12
[code]....
I haven't found a recommendation from Oracle not to use "optimize_index" for Domain Indexes with CDI. But in my case, it would be much faster just to drop and recreate the Domain Index in question.
I am trying to take exp / imp for Index organized table.
View 16 Replies View RelatedCurrently we are using "exp and imp" utilities to unload from production and load into Dev server. While importing, we are following below steps
(1) Load only data [by specifying INDEXES=N in the par file]
(2) Unlock statistics
(3) Load indexes, other objects [by specifying ROWS=N]
After doing these steps, both data, indexes and others objects are loaded. To verify indexes, we are checking DBA_INDEXES.
DBA_INDEXES :
-------------
OWNER INDEX_NAME TABLE_NAME STATUS LAST_ANALYZED
----- ---------- ---------- ------ -------------
MYSCH CP_INDEX_1 CP_TABLE_1 VALID 14/JAN/12
Question :-
(1) Does imp utility rebuild the indexes while loading data ? or it simply takes the rows from dump and load into test system without building from scratch ?
(2) I am trying to replace 'exp' and 'imp' with datapump utilities ? But, I am confused about the parameters to be used ?
(a) Can I load both data and meta data at the same time (Using CONTENT=ALL option) ?
(b) I am planning to implement this in two steps :
first load only metadata using - CONTENT=METADATA_ONLY TABLE_EXISTS_ACTION=REPLACE
then, load data - CONTENT=DATA_ONLY.