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.
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 RelatedI want to list the tables in a schema which contains a particular value or data.
View 2 Replies View RelatedI created a user and granted connect,resource priviliges. I gave access to this user for only 5 tables. when i check it later, other tables are also given access. How can i avoid this and give access to selected tables.
View 9 Replies View RelatedOracle 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 ?
I have 12 different tables
Record_jan
Record_feb
.
.
.
Record_nov
Record_dec
Based on the month i need to do some i/o operations from these table.
For eg: If it is january then Record_jan table should be accesed or for nov Record_nov table should be accesed.
If I try to store the table name in some variable and then give the variable name in place of table name then it gives error.
var:=Record_month;
Select * from var;
this gives error.
I just want to list and group all my tables that are linked together by constraints. I just want my tables to be able to be listed together as one particular database. my tables are , CUSTOMER, ORDER_INFO, ORDER_LINE, PRODUCT. They're all linked together by way of constraint and I want to list and print them all together as one DB. HOW DO I put them all in one schema and then also list them all together and print/illustrate them as one. also, I tried to import them into their own scheme but i ran into a series of probs regaurding the .dmp file being read.
View 2 Replies View RelatedI would like to know 2 days back lock conflicts session information which user held the lock,sql text,when the lock released. any sql query or view in oracle 11g db.
View 1 Replies View RelatedExample:
select * from emp
where empno in (123 234 345 124)
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'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?
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?
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 RelatedI am an Oracle newbie.
We have 2 fact tables and one lookup table in this structure:
FACTTABLE1 (C1_ID, C2, SALE)
FACTTABLE2 (C1_ID, C3, SALE)
LOOKUPTABLE (C1_ID, C1_NAME)
The DBAs have built 2 Materialized Views, which aggregates data in the fact tables at column C1 level
MAT_VIEW1 :SELECT C1_ID, SUM(SALE) SALES from FACTTABLE1 join LOOKUPTABLE on C1_ID
MAT_VIEW2: SELECT C1_ID, SUM(SALE) SALE from FACTTABLE2 join LOOKUPTABLE on C1_ID
We are using an old BI tool that can ONLY generate Inline Views in these formats.
CASE1:
select
INL_VIEW.C1_ID
,LOOKUPTABLE.C1_NAME
,sum(SALE) SALE
from
(select C1_ID, C2_ID, null C3_ID, SALE from FACTTABLE1)INL_VIEW
join LOOKUPTABLE
on INL_VIEW.C1_ID = LOOKUPTABLE.C1_ID
group by INL_VIEW.C1_ID, LOOKUPTABLE.C1_NAME
CASE2:
select
INL_VIEW.C1_ID
,LOOKUPTABLE.C1_NAME
,sum(SALE) SALE
from
(select C1_ID, null C2_ID, C3_ID, SALE from FACTTABLE2)INL_VIEW
join LOOKUPTABLE
on INL_VIEW.C1_ID = LOOKUPTABLE.C1_ID
group by INL_VIEW.C1_ID, LOOKUPTABLE.C1_NAME
CASE3:
select
INL_VIEW.C1_ID
,LOOKUPTABLE.C1_NAME
,sum(SALE) SALE
from
(
select C1_ID, C2_ID, null C3_ID, SALE from FACTTABLE1
union all
select C1_ID, C2_ID, null C3_ID, SALE from FACTTABLE2
)INL_VIEW
join LOOKUPTABLE
on INL_VIEW.C1_ID = LOOKUPTABLE.C1_ID
group by INL_VIEW.C1_ID, LOOKUPTABLE.C1_NAME
Oracle 11g rewrites Case 1 and Case 2 to use the correct materialized views. But for case 3, it goes to the base fact tables 1 and 2. Is there a way to make oracle use the MVs even if there is a UNION ALL in the inline view? There is a 1:M Foreign Key relationship between LOOKUPTABLE.C1_ID and the 2 fact tables.
In scott/tiger user total 30 view tables are stored.Right now i want to drop all views with using query.
View 3 Replies View RelatedI have a view with complex query that have grouping and aggregations in sub-queries.
Now we would like to change it to a materialized view. However, materialized view does not support GROUP BY clause in sub-query. The solution is to create several view for the sub-queries and then create a materialized view based on it. It seems it can work, but I guest every time complete refresh is required.
How do i list all tables from dba_tab_columns which contains both column name='id' and 'date'. I don't want to list the tables contains either 'id' or 'date'
View 8 Replies View Relatedis 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.
The database system they have has around 5000 tables. The majority of these are not used (it is an off the shelf package). What I am trying to do is get a list of all tables in the database BUT only those that contain records (not the empty tables) and then copy this to an excel spreadsheet. We use a tool called aquadata to run sql enquiry statements on.
View 5 Replies View RelatedI have 2 tables which contain 1000's of cash and stock transactions and I need to somehow get a list of the distinct clients
select distinct client_id
from pf_cash_txns pct, pf_stock_txns pst
Quote:ORA-00918: column ambiguously defined
I need to retrieve the list of tables with same name from 2 different schemas.
View 10 Replies View RelatedI am executing a script that is deleting some parent records and the corresponding child records as I have used the "on delete cascade" with the Foreign key Constraint.
My question is that can I list the records that are being delete from all the tables i.e. both parent and child tables. Is some thing like spooling can work in this or do I have some other option with which I can see(select) all the deleted records.
I am in the task of clean up of tables. I need to find the list unused tables and procedures. Is there any way where i can find when was the last time the table queried?
Give sql query to find the list of unused tables and procedures.
I have 2 tables Table a(girlscoutid, item, quarter)Table b(girlscoutid, fname, lname) I want to get the names of ppl who did not sell any item/s for the
4th quarter
girlscoutid item quarter7771
drinks 4QS9000
tickets 4QW7771
cookies 2QS5085books 3QF3010tickets3QW5085
cookies1QF lscoutid
fnamelname7771 jenn richochet9000 laura wilkins5085sally mae3010dora mckenzie
This is what I have, however, I get duplicates and also 7771 shows up on the list even though she sold an item on the 4Q.
SELECT fname, lname, a.girlscoutid
FROM a,b
WHERE a.girlscoutid = b.girlscoutidAND term NOT LIKE '4Q_';
I want to create a materialized view for the last 10 days with the enable query rewrite option.
e.g. i want to create a view with the list of employees who joined the company in the last 10 days.
create materialized view M_Employee
refresh fast on commit
enable query rewrite as
select joining_date , name
from
employee
where joining_datde < TRUNC(sysdate) - 10
I seem to get the error SQL Error: ORA-30353: expression not supported for query rewrite
30353. 00000 - "expression not supported for query rewrite"
*Cause: The select clause referenced UID, USER, ROWNUM, SYSDATE.
This error is self explanatory , but is there any work around to have a query liek this to list the employees based on sysdate.
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.
I can do any sql commands against the schema I want (PSUSYS) with my username(SCHWAN) in the sql window, but when I want to export a table(created with a different schema) no tables are listed in the export window. This does work for my coworkers with the same access.
At one time this used to work so I'm wondering if it is a parameter that I inadvertently changed.
Is it possible to get a list of tables in a schema on which Audits are enabled.
View 2 Replies View Related