Server Administration :: Complete Content Of Trigger Body In User Triggers

Aug 2, 2010

Column Trigger_body in table user_trigger is long data type.I want to find out the certain 'table name' was using intrigger.

Below is my sql statement:
select trigger_name
from user_triggers
where TRIGGER_BODY like '%tb_test%' or TRIGGER_BODY like '%TB_TEST%';

then it show me a error :
where TRIGGER_BODY like '%tb_test%'
*
ERROR at line 3:
ORA-00932: inconsistent datatypes

I had set long 32767 before execute above statement

View 7 Replies

Server Administration :: How To Prevent DBA User From Disabling Any Trigger

Jun 19, 2011

How can prevent DBA user from disabling any trigger ? suppose DBA user can't be able to execute following command

ALTER TRIGGER {trigger_name} DISABLE;

how can i protect it?

View 3 Replies View Related

Server Administration :: Time Remaining To Complete Table Statistics?

Jan 16, 2012

I use the following query to find out the remaining time to complete the table statistics which is running currently.

SELECT SID, SERIAL#, opname, SOFAR, TOTALWORK,username,context,
ROUND(SOFAR/TOTALWORK*100,2) COMPLETE
FROM V$SESSION_LONGOPS
WHERE
TOTALWORK != 0
AND SOFAR != TOTALWORK
order by 1;

SOFAR column shows 9325 and totalwork column shows 12287.How to calculate the columns in terms of hours and minutes ?

And also sometime the query never shows the output of the query for the current running query.

View 1 Replies View Related

Server Administration :: Disabled Triggers And Constraints

Oct 29, 2010

What should I do with those disabled CONSTRAINTS (most were those file starts with LOGMNR) & TRIGGERS found on our production dB? Are there any impact once I enable those?

View 2 Replies View Related

Server Administration :: TNS Error - DBCA After Database Creation Script Complete

Aug 15, 2010

I'm getting a "TNS disconnect" after database creation script creation in DBCA. Haven't figured out what's going on. I'm attempting to create a 2nd database on one linux 64 server. I get all the way through DBCA, but it won't go the final mile. Any thoughts? I've tried setting the port to a different port from the existing LISTENER, creating a LISTENER2 and associating it with the 2nd instance.

View 1 Replies View Related

Server Administration :: Possible To Write Triggers On Data Dictionary Tables And Views

Sep 30, 2011

Is it possible to create trigger on the various tables and views exists (i.e. dynamic performance views) in data dictionary, when ever any DML operations performs by Oracle it self?

View 6 Replies View Related

SQL & PL/SQL :: Invoking Statement Inside The Trigger Body

Jan 11, 2012

I need to find out the exact DML statement which invoked a particular trigger inside a trigger body.

View 7 Replies View Related

SQL & PL/SQL :: Write Trigger On A New Table - Body Not Working Properly?

Jun 29, 2010

I am trying to write a trigger on a new table. (dest_test) This is the first trigger that I have ever attempted (fairly new DBA) and I am having some trouble with the trigger body.It is a before insert trigger that will need to select from another table (dest) for a particular value being inserted (destination).

create table dest_test (
destination varchar2(4) not null,
db_name varchar2(10) not null
)

desc dest

[code]...

I am getting the exact opposite results than I want, though. If the value appears in dest, it is inserting into dest_test... NOT whatI want it to do!If the value doesn't appear in dest, it is throwing ora-6512 and ora-4088 errors. Is there a way to suppress these errors, or to graceful exit from the block so that the trigger completes without throwing these errors?

View 3 Replies View Related

Server Administration :: Externally Authenticated User Login Syntax As Proxy User?

Nov 15, 2010

I created an externally authenticated user in database. And can login without password with below syntax.

SQL> connect / @TESTDB
Connected.
SQL> show user;
USER is "SCOTT"

This scott user has a proxy permission to another DBuser PROXY_USER. Previously I used to login using below syntax.

connect scott[proxy_user]/password_for_scott@TESTDB

So now, what syntax should be used for this "ExternallY Authenticated" user to login as a proxy user?

