Server Administration :: ORA-39726 / Unsupported Add / Drop Column Operation On Compressed Tables

Jan 11, 2012

my 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.

View 7 Replies


ADVERTISEMENT

ORA-39726 / Unsupported Add / Drop Column Operation On Compressed Tables

Aug 8, 2011

I 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

View 2 Replies View Related

Compressed Table - Drop Unused Column

Feb 9, 2012

what 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.

View 7 Replies View Related

Server Administration :: How To Drop A Column In Huge Table

Aug 8, 2011

I 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?

View 5 Replies View Related

Server Administration :: How To Check A Partition Have Been Compressed

Oct 20, 2011

How can i check a partition whether it has been compressed? just as flowing test,i can not get the information about partition P_L1 whether been compressed.

SQL> Select
2 aa.compression,
3 aa.partition_name
4 From dba_tab_partitions aa
5 Where aa.table_name = 'TB_HXL_LIST';

COMPRESS PARTITION_NAME
-------- ------------------------------
DISABLED P_L1
DISABLED P_L2
DISABLED P_L3
DISABLED P_L4

SQL> Alter Table tb_hxl_list compress;

Table altered.

SQL> Alter Table TB_HXL_LIST
2 Move Partition P_L1 compress;

Table altered.

SQL> Select
2 aa.compression,
3 aa.partition_name
4 From dba_tab_partitions aa
5 Where aa.table_name = 'TB_HXL_LIST';

COMPRESS PARTITION_NAME
-------- ------------------------------
ENABLED P_L1
ENABLED P_L2
ENABLED P_L3
ENABLED P_L4

View 5 Replies View Related

SQL & PL/SQL :: How To Undo A Drop Table Operation

Feb 24, 2013

How do I perform Undo a drop table operation?

View 12 Replies View Related

SQL & PL/SQL :: ORA-32033 / Unsupported Column Aliasing

Sep 8, 2012

with ele(sno,name,state) as(select sno,name,state from sankar)delete from ele where sno=1 select sno,name,state from sankar

View 2 Replies View Related

Server Administration :: ASM Rebalance Operation Never End

Sep 19, 2011

I had added a new disk into disk group DATA1 with rebalance power of 5, it ran as estimated for about 2 days long, I can see the estimated time down to zero but the process never end.

The command used:

SQL> alter diskgroup DATA1 add disk '<path>' rebalance power 5;

Below is the disk status:

SQL> select group_number, disk_number, total_mb, free_mb, mount_status, header_status, state
from v$asm_disk where group_number=1 order by disk_number;

GROUP_NUMBER DISK_NUMBER TOTAL_MB FREE_MB MOUNT_S HEADER_STATU STATE
------------ ----------- ---------- ---------- ------- ------------ --------
1 0 1191626 314236 CACHED MEMBER NORMAL
1 1 1191626 314230 CACHED MEMBER NORMAL
1 2 1191626 314232 CACHED MEMBER NORMAL
1 3 1191602 314229 CACHED MEMBER NORMAL
1 4 1191626 314210 CACHED MEMBER NORMAL
1 5 1191626 314218 CACHED MEMBER NORMAL
1 6 1191626 314218 CACHED MEMBER NORMAL
1 7 1191602 314223 CACHED MEMBER NORMAL
1 8 1191626 314216 CACHED MEMBER NORMAL
1 9 1191626 314202 CACHED MEMBER NORMAL
1 10 1191626 314230 CACHED MEMBER NORMAL
[code]....

I've checked and follow the status for a few days more, it still running with EST_MINUTES=0. I think it was hang and never end.During the next few weeks, I've manually restarted the rebalance process by modifying its power to 6, 8, 10

SQL> alter diskgroup DATA1 rebalance power 10;

After every restart, the diskgroup rebalance again but has the same issue, never end.Here is the content in the alert log

NOTE: starting rebalance of group 1/0x9d2529a0 (DATA1) at power 5
Starting background process ARB0
Fri Sep 16 12:01:11 2011
ARB0 started with pid=15, OS id=21066
Starting background process ARB1
Starting background process ARB2
Fri Sep 16 12:01:11 2011
[code]....

As you can see, the rebalance process started 3 days ago, hanging till now

SQL> select group_number, operation, state, power, est_minutes from v$asm_operation;

GROUP_NUMBER OPERA STAT POWER EST_MINUTES
------------ ----- ---- ---------- -----------
1 REBAL RUN 5 0

View 3 Replies View Related

Server Administration :: Flashback Table Operation For The Sys User

Jun 7, 2010

Flashback 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.

View 5 Replies View Related

Server Administration :: ORA-30967 - Operation Directly On Path Table

Sep 30, 2013

While 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]...

View 3 Replies View Related

Server Administration :: How To Drop Datafile

May 25, 2012

I 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

View 5 Replies View Related

Server Administration :: How To Drop A Datafile

Dec 31, 2011

How to drop a datafile of tablespace.

SQL> alter database datafile '/u01/app/oracle/oradata/oracl/hxl01.dbf' offline drop;

Database altered.The command success,but the dic view show the datafile also.

SQL> select file_name,tablespace_name from dba_data_files;
FILE_NAME TABLESPACE_NAME
--------------------------------------------- ------------------------------
/u01/app/oracle/oradata/oracl/users01.dbf USERS
/u01/app/oracle/oradata/oracl/sysaux01.dbf SYSAUX
/u01/app/oracle/oradata/oracl/undotbs01.dbf UNDOTBS1
/u01/app/oracle/oradata/oracl/system01.dbf SYSTEM
/u01/app/oracle/oradata/oracl/hxl02.dbf TPS_TEST
/u01/app/oracle/oradata/oracl/hxl01.dbf TPS_TEST
6 rows selected.

