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 Repliesi'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?
Oracle version : 11.2.0.3
Some DBA_views are huge with lots of columns ; a DESC command's output becomes less readable ; example DESC command's output of V$SESSION view.
Lets say I want to see only the date columns in a DBA_view or V$view ; How can I do that in a DESC like output or similair ?
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.
I'm having a problem with grouping sets over dictionary views.
10g output:
SQL> select
2 -- 10g results
3 segment_name,
4 round(sum(bytes/1024/1024),2) mb
5 from dba_segments
[Code]...
ERROR at line 10: ORA-03001: unimplemented feature
Elapsed: 00:00:00.12
The query is fine over a non-dictionary table however (My actual code isnt against dual, but this makes it generic, the error is consistent)
11g output
SQL> select
2 -- 11g results
3 segment_name,
4 round(sum(bytes/1024/1024),2) mb
5 from dba_segments
[Code]....
ERROR at line 8: ORA-00904: : invalid identifier
Different error and that syntax, as far as I can tell, is sound - error message be damned.
An example of a query working on both versions is
select
employee_id,
sum(salary)
from
employees
group by grouping sets ((employee_id),null)
;
Am I missing something? Does grouping sets not work over the dictionary views?
Edit: Added version tags over the code to make it easier to read
There is a offline_dictionary.ir file created by Logminer which is like 13G in our "DIAG" directory. What is the significance of this file and can it be removed?
I tried to search about this file, but couldn't get much on this. This is what I got from metalink doc id 755838.1
Quote:2. HM_CREATE_OFFLINE_DICTIONARY
The job is created by the 11g script catmwin.sql which mentions that this is a job for creation of offline dictionary for Database Repair Advisor.
From unpublished Note 602459.1 - 'What Is OFFLINE_DICTIONARY': 'The system job SYS.HM_CREATE_OFFLINE_DICTIONARY executes the dbms_hm.create_offline_dictionary package which creates a LogMiner offline dictionary in the ADR for DRA name translation service. The job for generating the logminer dictionary is scheduled during the maintenance window. This job can be disabled. '
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?
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 RelatedWe want to find out difference of data for some tables between current day & previous day. We can use query with minus operation but it will take lot of time since table size is in range from 200 to 500 GB. We have to do this exercise every day.
View 5 Replies View RelatedColumn 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
need to know about materialized views.
View 2 Replies View RelatedCan we use views for real time migration? If yes then will you prefer to perform migration using views?
View 3 Replies View Relateddifference between V$ and V_$ views. It looks same to me. lets take the example of V$LOG and V_$LOG , both views returns same set of columns when described.
SQL> desc V$LOG
Name Null? Type
----------------------------------------- -------- -----------------
GROUP# NUMBER
THREAD# NUMBER
SEQUENCE# NUMBER
BYTES NUMBER
MEMBERS NUMBER
ARCHIVED VARCHAR2(3)
STATUS VARCHAR2(16)
FIRST_CHANGE# NUMBER
FIRST_TIME DATE
SQL> desc V_$LOG
Name Null? Type
----------------------------------------- -------- -----------------
GROUP# NUMBER
THREAD# NUMBER
SEQUENCE# NUMBER
BYTES NUMBER
MEMBERS NUMBER
ARCHIVED VARCHAR2(3)
STATUS VARCHAR2(16)
FIRST_CHANGE# NUMBER
FIRST_TIME DATE
We are using Oracle 10g and have 10 tablespaces defined for our Database which have 108 tables. Size of 108 tables is around 251 MB as seen during importing the dump. While creating these 10 tablespaces I used below parameters for allocation of space
SIZE 1M REUSE AUTOEXTEND ON NEXT 1M MAXSIZE 1M;
which set the initial space for 10 tablespaces to around 1032Kb each. Now my Question is after importing the dump , how the disk space for 10 tablespaces increases to 398 MB in total ?
Is there any relation of Tablespace disk space and Actual Data present in the tables ?
I am getting unexpected issue while fetching records from dba_ts_quotas & DBA_USERS Views.
FYI..
select username,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users
where username='DWHODS'
USERNAMEDEFAULT_TABLESPACETEMPORARY_TABLESPACE
DWHODS DWHODS_TBS_DATADWHODS_TEMP_DATA
select tablespace_name, username, bytes / 1024 / 1024 "Used MB",
[code].....
View dba_ts_quotas is giving tablespace name "USERS" where DBA_USERS showing default tablespace name "DWHODS_TBS_DATA".
I have created an user named "Raja" with a default tablespace as "Raja_TBS" along with a datafile "rajadata.dbf". I have taken the tablespace offline
SQL> alter tablespace raja_tbs offline;
Tablespace Altered. when I take a tablespace offline, which means I cannot read or write and the tablespace is currently unavailable for users. I am still able to create a table on the "Raja_TBS" while it is offline.
i have tried to create the index in diff tbs also but same error is there
SQL> create index inx_tbl_voicechat_unsub_ani on tbl_voicechat_unsub (ani) tablespace ideadb_index;
create index inx_tbl_voicechat_unsub_ani on tbl_voicechat_unsub (ani) tablespace ideadb_index
*
ERROR at line 1:
ORA-01115: IO error reading block from file 201 (block # 144265)
ORA-27070: async read/write failed
OSD-04016: Error queuing an asynchronous I/O request.
O/S-Error: (OS 23) Data error (cyclic redundancy check).
ORA-01115: IO error reading block from file 201 (block # 144265)
ORA-27070: async read/write failed
OSD-04016: Error queuing an asynchronous I/O request.
O/S-Error: (OS 23) Data error (cyclic redundancy check).
I tried more to write Bangla in Oracle 10gR1, or 10gR2 or Express but failed. When I write bangla in the "emp_name" field for employee name this can be save, But when I execute to see the data again then the data comes with a big change like some symbol for some character.
View 3 Replies View RelatedI 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.
I have one database which is recently upgraded from oracle 8.1.5 to oracle 10.2.0.4.The database is having around 300 tablespace and total size of the database is 1.5 TB.
The database was created in oracle 8i and all the teblespace were DMT(Dictionary Managed Tablespace) .Usually after up gradation all the tablespace are in DMT mode. Now my requirement is to convert all the tablespace into LMT (Locally Managed Tablespace) so that I can AVAIL ALL THE FEATURES OF LMT.
This database is a mission critical database and very less downtime can be allowed.
I have a table revenue
create table revenue
(
person varchar2(23),
month varchar2(3),
rev_amt number
)
and i have data in a file like below
Person Jan Feb Mar Apr Mai Jun Jul Aug Sep Oct Nov Dez
--------------------------------------------------------
Schnyder,345,223,122,345,324,244,123,123,345,121,345,197
Weber,234,234,123,457,456,287,234,123,678,656,341,567
Keller,596,276,347,134,743,545,216,456,124,753,346,456
Meyer,987,345,645,567,834,567,789,234,678,973,456,125
Holzer,509,154,876,347,146,788,174,986,568,246,324,987
Müller,456,125,678,235,878,237,567,237,788,237,324,778
Binggeli,487,347,458,347,235,864,689,235,764,964,624,347
Stoller,596,237,976,876,346,567,126,879,125,568,124,753
Marty,094,234,235,763,054,567,237,457,325,753,577,346
Studer,784,567,235,753,124,575,864,235,753,864,634,678
i want to load it into the table in the following way.
Person Month Revenue
-------------------------
Schnyder Jan 345
Schnyder Feb 223
Schnyder Mar 122
Schnyder Apr 345
Schnyder Mai 324
Schnyder Jun 244
Schnyder Jul 123
Schnyder Aug 123
Schnyder Sep 345
Schnyder Oct 121
Schnyder Nov 345
Schnyder Dez 197
........ ... ...
How to write control file to load this data into the above revenue table.
Question 1) I have read the following statement in a PL/SQL book.
Quote:To check whether an existing procedure is compiled for native execution or not, you can query the following data dictionary views:
[USER | ALL | DBA]_STORED_SETTINGS
[USER | ALL | DBA ]_PLSQL_OBJECTS
However, I when i query the view USER_PLSQL_OBJECTS I get the following error message:
Quote:ORA-00942: table or view does not exist
Question 2) I have read the PLSQL_WARNING can be set to DEFERRED at the system level. However, I am unable to defer it. tell me how to apply defer caluse to following statement:
Quote:ALTER SYSTEM SET PLSQL_WARNINGS ='DISABLE:ALL'
can i modify oracle data dictionary
View 6 Replies View RelatedChanging Data dictionary by DML Sentences (e.g Insert, Delete, Update,...), Will be possible? Or just by DDL Sentences?
View -1 Replies View RelatedI 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.
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.
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.
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.
I wanna to DROP a table called EMPLOYEES, but when I execute the DROP TABLE EMPLOYEES, I get a ERROR saying that I cant do it because this table do reference to another table(s).
I tried to use the DBA_CONS_COLUMNS and DBA_CONSTRAINTS data tables, but its not enough to find it.
How can I find these references?
which data dictionary should i choose to see the foreign key and primary key of table
View 2 Replies View Related