i have a union all query (two quiries i suppose) that runs fine and returns the results i am after.... when i try to use this query to create a view I get
SQL Command: CREATE OR REPLACE FORCE Failed: Warning: execution completed with warning
CREATE OR REPLACE FORCE VIEW "schema1"."name_of_view" ("column1", "column2", "column3") AS
SELECT b.column1, a.column2, c.column3
[Code].....
Am trying to create a materialized view as below
SQL> CREATE MATERIALIZED VIEW emp_view_73 BUILD IMMEDIATE REFRESH COMPLETE ON COMMIT
2 AS
3 SELECT em.employee_id,
4 em.employee_fname
5 ||' '
6 ||em.employee_lname employee_name,
[code]....
ERROR at line 12: ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
H want to Create a view which reside in one instance for example xxx Instance . I want to create the same view which reside in xxx instance to another instance for example YYY Instance How to acheive this ?I tried desc the View take the view script from XXX instance and copied the view script to YYY instance when I compiled Iam getting an Error
For Example My view in XXX Instance
CREATE OR REPLACE FORCE VIEW xxx.sample
(
First_name,
last_name
)
as
select "F_NAME",
"L_NAME"
FROM EMP;
When try to copy and compile the same view in my YYY Instance i am getting error
we have the following requirement for creating materialized view.
In database "DB1", a table "abc" is there in "user1" schema.
In database "DB2", in "user2", we are accessing the "abc" through DB link "DBL1" to DB1:
select * from user1.abc@DBL1;
we have created a public synonym for "user1.abc@DBL1" as "sabc".
In database "DB3", we need to create the materialized view to access "abc" in DB1 through DB2. we are not allowed to access "DB1" directly from "DB3". so we create a db link as "DBL2" in "DB3" to "DB2".
when i try to create a materialized view as below:
create materialized view abc_mv
as
select * from sabc@DBL2;
But got the error as "synonym translation invalid". When i access the SQL statement separately as below
select * from sabc@DBL2, its working but in Materialized view, it shown that error. Even i tried with schema name as well like "user2.sabc@DBL2" , but that also thrown same error.
Is there any option available for this type of creating materialized view?
i am getting ORA-04063 error while creating view in oracle.
View 2 Replies View Relatedwe are trying to create a materialized view (MV) which would access the remote database through db link. Now we need to do update on the local MV so that it should be reflected on the master table.
There is no primary key on this table and we are using "complete refresh" option. since we dont have control over remote database, we are not allowed to create MV log over there.
in this scenario, if i try to create updatetable MV with complete refresh, we are getting below error:
SQL Error: ORA-12013: updatable materialized views must be simple enough to do fast refresh
how to create such MV on this scenario?
our environment is:
Oracle 11.2.0.3
I have this sql (generated by discoverer plus), and work fine:
SELECT COUNT(o10475761.SUC_ID)
FROM GAR_DW.ARTICULOS o10475528,
GAR_DW.EMPRESAS o10475602,
GAR_DW.L_DIA o10475639,
[code]...
We need change the table stock_por_sucursal for a view like this, with similar indexes each table
select * from stock_por_sucursal_old
union all
select * from stock_por_sucursal_new
and the result is not good:
SQL Statement from editor:
SELECT COUNT(o10475761.SUC_ID)
FROM GAR_DW.ARTICULOS o10475528,
GAR_DW.EMPRESAS o10475602,
GAR_DW.L_DIA o10475639,
GAR_DW.V_STOCK_POR_SUCURSAL o10475761,
[code]...
finally add a HINT /*+ gather_plan_statistics push_pred(TABLE) */ and the result was not very good in this case, but improved the resolution of the view, the rest got worse
SQL Statement from editor:
SELECT /*+ gather_plan_statistics push_pred(o10475761) */ COUNT(o10475761.SUC_ID)
FROM GAR_DW.ARTICULOS o10475528,
GAR_DW.EMPRESAS o10475602,
GAR_DW.L_DIA o10475639,
[code]...
I'm trying to create a Materialized View on a remote database from a simple view. The reason is, the data owners don't want to grant explicit tables privileges to external subscribers.
A new schema is created to publish data in the form of a view. I've created mlogs on the master tables, and granted them to the subscriber, but it's still complaining about a missing primary key on the view. A primary key does exist in the master table.
Is there another work around for this situation without having to work inside the data sources' environment?
is it possible to create primary key on view and use this view for creating foreign key .
View 3 Replies View RelatedI am using view AB_EV_VIEW created with UNION ALL of 10 tables (ex. AB_EV_1, AB_EV_2.... )
I want to know the data comming in select * from AB_EV_VIEW is from which table ?
Suppose AB_EV_1 is having column ID, NAME
sql> select * from AB_EV_VIEW
100, A1
200, A2
300, A3
400, A4
500, A5
There are 2 databases, database A and database B. Database A is Oracle 11.2.0.2 which runs on linux and Database B is Oracle 11.2.0.2 which runs on windows xp machine. In database A, there are 100's of tables which are being updated every 10 minutes or 15 minutes. For reporting purpose, the developer wants to run report for the tables. But since database A is being updated every now and then, generating reports takes almost 15 to 20 minutes. So the reports can be generated in Database B. Once in a day the database B should have the updated data from database A so that the reports can be generated in database B with less time. What could be the best solution for the database B to have the updated data on daily basis from database A in oracle?
View 3 Replies View RelatedI am having trouble with creating a view based on the following: -
Total hours flown EVER, for each crew of the FBN001 on 20th October 2011.
Here is what i got so far (only works for each members flown on a particular flight, NOT on their whole hours flown in a period of a lifetime) : -
SELECT e.Employee_Number, e.FirstName, e.LastName,
SUM( ( (s.ESTIMATED_ARRIVAL) - (s.ESTIMATED_DEPARTURE) )*24)AS
TOTAL_HOURS_FLOWN
FROM SCHEDULE s, CREW_ASSIGNMENT ca, EMPLOYEE e
WHERE
s.Flight_Number = ca.Flight_Number
AND
s.Serial_Number = ca.Serial_Number
[code]...
I have create a procedure as
create or replace procedure aa is
abc varchar2(1000);
begin
abc:='create or replace view abc_view as select * from abc';
execute immediate abc;
end;
When i try to execute the same as
BEGIN
aa;
END;
i got error as
ORA-01031: insufficient privileges
ORA-06512: at "EARS.AA", line 5
ORA-06512: at line 3
why i get this error. there is no problem in grants. the same create or replace view query works if i directly executes in sql.
I want to create a materialized view for the with same logic as below view
CREATE OR REPLACE FORCE VIEW GFL_MCP_CORE.VW_CLIENT_HIERARCHY
(KEY_INDENTED, CLIENT_LEVEL, ID, NAME, CLIENT_TYPE_CODE,
L2_CLIENT_ID, L2_CLIENT_NAME, TOP_CLIENT_ID, TOP_CLIENT_NAME, CLIENT_MCP_FLAG,
CLIENT_GROUP_NAME, ORG_REGION_NAME, COUNTRY_NAME, PROCESSOR_CLIENT_CODE)
[code]....
Here is the sample data for the client table and other tables are generic.
Row#IDNAMECOUNTRY_CODECLIENT_TYPE_CODECLIENT_GROUP_IDPARENT_CLIENT_IDSTATUS_FLAG
134801ZhuanshenguangchangCNBR10048232909A
212515229ConsolidatedFCUUSCL54812515A
314821286 Golden Mile BranchCABR53112712A
42050014513Bay CityUSBR56612009A
53864712-44-125 LeicesterGBBR56038630A
63869114736 Freestar BrickyardUSBR10020238686A
731141Walsall StoreGBL254631103A
I am able to create view and the public synonym with the same name but when I am quering view result is showing .
Is there any logic behind this .
CREATE OR REPLACE VIEW emp_dtls as SELECT * FROM emp;
CREATE PUBLIC synonym emp_dtls for emp;
SELECT * FROM emp_dtls;
view result is showing
I executed the following statements
SQL> CREATE OR REPLACE Function test
2 ( name_in IN varchar2 )
3 RETURN number
4 IS
5 cnumber number;
6
7 cursor c1 is
[code]....
how I can view the full statement of creating the function.
Following
create or replace view dept_list as
select 1 state, 1 depth, 'DEPARTMENT' displ_value, null icn, null pk, 'A' sort_value
from dual
union
select -1 state, 2 depth, dname displ_value, null icn, null pk, 'A' sort_value
from dept
union
select -1 state, 3 depth, 'EMPLOYEES' displ_value, null icn, null pk, 'A' sort_value
from dept;
is placed in Pre_Form Trigger it generated error.
Error 103 at line 1, column 1
Encountered the symbol "create" when expected the following.
how to create a view from table which containes rows from every minutes. In view I want to select only rows at 15 or 30 minutes interval.
eg. my base table has 60 rows for 1 hour. The created view must contain 4 rows on 15 minutes interval basis at the boundary of that interval.
O/P of view should be like this.
Value Time
100 00:00:00
200 00:15:00
300 00:30:00
400 00:45:00
500 01:00:00
I am getting error (ORA - 00972 : identifier too long) while creating the view.
create or replace view ELVW_ATM_REC_HANDOFF_1(Rectype,Recseq,Record_Type,Record_Sequence,MESSAGE_TYPE,PAN,PROCESSING_CODE,TRANSACTION_AMOUNT,TRANSACTION_CUR RENCY_CODE,SETTLEMENT_AMOUNT,SETTLEMENT_CURRENCY_CODE,Billing_AMOUNT,Billing_CURRENCY_CODE,Transaction_DATE,Settlement_Date,TRACE,REF
[code]...
i have checked the length of the column name. Its less than 30 characters
When i try to create a trigger , i ended up with error.
SQL> create or replace
2 TRIGGER LOGON_TRG AFTER LOGON ON DATABASE
3 BEGIN
4 INSERT
5 INTO
6 user_audit_log
7 SELECT
8 user#,
[code]....
Warning: Trigger created with compilation errors.
SQL>
SQL> show error
Errors for TRIGGER LOGON_TRG:
LINE/COL ERROR
-------- -----------------------------------------------------------
2/5 PL/SQL: SQL Statement ignored
17/17 PL/SQL: ORA-00942: table or view does not exist
The command used to resolve the error is
GRANT SELECT ON v_$session TO jack;
Jack user has sysdba privilege. My question is 'sysdba' is a super and special user which has all the privileges in database. Then why does it need SELECT privilege on v$session to user to create the trigger?
This 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 am trying to create a commit on refresh materialized view so that whenever I perform DML followed by commit on Master table it should get quickly reflect on my Materialized view. I have given SELECT on table privilege to user where this view will be created On Master:
GRANT SELECT ON HR.EMPLOYEES TO SCOTT. From Scott:CREATE MATERIALIZED VIEW scott.hremp REFRESH FAST ON COMMITAS SELECT * FROM HR.EMPLOYEES; ERROR at line 4:ORA-01031: insufficient privileges Then I Grant all on EMPLOYEES table to SCOTT. GRANT ALL ON hr.EMPLOYEES TO SCOTT; Then got following error while creating materilized view:SQL> CREATE MATERIALIZED VIEW scott.hremp 2 REFRESH FAST ON COMMIT 3 AS 4 SELECT * FROM HR.EMPLOYEES;SELECT * FROM HR.EMPLOYEES *ERROR at line 4:ORA-23413: table "HR"."EMPLOYEES" does not have a materialized view log I create materialized view log on Master:SQL> CREATE MATERIALIZED VIEW LOG on HR.EMPLOYEES;Materialized view log created.
Even after this I am not able to create materialized view and now getting Table not Exist error:
SQL> CREATE MATERIALIZED VIEW scott.hremp 2 REFRESH FAST ON COMMIT 3 AS 4 SELECT * FROM HR.EMPLOYEES;SELECT * FROM HR.EMPLOYEES *ERROR at line 4:ORA-12018: following error encountered during code generation for "SCOTT"."HREMP"ORA-00942: table or view does not exist. Also I want to know why I needed to GRANT ALL to HR for creating this materialized View.
I have a list of materialized views in schema A. I want to create a refresh group and then refresh it from Schema B (Dynamically-run time based on some criteria). What Grants are necessary on schema B in order for it to be able to create and refresh the groups on Materialized views in Schema A.
I know that one of the Options is to, GRANT ALTER ANY MATERIALIZED VIEW as a SYS user. But I do not have any SYS privileges.
I 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.
I have following tables:
EMPLOYEE (E-Number, Name, Department, age)
ASSIGNMENT (E-Number, P-Number )
PROJECT( P-Number, Project, Manager)
Create a view to show employee names, age and total number of projects they are assigned to
I am writing the following PLSQL code-
BEGIN
DBMS_SCHEDULER.CREATE_JOB
(
job_name=>'TRIAL JOB',
job_type=>'PLSQL BLOCK',
job_action=>
[code]........
The new table used above has the following structure
Name Null? Type
STAMP TIMESTAMP(6) WITH TIME ZONE
However I get the following error on execution and am not able to make out why?
BEGIN
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at line 3
Is it better to use UNION (or) UNION ALL with DISTINCT,Which one will improve performance.
Query1:
SELECT deptno FROM emp
UNION
SELECT deptno FROM dept
Query2:
SELECT DISTINCT * FROM(SELECT deptno FROM emp
UNION ALL
SELECT deptno FROM dept)
I mean to say query1 is better (or) query2 is better,which query improves the performance.
error while creating a catalog db
error creating db
ORACLE error from recovery catalog database ORA-01552: cannot use system rollback segment for non-system tablespace 'TS'
ORACLE error from recovery catalog database:ORA-00942:table or view doesnt exist
RMAN-00571:--------------------------------------------------------------------------------------------------
RMAN-00569----------------ERROR MESSAGE STACK FOLLOWS-------------------------------------------
RMAN-00571:---------------------------------------------------------------------------------------------------
RMAN-06433:error installing recovery catalog
I have create the following procedure to create individual jobs dynamically on each schema.
CREATE OR REPLACE PROCEDURE APSP.JOB_TEST_RAM
AS
V_SQL VARCHAR2 (1000);
BEGIN
[Code]....
While executing this procedure I am getting the below error.
PLS-00103: Encountered the symbol "APPS_XX" when expecting one of the following:
If i create a table in Oracle 10g it givea me the follwoing Error.. what couls be the error...
ORA-00911: invalid character
create table shopper
(
cShopperId varchar2(30) primary key,
cPassword char(20) not null,
vFirstName varchar2(20) not null,
[code]....