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'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
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.
When I try to drop the roles in dbartisan tool, I get an error saying "Drop DDL for **_***********_ROLE has not been generated because it is a system object", but if i go to sql prompt and say "drop role rolename", it is working. How do I remove the role in dbartisan itself?
,I am trying to set up Streams on a 11.2.0.3 on a Windows 2008R2 server. Due to an error in running propagation, i a, trying to delete both Capture and Apply queues. I have deleted the Apply queues but unable to drop the Catpure,
SQL>EXEC DBMS_APPLY_ADM.STOP_APPLY(apply_name =>'LAO_NLPG73_BLPU_APPLY'); SQL> select * from dba_apply; LAO_NLPG73_BLPU_APPLY NLPG73_BLPU_APPLY_Q STRMADMIN YES RULESET$_732 STRMADMIN STRMADMIN 301355 ABORTED 09/10/2013 17:34:21 1013 ORA-01013: user requested cancel of current operation CAPTURED STREAMS APPLY SQL> select * from dba_queues; STRMADMIN NLPG73_BLPU_APPLY_Q
I 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?
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
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 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 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 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.
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 am using this procedure to load oracle data into csv file.
/* Formatted on 2012/05/10 17:18 (Formatter Plus v4.8.8) */ CREATE OR REPLACE PROCEDURE generate_order_csv ( p_dir IN VARCHAR2, p_file_name IN VARCHAR2, p_data_file IN VARCHAR2 := NULL )
i am not able to rename a column for original table, once i create a new one with Create table as select i can rename the new table column, but not the original one
SQL*Plus: Release 11.2.0.1.0 Production on Tue Feb 14 13:10:10 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production With the Partitioning, Real Application Clusters, OLAP, Data Mining and Real Application Testing options
SQL> show user USER is "WEBSYSTEMD6" SQL> alter table cbe rename column tnum to tnum_old; alter table cbe rename column tnum to tnum_old * ERROR at line 1: ORA-00900: invalid SQL statement
SQL> create table cbe_test as select * from cbe;
Table created.
SQL> alter table cbe_test rename column tnum to tnum_old;
I am facing some challenge while running update query on newly added column in existing table.
Environment Details Oracle 9i, version 9.2.0.6 Os Unix Aix 6.1
No of records in table : 12572770
Below are the step i followed.
1. In table testtablename, I have added new column COLUMNNAME29 with datatype VARCHAR2(8). 2. After adding the new column, i executed the update query to populate the data form COLUMNNAME1 to COLUMNNAME29. 3. The query is executed using COLUMNNAME24 in where clause, to drive query in index based.
SQL> desc testtablename Name Null? Type ----------------------------------------- -------- ---------------------------- COLUMNNAME1 VARCHAR2(8) COLUMNNAME2 CHAR(1) COLUMNNAME3 CHAR(1) COLUMNNAME4 VARCHAR2(8) COLUMNNAME5 VARCHAR2(11)
[Code]...
Table altered.
SQL> select index_name, column_position, column_name from dba_ind_columns where table_name = 'TESTTABLENAME' order by index_name,column_position;
1. The update query is hanging in database, it's not progressing (In single update, approximately 40000 records will get update) 2. No oracle error thrown in alert log or in session where the query being executed. 3. The event for the query is "db file sequential read". 4. When i update the newly added column COLUMNNAME29 with static value "1", the update completed successfully in few seconds. 5. Then i changed the static value to "1111" and executed the update statement, which result to query hanging in database. 6. I tried to update the existing column(COLUMNNAME1) in table with static value "1111", the update completed successfully.
Below are the queries completed successfully
Update Testtablename Set Columnname29 = '1' Where Columnname24 >= To_Date('01-12-2002 00:00:00', 'DD-MM-YYYY HH24:MI:SS' ) And Columnname24 < To_Date('01-01-2003 00:00:00', 'DD-MM-YYYY HH24:MI:SS')
[Code]...
Below are the queries hanging in database
Update Testtablename
Set Columnname29 = Columnname1 Where Columnname24 >= To_Date('01-12-2002 00:00:00', 'DD-MM-YYYY HH24:MI:SS' ) And Columnname24 < To_Date('01-01-2003 00:00:00', 'DD-MM-YYYY HH24:MI:SS')
Update Testtablename
Set Columnname29 = '1111' Where Columnname24 >= To_Date('01-12-2002 00:00:00', 'DD-MM-YYYY HH24:MI:SS' ) And Columnname24 < To_Date('01-01-2003 00:00:00', 'DD-MM-YYYY HH24:MI:SS')
Below is character set in database
SQL> select * from v$nls_parameters; PARAMETER VALUE ---------------------------------------------------------------- ---------------------------------------------------------------- NLS_LANGUAGE AMERICAN NLS_TERRITORY AMERICA NLS_CURRENCY $ NLS_ISO_CURRENCY AMERICA
I 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.