View 5 Replies View Related

Server Administration :: To Reopen And Drop Database

Apr 25, 2011

I created new database. Now I want to drop database. So I had login as sys user and executed shutdown immediate command. The database was closed.Then I tried to execute startup restrict mount command so that I can run drop database command. Then it is giving error as

ORA-12154: TNS:could not resolve the connect identifier specified

I tried to restart the service,also checked lsnrctl command,and tnsnames.ora file.Everything is fine,but still gives the same error as above.

View 4 Replies View Related

Server Administration :: Drop Index Hangs On SAP?

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

Server Administration :: Precautions Before Drop User

Jul 28, 2011

I want to drop some users which are no longer been used .What are the precautions i need to take before i drop users? I have taken logical backup (Export) of users i want to drop.Is there anything i missed out before i drop user?

View 4 Replies View Related

Server Administration :: Privileges For DROP Partition

Jun 21, 2011

I have one user CD_APP. I have one partition table CD.T_FCDR_DT. User has got ALTER/INSERT/UPDATE/DELETE/SELECT privileges on the table..

Now when I try to drop a partition, I get error as below:
-------------------------------------
SQL> show user
USER is "CD_APP"
SQL> ALTER TABLE CD.T_FCDR_DT DROP PARTITION D01 UPDATE GLOBAL INDEXES;
ALTER TABLE CD.T_FCDR_DT DROP PARTITION D01 UPDATE GLOBAL INDEXES
*
ERROR at line 1:
ORA-01031: insufficient privileges
--------------------------------------
Do I have to grant some other privileges for this user.

View 12 Replies View Related

Server Administration :: ORA-29702 / Error Occurred In Cluster Group Service Operation

Aug 12, 2010

I have deinstalled Oracle database, ASM and oracle software. I installed the oracle software, ASM and database. I have seen the error in the ASM trace called

Errors in file /opt/oracle/admin/+ASM/bdump/+asm_gmon_10518.trc:

ORA-29702: error occurred in Cluster Group Service operation
ORA-29702: error occurred in Cluster Group Service operation

View 11 Replies View Related

Server Administration :: ORA-00054 - Drop MATERIALIZED View Log?

Jan 17, 2013

SQL> drop MATERIALIZED view log on afccv.tbl_voicechat;
drop MATERIALIZED view log on afccv.tbl_voicechat
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

i dont want to kill the sessions or anything is there any way to set prority to do this task?

View 2 Replies View Related

Server Administration :: Drop A Table Moved To Recycle Bin?

Jan 6, 2012

I 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

Server Administration :: Unable To Drop User Without Database Shutdown

Apr 19, 2013

We are unable to drop user due to below error, how to drop the below user without shutdown the database.

SQL> drop user mvm_2010 cascade;
drop user mvm_2010 cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-14452: attempt to create, alter or drop an index on temporary table already in use

No session was available for particular user.

View 19 Replies View Related

Server Administration :: Unable To Drop User - Table Or View Does Not Exist

Jun 6, 2011

Im 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

View 4 Replies View Related

Server Administration :: Create / Drop User / Schema In Another Oracle Database Through Apex

Jul 7, 2012

Through an Oracle Apex application I need to create/drop a user/schema in another Oracle database. i.e., create/drop user remotely using an Oracle Apex application.

View 12 Replies View Related

Server Utilities :: Export Schema Which Contains Space More Than 100 GB Using Compressed Mode?

Jul 13, 2010

I need to export one schema which contains of huge space of 100GB in oracle 9i ,can u pls provide the command to export in the compress mode.

View 3 Replies View Related

Drop All Tables In Oracle 8

Dec 17, 2012

i want drop all tables in oracle8

View 2 Replies View Related

SQL & PL/SQL :: Drop All The Tables In A User At A Time

Sep 15, 2011

is there any syntax to drop all the tables at a time in a user

View 12 Replies View Related

Server Administration :: How To Get Tables In Oracle 10g

Dec 17, 2010

I have installed the Oracle 10g software and created Database my own in RHEL 4. i got the emp tables when i run @?/rdbms/admin/utlsampl.sql with main user system. After i created another user test and i tried to get the tables. but im getting error like table not exist.

View 11 Replies View Related

Server Administration :: Finding Constraints On Tables

Sep 2, 2010

I want to find out the columns on which primary constraints are enabled.Which view must I use to check this.

View 2 Replies View Related

Server Administration :: Moving Tables From Older To New DB?

Mar 23, 2013

I have problem with moving old DB to the new (the same DB 10.2.0 in Win 2003, first in 32 bit, second in 64 bit). I want move DB from 32 to 64 bit. Problem is that all objects in old DB were created in SYSTEM schema by SYS. I can't export that objets (with data) because impdp nor imp don't touch this objects (tables with indexes). I can't use export import procedure. I'm looking for another method to transfer data, which will be the best and the fastest? Maybe files copy on OS? I suppose it will be problems with configuration files, database have other tablespaces.

View 13 Replies View Related

Server Administration :: Describing All Tables In A Database

Jun 14, 2001

I am trying to describe all the tables in a database.We use desc or describe tablename; to describe a table, but what is the command to describe all the tables in a database (i don't need the system tables)

Once i log into sqlplus as a say ABC (SID or HostString) as a user then if i do a desc table name i get column name, data type and null not null etc but i i need that for all the tables in that ABC database..

View 8 Replies View Related

Server Administration :: How To Get Tables In Oracle 10g Database

Dec 13, 2010

I have installed oracle 10g software and created Database on RHEL 4. when i run the run.sql its successively done but im not getting tables. whats the prob.

View 17 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved