SQL & PL/SQL :: Create View Without Base Table?
Jun 25, 2012How to create a view without base table . some example?
View 8 Replies
ADVERTISEMENT
Server Administration :: How To Find The Base Table Of A View
Aug 15, 2011How to find the base table of a view,such as a view:
create or replace view vw_test as select * from tb_test;
Is there dic view to get the base table of view?
PL/SQL :: Update A Base Table Using Updatable Materialized View?
Sep 12, 2013I am using sqlplus. example to update a base table using a updatable materialized view.
View 17 Replies View RelatedPerformance Tuning :: Creating Index On Base Tables Of A View?
Dec 9, 2010I have a view on base tables holding historical data for previous 60 months(one table per month) with union all operators.create index on those base tables will improve performance or creating a primary key with disabled novalidate will improve for retrieving data?
The view has around 8 million data and used as a fact table with 4 dimension tables.A DTS package from MSSql side refreshes OLAP cube by retrieving data from these tables in oracle.
SQL & PL/SQL :: Create A Table Through Inline View?
Oct 1, 2010 there is a diff. problem for me.when i create table through inline view then it shows 2246 records but if i check these records only in select statement then it shows 124 records. i cant understand how table shows 2246 records even then atual records in inline view shows only 124 records.
following is a query
create table sam as
select * from
((
select distinct stck.item_code
from (
select item_code,bal
[code]...
SQL & PL/SQL :: How To Create Materialized View On Partition Table
May 16, 2012how to create materialized view on partition table?
View 1 Replies View RelatedSQL & PL/SQL :: Create Directory - Table Or View Does Not Exist
Oct 2, 2010I am trying to do the following-
CREATE DIRECTORY my_dir AS 'd:oraclefiles';
GRANT READ,WRITE ON my_dir TO PUBLIC;
The above throws up an error-
ORA-00942: Table or view does not exist
PL/SQL :: Join Between Table With User Based Content And Base Table
Jul 30, 2012I stumbled about some weird 11gR2 behavior (running on AIX).When I performed a join between a table with user based content (parts belonging to an sourcing scope) and a base table (parts available) whereas the parts have to fulfill a special regular expression, it showed that the same query is faster when using outer join than inner join (about 0.7sec vs. 20sec; which makes me believe that regexp_like works wrong when involved in an inner join).
i tried the same statement with a standard like (but not fulfilling the same condition).This time performance was as expected (inner join outperforming outer join).
Oracle version information
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
[code]...
I can see it, the execution plan for the "inner join" doesn't show so much more costs than the one for the outer (but why at all is does an inner join cost more?) ...The execution plan for both "not like" is the same and (surprisingly ;-) ) similar to "outer-regexp".
I hope sample data are not needed as there would be needed a lot...this is the second time I came across the "plan worse but execution time better" phenomenon.
SQL & PL/SQL :: XML Parsing Into Base Table
May 20, 2010I want to load XML into base table using PL/SQL procedure.For that I have wrote procedure but that does not work well .
View 4 Replies View RelatedSQL & PL/SQL :: Find Out Base Table Of Synonym?
Jun 10, 2011How to find out the base table of a synonym?
View 3 Replies View RelatedForms :: Changing A Block Not Base Table
Oct 15, 2012How do I change the status of a block to change?
View 7 Replies View RelatedSQL & PL/SQL :: Create Primary Key On View And Use This View For Creating Foreign Key?
Oct 8, 2010is it possible to create primary key on view and use this view for creating foreign key .
View 3 Replies View RelatedSQL & PL/SQL :: Trigger Fired - Final Update To Base Table Not Happening
Jul 1, 2011In my DB,We have a table rqst_list
Create table rqst_list(id Number, --(PK)
pkg_name varchar2(100),
status varchar2(100))
When a record is inserted into this table(rqst_list), a trigger is fired which calls the package which is there in pkg_name.The function does some function.If there are no exceptions while executing the package, then SUCCESS needs to be updated in the table for the id for which the trigger got fired.I have used AFTER INSERT ROW level trigger.
But,the final UPDATE (UPDATE the rqst_list table to SUCCES/FAILURE)to the base table inside the package is not happening.
Security :: Unable To See Data In Base Table Where Can Be Viewed In Synonym?
Sep 9, 2011I have one schema argus_app where there is a table cfg_enterprise. There is a view v$cfg_enterprisewhich selects data from cfg_enterprise and there is apublic synonym cfg_enterprise for v$cfg_enterprise.
When I login to argus_app and select from cfg_enterprise there is no data where as i log in to sys and select I can see data.
SQL> conn /as sysdba
Connected.
SQL> select enterprise_name from cfg_enterprise;
ENTERPRISE_NAME
[code]...
what would be creating this problem.
Application Express :: Select Some Data From Table Base On SYSDATE?
Sep 6, 2012I'm trying to select some data from table base on SYSDATE. The below query does not return any data.
My query is:
select count(TICKET_ID) "ECEMEA" from QTMT_TICKETS where STATUS_ID=1 and TEAM_ID=3 and RECEIVED_DATE=sysdate
why this does not work? Date format in my application is: DD-MMM-YY (16-AUG-12). In the database the dates are stored in this format: MM/DD/YYYY (08/25/2012). Does it mean, that I have to play with the format?
SQL & PL/SQL :: Create View In Another View?
Dec 1, 2011Can we create view in another view
View 10 Replies View RelatedSQL & PL/SQL :: Materialized View - Table Or View Does Not Exist
May 2, 2012I 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).
Enterprise Manager :: What Is The Difference Between Create External Table Vs Create Table
Apr 29, 2011What is the difference between CREATE EXTERNAL TABLE Vs CREATE TABLE .?
Is CREATE EXTERNAL TABLE included in CREATE TABLE?
SQL & PL/SQL :: How To Create View
Feb 25, 2011I want to create view as follows.
Example:
Create or replace v_name
select job,sal, <funcation>
from emp;
Note:- Funcation returning 4 values.
Requirement:- I want to create view 6 columns.
how to create view.
SQL & PL/SQL :: How To Create A View Using Five Tables
Oct 30, 2012I am trying to create view ..Using totally Five tables....
The main driven table almost 10 GB Data
When i am creating a view..
End of communication channel Error raise...
At the same time I am running query ..Running Fine...
SQL & PL/SQL :: Create View With Functions
Jan 17, 2011I have created a package with some functions in it.I want to create a view with from 2 tables but also i wanto to use the functions from some fields of the view. (e.g. if the wview has 10 fields the 5 of them will be from the 2 tables and the other from package functions).
View 2 Replies View RelatedPL/SQL :: Create Or Replace View
Sep 11, 2012I am about to pass 1Z0-051 and have been cramming. Ran across this scenario: Create or replace view. Correct answer: allows insert from view in multitable insert statement. Can I actually insert data from a view? My understanding is no data actually exists in a view. This has created a cavernous feeling of inadequacy.
View 3 Replies View RelatedCreate View Off Split String
May 22, 2012How do i split a string based on a space. And then create a view with three columns of that split string.
For example if i have a table with Full name as a column how do i split that string and create a view with firstname, middlename, lastname as column names with the split string as data.
If you need anything clarifying write back.
How To Create Index On Materialized View
Aug 6, 2010How to create index on materialized view which is refreshed daily basis.
View 3 Replies View RelatedSQL & PL/SQL :: Currency Conversion In SQL To Create A View
Apr 27, 2012I need to implement the Currency Conversion logic in SQL to create a view.
Sample data
Acount Table
Accno Amt Crncy_id
--------------------
101 1000 1
102 500 2
103 500 2
104 500 3
107 500 4
.
.
.
Currency Table
Crncy_id Crncy_nm
-------------------------
1 INR
2 USD
3 GBP
4 EUR
My local currency is INR, I need to write a query to convert the foreign currency to loacl currency INR.In my case i need to write aSQL for a view.It should not implement through PL/SQL
SQL & PL/SQL :: How To Create A View Having All Rows From Tables
Apr 21, 2010I have a table as shown below,
SQL> select * from Main_Table;
AA
--------------------
SUB1
SUB2
Here the SUB1 and SUB2 are "tables" and are similar in their structure. The "Main_Table" will be update dynamically and the no of rows in this table will vary.
Now my question , i need to create a view which will have all the rows from these tables ,in the current case it is something like
create or replace view sample
as
select * from SUB1 union all
select * from SUB2
How can this be achived. I tried as shown below:
spool file_to_exe.sql
select 'select * from ' || AA ||' union all ' from Main_table;
spool off
i will end up in a union all "extra" , if i do like this.
how can achieve this ?..
How To Create Unique Index On A View
Aug 24, 201211.2.0.1...How do I create an index on a view or any workaround that my view won't get duplicates?
SQL> create unique index indx01 on db_backup_details_vw(id);
create unique index indx01 on db_backup_details_vw(id)
*
ERROR at line 1:
ORA-01702: a view is not appropriate here
Replication :: Oracle 10g - Create Materialized View
Jun 16, 2008We are using Oracle 10g rel 2. The replication is setup on 1 server which is in City A, and the snapshot server is in City B.
City A .
Create materialized view log on table-a
with primary key
including new values.
City B database.
Create user test_rep identified by test
grant connect, resource, create any materialized view , table, view , procedure to test_rep.
Create materialized view city-A_db_MV
refresh fast
select * from cityA.Tablea@city-Adb
When i select from city-A_db_MV, it showed the complete table-A of city-A database.
Now if we make any changes to City-A table at the Master site, will it be propagated automatically to the MV site.
I guess we need to create jobs to push / or refresh fast .. isnt it. But exactly how to do it is a question.
Secondly if we make a replication group at Master site at city-A db, how do we refresh that Group and how to monitor whether it is refreshing on time or not? do we need to see the jobs every now and then.
but still a lot of questions unanswered, even though i had read the documents earlier.
1-The MView was created without identifying that after what interval it will be fast refreshed.
2- How to Manually refresh it. Does it support On Commit, I think it is not.
3- Where should be we make a group and then add the table to that group and refresh that group.
Should this group belongs to the Master Site or to MV site?
SQL & PL/SQL :: Cannot Create Materialized View - Insufficient Privileges
Aug 3, 2010i am trying to create materialized view but it give me insufficient privilege i give it all grant and i run it from sys it give the same.
View 3 Replies View RelatedReplication :: Unable To Create Materialized View
Mar 13, 2008I have to create a materialized view for a table which does not have index on any field.
While creating a Mview i am getting an error "TABLE DOES NOT HAVE THE PRIMARY KEY CONSTRAINT".
application developers do not want to create an index on the base table onto which MView is to be created.
is there any way to create a materialised view for the table without index, or is it necessary to have the index on the base table before creating MView on it.