Some triggers are dropped and stored in recycle bin. when i am trying to restore it by the command
1* alter trigger "BIN$FFRO1R1LSuSIZ6uyLocD6g==$0" rename to WFNOTIFICATION_GEN_PK
SQL> /
alter trigger "BIN$FFRO1R1LSuSIZ6uyLocD6g==$0" rename to WFNOTIFICATION_GEN_PK
*
ERROR at line 1:
ORA-38301: can not perform DDL/DML over objects in Recycle Bin
I want to to use Java stored procedures and triggers within the Oracle 11g XE ?
View 3 Replies View RelatedI went through the link given in orafaq and tried to work on my system but the things did not work.
SQL> select *
2 from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL> ALTER SYSTEM SET recyclebin = ON;
System altered.
SQL> SELECT Value FROM V$parameter WHERE Name = 'recyclebin';
VALUE
--------------------------------------------------------------------------------
ON
SQL> CREATE TABLE TEST_RBIN(VAL NUMBER);
Table created.
SQL> INSERT INTO TEST_RBIN(VAL) VALUES(10);
1 row created.
SQL> COMMIT;
Commit complete.
SQL>
SQL>
SQL> DROP TABLE TEST_RBIN;
Table dropped.
SQL> SHOW RECYCLEBIN;
SQL> SELECT * FROM USER_RECYCLEBIN;
no rows selected
Using oracle 11.2.0.3...How long does the content in recycle bin stay?
e.g is it dependant on size of undo tablespace and expires after certain time? Also if drop table, then recreate table with same name will this wipe the dropped table from the recycle bin automatically?
I am getting the following errors when I try drop a tablespace.
I already did the following.
a) The tablespace & its datafiles offline.
b) I have purged dba_recyclebin.
SQL> drop tablespace db_maintenance including contents and datafiles;
drop tablespace db_maintenance including contents and datafiles
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-38301: can not perform DDL/DML over objects in Recycle Bin
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 RelatedWe are facing space crunch in our 4 development 11i ERP environment servers and decided to reclaim the space . We are having 11.1.0.7 Database . What we observed is, there are many objects present in the recyclebin and we went on to purge the reclybin of MSC user.
SQL>select owner,sum(space)* 8 / 1024/1024 "Size in GB" from dba_recyclebin group by owner order by sum(space) ;
OWNER Size in GB
--------------- ----------
MSC 127.672485
IRCUS 22.5664063
GL 2.98492432
APPS .38079834
XXDII_ESB .192993164
APPLSYS .001159668
TMPBKP .000427246
But even after purging the recycle bin of MSC User , we didn't find any changes in DBA_FREE_SPACE for those tablespaces in which recyclebin objects were present. We would have seen the freed space in dba_free_space.
is it possible to view all user that was dropped? how can i know that one user was create by who>? i'm running on multi dba user in my db. are there any history record when we create user?
View 4 Replies View RelatedI am trying to delete the partition, but I am getting error "ORA-01426: numeric overflow"
The original partitioning was:
TABLE_NAME PARTITION_NAME NUM_ROWS
F_TFP_CP_MONTH P_201108 0
F_TFP_CP_MONTH P_201201 (NULL)
F_TFP_CP_MONTH P_201202 (NULL)
F_TFP_CP_MONTH P_99999999 (NULL)
F_TFP_CP_MONTH P_201106 1159130358
F_TFP_CP_MONTH P_201105 0
F_TFP_CP_MONTH P_201104 1212566971
F_TFP_CP_MONTH P_201103 1002557990
F_TFP_CP_MONTH P_201102 0
F_TFP_CP_MONTH P_201101 0
F_TFP_CP_MONTH P_201012 0
F_TFP_CP_MONTH P_201011 0
F_TFP_CP_MONTH P_201010 0
F_TFP_CP_MONTH P_201009 0
F_TFP_CP_MONTH P_201112 (NULL)
F_TFP_CP_MONTH P_201111 (NULL)
F_TFP_CP_MONTH P_201110 (NULL)
F_TFP_CP_MONTH P_201109 (NULL)
F_TFP_CP_MONTH P_201107 1627218307
All partitions were dropped, but three; these three returned the same error when trying to drop them:
SQL> alter table dw.F_TFP_CP_MONTH drop partition P_201112;
alter table dw.F_TFP_CP_MONTH drop partition P_201112
*
ERROR at line 1:
ORA-01426: numeric overflow
SQL> alter table dw.F_TFP_CP_MONTH drop partition P_201111;
alter table dw.F_TFP_CP_MONTH drop partition P_201111
*
ERROR at line 1:
ORA-01426: numeric overflow
SQL> alter table dw.F_TFP_CP_MONTH drop partition P_201110;
alter table dw.F_TFP_CP_MONTH drop partition P_201110
*
ERROR at line 1:
ORA-01426: numeric overflow
So, the situation now is that the table only has these three partitions, and we are not able to empty the table, so that we can later purge it and recover the
space.
free that space and empty the contents of the table?
OS: AIX
DB: 11G
I need to recover a dropped table, I have a RMAN backup of midnight and the table was dropped at 7:00 PM today. I do not have flash back enabled on the database. I am thinking of restoring midnight backup and applying the archives just before the table was dropped and opening the database in resetlogs method.
Flashback query is working fine for dropped table,but throwing error when trying to get back the previous date by dropping a column from a table.
FLASHBACK TABLE emp TO TIMESTAMP TO_TIMESTAMP('2013-07-13 05:00:00', 'YYYY-MM-DD HH24:MI:SS')
SQL Error: ORA-01466: unable to read data - table definition has changed
Is that a limitation of Oracle by not having query to flashback a column but a table.
One of the developer dropped 18 tables by mistake on USER@db schema. Developer asked whether I can restore those tables, but unfortunately they are not in the recycle bin. Developer is re-creating the table now though. But I am just curious whether we need to change these parameters (db_recycle_cache_size) in order support any similar requests in the future.
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recycle_cache_size big integer 0
recyclebin string on
Is it possible to audit when a constraint is disabled, enabled or dropped? Sometimes I wonder why some constraints are missing. To make sure someone is dropping I would like to audit that action. Further, I would need to compare schemas to realize if some constraints are missing. As it names are generated automatically by oracle, how could I easily run a select to compare the constraints that does not match between schemas?
View 10 Replies View RelatedI had droped one user from my oracle database..but when i run this below query i found that user exist
SQL>select username from dba_users;
but when i try to connect that user i get result
SQL> connect sunil/sunil
ERROR:
ORA-01017: invalid username/password; logon denied
Now tell me how to remove this user from result of this query
SQL>select username from dba_users;
Is there a way to restore the schema and its associated objects once it is dropped?
1. The flash back database feature is not ON in the database where schema was dropped.
2. The data files/ table space has not been dropped
3. There was no export taken for the schema.
We have scheduled many email alerts for management, these were running fine but from three days, we are facing problem.
We received following Error during sending email from Database.
ORA-29279: SMTP permanent error: 550 "Dropped spammer pretending to be us"
After a power failure, I have now dropped disks in my 2 diskgroups DG_RACDATA1 and OCRVD.
Disks has been renamed and prefixed by DROPPED...
Their state is now "FORCING"
Mount status = missing
header status = unknown
Mode status = Offline
These disks was belonging to a failure group with redundancy so the cluster still works ...but I have lost the redundancy.
How to reuse these offline disk or confirm that what I plan to do is good:
Shall I put them online ? ALTER DISKGROUP DG_RACDATA1 ONLINE DISK DROPPED0000_DG_RACDATA1
Shall I drop them ? ALTER DISKGROUP DG_RACDATA1 DROP DISK DROPPED0000_DG_RACDATA1 FORCE
and then: zeroing them and readd to failgroup ? but how ?
can we recover a dropped table from database as well as recyclebin.
View 13 Replies View RelatedIs it possible to restore dropped table OR wrongly updated table using RMAN backup.
View 18 Replies View RelatedWhat is the purpose and use of 'Instead of Triggers'
View 20 Replies View RelatedI have to write two triggers on same table for auditing different columns of different users(may be different modules).
I will have an audit table in which i will insert data such as (user_id,module_id,column_name,old_col_val,new_col_val,timestamp)
Now different users from different modules will update the data on same table may be same columns from different front end forms!
If we write directly, we will not be able to know which column is updated by which user.
My question is in this case how can we control the triggers to raise differently?
Im going to create a trigger(insert or update) for a table A, when it fires inserted or updated columns are should be insert or update into another table say table B, one column(Processed_time) in table B where its value will be by subtracting two columns in table A(response_time,Submission_time) all are timestamp type.
How to update(Processed_time) in table B by subtracting above two columns in table A.
I have rather large compound triggers that I discovered were not firing this morning, so I created a simpler compound trigger to test:
CODECREATE OR REPLACE TRIGGER "test"
FOR INSERT OR DELETE OR UPDATE OF KI_NM ON CHEMAXON.CB1ASSAYS
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
ENABLE
[code]...
It's just not firing. The tables are all in the owner's schema (who has DBA rights). My Google-fu is failing me, and I'm not sure how to start troubleshooting general trigger failure.
The trace directory is full of trc files with the text below:
--------Dumping Sorted Master Trigger List --------
Trigger Owner : INPUT
Trigger Name : CUS_TST
--------Dumping Trigger Sublists --------
There is like a file generated every minute, and i cant stop it from happening.I have tried setting the trace_enabled parameter to FALSE but no success.
I have column names stored in a table(say t1).
Inside a trigger I have to dynamically get the : old. values for only these columns.
This I need because during update trigger on a table (say t2) I am supposed pickup the coumn names stored in t1 and get the : old values of only these columns to insert into a new table .
I am creating triggers for audit operation when any insert/update/delete happens on a table automatically.Now i need the same functionality in procedure.
My requirement is to create procedure for all those triggers.
There are two servers A and B ,and i am maintaining one table suppose table1 and another table suppose table2 , DML triggers are made for both of these tables.The action of DML trigger is that the movement we are inserting values , suppose a table1 on server A so automatically the same set of value in table2 on server Bso my problem is that the movement i inserted value on table1 on server A ,
the same set of values were inserted into table2 of server B and as the values were inserted intotable2 , the same set of values were inserted into the tabld1 because of the trigger. How to stop the non ending loop which occurs due to the simultaneous firing of triggers and insertion of same records into both the table endlessly...
version :---Personal Oracle Database 10g Release 10.2.0.3.0 TNS for 32-bit Windows: Version 10.2.0.3.0
I'm having a bit of a problem getting the syntax of a trigger right. Unfortunately, I have no DBAs locally, I use some third party software, and for reasons beyond my understanding, I have been told to use triggers, and not stored procedures, so I'm running with it.
The set up:
STRUCTURES table: contains several columns, one of which is the unique ID column.
ASSAY table: contains several rows, also with the same ID column, but can have more than one row per ID (several assays per compound). One column is XC_ASSAYS.
The idea of the trigger is basically: When a row in the ASSAYS table is updated, pull out the ID of the row, then calculate the average of the XC_ASSAYS columns for those rows, and report it to the STRUCTURES.XC_ASSAY column for that row ID.
My best attempt thus far results in compilation errors.
CREATE TRIGGER INHIB_W_ALA_TR
AFTER INSERT OR UPDATE ON ASSAYS
FOR EACH ROW
BEGIN
UPDATE STRUCTURES SET XC_ASSAY = (SELECT AVG(XC_ASSAY) FROM ASSAYS WHERE ASSAYS.ID = :NEW.ID) WHERE STRUCTURES.ID = :NEW.ID
END;
/
The resulting errors are:
LINE/COL ERROR
-------- -----------------------------------------------------------------
2/1 PL/SQL: SQL Statement ignored
2/190 PL/SQL: ORA-00933: SQL command not properly ended
12/0 PLS-00103: Encountered the symbol "end-of-file" when expecting
[code]...
I don't understand some of the errors, such as why line 2 SQL is ignored (it seems correct?), or I'm supposed to properly terminate the trigger (I've read ; and /, but I'm getting the end-of-file errors when I do so). Tried shuffling syntax and ' or " around - and I can't get it.The body SQL works when I replace :NEW.ID with an actual variable (such as 'NMP12'), but I'm not sure how to pass the ID variable from the updated row into the body. The ID is not updated, but other columns are.
I have a question about GG Sequence Replication and Triggers. My main database, which I would like to replicate on another server, is highly dependent on sequences for assigning surrogate keys to every row in every table in the application. I know that I need to add Sequence support to my source database (plus supplemental logging, etc), but I'm curious about the target database.
I do not anticipate allowing Read/Write access to this database - we are migrating from 10.2.0.4 (source) to 11.2.0.3 (target) on a new platform, and I want to keep the 11g database up-to-date with our production data until it is time to begin the actual conversion of our application. My thinking is that if I use the SUPPRESSTRIGGERS dboption in my Replicat session, this should take care of the use of the Sequences for assigning the surrogate key values, and the data should add to the tables normally without any intervention by the sequences/trigger combination. I know I will have to manually "correct" the sequences on my 11.2.0.3 database whenever I want to open this database up for use, but I have a script for this ready to go.
Also, in my source database, I am using Oracle Context indexes for generic name searching - this feature creates a number of DR$ named tables in the main application schema that I am replicating (approximately 50 of them). I am assuming that I should EXCLUDE these tables from the replication, as the context indexing should automatically update them as changes to the underlying data are applied via the replication of the indexed tables.
why when-mouse move,up,enter,leave are not working in oracle forms 10g except when-mouse-click and when-mouse-doubleclick on any items.
what i need to do to execute this triggers..