View 10 Replies View Related

Forms :: Alerting User When Changing Field Without Content?

Jan 5, 2012

I had created a form, in that i having two fields for username and password.

The problem is i want to make the fields as fill must.

if the user leaving the username field without entering anything trigger must raised. Otherwise if we are entering the data it should allow him to go for the next block without any raising of triggers.

View 8 Replies View Related

Server Administration :: Creating A View In User From Another User?

Nov 9, 2011

I have a strange problem when creating a view in user from another user

I have a user called "Cash_tst"

its syntax creation is

-- Create the user
create user CASH_TST
identified by ""
default tablespace CASH
temporary tablespace TEMP
profile DEFAULT
quota unlimited on cash;
-- Grant/Revoke object privileges
grant connect to CASH_TST;
grant dba to CASH_TST;
grant resource to CASH_TST;

-- Grant/Revoke system privileges
grant create any view to CASH_TST;
grant unlimited tablespace to CASH_TST;

I want to create a view

CREATE VIEW TAMER
AS SELECT *
FROM [b]AROFL[/b].RA_CUSTOMER_TRX_LINES_ALL_BEFO

"AROFL" is another user on the same database
when try to create the view "tamer" i got message of
"insufficent privilege"
although i granted "create any view" to the user "cash_tst"

View 12 Replies View Related

SQL & PL/SQL :: Create 2 Triggers And Each Trigger Will Call Only One Procedure

Feb 14, 2011

What is more efficient?

- To create one trigger on the table and make it call two unrelated procedures
OR
- To create 2 triggers and each trigger will call only one procedure.

View 13 Replies View Related

Complete The OCA In 10g For Oracle Administration

Aug 5, 2013

I have done IZ007 in 9i. Now I wanted to complete the OCA in 10g for Oracle Administration. which exam I need to go for to complete 10g OCA.

View 1 Replies View Related

PL/SQL :: Create Trigger To Insert Data From One User To Another User In Same Database?

Apr 16, 2013

I Created One Trigger as Follows

CREATE OR REPLACE TRIGGER TRIGGER1
BEFORE INSERT
ON table1
FOR EACH ROW

[code]......

Here , I Want To Insert The Data From My User To Test User . In This Situation When I Execute The Above Trigger It Shows The Error PL/SQL: ORA-00942: table or view does not exist

View 3 Replies View Related

SQL & PL/SQL :: Trigger Which Changes Content Of One Column Based On Information Within Another Column

Sep 10, 2010

I am trying to create a trigger which changes the content of one column based on the information within another column.

For Example, if the 'ITEMQUANTITY' field drops below 1 then I want the STATUS column to say 'Out Of Stock'.

View 23 Replies View Related

PL/SQL :: Join Between Table With User Based Content And Base Table

Jul 30, 2012

I stumbled about some weird 11gR2 behavior (running on AIX).When I performed a join between a table with user based content (parts belonging to an sourcing scope) and a base table (parts available) whereas the parts have to fulfill a special regular expression, it showed that the same query is faster when using outer join than inner join (about 0.7sec vs. 20sec; which makes me believe that regexp_like works wrong when involved in an inner join).

i tried the same statement with a standard like (but not fulfilling the same condition).This time performance was as expected (inner join outperforming outer join).

Oracle version information
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
[code]...

I can see it, the execution plan for the "inner join" doesn't show so much more costs than the one for the outer (but why at all is does an inner join cost more?) ...The execution plan for both "not like" is the same and (surprisingly ;-) ) similar to "outer-regexp".

I hope sample data are not needed as there would be needed a lot...this is the second time I came across the "plan worse but execution time better" phenomenon.

View 10 Replies View Related

Server Administration :: Preventing Database Shutdown By A Trigger?

Jan 15, 2013

i mean can i write a trigger to prevent shutdown in certain time.

create or replace trigger trig1 before shutdown on database begin if to_char(sysdate,'hh24') <16 then 'what is the right thing to do here?'

View 3 Replies View Related

