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 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
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?
i 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?
I 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)?
When I used to work with foxpro we had used dot matrix printer. At that time if we want, we can print in normal mode, expanded mode and compressed mode. it will be part of your spool file.
Now am printing a text file from forms using text_io package. I would like to know in this how do i print the content in compressed mode using CHR function or some other method.
I have read and used the AWR script (mentioned in the page Finding unused index for finding unused customised (Z) indexes in our SAP system using oracle 10.2.0.2 as the SAP database.
But this returns no rows. Is there any precondition? I want to know how much / many times the indexes are used...We are smelling that lot of unused index are there in the database.
There 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!
I 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 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.
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 am able to duplicate (from active database) 12.1.0.1 database to destination server successfully,however when I add either compressed backupset and/or section size to duplicate commandrecovery at the end of the operation fails due to missing archive log (which is at source server)
******************************************failed duplication****************************************** [oracle@r121 ~]$ rman target sys/sys_pwd@t12 auxiliary sys/sys_pwd@c12Recovery Manager: Release 12.1.0.1.0 - Production on Fri Sep 6 11:30:09 2013 Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved. connected to target database: T12 (DBID=1222223202)connected to auxiliary database: T12 (not mounted) RMAN> duplicate target database to c12 from active databasesection size 2gspfileparameter_value_convert 't12', 'c12'set db_file_name_convert 't12', 'c12'set log_file_name_convert 't12', 'c12'nofilenamecheck;2> 3> 4> 5> 6> 7>Starting Duplicate Db at 06-SEP-13using target
CODECREATE TABLESPACE my_ts DATAFILE 'C:\Oracle\oradata\db\my_ts.dbf' SIZE 5M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K; ALTER DATABASE DATAFILE 'C:\Oracle\oradata\db\my_ts.dbf' AUTOEXTEND ON;
Its was sucessfully created and my_ts.dbf file has 5MB
charging with data...
CODEcreate table big_table tablespace my_ts as select * from dba_objects; select * from big_table; begin for i in 1..10 loop insert into big_table select * from dba_objects; end loop; end;
Now the my_ts.dbf file has 90MB
Now I want drop this table: CODEdrop table big_table purge;
And my tablespace file still has 90MB.
I already tried to restart the database but doesn't works...
I have been trying to drop an unused column in a partitioned table, and the number of records stored in this unused column was very high. I kept on running into errors as follows:
ORA-01562: failed to extend rollback segment number 10
ORA-01650: unable to extend rollback segment R09 by 256 in tablespace RBS
I tried to "SET TRANSACTION USE ROLLBACK SEGMENT <name>" with a larger rollback segment, but it still did not work. Can I drop the "unused column" from each partition instead?
How to apply that? Or, what are my options besides increasing the size of the rollback segment?
If you mark a column unused, is there any way to project it? I know the docs say you can't, but as the data is still there I would have thought it should be possible. I can see the column in dba_tab_cols, but the obvious ways of making it usable don't work:
orcl> select column_name,hidden_column from user_tab_cols where table_name='DEPT';
COLUMN_NAME HID ------------------------------ --- LOC NO DNAME NO DEPTNO NO
orcl> alter table dept set unused column loc;
Table altered.
orcl> select column_name,hidden_column from user_tab_cols where table_name='DEPT';
COLUMN_NAME HID ------------------------------ --- SYS_C00003_13071316:19:02$ YES DNAME NO DEPTNO NO
orcl> select "SYS_C00003_13071316:19:02$" from dept; select "SYS_C00003_13071316:19:02$" from dept * ERROR at line 1: ORA-00904: "SYS_C00003_13071316:19:02$": invalid identifier
orcl> alter table dept rename column "SYS_C00003_13071316:19:02$" 2 to loc; alter table dept rename column "SYS_C00003_13071316:19:02$" * ERROR at line 1:ORA-00904: "SYS_C00003_13071316:19:02$": invalid identifier
orcl> alter table dept modify "SYS_C00003_13071316:19:02$" 2 visible; alter table dept modify "SYS_C00003_13071316:19:02$" * ERROR at line 1: ORA-00904: "SYS_C00003_13071316:19:02$": invalid identifier
I am in the task of clean up of tables. I need to find the list unused tables and procedures. Is there any way where i can find when was the last time the table queried?
Give sql query to find the list of unused tables and procedures.
I 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?
I'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]........
How 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.
