SQL & PL/SQL :: View Is Created Using Multiple Tables

Apr 28, 2011

Suppose 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


ADVERTISEMENT

New Tables And Indexes Created Not Show In Dba-segments View?

Aug 8, 2012

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]...

View 2 Replies View Related

REFRESH GROUP Is Automatically Created When A Materialized View Is Created?

Aug 17, 2012

tell me if a REFRESH GROUP is automatically created when a materialized view is created?

View 3 Replies View Related

SQL & PL/SQL :: Using Create View And Joining Multiple Tables In Oracle

Apr 8, 2012

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.

View 9 Replies View Related

Trigger When View Created?

Aug 24, 2011

I want to create a meta data table which has all the views users can access with a description of what the view was supposed to do.

Is there a trigger that can be created that fires when a view is created? To make it easy only one user in this system (oracle 9.2.0.8) has the create view privilege.

I know I could write a procedure to create views with dynamic sql that would do this but that seems a bit of overkill.

the sequence of events should go like this:
view created
trigger fires
record with view name and creation date inserted in a table
later, manually go to the table and add explanatory comment

or can I add a comment to a view like you can to tables?

View 3 Replies View Related

Source SQL For Oracle View Created

Apr 15, 2013

Is there a way to find the source sql that created a view? Do you know the sql the sql that created a view?

View 5 Replies View Related

Oracle 10g - Associate Created View With Certain Level Of Security

Nov 12, 2006

I have the following questions :

1. After creating a view, how do I associate that view with certain level of security. As in, only a few users must be allowed to access that view.
2. How can I create a new user-login and password for my database application?

View 1 Replies View Related

Client Tools :: Can't See Tables Created

Apr 22, 2012

1. I logged in the oracle sqlplus using cmd with sys as sysdba user and run the attached script for creation of some tables.

2. The script ran successfully and I could see the data in all tables which are , select * from authors; select * from books; select * from inventory;

3. I logged in with same user using toad and no such table is showing.

PS: I used commit after step 2.

View 3 Replies View Related

Security :: View / Obtain Password Once Database Link Has Been Created?

Dec 24, 2011

Is there a way to view/obtain the password once a database link has been created. Some sys dba table?

View 7 Replies View Related

Search History For All Tables Created By A User

Mar 28, 2012

I want to find the history of manipulation the database by filtering the user who created the last tables, what tables are, when he created it etc ..

I'm using oracle XE and the client is toad.

View 3 Replies View Related

Server Utilities :: Loading Multiple Input Files Into Multiple Tables

Jul 9, 2012

NGFID;RECTYPE;RECNAME
25;7;POLES
PARENT
CHILD;1401;9845075;2020
817;8;SUPPORT
PARENT
CHILD

Required output:-

AREA_SRNO = 1
AREA_NAME = '3rivieres.export.ngf'

File :-mauri.export.ngf

NGFID;RECTYPE;RECNAME
257;7;POLES
PARENT
CHILD;1401;9845075;2020
8174;8;SUPPORT
PARENT
CHILD

Required output:-

AREA_SRNO = 2
AREA_NAME = 'mauri.export.ngf'....etc

