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
ADVERTISEMENT
PL/SQL :: Unable To Drop Unused Column?
Nov 14, 2013SQL> SELECT * FROM DBA_UNUSED_COL_TABS; OWNER TABLE_NAME COUNT ------------------------------ ------------------------------ ---------- CRIS_WAREHOUSE_USER F_TRADING_COLLATERAL_SR 1 SQL> SELECT * FROM DBA_UNUSED_COL_TABS; OWNER TABLE_NAME COUNT ------------------------------ ------------------------------ ---------- CRIS_WAREHOUSE_USER F_TRADING_COLLATERAL_SR 1 SQL> alter table F_TRADING_COLLATERAL_SR drop unused columns; Table altered. SQL> SELECT * FROM DBA_UNUSED_COL_TABS; OWNER TABLE_NAME COUNT ------------------------------ ------------------------------ ---------- CRIS_WAREHOUSE_USER F_TRADING_COLLATERAL_SR 1 SQL>
View 6 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.
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?
ORA-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.
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]...
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 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 RelatedSQL & PL/SQL :: Drop Partitions With Zero Rows
Sep 26, 2011Query to drop partitions on a table who have no.of rows as zero.
select 'ALTER' || '' || 'TABLE' || TABLE_NAME || 'DROP' || 'PARTITION' || PARTITION_NAME from dba_tab_partitions where TABLE_OWNER='xyz' ;
select count(*) from table_name partition (partition_name);
SQL & PL/SQL :: Unable To Drop A Trigger?
Jun 14, 2011I have a trigger called DDL_TRIG which is used to audit all DDL level operation on the Database. The trigger script is as below:-
CREATE OR REPLACE TRIGGER ddl_trig
AFTER DDL
ON DATABASE
BEGIN
INSERT INTO ddl_log
(user_name, ddl_date, ddl_type,
[code].....
The Table DDL_LOG exists.Now i want to disable or drop this trigger and it wont allow us. While doing on one schema it fails and with below error:
when logged in schema1 and give drop or disable command it fails
ORA:04098:trigger schema2.ddl_trig is invalid and failed re-validation.
when logged in schema2 and give drop or disable command it fails
ORA:04098:trigger schema1.ddl_trig is invalid and failed re-validation.
how can we drop this trigger in both the schema's. This trigger is not allowing us to do any activity on the Database and causing lot of problem.
SQL & PL/SQL :: Create A Drop-down Field?
Oct 16, 2013i want to create a dropdown field it will display list of currencies for selection derived from
sy_curr_code (columns currency_code and des1)
in the s_sales_center(table )
-If INF_SRC_DB = AUS, will default to USD, otherwise default to BPS.
i have created this so far
vSQl := 'select toValueText(a.code1, a.descr) from (select currency_code code1, DES1 descr1 '||
'from sy_curr_code where active = ''Y'' '||
'and subcat1 = ''Y'' '||
') a ';
how to add the other logic into my code
SQL & PL/SQL :: Unable To Drop A Procedure?
Nov 29, 2012I tried to Drop a procedure we have created, but an error is raised while droping it.
ora-04043 object does not exist
PL/SQL :: Unable To Drop A Broken Job
Jan 3, 2013i want to drop a broken job but it is taking long time why?
i am using below syntax:
begin
dbms_job.broken(job=> 345,FALSE);
commit;
end;
/
How To Drop More Than One User At Time
Nov 3, 2012in one short i want to drop more than one user?
View 6 Replies View RelatedPL/SQL :: Drop Context Index
Nov 9, 2012i 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 RelatedXE :: How To Drop User And Schema
Aug 31, 2012I want to free up some space , as i have created some testing users and schema.
View 3 Replies View RelatedHow To Monitor Drop User
Jan 31, 2013How to monitor the DROP USER command?
OS: AIX, Oracle: 11.2.0.2
Drop Or Disable A Scheduled Job
Oct 11, 2012I have a job running under HUMADM user and would like disable or drop it.I do not have the login credentials of HUMADM user but can log into the database as system/sys user.
When I run the below commands as sys/system user it says humadmjob must be a job or it is not running.This is because it is looking for the job under sys/system user.
exec DBMS_SCHEDULER.drop_job (job_name => 'humadmjob');
exec DBMS_SCHEDULER.stop_job (job_name => 'humadmjob');
SELECT owner, job_name, job_class, enabled FROM dba_scheduler_jobs;
OWNER JOB_NAME JOB_CLASS ENABLED
====== ========= ======= ======
HUMADM HUMADMJOB DEFAULT_JOB_CLASS TRUE
how to stop/disable this job? My oracle version is Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bi
Unable To Drop Procedure
Oct 29, 2013i'm issuing drop procedure generatePasswordForEportalUser
i have an
error ORA-00600: [kqlobjlod-no-result-from-proc$], [1403], [659858], [], [], [], [], [], [], [], [], []. i found on metalink then Doc ID 1267554.1
is about this error i'm using oracle 11.2.0.3 in this document there is an info that 11.2.0.2 solves the issue Interesting thing is that select * from user_procedures where upper(object_name) like upper ('generatePasswordForEportalUser');
generatePasswordForEportalUser6598581PROCEDURENONONONONODEFINERobject name is in lovercase it was created by create procedure generatePasswordForEportalUser asbeginnull;end ;without " name " why it is in lowercase in data dictionary
Fast Method To Drop Many Users
Oct 21, 2010I have 2 tests Oracle instances (a 10g and a 11g) in which somebody added many users (almost 50k), every user having a TEST table with no rows in its schema.
I have to drop these users without recreating the instances, but DROP USER username CASCADE is taking almost 1 minute for each user. This will take me to almost a month.
Drop Tempfile Without Bouncing Instance?
Jul 4, 2012we have lost a Tempfile due to SAN problem ( the temp tables has 7 tempfiles ) Problem is that some application servers have still that tempfile available since they are connected since a longteime and therefore some users receive an error while trying to use it .
I just wanted to drop it from the system dictionary but i can't i always receive and error ( datafile cannot be dropped at this time)
and i can't see what session is using it, that query wouldn't be useful since i have 7 datafiles and it's based on Tablespace name only .
How can i drop that tempfile without bouncing the instance ?
I'm running Oracle 10.2.0.4 on windows 2003
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!
Server Administration :: How To Drop Datafile
May 25, 2012I can not drop datafile in a tablespace, how can i do?
SQL> Alter Database Datafile '/u02/app/oracle/oradata/oracl/hxl06.dbf'
Offline 2
3 /
Database altered.
SQL> Alter Tablespace tps_hxl
Drop Datafile '/u02/app/oracle/oradata/oracl/hxl06.dbf'; 2
Alter Tablespace tps_hxl
*
ERROR at line 1:
ORA-03264: cannot drop offline datafile of locally managed tablespace
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"