I want to create a meta data table which has all the views users can access with a description of what the view was supposed to do.
Is there a trigger that can be created that fires when a view is created? To make it easy only one user in this system (oracle 9.2.0.8) has the create view privilege.
I know I could write a procedure to create views with dynamic sql that would do this but that seems a bit of overkill.
the sequence of events should go like this:
view created
trigger fires
record with view name and creation date inserted in a table
later, manually go to the table and add explanatory comment
or can I add a comment to a view like you can to tables?
CREATE or REPLACE TRIGGER my_trigger BEFORE UPDATE OF PASSWORD ON mytable FOR EACH ROW DECLARE BEGIN :NEW.PASSORD := 'xyzt'; EXCEPTION WHEN OTHERS THEN raise_application_error(-20001,'Error '|| sqlerrm); END trigger_create_user;
But, when i execute it, i get the following message:Warning: Trigger created with compilation errors.Even if i do nothing in trigger body:
CREATE or REPLACE TRIGGER my_trigger BEFORE UPDATE OF PASSWORD ON mytable FOR EACH ROW DECLARE BEGIN END trigger_create_user;
I have created trigger on database level in system schema. While i am creating new tables in system schema, trigger logged the entry but when i am creating table in scott schema it is not working for that.
CREATE OR REPLACE TRIGGER ddltrigger AFTER DDL ON DATABASE BEGIN INSERT INTO aud_log (user_name, ddl_date, ddl_type, object_type, owner, object_name ) VALUES (ora_login_user, SYSDATE, ora_sysevent, ora_dict_obj_type, ora_dict_obj_owner, ora_dict_obj_name ); END;
1. After creating a view, how do I associate that view with certain level of security. As in, only a few users must be allowed to access that view. 2. How can I create a new user-login and password for my database application?
I have created 3 tables and some indexes, but these objects do not show up in dba_segments view. Is this a normal behaviour? Previously, with dictionary managed tablespace, I can specify the minimum extent to create, when the table/index is created. But I'm not sure how the locally managed tablespace work.
I'm using Oracle 11g R2 (11.2.0.1.0) for Microsoft Windows (x64), running on Windows 7.For the purpose of reproducing this issue, I have created the tablespaces as follow:
CREATE TABLESPACE CUST_DATA DATAFILE 'd:appasusoradataorcl11gr2CUST_DATA01.DBF' SIZE 512K AUTOEXTEND ON NEXT 256K MAXSIZE 2000K EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K SEGMENT SPACE MANAGEMENT AUTO;
I have two different tables having similar structure but data is coming from different source.finally i want to update the view so that the it should affect the base table.
create table emp1 as select empno,ename,job,deptno,sal from emp where deptno=10; create table emp2 as select empno,ename,job,deptno,sal from emp where deptno=20; create view emp_view as select * from emp1 union all select * from emp2; [code].......
I'm trying to insert data in my_second_table using a trigger and a view when I insert the data in my_first_table but there's something wrong in the code.
CREATE OR REPLACE TRIGGER my_trigger AFTER INSERT ON my_first_table FOR EACH ROW BEGIN
[Code]...
I'm suspecting that the problem is in the :new.cod_emp
P.S.: I'm using: Oracle Database 11g Release 11.2.0.1.0 - 64bit Production on Enterprise Linux Server release 5.5 (Carthage)
I have created a materialized view in schema SchemaBB of Server B with a trigger
CREATE MATERIALIZED VIEW WORKAREA.TABLA_TEST70 BUILD IMMEDIATE REFRESH FAST ON DEMAND WITH PRIMARY KEY AS SELECT TEST_PK, TEST_TEXTO [code].........
All schemas have the appropriate grants.
When inserting in TABLA_TEST (SchemaAA) and refreshing MView TABLA_TEST70 things go nicely.
But, when updating a record in the original TABLA_TEST and refreshing the MView the results in TABLA_TEST in SchemaBA are as if I have deleted the record (FECHA_DELETE is set to SYSDATE).
i try to make trigger to execute immediate create or replace view
( CREATE OR REPLACE TRIGGER xxxx BEFORE INSERT ON table1 REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW declare l number ; v_ddl varchar2(4000); v_job number; [code]....
it give me error for insuffition privilage in execute immediate and after i make GRANT CREATE ANY TRIGGER TO user give me error can't commit in trigger.
When i try to create a trigger , i ended up with error.
SQL> create or replace 2 TRIGGER LOGON_TRG AFTER LOGON ON DATABASE 3 BEGIN 4 INSERT 5 INTO 6 user_audit_log 7 SELECT 8 user#,
[code]....
Warning: Trigger created with compilation errors.
SQL> SQL> show error Errors for TRIGGER LOGON_TRG: LINE/COL ERROR -------- ----------------------------------------------------------- 2/5 PL/SQL: SQL Statement ignored 17/17 PL/SQL: ORA-00942: table or view does not exist
The command used to resolve the error is
GRANT SELECT ON v_$session TO jack;
Jack user has sysdba privilege. My question is 'sysdba' is a super and special user which has all the privileges in database. Then why does it need SELECT privilege on v$session to user to create the trigger?
i want to create "on update" database trigger on materialized view which is on replication schema.I would like to update rows in table when update occurs on materialized view on master side.I wrote some code and tried with this:
CREATE OR REPLACE TRIGGER a after update on A@dirfep.us.oracle.com FOR EACH ROW BEGIN
I'm trying to create a Materialized View on a remote database from a simple view. The reason is, the data owners don't want to grant explicit tables privileges to external subscribers.
A new schema is created to publish data in the form of a view. I've created mlogs on the master tables, and granted them to the subscriber, but it's still complaining about a missing primary key on the view. A primary key does exist in the master table.
Is there another work around for this situation without having to work inside the data sources' environment?
We wrote one data load process to load GZ files into Database.during process we will change client facing view definitions to backup table so that we can work on base tables.
This view definition changes are related to FROM and WHERE clause (not columns/type). during load process, client/user may connect to current server and accessing these views. My question is what will be the reflection of changing view definition while user is accessing view?
I created a scenario- STEP1: Created a view- create or replace view view_01 as select object_name from dba_objects union all select object_name from dba_objects union all select object_name from dba_objects union all [code]....
View definition is replaced by new definition while select is executing on that view. select returned number of records as per view definition one.
I have a materialized view "pro_mview",I am trying to refresh the MVIEW by using the following statement.
EXEC DBMS_MVIEW.REFRESH('pro_mview','C');
But I am getting the below error.
* Error at line 1: ORA-12008: error in materialized view refresh path ORA-00942: table or view does not exist ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2256 ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2462 ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2431 ORA-06512: at line 1
I am able to fetch the data from that materialized view(pro_mview).
what would be the difference between a view and a materialized view? whether DML possible on a view? i think error occurs if DML tried on a view which is a combination of two or more tables, whether DML possible on a materialized view?
Is there any way to get the last Sequence Number generated regardless the Sequence name? Something like dbinfo('sqlca.sqlerrd1') within Informix?
Imagine I have 2 sequences. Imagine that I run both several times until 20:00 hours. Imagine that at 21:00 hours I would like to know what was the last generated number created by any of the sequences. I don't wann know wich sequence generated the last NEXTVAL, I only want to know the value of the last NEXTVAL used.
Is it possible without mentionating the Sequence name?
