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 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 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 Relatedneed 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
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".
How to create dynamic views? I need literature on dynamic views.
View 5 Replies View RelatedIs 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 RelatedDespite it being one of the major selling points of Exadata (especially from X3 onwards), I'm struggling to find much information on our usage of the Exadata Smart FlashCache (I'm running RDBMS 11.2.0.2 BP7 on a V2 quarter-rack).
I can verify usage of the FlashCache by checking whether the object has been 'pinned' to the FlashCache via DBA_SEGMENTS and I can check for FlashCache usage by querying gv$sysstat (and even v$mystat), but are there other views that I could use? It seems a bit strange for Oracle not to provide the DBA all that much insight into their usage of this feature...
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?
We are using the below query to create a Materialized View but it has been running since 3 hours. It is an Oracle 9i database running in HP-UX.The quey is as follows,
(SELECT
/*+ use_nl(A) parallel (A,4)*/
A.ICD_CODE AS ICD_CODE,
A.ICD_DESC AS ICD_DESC,
A.PROC_GROUP as PROC_GROUP,
B.COMPL_ICD_CODE AS COMPL_ICD_CODE,
B.COMPL_GRP_TXT AS COMPL_GRP_TXT,
C.PROC_TYPE AS PROC_TYPE ,
[code]....
how joins work with in-line views.I have a query and its explain plan as below:
SELECT e.ename,e.deptno,d.dname FROM
dept d,
emp e
WHERE e.deptno=d.deptno
AND e.deptno=20
[code]....
I do not find any difference in both the explain plans. Both are same. In my second query, the filtered rows will be joined to dept table. And hence the baggage will reduce.But how can I verify that in-line view has worked better?
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Solaris: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
5 rows selected.
I have a problem with views and nested selects which I cannot explain. Here is a trimed down version of the research I have done. notice the following:
1) all code is executed from the same user CDRNORMALCODE. this user has all views and procedural code
2) all data is owned by a different user CDRDATA. This user has no views and no code.
My problem is this:
If I reference the table directly with a delete statement that uses a nested select (i.e. IN clause with select), the index I expect and want is used.But if I execute the same delete but reference even the most simple of views (select * from <table>) instead of the table itself, then a full table scan is done of the table.
Here is an execute against the table directly (owned by cdrdata). Notice the reference to the table in the table schema on line 3. Also please notice INDEX RANGE SCAN BSNSS_CLSS_CASE_RULE_FK1 at the bottom of the plan.
SQL> show user
USER is "CDRNORMALCODE"
SQL>
SQL> explain plan for
2 delete
[code]...
OK, here is an update. The views I am useing normally have instead of triggers on them. If I remove the instead of trigger the problem looks like it goes away, when I put the trigger back the problem comes back.But why would an instead-of-trigger change the query plan for a view?
SQL> DELETE FROM PLAN_TABLE;
5 rows deleted.
SQL> explain plan for
2 delete
3 from BSNSS_CLSS_MNR_CASE_RULE_SV
[code]...
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?
How to export the procedure,views,function and packages in a database, by using Export commmand.
View 2 Replies View RelatedI am looking for some tools for Performance analysis and optimization for Oracle. For now I looked over Spotlight, Ignite and Embarcadero DB Optimizer.
View 1 Replies View RelatedWhy do we need materialized views when the normal views also serve the same purpose?
View 2 Replies View Related I am writing a checker that should hit all the views one after other in database and report if there is an SQL error ( Page: Data cannot be retrived from the view XYZ).
The query I am running is
"select * from $view fetch first 1 rows only".
better query which runs faster as I have to query all the views in db in the script ( script runs every 10 mins on cron).
I have a problem Mat view refresh...I have to take a snapshot of the table before nsert or update to the table....below is the code written to achieve this..
create materialized view my_view refresh complete as select * from t2;
create or replace trigger TRG_T2
before insert or update on T2
declare
X number;
[code]....
However this is not getting refreshed... Also i noticed that it works for the very first time (after first insert or update) and then for successive ones it doesnt.. ..
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';
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 want to list out all the views in a schema. What table can I query?
View 2 Replies View RelatedI have a table tblcustomer and a view called vworder. I need to create a trigger such that any data being added into vworder first checks if the field customerid has the data in customerid of tblcustomer.. all it has to do is spit out a error "Customer ID not found"
So I created a view such as
Quote: create or replace TRIGGER trig_order
BEFORE INSERT OR UPDATE ON vworder
FOR EACH ROW
DECLARE
cust_id VARCHAR2(20);
BEGIN
SELECT customerid INTO cust_id FROM tblcustomer WHERE customerid = :new.customerid;
EXCEPTION
WHEN NO_DATA_FOUND THEN
Raise_application_error(-20000, 'Customer ID Does Not Exist');
END;
But it comes with error
Quote: ORA-25001: cannot create this trigger type on this type of view 25001. 00000 - "cannot create this trigger type on views" *Cause: Only INSTEAD OF triggers can be created on a view. *Action: Change the trigger type to INSTEAD OF.
I am not an expert at all and I need to finish this today itself.
I have a table "tl" which is partitioned--say 30 partition and for each partition there is a seperate view like as follows
create view view_t130 as select * from tl partition (p30);
create view view_t129 as select * from t1 partition (p29);
.
.
.
create view view_t101 as select * from t1 partition (p01);
my question is how to use hints on this table if your are using view to access the data from internal table.
Normal structure is if i don't wrong:-
index( <<view name|view alia name>> <<table name|table alias name>> name of index)
Consider my case
select * from view_t130 where <index_column> --not picking up index
i want to give expicit index hint.so i used the same structure that i specified above but it didn't work.
select /*+ index( view_t130 t1 <index_name) */ * from view_t130 where <index_name>
how to give explicit index hint..but one constraint is i cannot give any alias names for internal tables because those(view structure) are generated by predefined scripts..so it's not possible to change it.
Is there any function to refresh all views and functions in oracle.
View 4 Replies View RelatedI have n number of materialized views in my schema ..I want to refresh all M_views at once by using cursor
View 3 Replies View Relatedhow to avoid the bind variable in view.
The query is correct but it contains bind variable Based on this query, View has to be created for report
like example select * from emp where deptno = :deptno
How to get the correct result by avoiding bind variable because view does not accept bind variable.
I need to know that what is best approach for replication. Materialized View or Streaming. We have to replicate a subset of database at different nodes.
View 3 Replies View Relatedfor following two questions.
1. I have created a materialized view with following syntax in 11g R1 database.
CREATE MATERIALIZED VIEW fr_emp_bonus_record_vw
BUILD IMMEDIATE
REFRESH COMPLETE
START WITH SYSDATE NEXT SYSDATE+1/48
ENABLE QUERY REWRITE AS
SELECT person_id, <columns>
This MV will have around 300K records and this MV will be used in other queries based on person_id. So I have created an index on mv.person_id with following syntax. I have chosen REFRESH COMPLETE, I am assuming that, Oracle truncates this table and recreates it. When it recreates this MV, does it analyze automatically or do I need to do it manually.
CREATE UNIQUE INDEX mv_u1 ON mv(person_id);
2. It takes around 2 minutes to populate 300K. While it is populating the data, if somebody accessing this view or query where this view is being used, are they going to get any error?
I'm using Toad 11.6, I can see about 156 sessions for the database in the session browser but when I do a select from v$session, only 40 sessions are showing up. I was able to look at all the sessions until yesterday. I tried several views like v$session, v$open_cursor etc, but only 60 sessions show up. I'm connected to the database using same user login yesterday and this morning.. May be this user had some privileges revoked last night??If so is it possible to limit sessions in these views (v$session etc) but it's strange that I can see them in the session browser in Toad. 'm very confident that there are 156 sessions in the database but it's just that I'm not able to see them in the v$session etc views. I need to troubleshoot a session but it is not showing up in any views.
View 9 Replies View Related