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
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.
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?
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';
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
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]...
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
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.
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.
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?
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.
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
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?
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?
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
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.
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.
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.
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..
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.