CREATE TABLE NGF_REC_LINK
(
AREA_SRNO NUMBER(2),
AREA_NAME VARCHAR2(40),
NGFID NUMBER(20),
TABLENAME VARCHAR2(40),
PARENT VARCHAR2(200),
[code].......

find the ctl file (ngf_test.ctl) and modify the ctl file as per my requirement.

View 6 Replies View Related

SQL & PL/SQL :: View Created With Compilation Errors / ORA-01775 - Looping Chain Of Synonyms

Jun 21, 2011

I created a view in DB2 .I copied the code of view from DB3 and created in DB1. The view got created but with compilation errors.

SQL>
SQL> CREATE OR REPLACE FORCE VIEW bala.cee_efacts_pie_order_v (cenumber,
2 installationid,
3 areanumber,
4 clli,
5 servicedate,
6 forecasttype,
7 jobid,
8 shippriority,
[code]....

I checked the status of view in user_objects , it was invalid.How to make it valid ?

View 11 Replies View Related

Performance Tuning :: Index Has Been Created On Both Depart_id For Two Tables

Jul 30, 2010

SELECT department_id
FROM (SELECT department_id
FROM employees
UNION
SELECT department_id
FROM employees_old )
WHERE department_id=100;
[code]....

The index has been created on both depart_id for the two tables. The only difference between the two I observed was the 1 recursive call for the 1st sql.and also, one additional view in the plan.There is a little difference in bytes sent over the network.

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
[code]....

Is there any performance impact you find in those above two sqls if you compare?

View 14 Replies View Related

Server Utilities :: Load 780 CSV Files Into 12 Tables Created In Database - Sql Loader?

Jul 22, 2011

I have 780(12*65) csv files generated from 65 databases.Now I have to load this 780 csv files into 12 tables created in my database for some monitoring and reporting purpose.to call the sql loader I am plannig to create 780 lines like below.

sqlldr abc@tns/pwd control='E:htmlctlhtml_broken_jobs_rpt.ctl' log='E:htmlreportloghtml_broken_jobs_rpt.log'
sqlldr abc@tns/pwd control='E:htmlctlhtml_db_size_rpt.ctl' log='E:htmlreportloghtml_db_size_rpt.log'
sqlldr abc@tns/pwd control='E:htmlctlhtml_fragmentation_rpt.ctl' log='E:htmlreportloghtml_fragmentation_rpt.log'
sqlldr abc@tns/pwd control='E:htmlctlhtml_index_stats_rpt.ctl' log='E:htmlreportloghtml_index_stats_rpt.log'
sqlldr abc@tns/pwd control='E:htmlctlhtml_invalid_object_rpt.ctl' log='E:htmlreportloghtml_invalid_object_rpt.log'
sqlldr abc@tns/pwd control='E:htmlctlhtml_long_running_queries_rpt.ctl' log='E:htmlreportloghtml_long_running_queries_rpt.log'

we know creating 780 control files is the difficult task.So I have created only 12 control files. is there any mechanism to pass a varible (planning to declare it in the sqlldr line) to the infile clause like below in sql loader?

infile "E:htmlreportoutput&a_html_broken_jobs_rpt.csv"

here a is the variable name. it will change every 12 csv files once.

or

is there anyother way to achive this?

View 8 Replies View Related

Server Administration :: Find When Database Role Created And Who Has Created?

Feb 14, 2013

Is there a way to find when was a database role created and who has created?

View 5 Replies View Related

Multiple Sub-queries In View

Jun 16, 2009

I have a table similar to the following,

USER, DETAILS, TYPE, UPDATED
1, user1home1, 1, 01/05/2009
1, user1home2, 1, 02/05/2009
1, user1work, 2, 03/05/2009
1, user1mobile1, 3, 04/05/2009
1, user1mobile2, 3, 05/05/2009
1, user1email, 4, 06/05/2009
1, user1other, 5 ,07/05/2009
2, user2home1, 1, 01/05/2009
2, user2home2, 1, 02/05/2009
[code]...

which contains multiple contact details for users of different types; type 1 is home, type 2 work etc. The following query returns the user's number and the latest home number for that user.

select user, details as latest_home_number from nc_test t
where type = 1
and updated = (select max(updated) from nc_test t2
where t2.user = t.user
and t2.type = t.type)
order by t.user

However I am not very experienced with sql and I am not sure how to create a view which would contain the fields:

user, latest_home_number, latest_work_number

View 3 Replies View Related

SQL & PL/SQL :: Multiple Row View From Splitting Table?

May 8, 2010

Table Name : Trans

chitta_enn number(10,0)
varavu_patti varchar2(100)
pattru_patti varchar2(100)
Thogai number(10,2)

where in the data's are as follows
chitta_enn varavu_patti pattru_patti Thogai
101 panam null 101.00
101 null sambalam 51.00
101 null kamishan 50.00

I need to create the view as follows

View Name : Pattiyal

vivaram varchar2(2000)
varavu number(10,2)
pattru number(10,2)

The view data should get display as follows
vivaram varavu pattru
sambalam kamishan null 101.00
panam kamishan 51.00 null
panam sambalam 50.00 null

Logic:
Each table row will have only one value either in varavu_patti or in pattru_patti. On selecting the row, thogai must be posted in varavu when varavu_patti is not null or should be posted in pattru when pattru_patti is not posted.on selecting the table row, vivaram should contain all other rows varavu_patti and pattru_patti on equating chitta_enn

Is it possible to create a view as above

View 1 Replies View Related

SQL & PL/SQL :: How To Create A View Using Five Tables

Oct 30, 2012

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...

View 5 Replies View Related

PL/SQL :: Inserting In View Which Contains From Two Tables

Sep 25, 2012

There 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.

View 1 Replies View Related

SQL & PL/SQL :: How To Sum Over Multiple Tables

Feb 21, 2010

I'm trying to do a sum over 2 different tables but can't get it to work...This is the idea:I have a table A with client ID, time-id (per day), purchase amount and segment code.

In another table (let call it B) I have a lot of client ID's and also their purchase amount, time-id and segment code. I want to sum the purchase amount for every client from table A and B for clients with certain segment code from table B.

This is what I have now:

select client_id, purchase_amountA+ purchase_amountB from tableA, tableB where
A.client_id = B.client_id
and time_id between 20090101 and 20091001
and B.segment_code = 'A'

This does the job, but it selects only client_id's which are in both tables. I want to select all client_id from table B with segment_code 'A' and add the purchase_amount from table A to their purchase amount from table B, at least, if they have any purchase amount in table A.

View 4 Replies View Related

SQL & PL/SQL :: Multiple Tables

Sep 30, 2010

Video Rental Shop

Each customer has a video card , When Customer rent a CD , Shopkeeper register an issue date and a Return Date . If customer return CD after Return Date Then There will be a fine of 2 Dollor .

After every 6 Months The shop Keeper review each customer Account , and Send Gifts to those customer whose Total Amount is More than 50 Dollar .and also send letters to those whose Fines Are More than 20 Dollor .

Now I am unable to understand that how many table i need to create for this .

What i have created so far is given below ,

When Customer Rent a CD then Shopkeeper will submit Following Information .

Customer_id 101
Issue DateDATE
Expected_return_dateDATE
Original_return_date-
Fine -
Total_Amount -

And at the time of return , he will Put these information .

Customer_id 101
Issue DateDATE
Expected_return_dateDATE
Original_return_date DATE
Fine 2
Total_Amount5

But Do i need to create another table for each customer also ? That will store customer total amount , total Fines ,and shopkeeper will view it after every six months. Which type oo table i need to create ?

View 18 Replies View Related

SQL & PL/SQL :: How To Create A View Having All Rows From Tables

Apr 21, 2010

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 ?..

View 5 Replies View Related

SQL & PL/SQL :: Store Data From Multiple Places In Materialized View?

Apr 14, 2011

I have a PL/SQL procedure which gathers data from multiple places as well as calculates some data. I want to store all this in a materialized view.So, I created an object type (I've shortened the definitions):

CREATE OR REPLACE TYPE mf_record_type AS OBJECT
(identifier VARCHAR2(6),
name VARCHAR2(100));

Then created the table type of the object:

CREATE OR REPLACE TYPE mf_table_type IS TABLE OF mf_record_type;

Then in the stored procedure defined a variable of the table type:

v_mf_record mf_table_type := mf_table_type();

Then I loop and populate the record type:

v_mf_record.EXTEND(1);
v_mf_record(x) := mf_record_type(v_rec.identifier, v_mf_detail.name);

When all that is done I try and create the materialized view:

EXECUTE IMMEDIATE ('CREATE MATERIALIZED VIEW mf_snapshot_mv AS
SELECT * FROM TABLE (CAST (v_mf_record AS mf_table_type))');

ORA-00904: "V_MF_RECORD": invalid identifier

Am I doing something wrong here? Can't I create the materialized view based on something other than a physical table?

View 1 Replies View Related

Selecting Via Multiple Tables

Jan 19, 2011

I need to calculate a list of people, who got some services more that 2 times with the same service koda (pas_kodas) to the same person (zmo_kodas). It should not depend on report number.

[URL]...

What I get is in green (services are calculated more than 2 times BUT in the same report).

What I need is in red: calculate servises more that 2 times ACCROSS all reports to the same person (zmo_kodas).

[URL]...

One person (zmo_kodas) can have a lot of reports (ats_nr).

Every report can have one or more services (pas_kodas).

View 1 Replies View Related

SQL & PL/SQL :: Get Sum Amounts From Multiple Tables?

Mar 2, 2011

I have a primary table and some secondary tables. I need to use the key from Primary table and get the sum/totals from the other tables with a single query.

For example, I have 3 tables employee, employeesal and employeerewards in which employee is the primary table. I need to get the total salary for an employee from employeesal table and total rewards from the employeerewards table using one query.

I am pasting the test data below:

create table employee (eno number(9), ename varchar2(40));
insert into employee values(1,'Jack');
insert into employee values(2,'Jim');

[Code]....

I am looking for a better way of doing this in terms of performance. In my projecct, those 3 tables are very big tables and I doubt if I do two inner queries and then use a outer query may not be the best way to do it.

Is there any better way to write this query in terms of performance?

View 8 Replies View Related

SQL & PL/SQL :: JOIN For Multiple Tables

Mar 29, 2011

Here is what I'm trying to do:

I have three tables:

TABLE1 TABLE2 TABLE3
NCI 1AB OPEN
NCI (NULL) (NULL)
INV (NULL) (NULL)
NCI 2AB REOPEN

What I want to do is to return all of the rows from TABLE1 that are NCI regardless, and if they are NCI, I want to return the corresponding records from TABLE2 and TABLE3.

If TABLE1 has a record of NCI but there are no corresponding records in TABLE2 or TABLE3, then of course the columns for TABLE2 and 3 would be blank.

I can get all of the NCI records from TABLE1 when I LEFT JOIN with TABLE2, but when I try to specify TABLE3 in the FROM statement, only the records that are NCI in TABLE1 AND have data in TABLE2 are returned, not just all records with NCI in TABLE1.

Let me know if I can further clarify.

I know that you do not have access to my tables, but here is an example of my code so that you may understand my quandary further:

SELECT l.sku AS "SKU",
l.loc AS "LOC",
l.qty AS "QTY",
o.ncikey AS "NCI",
r.description AS "NCI DESC",
o.qtyexpected AS "NCI QTY EXP",
o.qtyreceived AS "NCI QTY REC",
o.loc AS "NCI LOC",
o.status

[Code]...

View 7 Replies View Related

SQL & PL/SQL :: Getting Data From Multiple Tables

Feb 11, 2011

What are some performance issues you might run into when having to query 3 or 4 tables at a time.

View 1 Replies View Related

SQL & PL/SQL :: Merging Into Multiple Tables

Feb 12, 2012

Can I use MERGE statement to insert the same thing into multiple tables?

Like below--

Merge into tbl1
using tbl2
when matched then update...
when not matched then insert all.

View 6 Replies View Related

SQL & PL/SQL :: Selecting Via Multiple Tables?

Jan 19, 2011

I need to calculate a list of people, who got some services more that 2 times with the same service koda (pas_kodas) to the same person (zmo_kodas). It should not depend on report number.

[URL]

What I get is in green (services are calculated more than 2 times BUT in the same report).
What I need is in red: calculate servises more that 2 times ACCROSS all reports to the same person (zmo_kodas).

[URL]

One person (zmo_kodas) can have a lot of reports (ats_nr). Every report can have one or more services (pas_kodas).

View 2 Replies View Related

SQL & PL/SQL :: Trigger From Multiple Tables

May 1, 2013

I need to set up a trigger to check and enforce that the age of an employee at hire date is older than 18 when a new record is inserted. The age is on the PERSON table, and the hire date is on the EMPLOYEE table.

My attempt:

CREATE TRIGGER AGE_HIRE
BEFORE INSERT OR UPDATE ON EMPLOYEE
FOR EACH ROW
BEGIN
IF FLOOR EXTRACT (YEAR FROM (PERSON.BIRTHDATE - EMPLOYEE.HIREDATE)) > 18
THEN DROP NEW
ELSE INSERT NEW
FROM PERSON, EMPLOYEE
END;

View 8 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved