All Index To Be Created In Different Tablespace Like Medium_index
Dec 27, 2011
I have created queue table using below script
BEGIN
SYS.DBMS_AQADM.CREATE_QUEUE_TABLE (
QUEUE_TABLE => 'scott.NG_MSG_QUEUE_TBL',
QUEUE_PAYLOAD_TYPE => 'SYS.AQ$_JMS_TEXT_MESSAGE',
COMPATIBLE => '10.0.0',
[code].......
Indexes
--------
INDEX_NAME TABLESPACE_NAME
------------------------------ --------------------------
AQ$_NG_MSG_QUEUE_TBL_T MEDIUM_DATA
AQ$_NG_MSG_QUEUE_TBL_I MEDIUM_DATA
SYS_IL0001100359C00037$$ MEDIUM_DATA
SYS_IL0001100359C00040$$ MEDIUM_DATA
SYS_IL0001100359C00041$$ MEDIUM_DATA
SYS_C0073180 MEDIUM_DATA
I want all the index to be created in different tablespace like 'medium_index. can we specify tablespace for index when we are creating queue table.
View 1 Replies
ADVERTISEMENT
Jun 30, 2011
how to know weather procedure is working or not in a database ..
how to know index is created on table or not.
View 2 Replies
View Related
Apr 13, 2012
i have created a new tablespace and assigned it to new user. What are the minimum no. of grants i should give, so that the user can made any kind of changes to all the objects within the tablespace and cant access other tablespace.
View 3 Replies
View Related
May 17, 2011
what my issue is with this virtual column
CREATE TABLE C0HARPA.VCOL_TAB
(
col1 VARCHAR2(30 BYTE),
v_col1 VARCHAR2(6) GENERATED ALWAYS AS (SUBSTR(col1,1,6)) VIRTUAL
)
TABLESPACE TOOLS
[code]...
Lastly, can an index be created on a virtual column? I am thinking no since it calculated?
View 3 Replies
View Related
Mar 7, 2011
I am attempting to perform a TSPITR on a newly created tablespace. The error indicates that the tablespace is not in the recovery catalog in which case it is. The database is Oracle 11g on Win2k8 64bit enterprise edition.
List of Datafiles in backup set 52
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 3959509 06-MAR-11 +DATA/orcl/datafile/system.257.742678049
2 Full 3959509 06-MAR-11 +DATA/orcl/datafile/sysaux.258.742678049
[code]...
View 4 Replies
View Related
Feb 25, 2010
DT1 is a column of date datatype and there is no index created on the table
I want to add the below lines in the sql
TO_CHAR(DT1,'YYYY') BETWEEN '2005' AND '2009'
Which one should I use in where condition to query and why?
1. TO_CHAR(DT1,'YYYY') BETWEEN '2005' AND '2009'
2. DT1 BETWEEN '01/01/2005' AND '31/12/2009' (as NLS date format will not change)
3. DT1 BETWEEN to_date('01/01/2005','dd/mm/yyyy') AND ('31/12/2009' ,'dd/mm/yyyy')
View 6 Replies
View Related
Jul 30, 2010
SELECT department_id
FROM (SELECT department_id
FROM employees
UNION
SELECT department_id
FROM employees_old )
WHERE department_id=100;
[code]....
The index has been created on both depart_id for the two tables. The only difference between the two I observed was the 1 recursive call for the 1st sql.and also, one additional view in the plan.There is a little difference in bytes sent over the network.
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
[code]....
Is there any performance impact you find in those above two sqls if you compare?
View 14 Replies
View Related
Mar 10, 2011
Well, I have a oracle database 10g and the tablespace INDX was getting up to 32 GB size. Now I added second datafile to the space, but can I shrink this space? In my view this space is responsible for indexes, right? There is a command to rebuild the indexes or there's another trick?
View 1 Replies
View Related
Feb 11, 2011
Aim was to shrink datafile and got "ORA-03297: file contains used data beyond requested RESIZE value". Datafile size 7GB but used 128MB.
So created new Tablespace and moved all tables and index partitions to new TS. Now I see some entries in USER_INDEXES, which I need to move to new TS. But they are IOT's.
INDEX_NAMEINDEX_TYPETABLE_OWNERTABLE_NAME
SYS_IOT_TOP_1082946IOT - TOPDSI_DPIPESYS_IOT_TRNS_1082943
SYS_IOT_TOP_1082949IOT - TOPDSI_DPIPESYS_IOT_TRNS_162982
how to move these indexes to new TS.
View 28 Replies
View Related
Aug 27, 2010
Is there a way to find the tablespace used by an index?
View 4 Replies
View Related
Sep 3, 2010
I have already created large no of indexes on my Database without specifying specific tablespace, now I would like to move all the created indexes into particular tablespace.
View 3 Replies
View Related
Apr 14, 2013
How to saperate inndexes and tables in two different tablespcae.......???
View 2 Replies
View Related
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
Oct 15, 2012
db 10.2.0.4
AIX 5.2
I am trying to drop a index tablespace I moved all indexes to new tablespace there is no indexes in that tablespace even any objects but still it is not dropping.it is giving error as below.
SQL> drop tablespace ret_index including contents;
drop tablespace ret_index including contents
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-02429: cannot drop index used for enforcement of unique/primary key
SQL> alter tablespace ret_index drop datafile '/path/index3.dbf';
alter tablespace ret_index drop datafile '/path/ret_index3.dbf'
*
ERROR at line 1:
ORA-03262: the file is non-empty
try to resize
SQL> alter database datafile 4 resize 100m;
alter database datafile 4 resize 100m
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value
Tablespace size is 280gb
View 32 Replies
View Related
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
Feb 14, 2013
Is there a way to find when was a database role created and who has created?
View 5 Replies
View Related
Aug 17, 2012
tell me if a REFRESH GROUP is automatically created when a materialized view is created?
View 3 Replies
View Related
Jan 18, 2012
How to import dump into specific tablespace instead of default tablespace users.
I want to import my dump file to newly created tablespace ,so how can i do that . I have created new user called cvm and while creating it i mentioned default tablespace to newly created tablespace . But when i try to import my dumo file it goes to users tablespace .
View 2 Replies
View Related
May 27, 2011
i have a tablespace which contains 121 datafile(max limit reached) as a dba what we have to do?
creating a new tablespace with a datafile and assign the users to the current tablespace which i created now.iif the above process is correct,after some time the tablespace which was filled up got freed up.now can i give the access to the users previous (i.e. freed up tablespace) and current tablespaces
View 9 Replies
View Related
Jan 26, 2011
My database version is
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE 11.1.0.7.0 Production
TNS for Linux: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production
My os version is
Linux damdat01 2.6.18-128.7.1.el5 #1 SMP Wed
Aug 19 04:00:49 EDT 2009 x86_64 x86_64 x86_64
GNU/Linux
My database is OLP system.
My question is what are the advantages and disadvantages having one single tablespace versus multiple tablespace?
Easy to maintain when you have single tablespace. but hard to track the IO issues if you have one single tablespace.
View 7 Replies
View Related
Jun 27, 2012
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.
View 5 Replies
View Related
Jun 28, 2011
I have a huge table (about 60 gb) partition over range. The index on this table is global index created on 4 columns together. I have a query which is running very slowly. The explain plan is showing the use of this global index.Explain plan is not showing pstart and pend because the index is global.
View 6 Replies
View Related
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
Nov 29, 2010
I am facing the error "ORA-01502: index or partition of such index is in unusable state " while loading the text data using
sql loader with direct path (direct = Y ,rows = 10000) option. Table consists an composite non unique index. If I query the dba indexes for the effected index it shows the index status as VALID. There was no maintaince done on the effected table or index. I have tried loading the same data using conventional path but didn't found any issues for the same.
View 3 Replies
View Related
Nov 19, 2010
I have a query which had a join:
a.c1=b.c1 and a.c2=@var
where @var is user supplied input at runtime...We had a index on a.c2 . The CBO would use this index to generate an opitimised query plan.We found some records from table "b" were dropping due to inner join. So we made a change in join. It'd be like
a.c1(+)=b.c1 and nvl(a.c2,@var)=@var
This query is no longer using the index, instead its doing a full table scan causing the query to slowdown.I have tried creating index on nvl(a.c2,'31-dec-9999')
But the CBO won't use it.Anyway to create index on this col so that full table scan can be avoided?
View 2 Replies
View Related
Aug 9, 2013
How to force an index if the table not using the index?
View 10 Replies
View Related
Aug 27, 2012
We have occurrences of enq : TX - index contentions in the database. Using the SQL ID, we have identified the INSERT statement and the table which they are trying to insert.
This table has almost 25 different indexes, some of which are unique as well.I am wondering how to identify the actual index causing issue, out of these 25 indexes.
Is there any way to pin point to the name of index which is causing the lock?My plan is, once the index is identified, I would like to check the extents and inittrans and other attributes of this index to fix.
View 5 Replies
View Related
Jul 11, 2012
Can we create non-cluster index on a clustered index?
View 5 Replies
View Related
Jan 6, 2012
I have one database which is recently upgraded from oracle 8.1.5 to oracle 10.2.0.4.The database is having around 300 tablespace and total size of the database is 1.5 TB.
The database was created in oracle 8i and all the teblespace were DMT(Dictionary Managed Tablespace) .Usually after up gradation all the tablespace are in DMT mode. Now my requirement is to convert all the tablespace into LMT (Locally Managed Tablespace) so that I can AVAIL ALL THE FEATURES OF LMT.
This database is a mission critical database and very less downtime can be allowed.
View 6 Replies
View Related
Aug 3, 2010
Any on give explanation for difference between Index and Clustered Index?
It will be great if i get explanation how memory allocation and Execution takes place.?
View 4 Replies
View Related
