SQL & PL/SQL :: How To Undo A Drop Table Operation
Feb 24, 2013How do I perform Undo a drop table operation?
View 12 Replies
ADVERTISEMENT
Undo Segments In Undo Table Space
Nov 29, 2012Can we find out no of undo segments in undo Table space ? If so , how to find? what's their max limit ?
View 9 Replies View RelatedORA-39726 / Unsupported Add / Drop Column Operation On Compressed Tables
Aug 8, 2011I am getting ORA-39726 error when a dropping a column on a non-compressed table.
SQL> select distinct TABLE_OWNER,COMPRESSION,COMPRESS_FOR from dba_tab_partitions where TABLE_NAME='STM_AE_STMT_HISTORY_DETAIL';
TABLE_OWNER COMPRESSION COMPRESS_FOR
------------------- ---------------------- -------------------
APP_SU DISABLED
SQL> ALTER TABLE APP_SU.STM_AE_STMT_HISTORY_DETAIL DROP COLUMN RATE_DESCR;
ALTER TABLE APP_SU.STM_AE_STMT_HISTORY_DETAIL DROP COLUMN RATE_DESCR
*
ERROR at line 1: ORA-39726: unsupported add/drop column operation on compressed tables
Server Administration :: ORA-39726 / Unsupported Add / Drop Column Operation On Compressed Tables
Jan 11, 2012my user is trying to drop columns, but she gets below error:
SQL Error: ORA-39726: unsupported add/drop column operation on compressed tables
39726. 00000 - "unsupported add/drop column operation on compressed tables"
i just checked whether table is compressed or not, it is not compressed it seems:
select owner, table_name,COMPRESSION,COMPRESS_FOR from dba_tables
2 where owner = 'EQUIPMENT' AND TABLE_NAME = 'ETHRNT_VRTL_CNXN_HRLY_AGG_SWP';
OWNER TABLE_NAME COMPRESS COMPRESS_FOR
------------------------------ ------------------------------ -------- ------------
EQUIPMENT ETHRNT_VRTL_CNXN_HRLY_AGG_SWP DISABLED
1 row selected. i am able to set one column as UNUSED. and then i am able to see the count accordingly from below view:
select * from DBA_UNUSED_COL_TABS
where owner ='EQUIPMENT' and table_name = 'ETHRNT_VRTL_CNXN_HRLY_AGG_SWP';
but not able to drop the unused columns. if i tried to drop a column directly from the table, that also giving above error.
Server Administration :: Sizing Undo Tablespace And Setting Undo-retention?
Jan 30, 2004regarding sizing undo tablespace and undo_retention parameter.we have to implement the database in production system with 40 users but how much space should be allocated to undo tablespace is there any propotions related to virtual memory and the
parameter.i have gone thru oracle doc's and some related sites.its an ERP aplications that contains 20 modules .I am an new one to this dba level
SQL & PL/SQL :: Undo Dropped Column From Table
Jul 13, 2013Flashback query is working fine for dropped table,but throwing error when trying to get back the previous date by dropping a column from a table.
FLASHBACK TABLE emp TO TIMESTAMP TO_TIMESTAMP('2013-07-13 05:00:00', 'YYYY-MM-DD HH24:MI:SS')
SQL Error: ORA-01466: unable to read data - table definition has changed
Is that a limitation of Oracle by not having query to flashback a column but a table.
Alter Table Fails - Ran Out Of Undo Space?
Jun 11, 2009I want to alter a very large table.
ALTER TABLE MYTABLE ADD
(
ENTRY_TSTMP DATE DEFAULT SYSDATE NOT NULL
)
My table is very large and I am getting an error saying I am out of undo space.
The dba says the undo space is as big as the table.
Undo Table Space Not Working - REDO Being Generated?
Feb 14, 2013I am using Oracle 10.2.0.3. Since yesterday i am seeing a session with sid 1160 using undo tablespace but not able to find how much it is using .I need to know which session and from which module and how much is the Undo being used by those sessions. I have tried searching but all the queries provide me with some different results each time.
Also i need the same information for REDO being generated .
SQL & PL/SQL :: All DML Operation Can Be Done In DUAL Table?
Apr 29, 2010What are all the DML operation can be done in DUAL table.?
View 5 Replies View RelatedTruncate / Drop Partitions In Table Having Nested Table Columns
Sep 7, 2012I have a table that has 2 columns of type nested table. Now in the purge process, when I try to truncate or drop a partition from this table, I get error that I can't do this (because table has nested tables). how I will be able to truncate/drop partition from this table? IF I change column types from nested table to varray type, will it work?
Also, is there any short method of moving existing data from a nested table column to a varray column (having same fields as nested table)?
Server Administration :: Flashback Table Operation For The Sys User
Jun 7, 2010Flashback table operations are not supported for the SYS user . I have found the above line in one of the article.
I don't know why the SYS user can not able to flash back the table.
Server Administration :: ORA-30967 - Operation Directly On Path Table
Sep 30, 2013While doing scanning of character set, error occurred in between.
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
Current database character set is AL32UTF8.
Enter new database character set name: > WE8MSWIN1252
Enter array fetch buffer size: 1024000 >
Enter number of scan processes to utilize(1..64): 1 >
Enumerating tables to scan...
. process 1 scanning REINS.RI_TD_EXECUTION_LOG[AAASwXAAAAABwkAAAA]
. process 1 scanning INS.SERVICE_TRANSACTION_LOG[AAAS5hAAAAADWiAAAA]
. process 1 scanning REINS.RI_TD_VOUCHER_DETAILS[AAASx+AAAAAC6KAAAA]
. process 1 scanning REINS.RI_TT_VOUCHER_DETAILS[AAAS0OAAAAAAMiAAAA]
. process 1 scanning CONFSYS.DML_AUDIT_LOG_FOR_DESIGNER[AAASnwAAAAAAsIAAAA]
. process 1 scanning REINS.RI_TD_TRANSACTION_LOG[AAASx0AAAAACsoAAAA]
[code]...
SQL & PL/SQL :: Drop A Table?
Apr 6, 2010When drop a table what happen to view,sequence,synonyms,index,constraint for the table
View 9 Replies View RelatedSQL & PL/SQL :: Privilege - Just Drop Special One Table?
Jul 3, 2013There two users a and b,and the table b.test_part.And one procedure under a ,text like below:
create or replace procedure a.sp_test
is
vs_sqls varchar2(32767);
begin
vs_sqls:='alter table b.test_part truncate partition p_day';
execute immediate vs_sqls;
end;
now,i have to grant drop any table to a.but in fact,i prefer to drop the special one table "b.test_partany" rather than any other table.how ? no by trigger!
SQL & PL/SQL :: Unable To Truncate / Drop Table
May 13, 2011I am trying to Truncate the table but it doesnt get truncated. When I issue the command it even doesnt throw the error. I also tried to drop the table but cant even able to drop the table. I thought table might be locked. But it allows me delete a row from the table.
I am using Oracle 11g Release 11.1.0.7.0
SQL & PL/SQL :: Drop Table With Special Character?
Jul 15, 2011I have created a table with space by mistake.how to drop that..?
QL> select * from cat where table_name like 'OM_DW_RTNORD%';
TABLE_NAME TABLE_TYPE
------------------------------ -----------
OM_DW_RTNORD? TABLE
OM_DW_RTNORD TABLE
[code]....
no rows selected
SQL> drop table OM_DW_RTNORD? purge;
drop table OM_DW_RTNORD? purge
*
ERROR at line 1:ORA-00911: invalid character
SQL & PL/SQL :: Drop Global Temporary Table?
Dec 6, 2011how to drop global temporary table?
while droping global temporary table we are getting below error
"ORA-14452: attempt to create, alter or drop an index on temporary table already in use"
SQL & PL/SQL :: How To Drop Queue Table From Database
Nov 26, 2011,how to drop the queue table from our database.when iam trying to drop the queue table aim getting this error
error dropping AQ$_DEMO_QUEUE_TABLE_G;
ORA-00942:TABLE OR VIEW DOES NOT EXIST
SQL & PL/SQL :: Drop Index Which Has Constraint On Table?
May 18, 2011How 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
PL/SQL :: Truncating Or Drop Table Is Faster?
Jul 8, 2012I have a table with 80 million records. Is dropping this large table is faster or truncating is faster?
View 6 Replies View RelatedDrop Table Response Time?
Jul 23, 2012I am working on 10.2.0.4 oracle version database of my production,
when executed a simple drop command , the total time it took is 26 secs. on Avg. the table is holding only 20 records. this is happening for the last few weeks , prior to that it took less than 0 secs.
05:22:58 SQL> drop table C$_100GL_INTERFACE ;
Table dropped.
Elapsed: 00:00:26.67
but on successive executions the elapsed time falls to 10-15secs.
And on the same test env, we are achieving the expected results less than 0 secs.
Drop Some Partitions In Table On Production Environment
Jul 8, 2011I have to drop some partitions in table on production environment (to get free space). The environment have to be continuously available. I was considering of use ALTER TABLE ... DROP PARTITION ... UPDATE INDEXES but it is slow, because of use clause UPDATE INDEXES. Is there another possibility to remove these data?
View 2 Replies View RelatedCompressed Table - Drop Unused Column
Feb 9, 2012what happens if you mark a column unused in a compressed table and then alter table drop unused columns? We had a customer do this and Oracle threw a -3113 (end of communication) error. They did a system restore before contacting us and blew away any evidence in alert logs/trace files. They did this on a 400GB compressed table.
My question is, when you drop an unused column off a compressed table, does it uncompress? Where does this uncompression occur? In the instances default tablespace? In the tablespace configured for the table?
Basically, we are wondering whether the error was due to poor error-handling of the system running out of space during decompression and trying to see if we can reproduce it. This was on an 11.1.0.7 system.
SQL & PL/SQL :: Create And Drop Table Inside The Procedure
Jun 10, 2013I've to create a table every time a procedure is run, initially the table should be dropped and then created every time. From this procedure, the table is neither created nor dropped.
I cant track the error. Why is it so?
CREATE OR REPLACE PROCEDURE TESTPROC IS
S_SQL VARCHAR2(1000);
BEGIN
S_SQL := 'DROP TABLE MYTEST PURGE';
EXECUTE IMMEDIATE S_SQL;
[code]........
Backup & Recovery :: Recover Drop Table
Dec 29, 2011How i can recover my drop table..i use user managed backup concept after dropping table i cant recover my table..how i use step that my dropped table recover.
View 12 Replies View RelatedDrop Statement On Table Taking Long Time
Sep 20, 2010We are firing a normal Drop command on our database and the database version is 10.2.0.4.The database is running on AIX v5.The command is taking more time than usual .
When i am monitoring the session i can see that a call is being made to procedure "aw_drop_proc".Could i ask you if this is something that is taking more time than usual.
We are not having any partitions on the nested tables .We have a pack of tables and we are dropping this pack through a procedure.The pack comprises of nested tables & normal tables.To drop a nested table it is taking around 6 seconds(Table with no rows) and a normal table(With no rows) it is taking 17 milli seconds.We have a partition on Normal table.
The same operation in windows is taking very less time when compared to AIX.
Server Administration :: How To Drop A Column In Huge Table
Aug 8, 2011I want to drop a column in a huge table which contain about 420,000,000 rows,i use the alter table drop coumn command to execute,and found it takes a long time and generate huge redo.
Is there any quickly way to drop a column in a huge table?
SQL & PL/SQL :: Unable To Drop Interim Table After Redefinition Process
May 11, 2012I performed the Table Redefinition process on one of my transaction tables. I wanted to redefine the existing table to a new table with partitions. Everything worked fine until i reached the last step where we had to drop the interim table. Now in between the redefinition , we created the interim table with partition along with the constraints and indexes and then atlast ran the "dbms_redefinition.finish_redef_table" proc to finish the redefinition.
And after that when I tried to drop the interim table , I am getting the below error message
"ERROR at line 1: ORA-02449: unique/primary keys in table referenced by foreign keys"
So this means all the constraints from my actual table were swapped with the interim table and hence I am getting this error.So is this a normal case or I have missed something while performing the redefinition? If thats normal then should i Just Drop table tablename CASCADE CONSTRAINTS?
Backup & Recovery :: RMAN - How To Recover Drop Table
Jan 3, 2012how i can recover my drop table using until time in rman scenario.
View 3 Replies View RelatedServer Administration :: Drop A Table Moved To Recycle Bin?
Jan 6, 2012I have a question about recyclebin.When i drop a table,the talbe will be moved to recyclebin,the name is changed to BIN$...,but the constraint built on the table aren't moved to recyclebin and their name are also BIN$...,why?
View 1 Replies View Related