I am using Oracle 10g XE (express edition). If I try to create a bitmap index, I get an error
ORA-00439 feature not enabled: Bit mapped Indexes
How do I solve this problem and create a bitmap index?
ORA-00439: feature not enabled: Partitioning...
how to enable partitioning in database....
my version is......
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production.
I am having Oracle 9i relaese 2 on my db server. I am getting the following error every time I try to create a bitmap index:-
ORA-00439: feature not enabled: Bit-mapped indexes
I have queried the v$option table .Here the value of parameter Bit-mapped indexes is FALSE.
The result of v$version is :-
Oracle9i Release 9.2.0.1.0 - 64bit Production
PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production
TNS for Solaris: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production
Actually when we created the database our installation was halted . so we manually created the database using Create database command.
How can we enable the BITMAP Index Feature now.
i installed my oracle 10g R2 in my pc but while startup my database database manually i got error msg cmd> startup nomount
ORA - 00439 : Feature not enabled: Real Application Clusters
I am using ORACLE 11gR1.
I was running a test on a huge table with a bitmap index on one of the columns. But bitmap index is not getting used. Below are the test details.
create table test (col1 number, col2 number);
begin
for i in 1..1000000
loop
if mod(i,2) = 0 then
insert into test values(i, 'Y');
else
insert into test values(i, 'N');
end if;
end loop;
end;
COMMIT;
The intention here is to have only two distinct values in the entire table. Based on these values I will not build BITMAP index on col2.
CREATE BITMAP INDEX BITMAP_TEST on TEST(col2) COMPUTE STATISTICS;
Now when I run the below query, it doesn't uses BITMAP index. Instead the explain plan shows a full table scan.
select * from test where col2 = 'Y';
Now when I force ORACLE to use index through hint, the cost is too high while using the bitmap index(probably why the ORACLE chose not to use the index at the first stage).
I have read somewhere, that BITMAP index is useful when we have more than 1 or 2 bitmap indexes on other columns of the same table as well And the query should be like
select * from table where col1 = 'Y' and sex = 'F';
In this case oracle will use BITMAP but not in the case where there is only one column that has BITMAP index.
Considering all the factors stated above, is there any way I can fine tune my original query?
select * from test where col2 = 'Y';
I was executing following query and it was taking about 20 sec's to execute before i decided to create B-Tree(Normal) index on column DeliVery.
Select
DeliVery,Code,Sum(Units),Sum(Loads),Count(units),
Count(Loads)
Where
[code]...
After creating B-Tree index on Column Delivery the execution time has been reduced to one second ,thats what i wanted. But If i create Bitmap index on the same column then execution time is not getting reduced and is still same around 20 sec.I think theoratically "Delivery" column is the right candidate for Bitmap index? whether should i create bitmap index or stick with B-Tree index?
i want to create a bitmap index,but getting the error shown below..i created primary key on column dname of dept and unique constraint on empno of emp.
SQL> create bitmap index bindx on dept d(d.dname) from emp e,dept d where e.deptno=d.deptno;
create bitmap index bindx on dept d(d.dname) from emp e,dept d where e.deptno=d.deptno
*
ERROR at line 1:
ORA-25954: missing primary key or unique constraint on dimension
index types like cluster,bitmap,binary tree,functional. Specify the differences between these types
View 5 Replies View RelatedI have an IOT table with partitioned on list. I have p1,p2 and p3 partitions. Now I would like to create a bitmap index on partition key.
I gave ALTER TABLE .. MOVE MAPPING TABLE;
But getting the below error,
ORA-28660: Partitioned Index-Organized table may not be Moved as a whole.
oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
"CORE 11.1.0.6.0 Production"
I have a procedure that is run weekly to load the data, for which it calls another procedure. This weekly procedure, disables the index, load the data, and rebuilds the index. During rebuilding of index, its giving ORA-00054: resource busy and acquire with NOWAIT error. I dont have privileges to look for session id, and kill the session. How to avoid this error.
create or replace
PROCEDURE "WCL_WEEKLY_UPLOAD" (
p_event_id IN NUMBER
)
[code]....
As you can see, after the procedure wc_upload, COMMIT is issued, and then the rebuilding of the index is starting. So dont know what is causing the table to lock.
we have a strange symptom in a database Oracle 11.2.0.2 EE. Following question comes from our application developers.
The following SQL statement:
SELECT
v.reporting_month,
sum(v.f_s) "REV_S",
sum(v.f_f) "REV_F",
sum(v.f_c) "REV_C",
[code].......
gives different result when we exchange the index ksr_valid_until_i on table kreditkarten_sets_rs. For some reasons we changed the index from bitmap to normal and are getting different results. Switching back gives us the same results as before. When we avoid the usage of this index in the statement than we are getting the same results as when we are using the normal index.
is there some performance/access difference between a bitmap index on a number column and char(1) column? Both columns are not null with a default value.My application has a querie like this:
select ass.column20, ass.column30from table_a pucinner join table_b asson ass.column1 = puc.column1where pc.column_char = 'S'and ass.column_char02 = 'P'
If I create a bitmap index on column "column_char", the access plan is not changed. But changing the column datatype to number(1) and obviously the values, the index is accessed and the cost decreases.This table has 4.000.000 rows. Oracle 11.2.0.2SO
I have 2 sets of phone number ranges and need to find the ranges that intersect.
create table rng1
(
rng_start varchar2(10),
rng_end varchar2(10)
);
[code]....
When I try to find the ranges matching (intersecting) a certain range (e.g. '21112'-'2111299999'), optimizer does a FULL TABLE SCAN, which is not opimal in my opinion. I use the hint to force the optimizer use the indexes.
select /*+ index_combine(rng2 x_rng2_start, x_rng2_end) */ *
from rng2
where
rng2.rng_start <= '2111999999'
and rng2.rng_end >= '2111';
Plan
SELECT STATEMENT ALL_ROWSCost: 60 Bytes: 17,344 Cardinality: 1,084
7 TABLE ACCESS BY INDEX ROWID TABLE LUMAMIPA.RNG2 Cost: 60 Bytes: 17,344 Cardinality: 1,084
6 BITMAP CONVERSION TO ROWIDS
5 BITMAP AND
2 BITMAP MERGE
1 BITMAP INDEX RANGE SCAN INDEX (BITMAP) LUMAMIPA.X_RNG2_START
4 BITMAP MERGE
3 BITMAP INDEX RANGE SCAN INDEX (BITMAP) LUMAMIPA.X_RNG2_END
However, when I try to join the 2 sets of ranges by matching the intersecting ranges, then the range scan and "BITMAP AND" of the 2 indexes is not done, even though the hint is specified:
select /*+ index_combine(rng2 x_rng2_start, x_rng2_end) */
count(*) cnt
from rng1
join rng2 on
rng1.rng_start <= rng2.rng_end
and rng1.rng_end >= rng2.rng_start;
[code]...
How to make the optimizer use the range scan of both indexes? Is there a better way to match the ranges?
My operating system is Windows Xp Professional and the Oracle database version is 10.2.0.
My task is to export datas using export utility. Following are the steps I did in my sqlplus connecting to a database db2.
SQL> CONN / AS SYSDBA
Connected.
SQL> ALTER USER scott IDENTIFIED BY tiger ACCOUNT UNLOCK;
User altered.
SQL> CREATE OR REPLACE DIRECTORY test_dir AS '/u01/app/oracle/oradata/';
Directory created.
SQL> GRANT READ, WRITE ON DIRECTORY test_dir TO scott;
Grant succeeded.
SQL> expdp scott/tiger@db2 tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log
I was not able to export the tables, resulting in the following error.
QUOTE SP2-0734: unknown command beginning "expdp scot..." - rest of line ignored.
What should I do to rectify the error?
Using Oracle 11g's compression feature in production? I haven't read anything negative yet, that doesn't meant that there isn't anything that could have an adverse affect. I wanted to check to see if there are any affects on the performance or any disadvantages of using this compression feature. I have tested this on one my major tablespace and I did see a big difference in the reduce size on the tablespace but I am still hesitated to put this into production.
View 1 Replies View Relatedwhat does a bitmaped block represent in oracle9i.
View 3 Replies View RelatedI have a few dimension tables with surrogate keys and a fact table with foreign keys on columns referring to the dimension table. I want to create primary keys on the dimension tables and foreign keys on the fact table, but with minimal overhead of constraint validation when loading the data.
Other than bitmap indexes on the FK columns on the fact table, I also want to create a bitmap join index on the fact table joined to the dimension table on a handful of dimension columns.
Which is the best suited constraint definition? Only RELY, or RELY + DISABLE, or RELY + DISABLE + NOVALIDATE, or RELY + NOVALIDATE, or any other?
Are NULLs get indexed in bitmap indexes? How can I verify that.
View 3 Replies View Relatedfor error: ORA-03001: unimplemented feature.
SQL> SELECT /*+ PARALLEL (a 8) PARALLEL (b 8) */
SUM (space_used_delta) / 1024 / 1024 "USED_MB",
2 3 SUM (c.bytes) / 1024 / 1024 "TOTAL_MB",
4 ROUND (SUM (space_used_delta) / SUM (c.bytes) * 100, 2) || '%'"PERCENT"
[code]...
ERROR at line 5:
ORA-03001: unimplemented feature
SQL>
All the tables are present, still..??
I want to implement word wrap feature in code.
Requirement is format given multi line text in the specified lines and each line be of specified character. Here words should not be broken, instead they must come to new line if lines are available.
Function could of the sort:
CREATE OR REPLACE FUNCTION wrap_text(p_text VARCHAR2
,p_len NUMBER-- no of lines
,p_chr NUMBER--no of chr/line
)
RETURN VARCHAR2;
I am using Oracle 10g enterprise edition .
Currently, Partition is enabled in my database.I want to disable it.
SQL> SELECT * FROM V$OPTION WHERE PARAMETER = 'Partitioning';
PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
Partitioning
TRUE
the steps to disable the partition feature.
you have an undo tablespace which has autoextend on feature.after a timegap your undo tablespace presently is 100GB
here as a DBA what you will do?
I used the Exchange Partition feature to swap segments between 2 tables- one Partitioned, and one Non-Partitioned. The exchange went well. However, all the data in the partitioned table has gone to the partition which stores the maxbound values.
/** actual table names changed due to client confidentiality issues */
-- Drop the 2 intermediate tables if they already exist
drop table ordered_inv_bkp cascade constraints ;
drop table ordered_inv_t cascade constraints ;
/**
1st create a Non-Partitioned Table from ORDERED_INV and then add the primary key and unique index(s):
*/
create table ordered_inv_bkp as select * from ordered_inv ;
alter table ordered_inv_bkp add constraint ordinvb_pk primary key (ordinv_id) ;
--
create unique index ordinv_scinv_uix on ordered_inv_bkp(
SCP_ID ASC,
[code]....
-- Next, we have to create a partitioned table ORDERED_INV_T with a similar
-- structure as ORDERED_INV.
-- This is a bit tricky, and involves a pl/sql code
declare
l_dt_start DATE;
l_ptn VARCHAR2(50);
cnt PLS_INTEGER;
l_cnt_initial PLS_INTEGER;
ts_name VARCHAR2(50);
l_sql VARCHAR2(10000);
ts_indx VARCHAR2(100);
[code]....
-- Add section to set default values for the intermediate table OL_ORDERED_INV_T
FOR crec_cols IN (
SELECT u.column_name ,u.nullable, u.data_default,u.table_name
FROM USER_TAB_COLUMNS u WHERE
u.table_name ='ORDERED_INV' AND
u.data_default IS NOT NULL )
LOOP
[code]....
-- Next, use exchange partition for actual swipe
-- Between ordered_inv_t and ordered_inv_bkp
-- Analyze both tables : ordered_inv_t and ordered_inv_bkp
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'HENRY220', TABNAME => 'ORDERED_INV_T');
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'HENRY220', TABNAME =>'ORDERED_INV_BKP');
END;
/
SET TIMING ON;
[code]....
What is the scenario, we should use the new feature of 11g called-Pending Statistics.
Eg:-
EXEC DBMS_STATS.set_table_prefs ('SCOTT', 'EMP', 'PUBLISH', 'false');
I need to exclude a single schema from the autostats gathering feature in 11g. The tables in this schema are analyzed at the appropriate time via the application code. The autostats gathering job sometimes kicks in at a time in which the tables are getting updated or loaded which can skew explain plans during the updates/inserts.
I've searched through the oracle documentation and cannot find a way to simply "exclude" the schema without locking it. I see it is possible to disable the autostats at the entire db level but not at the schema level.
I want to secure files that I saved in xml db with TDE, but tablespace encryption(tde) is not supported on the sysaux tablespace.
Is there any way to move the tables where XML DB saves data to another tablespace where TDE is enabled?
I am running oracle Apps 11.5.10.2, trying to enable duplex printing for all PDF / XML reports.
View 1 Replies View RelatedI have added a bitmap image in my workbook but when i am exporting it into excel or HTML ,only text part of the title is exporting into excel file . The bitmap is only visible in discoverer workbook ,after exporting to excel or HTML, it disappears,
i am using oracle 9i discoverer version 9.0.2.0.
We have 10g physical standby set in our environment and we are migrating to 11g now. We want to use the active data guard feature of 11g to run the live reports on standby rather production. Questions I have is:
1) On our current 10g standby environment, we use db_name=cusms which is exactly matching with the production database name. I don't see we are using database_unique name on our standby. But I have read several blogs where everyone talks about using db_unique name on standby and db_name can be exactly matching with production on 11g. I wanted to know, is db_unique name a new requirement to have on 11g? can I go ahead and not use db_unique name and just have db_name exactly matching with production? What are the implications of doing so? The reason we want to stick to this is in-case of failover we want the database name to be the same. But I want to hear your thoughts on this:
2)While building standby, I did noticed few things and want your clarifications:
a.On standby database, should I mount instance using pfile or spfile or it doesn't matter?
b. Lets say if I use either spfile or pfile, can I just have db_unique name in that file and just start the instance in no mount and do the duplicate from rman?
c.As soon as my duplicate target database for standby from active database got finished, I usually exit the rman session and go to sqlplus and shutdown the standby instance. (Is this ok to do)
d.Then I start the standby instance with startup (mount and open the database) this should open the standby database in read only mode. Following I issue alter database recover managed standby database using current logfile disconnect to put the database in recovery mode. (any steps missing here)
e.Then go to primary and do few log switches and come back to standby to see if the primary changes moved to secondary or not.
But what I have observed is:
a. When I do the duplicate it runs successful. But during the course of duplicate, primary system generates few archives which are not shipped or applied on standby. When I go to standby to recover the database, it says media recovery needed and ask for archives files. I need to manually move this files from primary to standby to apply. Isn/t this automatically taken care?
b. I also noticed after I can not open the standby database in read only mode after the duplicate command. While trying to open, it says database media recovery needed. What's the best procedure to open the database in read only mode immediately?
c. On my standby init.ora lets say if I use db_unique name, where would my control file be place? Will oracle create controlfile from primary and put it on my standby database and put an update an entry into my pfile or spfile?
Since tablespaces can be enabled/disbaled for flashback is there a query that can tell me the flashback status of each tablespace in my DB.
View 2 Replies View Related
