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
ADVERTISEMENT
Truncate / 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)?
SQL & 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 :: How To Undo A Drop Table Operation
Feb 24, 2013How do I perform Undo a drop table operation?
View 12 Replies View RelatedSQL & 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 RelatedServer Administration :: Unable To Drop User - Table Or View Does Not Exist
Jun 6, 2011Im facing the problem whenever I try to drop a user. Following thing that I m trying..
system@vahan> drop user knp cascade;
Error at line 1:
ORA-00604: error occured at recursive SQL level 1
ORA-00942: table or view does not exist
ORA-06512: at line 7
PL/SQL :: Index-Organized Table Truncate Vs Lock / Stage / Drop / Recreate?
Apr 24, 2013I 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;
SQL & PL/SQL :: ORA-02303 - Cannot Drop Or Replace A Type With Type Or Table Dependents
Feb 1, 2012i am trying to run a script in which a command tries to create or replace a type.
i get this error:
ORA-02303: cannot drop or replace a type with type or table dependents
SQL>
SQL> --create a test user:
SQL>
SQL> create user tuser identified by tuser
[Code]....
Table created.
SQL>
SQL> --then change the type:
SQL>
SQL> create or replace type t1 as object (obj_type number(15))
2 /
create or replace type t1 as object (obj_type number(15))
*
ERROR at line 1:
ORA-02303: cannot drop or replace a type with type or table dependents
SQL>
SQL> --if i'll do FORCE action on the type - it'll corrupt my depandant table:
SQL>
SQL> drop type t1 FORCE
2 /
Type dropped.
SQL>
SQL>
SQL>
SQL>
SQL> desc dpntnt_table
Name Null? Type
----------------------------------------- -------- ----------------------------
ID1 NUMBER(7)
SQL>
SQL>
SQL>
SQL> --if i re-create it - my table is still corrupted:
SQL>
SQL>
SQL> create or replace type t1 as object (obj_type number(15))
2 /
Type created.
SQL>
SQL>
SQL>
SQL> desc dpntnt_table
Name Null? Type
----------------------------------------- -------- ----------------------------
ID1 NUMBER(7)
SQL>
--if i re-create it - my table is still corrupted:
create or replace type t1 as object (obj_type number(15))
/
desc dpntnt_table
[/code]
1. If i'll do drop type FORCE what will happen to the dependent object(might be a table for example) ?
2. I understand that this type is already assigned to some object, but i can't seem to find out which one.
how do i find out which object is depending on the type i want to create or replace?
SQL & PL/SQL :: Cannot Drop Or Replace A Type With Type Or Table Dependents
May 12, 2011When i tried to drop a type using below command , i received errors
DROP TYPE JAM_ACAS_MSG_TYPE
ERROR at line 1: ORA-02303: cannot drop or replace a type with type or table dependents
Sum Of All Drop Down Values
Nov 28, 2012I have a dropdown in my jsp with all users from database. When I select a user I'm able to get the details of the corresponding result, but when I click on all it should give the total individually. Below is the code that I use to get individually.
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@include file="DBCon.jsp" %>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<meta charset="utf-8" />
[code]...
How To Drop Column
Aug 25, 2010i am tring to drop column but it is taking much time . And there is not locking session also. The table size is 500GB. We have any way to drop to column in fast?
View 2 Replies View RelatedPL/SQL :: Drop All Procedures At One Go?
Aug 29, 2012I have a schema sys_eg .I dont have any DBA privileges .How can i delete all the procedure with one sql or pl/sql.
ORACLE 11G
I am using sql developer 3.2 version tool
Oracle 11.2.0.2 - How To Drop Partition
Apr 19, 2012we have our production database running on 11.2.0.2. I have a user table which has partitions. i would like to drop the partition and seeing weird issue:
The command to drop the partition i have is:
alter table SA.accounts drop partition dec_20111203 update global indexes;
Say after 10 minutes, the session is terminated and we are seeing the following: ORA-00028. We don't have a process that kills the session so we are trying to find the root cause why the session is being killed. what might be going on? also as an alternative - i was looking to do the following and want to know - if this works or not:
ALTER TABLE sa.accounts TRUNCATE PARTITION dec_20111203;
Then try to do the drop partition again.
SQL & PL/SQL :: Determine Index Drop?
Apr 3, 2012How to find out who has dropped and when was the index created in database?
View 39 Replies View RelatedDrop All Tables In Oracle 8
Dec 17, 2012i want drop all tables in oracle8
View 2 Replies View Related