SQL & PL/SQL :: Alter Queue Table Column?
Mar 23, 2011
I need to Modify the column(MSGID) data type from RAW to BLOB for a Queue Table, I'm getting the following error.
BANNER
---------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE11.2.0.2.0Production
YUV >
YUV > DESC PDA_REPORT_MESSAGE_QTAB
Name Null?Type
----------------------------------------------------- -------- ------------------------------------
Q_NAME VARCHAR2(30)
MSGID NOT NULL RAW(16)
YUV >
YUV >
YUV > ALTER TABLE PDA_REPORT_MESSAGE_QTAB MODIFY (MSGID BLOB);
ALTER TABLE PDA_REPORT_MESSAGE_QTAB MODIFY (MSGID BLOB)
*
ERROR at line 1:
ORA-22858: invalid alteration of datatype
YUV >
YUV >
YUV >
YUV >
YUV > ALTER TABLE PDA_REPORT_MESSAGE_QTAB ADD (MSGID_NEW BLOB);
ALTER TABLE PDA_REPORT_MESSAGE_QTAB ADD (MSGID_NEW BLOB)
*
ERROR at line 1:
ORA-24005: Inappropriate utilities used to perform DDL on AQ table PDADBA.PDA_REPORT_MESSAGE_QTAB
YUV >
View 10 Replies
ADVERTISEMENT
May 4, 2011
I have a table with usernames and passwords. The passwords are stored in plaintext. I would like to issue an ALTER command on the password field to store a hash instead, and then repopulate those fields with an encrypted version of the plaintext passwords that were there before.
I would prefer to do this in a procedure, as I am going to perform it in a test environment first, then eventually in the production environment.
View 2 Replies
View Related
Apr 14, 2012
I have a table PR in that some data is there for instance, My Mr_NO was Char(11) I modify MR_No to Char(13)My Table Structure now is:
MR_No Char(13),
Mr_Date Date
My Previous data is MR_NO=APN00209085
I want to add two 00 after alter the table I want my result data like APN0000209085.I am updating through this command
update PR set Substr(MR_No,4,2)='00'
ERROR at line 1: ORA-00927: missing equal sign
Result I want is APN0000209085
View 13 Replies
View Related
Mar 11, 2011
We have some tables in our database in which for loading data we have the setup in place to do Exchange partition after data load into staging. Today we did changes to column length to one pair of main and staging table. Post that Exchange partition stop working.
View 1 Replies
View Related
Nov 26, 2011
,how to drop the queue table from our database.when iam trying to drop the queue table aim getting this error
error dropping AQ$_DEMO_QUEUE_TABLE_G;
ORA-00942:TABLE OR VIEW DOES NOT EXIST
View 1 Replies
View Related
Jan 19, 2010
I am trying to propagate messages between two queues that are in the same database.I did exactly as it says here: URL.....
The message is propagated successfully.The problem is that it doesn't to dequeue the message with the default subscriber of the destination queue.
The only difference between what I did and what is in the example above is that I didn't create a database link. I don't think I need to because the queues are in the same database, and the problem is not the propagation because as I said the message has moved to the destination queue.
View 4 Replies
View Related
Sep 13, 2012
I have a partition table which is partition on date_loaded column, can i alter the partition key column to orig_date_loaded column
View 9 Replies
View Related
May 20, 2010
i have a database in which some tables. Now i want to alter column by using this query.
ALTER TABLE SALE_INVO_DETAIL_COPY
MODIFY ("QTY" number(5,2))
this column properties is QTY NUMBER(5)
now i want to convert it into QTY NUMBER (5,2)
View 2 Replies
View Related
Jul 4, 2013
I am using 11.2.0.3.0 version of oracle.
Now we are supposed to apply column level TDE to some of our table in database. Now it will be a 'ALTER' on the columns. it involves 4 big tables out of which 3 tables having size ~30GB(one is partitioned table) and another one ~800GB(Not partitioned) Now the concern is, what will be the efficient/safest way to apply TDE on columns, below are the two options with us. (NOTE - We do have downtime window during DB maintenance but looking at the size of the table, i suspect it might take lot.)
1. Directly apply 'ALTER' on the columns. (Note- i was testing on my local, it took 3hrs for a 30GB table to ALTER the column to TDE)
2. Use Table Redefinition for Altering the column. (Creating interim table with column as TDE and then Redefining whole table).
View 5 Replies
View Related
Jun 19, 2013
What is the difference between alter session and alter system?
View 2 Replies
View Related
Jul 26, 2011
if a user have alter table gant but could not alter .. what additional grant it need
SQL> alter table HRS_PERS_FIELDS_INC modify(PER0000252 NUMBER(19,3));
alter table HRS_PERS_FIELDS_INC modify(PER0000252 NUMBER(19,3))
*
ERROR at line 1:
ORA-00942: table or view does not exist
View 11 Replies
View Related
Jul 22, 2010
I created a DDL trigger which manipulates columns of a table after an "alter table" statement.
For us this was all the time
"alter table XXX add NEW_COL varchar2(20)"
or
"alter table XXX modify NEW_COL varchar2(20)".
Unfortunatly we have now a requirement which statements "alter table XXX drop NEW_COL varchar2(20)".
My trigger works fine - you see no problem directly after statement execution but the table is then in an invalid status.
The connection will be destroyed after you tried to see the data of this table.
Also the export (creating a dump) will not work.
The problem I see is that I the database will do a "modify" on a column which has internal the status "dropped".
How I can get the information what kind of alter table statement will be executed?
Or is there any chance to select only columns from USER_TAB_COLUMNS without the "dropped" flag?
create or replace trigger TRIGGER
after alter
on SCHEMA
declare
vCharSet NLS_DATABASE_PARAMETERS.VALUE%TYPE;
begin
-- Select all columns of a table
select COLUMN_NAME, DATA_TYPE, DATA_LENGTH
from USER_TAB_COLUMNS
where TABLE_NAME = vTABLE_NAME; ...
-- Loop for all columns from our select
...
vSTATEMENT := 'alter table ' || vTABLE_NAME || ' modify ' || VOBJECTREC.COLUMN_NAME || ' ' || VOBJECTREC.DATA_TYPE || '(' || vNEW_DATA_LENGTH || ')';
--For example:
-- alter table XXX modify NEW_COL varchar2(20)
execute immediate vSTATEMENT;
exception
...
end;
/
View 11 Replies
View Related
Jun 11, 2009
I want to alter a very large table.
ALTER TABLE MYTABLE ADD
(
ENTRY_TSTMP DATE DEFAULT SYSDATE NOT NULL
)
My table is very large and I am getting an error saying I am out of undo space.
The dba says the undo space is as big as the table.
View 1 Replies
View Related
Aug 3, 2010
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
"CORE 11.1.0.6.0 Production"
I altered existing table EVENT_SUB - added 3 columns. After that, I noticed all the procedures which had mention of this table name went in INVALID status, even if its simple SELECT, ALTER OR INSERT as shown below..
SELECT * FROM EVENT_SUB
OR
INSERT INTO EVENT_SUB...
OR
ALTER TABLE EVENT_SUB
WHERE....
So I had to recompile all the procedures associated with it. Is there any other ways to achieve this, like a line of code to add in the procedure itself, right after this DDL statements.
Sometimes i use this:
select object_name, object_type from all_objects where owner='TOYCOM' and status='INVALID'
Then, I would simply recompile the invalid objects.
For indexes, i do...
alter index <name> rebuild;
BTW, I did try to preview message, and then click on Create Topic, it gave me error..again.
"A system error has occurred.
View 3 Replies
View Related
May 12, 2011
I ran the following PL/SQL Code & I am getting the following Error:
Drop table MODIFIEDTABLE
/
Commit
/
create table MODIFIEDTABLE(TABNAMES varchar2(100))
/
DECLARE
[code].....
RESULT:
Table dropped.
Commit complete.
Table created.
DECLARE
*
ERROR at line 1:
ORA-01735: invalid ALTER TABLE option
ORA-06512: at line 12
View 2 Replies
View Related
Nov 30, 2011
how to play around with NDS dynamic sql and I'm trying to add a column on the fly.Basically the procedure is trying to take a table name, column name, and eventually a data type and adds it to a table.
It works fine without the bind variable for the column name, accepting the table name on the fly.As soon as it tries to use the column name I get an ORA-00904 invalid identifier exception.
Here is the procedure I'm using
CODEcreate or replace
procedure test(tbl_name varchar2, col_name varchar2) IS
qry varchar2(500);
begin
[code]....
Here is how I'm executing it.
CODEexecute test(tbl_name => 'BB_SHOPPER', col_name => 'MEMEBER');
View 3 Replies
View Related
Sep 1, 2012
SQL> ALTER SESSION SET CURRENT_SCHEMA = CLA_T3;
Session altered.
SQL> select sys_context('USERENV','SESSION_USER') current_user,
2 sys_context('USERENV','SESSION_SCHEMA') current_schema
3 from dual
4 ;
CURRENT_USER
--------------------------------------------------------------------------------
CURRENT_SCHEMA
--------------------------------------------------------------------------------
CSR_ETL
CLA_T3
SQL> set linesize 300;
SQL> /
CURRENT_USER
----------------------------------------------------------------------------------------------------
CURRENT_SCHEMA
----------------------------------------------------------------------------------------------------
CSR_ETL
CLA_T3
SQL> create table cla_t3.test (r number, b char(2));
create table cla_t3.test (r number, b char(2))
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> create table test (r number, b char(2));
create table test (r number, b char(2))
*
ERROR at line 1:
ORA-01031: insufficient privileges
After Setting current schema to 'CLA_T3', I am unable to create table in cla_t3 schema.
View 5 Replies
View Related
Oct 18, 2012
alter index test_idx1 shrink space;
I've heard that this statement causes a table lock but cant find any information on this.if it is so, is it a write lock or also a read lock of the table?
View 5 Replies
View Related
Aug 11, 2010
can you use alter table command to disable/enable constraints in a form if you can how, if you cant why
View 4 Replies
View Related
Sep 20, 2011
i need a trigger with alter commands to alter the table structure,it will be captured in a separate meta data table(META)
CREATE OR REPLACE TRIGGER meta_alter AFTER Alter ON SCHEMA
BEGIN
update meta set column_name=:new where table_name=ora_dict_obj_name column_name=:old;
END;
/
Meta table contains Table name and column name..i attached the table data in atext file
View 39 Replies
View Related
Jun 9, 2010
I am researching a performance problem on an Oracle Preprd DB in a RAC cluster using AQ. The queue table has 88 records, is about 900Meg in size, takes 90+ seconds to do a select count(*). In Prod the same table is about 44 records, 80 Meg in size, and takes about 9 seconds to query the table. The DB is at 10.2.0.4 running on a LINUX/Sun host. In the USER_DATA column I am seeing an entry in the STR_VALUE that displays 'Unable to successfully deliver a message after "MaxDeliveryCnt" attempts. Please verify that the onMessage() method of the MDB does not throw a RuntimeException' for each record in the table. I don't have direct access to the PROD DB so I can not verify this message in that environment. My question, is this table holding onto records that should be cleared out and should this table be dropped and rebuilt to reduce its size. I have seen this technique improve performance for other non-queueing tables. But I don't know if this is possible with a queue table.
View 5 Replies
View Related
Dec 9, 2010
I am trying to drop a user but i get the following error
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-24005: must use DBMS_AQADM.DROP_QUEUE_TABLE to drop queue tables
A couple of questions on this error:
- I did a search on the forum and the thread [URL] appears to have a solution to it by using
DBMS_AQADM.DROP_QUEUE_TABLE(queue_table => 'DEF$_AQCALL', force =>TRUE);
What i am not sure of is which queue will the above statement drop if run as sys and there are multiple schemas with different schema names but with the same queue name?
- We recently rebuilt the database server and prior to the rebuild i have always managed to drop a user without the above error. Is it likely that some setting somewhere is changed?
View 2 Replies
View Related
Nov 28, 2012
I want to send an email notification using oracle aq for events like when a message goes to error queue. Similarly,Can I send an email notification when I create a queue or drop a queue?
How can I do this?
View 3 Replies
View Related
Jan 21, 2013
Due to firewall restriction, i have to configure the streams in queue forwarding. Intermediate database will hold two different ip's (one from source and another to target) and will not talk each other.
View 1 Replies
View Related
Feb 18, 2010
I am calling a report from a parameter form - the report is launched using a run report button, that all works great. What I want to do now is to add a 'Cancel Report' button to the form so that the end user can cancel the report and remove it from the reports queue.
View 1 Replies
View Related
Oct 10, 2013
,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
[code]...
View 5 Replies
View Related
Sep 20, 2013
I am using Java print API (javax.print package) to send a bunch of documents for printing. Below is the code section that I am using to print documents through java program. When the document is sent for printing, I see the Job name is created properly in the print spooling queue on windows machine. But, when i go to the actual printer, the job name is different on the printer than what i saw in spooling. Since I am printing 100s of documents in batch, it gets very difficult to identify which document did not print, in case of issues. I also used the java.awt.print package . The java.awt. print. PrinterJob has a method setJobName(String). When i used this package, I got the name appear properly in both places. But I wish to use javax.print with the document name appear on printer queue.
public void printDocument(File pDoc, PrintService pService, DocFlavor pFlavor)
throws Exception { logMessage(true, "Printing Doc::" + pDoc.getAbsolutePath());
FileInputStream is = new FileInputStream(pDoc);
// Create the print job DocPrintJob job = pService.createPrintJob();
//Set print request attributes with file name as job
[code].....
View 0 Replies
View Related
Jun 19, 2012
Can i alter the table to create partition on non partition table, i have tried and could not create it. Do we have some other means to do it as this is the live table and cannot drop them else will lose the data.
View 1 Replies
View Related
May 30, 2012
I need to import some data from .csv files. There is one file each day, so I want them to be automatically imported into the DB. This is the format it comes in:
DSM,LOD,20120524,01,01,9999AMP02,1.1262240,M,0.6397380,M
DSM,LOD,20120524,01,02,9999AMP02,1.1315700,M,0.6450840,M
DSM,LOD,20120524,01,03,9999AMP02,1.1297880,M,0.6450840,M
I want this data to go into TEMP_TABLE. It then needs to reformatted as it goes from temp_table to my_table:
filename,readingID, field1,field2,date,num1,num2,meterid,read1,m1,read2,m2
so filename is the actual name of the .csv file that this row came from. And reading id is date, num1, num2, meterid combined. And the remaining fields coming from temp_table
This is what I have:
procedure import_data()
begin
TEMPFILENAME CHAR(60)='DSM_2010_Husky_Oil_20120525064122_20120525065011.csv';
create table temp_table
(dsm char(3),lod char(3),usage_date date,he char(2),reading char(2),loc_id char(9),mwh number(15,10),eormmwh char(1),mvar number(15,10),eormmvar char(1));
[code]....
, which does not work at all.
View 2 Replies
View Related
May 15, 2011
i have two questions.
(1) how can i fill some value in a table column based on some existing column value automatically without user intervention. my actual problem is i have 'expiry date' column and 'status'. the 'status' column should get filled automatically based on the current system date. ex: if expiry date is '25-Apr-2011' and current date is '14-May-2011', then status should be filled as 'EXPIRED'
(2)hOw can i build 'select' query in a report (report 6i) so that it will show me list of items 'EXPIRED' or 'NOT EXPIRED' or both expired and not expired separately in a single report based on user choice. 'EXPIRED' & 'NOT EXPIRED' can be taken from the above question no. 1.
View 3 Replies
View Related
