SQL & PL/SQL :: ORA-30506 - System Triggers Cannot Be Based On Tables Or Views
Jan 24, 2012
i'm trying to audit ddl actions(a strange "disappearing of a table every couple of days) on a specific table.
i want to trigger those actions, but not on a database level, but a table's level only(or at least a user schema's).
when i execute
CREATE OR REPLACE TRIGGER system.audit_trg BEFORE DROP ON tu2.d
BEGIN
INSERT INTO
[Code].....
i get an error
ORA-30506: system triggers cannot be based on tables or views
and if it is - how do i adjust my plsql?
View 29 Replies
ADVERTISEMENT
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
Aug 4, 2011
I have a created a materialized view which is based on a view on remote database. Now how do I refresh the view.
Materialized view is created by
CREATE MATERIALIZED VIEW mv_employee_name
AS SELECT EMPLID, EMPL_NAME
FROM VEMPDATA@REMOTEDB
WHERE REGION = 'US';
I am wondering how the refersh happens or how do I specify the refresh clause.REFRESH FAST option is looking for VIEW LOG on the master table but in this case its a remote view, so I cannot create any object on remote db.
View 1 Replies
View Related
Jan 4, 2013
I'm new to oracle DB,i've been given access to Oracle as a user. when i try to create a table under my default schema i get the insufficient privileges error.
How do i check from the system views if i have create table privilege under my own schema?
View 2 Replies
View Related
Aug 26, 2011
I 've noticed transactional triggers, as a value for Query data block property, How can i use it, and is it really 's mode advantages than using from clause and procedure based with DMLs on non-based items?
View 3 Replies
View Related
Apr 1, 2009
I have a Materialized view based on 2 views. I modified one column in both the views.Do i need to refresh the Materialized view.
E.g. :
In view1 and view2 i changed column like this
nvl(test.no,'n/a') testdata --changed
nvl(test.id,'n/a') testdata --original
CREATE MATERIALIZED VIEW MV_test
NOCACHE
LOGGING
NOCOMPRESS
NOPARALLEL
BUILD IMMEDIATE
[code].........
View 3 Replies
View Related
Feb 24, 2010
I am unable to understand why row level triggers cant be used in mutating tables.
If you need to update a mutating table, you could bypass these restrictions by using a temporary table, a PL/SQL table, or a package variable. For example, in place of a single AFTER row trigger that updates the original table, resulting in a mutating table error, you might use two triggers--an AFTER row trigger that updates a temporary table, and an AFTER statement trigger that updates the original table with the values from the temporary table.
View 1 Replies
View Related
Aug 16, 2011
I wanted a query that will give me the list of views using three tables
when i fire
select * from dba_views where text like '%SELECT%';
it is giving me error as expected number got long.
View 2 Replies
View Related
Oct 10, 2013
I'm having trouble with a little assignment.
"Create a view named vuPassFailRate that will show the pass rate and fail rates of each test."
I have a table named Test_ID containing the following columns:
TEST_ID
TEST_NAME
PASSING_GRADE
And another table named Test_History containing the following columns:
TEST_ID
STUDENT_ID
SCORE
I'm assuming i have to create an inline view, and to work out the pass/fail rates i need to do something along the lines of (For pass rate) Where SCORE is greater than or equal to PASSING_GRADE, and TEST_ID equals TEST_ID, divide by a count of SCORE and multiply by 100. I just cant work out the nested select statements, and work out the formula using two columns in two tables.
I have been staring at this problem for so long now i cant see the wood for the trees.
View 3 Replies
View Related
Oct 5, 2012
Going through my first attempt ever to connect to a Oracle database with a .Net application. Using Visual Studio 2012, I have downloaded and installed the latest version of odp.net, actually both the 32 and 64bit versions.
Application is targeting the 4.5 Framework, Latest version of Entity Framework installed (but not important I think at this point) Added a reference in my project to the Oracle Data Provider ( tried with both 2.0 and 4.0 )
Created a connection string with EZ Connect, stored in web.config file. I have tried 2 different projects to use EF and one without. Trying to create a edmx or using a datasource adapter brings up the connection wizard or prompts to use the existing one, but no matter what I try, there are no tables, views or procedures listed.
From Server Explorer, I can open the Data Connections and verify that the connection to the Oracle database is Open. During the Connection Wizard, there were no errors, and all the databases available were listed. Yet, connecting to and opening the connection to this specific database or anyone for that matter no tables, views or procedures are listed.
View 14 Replies
View Related
Oct 10, 2013
I'm having trouble with a little assignment. "Create a view named vuPassFailRate that will show the pass rate and fail rates of each test." I have a table named Test_ID containing the following columns:
TEST_IDTEST_NAMEPASSING_GRADE
And another table named Test_History containing the following columns:
TEST_IDSTUDENT_IDSCORE
I'm assuming i have to create an inline view, and to work out the pass/fail rates i need to do something along the lines of (For pass rate) Where SCORE is greater than or equal to PASSING_GRADE, and TEST_ID equals TEST_ID, divide by a count of SCORE and multiply by 100. I just cant work out the nested select statements, and work out the formula using two columns in two tables. I have been staring at this problem for so long now i cant see the wood for the trees.
View 2 Replies
View Related
Mar 28, 2011
i just posted another topic where i heard about external table and i had a few questions concerning them. I thought it was best to create a new topic than to continue on the other one...
I noticed that to create an external table the CTL is like this:
CREATE TABLE emp_load (FIELDS description)
ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY ext_tab_dir
ACCESS PARAMETERS (RECORDS FIXED 62 FIELDS (employee_number CHAR(2),
[Code]...
1) This creates an external table, but, is it possible to Create a normal table in a CTL file? For physical tables, the table has to exist right?
2) if you create a view linked to 2 external tables and if the CSV files are updated each day, the external tables will be updated automatically, and the view will be updated as well?
3) Can't there be any synchronisation problems?
4) What happens if a select request (or someone requests on the view) while the CSV file is being updated?
5) Is there anyway you can protect the accesses from those tables/views when the CSVs are being updated?
6) Is it possible to create an index on these sort of tables?
7) Is it possible to index a view?
8) Are external tables visible on a tool like sql developper?
View 11 Replies
View Related
Sep 26, 2012
While practicing with Triggers, the following error was encountered. An Object Type and an object Table are created.
create or replace
type typPerson is object
(id number,
firstname varchar2(30),
lastname varchar2(30)
[code].........
I executed the below insert statement, and I got the following error.
SQL> insert into person_obj_tab values (10,'Object1','From Trigger');
insert into person_obj_tab values (10,'Object1','From Trigger')
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 4153
Session ID: 136 Serial number: 305
[code]......
SQL> insert into person_obj_tab values (10,'Object','Original');
1 row created.Question:
1) Trigger of version 1 did not report any error during compilation, but during DML execution, hangs for sometime and gives the above error.
2) Whether direct assignment of Objects of greater size is possible inside triggers built on object Tables?
3) Suppose an object contain multiple attributes (say more than 20), then how to assign them inside a trigger?
View 1 Replies
View Related
Nov 5, 2012
is there some open source or free tool which can graphical display V$ Views. Can TOAD do that in a good maner?
in UNIX there is the "sar" command, but a Java tool "ksar" for displaying the statistics in user friendly fashion.
View 2 Replies
View Related
Mar 19, 2007
I am attempting to update a single field in one table based on a select from two tables. However, I am receiving the following error.
ORA-00933: SQL command not properly ended
The sql I am using is as follows:
update PS_TRNS_CRSE_DTL
set RQMNT_DESIGNTN = 'TRN'
FROM PS_TRNS_CRSE_DTL A, PS_STDNT_CAR_TERM B
where (SELECT A.EMPLID, A.ACAD_CAREER, A.INSTITUTION, A.MODEL_NBR, A.ARTICULATION_TERM, A.TRNSFR_EQVLNCY_GRP, A.TRNSFR_EQVLNCY_SEQ, A.TRNSFR_STAT, A.GRADING_BASIS, A.RQMNT_DESIGNTN, B.STUDY_AGREEMENT
FROM PS_TRNS_CRSE_DTL A, PS_STDNT_CAR_TERM B
[code]......
View 6 Replies
View Related
Oct 19, 2010
My tables looks like this:
Desc Table A (account)
Account1_id
Account2_id
name,
empid
Table B (Bill )
BillNo
Advertiserid
agencyid
total vvalue
I need to pick up total value from table B where the unique combination of advertiser-Agency id is the same as the given account1_id -Account2_id combination in table A for each employee id.
In other words my output should be like
Empid | Account_id (should be same as advertiserid)| Account2_id (same as agencyid) | sum(total_value) for this adv-agency combination.....
objective: Get the total value from table B for each unique account1-account2 combination (advertiser-agency in other words) .
I am not sure if I should use a correlated subquery or how to handle the situation....Right now I am just checking the two columns separately like this:
select.......from a,b
where b.advertiser_id = a.account1_id and b.agencyid = b.account2id
Is it correct to do so? I have a feeling that I am missing something if I join them separately like this.
View 3 Replies
View Related
Oct 17, 2013
I am removing sal column from table tab_emp; i want to check whether any materialized view or view using this column by querying using data dictionary :- if i use like condition against query column of all_mviews it is throwing error sicne it is long data type. is there a way to search it without creating any function and use it in a query.
View 3 Replies
View Related
Nov 26, 2010
have two queries that will return same columns from two different set of tables ( column mapping has been taken care of). The return type is out ref cursor. (P_SUPPLY_REORDER )
Query 1-SO
-----------------------------------
select
so.SMO_NO,
so.SPLY_ORD_DT,
so.fk_CUST_ID as CUST_ID,
so.CUST_PO_NO,
so.ATTENTION_NAME,
[code].....
Query-2 Xcom
--------------------------------------
select
null as sMO_NO,
xso.created_date as SPLY_ORD_DT,
xso.fk_cust_id as cust_id,
cust.cust_po_no as cust_PO_NO
,(sta.SHIP_TO_ATTN_FIRST_NAME||''||sta.SHIP_TO_ATTN_LAST_NAME) as attention_name,
xsol.CARTONS_ORDERED as SPLY_ORD_QTY,
[code].......
Now the requirement is
One of four conditions are possible for each Supply Reorder Number:
. Both table queries return no records- Populate all the P_SUPPLY_REORDER output fields with nulls
. SUPPLY_ORDER returns a record, but XCOM_ORDER_HEADER returns no records
- Populate output fields with values from the join of SUPPLY_ORDER and SUPPLY_ORDER_LINE.
. SUPPLY_ORDER returns no records, but XCOM_ORDER_HEADER returns one record
- Populate output fields with values from the join of XCOM_ORDER_HEADER and XCOM_ORDER_LINES.
. SUPPLY_ORDER returns a record, and XCOM_ORDER_HEADER returns a record; find out the latest order by comapring max(SPLY_ORD_DT)
from SUPPLY_ORDER with max(CREATED_DATE) from XCOM_ORDER_HEADER.
- If the latest order is in SUPPLY_ORDER, then populate output fields with values from the join of SUPPLY_ORDER and SUPPLY_ORDER_LINE.
- If order dates are equal from both join results, then populate output fields with values from the join of SUPPLY_ORDER and SUPPLY_ORDER_LINE.
- If the latest order is in XCOM_ORDER_HEADER, then populate output fields with values from the join of XCOM_ORDER_HEADER and XCOM_ORDER_LINES.
Question is how can we switch over the queries to pull respective dataset based on these conditions ( checking that which table join is going to return a row and then based upon latest order if both tables return a row) and all this logic as part of single SQL statement that is returned as OUT Ref Cursor.
View 7 Replies
View Related
Feb 3, 2013
i have a list of 500 tables. I want to delete data from those tables based on a condition. (Data before 2008 year needs to be deleted). Each table has a column based on which data needs to be deleted. Provide a code which does this efficiently and fast. Bulk collect is preferable.
View 17 Replies
View Related
Jan 10, 2011
How to export a data, which is a join of three tables.Will Export or dbms_datapump supports for above scenario.
Database: DB1
Tables: T1, T2 & T3
Select: t1.*,t2.*,t3.*
Join: t1.c1=t2.c1 and t2.c1=t3.c1
View 2 Replies
View Related
Jul 26, 2012
i'm trying to do an export/import process using command prompt and the idea is export a records based on the date condition. and the date will be the parameter. my code is like this:
exp <username>/<password>@<database> file=<table_name>.dmp tables=<source_table> query="where <date> between &start_date AND &end_date";
is it possible to do like this, that it should prompt you to enter the start and end date?
then my import script:
imp <username>/<password>@<database> dumpfile=<table_name>.dmp tables=<target_table>;
the idea is get only the records from ProdDB based on the date condition, and append it to the MISDB.
View 12 Replies
View Related
Jul 7, 2010
I have to convert some existing materialized views (fast refresh) to partition materialized views.
Database version is oracle 10.1.0.4. I have decided to use on prebuilt table option to do the partitioning as it minimizes the time to transfer from the master site.
1) stop replication
1) create interim tables with similar structure as the materialized views
2) transfer all data from the materialized views to the interim tables
4) script out the materialized views structure and add in on prebuilt table option in the scripts
5) drop the materialized views
6) rename the interim tables with the same name as the materialized views
7) run the scripts to create the materialized views with on prebuilt table option
8) refresh the newly created materialized views -> it should take a short time since I am using on prebuilt table option
But I am facing one major issue. That is if I drop the materialized views, the materialized view logs of the master tables are purged. When the materialized views are refreshed fast, there are some data missing. the data that are purged out when the materialized view are dropped.
Do you happen to know other ways that existing materialized views can be converted to partitioned materialized views? Do you have any workaround to prevent the materialized view logs from being purged?
View 3 Replies
View Related
Apr 1, 2012
We have system.current_item and system.cursor_item.....
both represent the same....
like this we have few other system variables also .....
what is the main difference between current_item and cursor_item?
View 2 Replies
View Related
Aug 21, 2012
couple of questions about below parameters, do they measure single processor or avg usage across all processorss and cpus for example will i recieve alert if CPU system time goes above 95% for single processors? or not
CPU Idle Time (%)
CPU System Time (%)
CPU User Time (%)
View 1 Replies
View Related
Dec 26, 2011
What is the purpose and use of 'Instead of Triggers'
View 20 Replies
View Related
Dec 4, 2010
I 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?
View 3 Replies
View Related
Jul 1, 2010
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.
View 2 Replies
View Related
Nov 1, 2011
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.
View 2 Replies
View Related
Dec 3, 2010
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.
View 2 Replies
View Related
Jul 26, 2004
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 .
View 5 Replies
View Related
