Drop Index Tablespace - File Is Non-empty
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
ADVERTISEMENT
May 10, 2011
While I was exporting ( schema level ) ,I am getting following errors :
vaughn$ exp parfile=qar808par.txt
Export: Release 11.1.0.7.0 - Production on Tue May 10 10:52:34 2011
Copyright © 1982, 2007, Oracle. All rights reserved.
Username: system
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
Export done in WE8ISO8859P9 character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)
About to export specified users .
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user TOXFIRE_TO_TSFDA
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user TOXFIRE_TO_TSFDA
About to export TOXFIRE_TO_TSFDA's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
EXP-00056: ORACLE error 25153 encountered
ORA-25153: Temporary Tablespace is Empty
EXP-00000: Export terminated unsuccessfully
'Parfile' contents :
owner=TOXFIRE_TO_TSFDA
file=exp_TOXFIRE_TO_TSFDA_QAR808.dmp
consistent=y
direct=y
recordlength=65535
log=exp_TOXFIRE_TO_TSFDA_QAR808.log
What is the reason, and It is saying that temporary tablespace is empty ( temp tablespace have almost free space ) why it is saying like that? And users default temp tablespace is that tablespace only ( database have only on temp tablespace
View 1 Replies
View Related
Apr 3, 2012
How to find out who has dropped and when was the index created in database?
View 39 Replies
View Related
Nov 9, 2012
i tried creating an context index. But it is aborted in the middle. do to reuse the spce i want to drop the index. But it shows an error that " cannot drop index marked as process" how to delete the index.
View 1 Replies
View Related
May 18, 2011
How to drop an index which has a constraint on the table?
while droping the index on the table wheather i should remove the
constraint or disable the constraint or without droping the
constraint
View 1 Replies
View Related
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
Oct 9, 2013
My db version: Oracle 11g I have an empty csv file.I created a external table for the empty csv file.When I run:select count(*) from externaltblname;It returns 1. It should return 0 right. In the definition, I specified "SKIP 1"But still it returns 1. When I use this external table to load into a target table. It loads a single row with null values.How to fix this.
View 2 Replies
View Related
Apr 14, 2010
I have text file with data in UNIX. I want to clear only records(make empty the file) using UTL_FILE..
I know we can remove the text file from UNIX and create file again with same name...
Is there any other way??
View 5 Replies
View Related
Apr 24, 2013
I ran into an issue in a project where a function is recreating an index-organized table by doing:Table Structure:
CREATE TABLE table_iot(
...)
ORGANIZATION INDEX
OVERFLOW ...;
Recreate Steps:
1) Populate global temporary staging table (gtt) with data
-- where gtt is staging for target index-organized table (iot)
2) Lock the target index-organized table (iot)
3) Copy old iot data to gtt
-- gtt now contains old and new data
4) Create new index-organized table (iot2) from gtt
-- iot2 now contains old and new data
[code]...
Because index-organized tables are primarily stored in a B-tree index, you can encounter fragmentation as a consequence of incremental updates. However, you can use the ALTER TABLE...MOVE statement to rebuild the index and reduce this fragmentation.The following statement rebuilds the index-organized table admin_docindex:
ALTER TABLE admin_docindex MOVE;
View 3 Replies
View Related
May 30, 2011
how to get rid of the first EMPTY line generated by SQL*Plus when spool'ing to a file.
I tried using set pagesize 0 and while that get rid of the empty blank line, it also disabled the heading ...
Currently using sed as a work around. But unfortunately, can't use sed on Windows?
set pagesize 200
set heading on
set trimspool on
set lines 200
set verify off
set echo off
set feedback off
[code].......
As mentioned, using pagesize 0 remove the blank line but I also lost the heading.
View 4 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
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
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
Aug 21, 2013
when going through some white papers i find athing which made me confusing it stats that dropping a database will also drop the sp file.
is it true
sp file is important is n't it
View 1 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
Oct 23, 2012
I'm trying to increase the size of my system tablespace. The tablespace contains one datafile size 900MB and it can increase to 2GB.
I'd like to change this to autoextend as I'm going to run csscan on the database.
When I do change the autoextend and unlimited part of the file I get: ORA-03297: file contains used data beyond requested RESIZE value
I've also tried to increase the datafile to 4GB but still the same. I can see that there is more then enough space on the filesystem.
Oracle Enterprise edt 10.2.0.5
View 8 Replies
View Related
Jun 16, 2011
I am working on tuning the performance of one of the concurrent request in our 11i ERP System having database 11.1.0.7
I had enabled oradebug trace for the request and generated tkprof out of it. For below query which is taking time , I found that , in the trace generated , wait event is "db file sequential read" on an PO_LINES_N10 index but in the generated tkprof , for the same below query , the full table scan for PO_LINES_ALL is happening , as that table is 600 MB in size.
Below is the query ,
===============
UPDATE PO_LINES_ALL A
SET A.VENDOR_PRODUCT_NUM = (SELECT SUPPLIER_ITEM FROM APPS.IRPO_IN_BPAUPDATE_TMP C WHERE BATCH_ID = :B1 AND PROCESSED_FLAG = 'P' AND ACTION = 'UPDATE' AND C.LINE_ID =A.PO_LINE_ID AND ROWNUM = 1 AND SUPPLIER_ITEM IS NOT NULL),
LAST_UPDATE_DATE = SYSDATE
===============
Index PO_LINES_N10 is on the column LAST_UPDATE_DATE , logically for such query , index should not have got used as that indexed column is not in select / where clause.
Also, why there is discrepancy between tkprof and trace generated for the same query .
So , I decided to INVISIBLE the index PO_LINES_N10 but still that index is getting accessed in the trace file .
I have also checked the below parameter , which is false so optimizer should not make use of invisible indexes during query execution.
SQL> show parameter invisible
NAME TYPE VALUE
----------- optimizer_use_invisible_indexes boolean FALSE
View 5 Replies
View Related
Jul 6, 2010
I was wondering if there is any way to know in which Tablespace and Datafile my Table is located. I have exported a table and about to delete it as i am partitioning it.
View 9 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
Jul 21, 2011
Once a year in the application we have a specific query that gets used a lot. It's an UPDATE that updates a single record in a single table with a few different datatypes, but the issue is happening with one of the VARCHAR2 fields. It updates one VARCHAR2(2000) and three VARCHAR2(4000) fields at the same time.
This year, 9 of the 95 times it was used resulted in one of the VARCHAR2(4000) fields as null in the database. The users would not want this field to be null and 5 of the 9 have told us they entered something (the form they're filling out is a research proposal and leaving this field empty would be pointless because it's part of the funding request, so they're not doing it). The application isn't doing it because it's not consistent. I've checked the application and these fields can't be nulled any other way.
We just found the issue so I looked back over the past years back to 2005. Last year it didn't happen at all. In 2010 it happened a handful of times. Some years there were even more times. It's not always the same field but it's always a VARCHAR2 of at least 2000 characters.
I have a lot more information but it's all just details (let me know if you need to know more). I'm wondering if there is a bug in 10g with these types of fields. I don't believe it's malicious behavior on an individual's part but I suppose that's always possible.
how to research something like this. I tried to get access to Oracle Support and the Knowledge Base I heard they have but it doesn't look like I can do that
View 2 Replies
View Related
Mar 23, 2009
I have a query that will either return one record or zero records. When it returns zero records I want to replace my attributes with a sentinel, like 'N/A'. I tried the CASE statement but couldn't get anything to work
Sample (does not work):
select
(case when exists (select product from tbl_product where productid = '123') then product else 'N/A' end) product
from tbl_product
where productid= '123';
If one record exists it should produce: 'My Widget' (or whatever)
If zero records exist it should produce: 'N/A'
View 3 Replies
View Related