Server Administration :: User ID Fetching

Sep 22, 2011

In my project a user has updated a column with wrong value which had caused some issues.

I wish to find the userid of person who did the update on the table.

There is no trigger on that table

I used the following queries but have not got success

select * from USER_TAB_modifications where table_name like '%XYZ%';

select * from USER_AUDIT_OBJECT ;

I WAS ABLE TO GET WHEN LAST UPDATE TOOK PLACE on table but not userid details

View 1 Replies View Related

Server Administration :: Oracle RAC ASM User?

Sep 20, 2011

We have installed Oracle RAC 11gR2, installation was all successful.But i cannot see ASMSNMP user to login as ASM enterprise manager .If i tries to login as sys (even after assigning SYSASM role) he cannot login.

Quote:Set Oracle_sid=+ASM1
sqlplus / as sysasm
grant sysasm to sys;
conn sys/*** as sysasm
ORA-01031 insufficient privileges

View 3 Replies View Related

Server Administration :: Object Created In Different User?

Jul 15, 2010

Whenever I create a table using login A, the table gets created successfully. But when I query the dba_objects, the object owner is different, login B. What might be the reason behind this?

View 9 Replies View Related

Server Administration :: Error While Dropping User?

Mar 30, 2012

Got following error while droping user

DB:9.2.0.8.0 OS:Windows 2003

SQL> drop user custom cascade;
drop user custom cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-21700: object does not exist or is marked for delete

View 1 Replies View Related

Server Administration :: PGA Memory For Every User Or Every Session

Mar 18, 2013

I need to ask about PGA memory. Is this memory for every user or every session

i.e. PGA 200M

I have 2 users with 300 connection with one of them. Is 200M for 300 connection or for every connect.

View 2 Replies View Related

Server Administration :: DB Link For One To Many User Access

Sep 14, 2010

I need to create one to many user DB link in oracle 10g. Meaning I have a user A in database 1 and I want to access the objects from user B,C,D in database 2, how to create a public database link so that i can have this one to many user access?

View 8 Replies View Related

Server Administration :: Number Of Connection For A User

Aug 27, 2013

How to determine number of connections establishing from application server to database server for a particular user and also query the user is running in database.

user -- application user created in database.
same user exist in application.

View 15 Replies View Related

Server Administration :: Assign User Roles To Uses?

Aug 19, 2011

Is there any default way to assign user roles to uses ?

Suppose I want assign to user different roles to different users on views, tables etc.

I wan to implement Access control list & Access request objects (ACL/ARO).

View 7 Replies View Related

Server Administration :: Precautions Before Drop User

Jul 28, 2011

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?

View 4 Replies View Related

Server Administration :: User Login Fails?

Sep 27, 2011

I just create a user and tried to log in but it gives the following error, why?

SQL*Plus: Release 10.1.0.4.2 - Production on Tue Sep 27 10:57:55 2011

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL> create user user1 identified by user1;

User created.

SQL> conn user1
Enter password: *****
ERROR:
ORA-12560: TNS:protocol adapter error

Warning: You are no longer connected to ORACLE.

View 26 Replies View Related

Server Administration :: Insufficient Privileges To Other User

May 21, 2011

WHEN I CONNECT TO SCOTT OR ANY KIND OF USER (EXCLUDE SYS)I CONNECT EASILY BUT PROBLEM WITH SCOTT OR OTHER THEY CANNOT BE
SHUTDOWN THE DATABASE AT THAT TIME THE ERROR ARISES ARE

ORA-01031: insufficient privileges SO OUR DATABASE CANT BE SHUT IT DOWN.

View 5 Replies View Related

Server Administration :: Create User Email

Mar 22, 2011

I want whenever a user is created in database the server send me a mail about this.

View 3 Replies View Related

Server Administration :: Connect 9i To 10g With Same Database And User?

Oct 19, 2012

last two years i am using oracle 9i in windows 2003 server but now i installed oracle 10g on another server (PC) , i want to connect 9i to 10g with same database and user .

View 3 Replies View Related