I 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...
I 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 ?..
I'm stuck on the CREATE VIEW part and the SELECT statement to get the output of the CREATE VIEW. We were instructed not to use AND and simply use JOIN on this exercise.
Question - For employee John Smith, give the project(s) he is working on, his department's name and the name of his division. See crows foot diagram on a separate file.
CREATE TABLE DIVISION
(div_code NUMBER(5),
emp_num NUMBER(5),
div_name VARCHAR(25),
CONSTRAINT pk_division primary key (div_code));
[code].......
SELECT - this SELECT statement is supposed to generate the output of the CREATE VIEW CODE above. SEE Crows Foot diagram attached.
is it possible to create primary key on view and use this view for creating foreign key .
View 3 Replies View RelatedThere is an error after error, that's why I came here
What's is wrong? error message: virtual column not allowed here
CREATE TABLE test2(
id NUMBER PRIMARY KEY,
surname VARCHAR2(20) NOT NULL);
CREATE TABLE test1(
[Code]...
It would be nice to describe this situation and where does this error come from.
Can we create view in another view
View 10 Replies View RelatedSuppose a view is created using multiple tables. Can we update the view? And when we update the view will it effect the base table?
View 5 Replies View RelatedAny tool or way to view the schema and relations between tables of an Oracle database?
View 1 Replies View RelatedThis is just for learning purposes. What if I would like to create my own materialized view utilizing only tables and a job.
View 28 Replies View RelatedI 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.
I have created 3 tables and some indexes, but these objects do not show up in dba_segments view. Is this a normal behaviour? Previously, with dictionary managed tablespace, I can specify the minimum extent to create, when the table/index is created. But I'm not sure how the locally managed tablespace work.
I'm using Oracle 11g R2 (11.2.0.1.0) for Microsoft Windows (x64), running on Windows 7.For the purpose of reproducing this issue, I have created the tablespaces as follow:
CREATE TABLESPACE CUST_DATA
DATAFILE 'd:appasusoradataorcl11gr2CUST_DATA01.DBF' SIZE 512K
AUTOEXTEND ON NEXT 256K MAXSIZE 2000K
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K
SEGMENT SPACE MANAGEMENT AUTO;
[code]...
I 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 RelatedI 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 RelatedI 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.
How 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 which is refreshed daily basis.
View 3 Replies View Related 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]...
How to create a view without base table . some example?
View 8 Replies View RelatedI 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
11.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
how to create materialized view on partition table?
View 1 Replies View RelatedWe 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?
i 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 RelatedI 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.
i try to make trigger to execute immediate create or replace view
(
CREATE OR REPLACE TRIGGER xxxx
BEFORE INSERT ON table1
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
declare
l number ;
v_ddl varchar2(4000);
v_job number;
[code]....
it give me error for insuffition privilage in execute immediate and after i make GRANT CREATE ANY TRIGGER TO user give me error can't commit in trigger.
How I can create a Materialized View without having any data in it.
For e.g.
I create a Materialialized View based on a View.
CREATE MATERIALIZED VIEW test_mv
REFRESH FORCE ON DEMAND
AS
SELECT * FROM test_view
In the above case the data fetched by the view test_view gets stored in the Materialized View test_mv. Suppose I want materialized view test_mv to get created with all the columns of test_view but not the data. I will refresh the materialized view test_mv later for data as and when required.
What shall I do for immidiate formation of materialized view test_mv without data.
I created a view with parameters l_id and l_name, how can i find them in oracle view?
Create Table tb_test
(
Id Number,
Name Varchar2(64)
);
Create Or Replace View vw_tb_test(l_id,l_name)
As
Select Id,Name From tb_test;
I am trying to create view that selects a number of rows from a table and has a calculated field which is a counter form 1 to n next to each row.
In the below example, s_name is repeated many times each with a differnet serial, I want to cteate a view that shows a counter for the occurences of s_name next to the serial.
CREATE TABLE XYZ
(
SERIAL NUMBER PRIMARY KEY,
S_NAME VARCHAR2(30)
);
INSERT ALL
INTO XYZ VALUES (1, 'ABC')
INTO XYZ VALUES (11, 'ABC')
[code].....
1.As we can create materialized view for accessing data from other schema but same database. will it be effective or it will act as a normal view.
2.Will materialized views can be created in Fast mode for the above scenario?
I 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