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
ADVERTISEMENT
Replication :: Create Materialized View - Insufficient Privileges
Feb 14, 2008CREATE MATERIALIZED VIEW Matview1
NOLOGGING
NOCACHE
NOPARALLEL
REFRESH COMPLETE ON DEMAND
START WITH sysdate
NEXT sysdate + 1
WITH ROWID
ENABLE QUERY REWRITE AS
select Query;
if i run select query it works fine .. also the user has create materialized view and query rewrite privs .. not sure why i am getting insufficient privileges error still ..
SQL & PL/SQL :: Getting ORA-01031 / Insufficient Privileges Error When Create A View?
Mar 12, 2013create view TodaySurgeries
as select s.surgery "Surgery", p.full_name"Patient Name",e.full_name"Doctor Name",
f.floor_id"FloorID",r.room_id"RoomID",
to_char(s.surgery_date,'dd-mm-yyyy hh24:mi:ss')"Surgery Date"
from floors f,rooms r,employees e ,patients p, surgeries s
where f.floor_id=r.floor_id
and p.patient_id= s.patient_id
and r.room_id= s.room_id
and s.doctor_id= e.employee_id
I had been granted to the user grant session and resource
but the error is
create view TodaySurgeries
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL & PL/SQL :: Error When Create Synonym / ORA-01031 / Insufficient Privileges
Mar 13, 2013from sysdba i grant to my user hospital creat any synonym but give me this error why ?
SQL> conn sys as sysdba
Enter password:
Connected.
SQL> grant create any synonym to hospital
2 ;
Grant succeeded.
SQL> conn hospital
Enter password:
Connected.
SQL> create public synonym Sur
2 for surgeries;
create public synonym Sur
*
ERROR at line 1:
ORA-01031: insufficient privileges
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 :: How To Create Materialized View On Partition Table
May 16, 2012how to create materialized view on partition table?
View 1 Replies View RelatedReplication :: 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?
Replication :: 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.
Replication :: Create Materialized View Without Having Any Data In It
Nov 5, 2008How 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.
SQL & PL/SQL :: Create Materialized View For Accessing Data
Feb 29, 20121.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?
Insufficient Privileges To Start DB?
May 25, 2011I have installed oracle on my linux machne recently as an oracle2 user. To day i found my listener up when start my as database ( by logging in to the sqlplus ) sqlplus /nolog - i get into sqlplus
When i startup the database
SQL>startup - I get this error ORA-01031: insufficient privileges
I tried to check if my oracle user is in the dba and oinstall group. it wasn't
I added the user to the group by doing usermod -G dba,oinstall oracle2
i chek in the group to see if the user has been added to the group i see the user added but i still get the same error ORA-01031: insufficient privileges when i try to start the database.
I read some where that you can try to uncomment the SQLNET.AUTHENTICATION_SERVICES line on sqlnet.ora file but on by my file (SQLNET.ORA) there is only one line written (NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT))
ORA-01031 - Insufficient Privileges
Jul 14, 2010i have three scheme in one instance for example
x,y and z
i create view in x as x_view
next step create view in y as y_view select from x_view ;
and finished, create another view in z as z_view select from y_view;
i gave grants to all user , but when i try this query with x user as select * from z.z_view get this message ORA-01031: insufficient privileges;
attention that connect as x and try select * from z.z_view
SQL & PL/SQL :: ORA-01031 / Insufficient Privileges
Dec 28, 2007i have created a context from schema 'Schema1' using "accessed globally" option and created on package to set the values for conext. It is working fine.
When i deploy the same package on schema2 (remember the context is on schema1) and try to execute it on schema2 then it is giving me the following error.
Connected to:
Oracle Database 10g Release 10.2.0.1.0 - Production
SQL> exec krishna_pkg.MyProcedure
BEGIN krishna_pkg.MyProcedure; END;
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SESSION", line 90
ORA-06512: at "LGE2008DEV.KRISHNA_PKG", line 6
ORA-06512: at line 1
PL/SQL :: Insufficient Privileges / Set ORACLE_SID
Aug 28, 2013I installed 11g in my system.
Then I used to login as username@admin/password.
It returns error as ORA-12154: TNS:could not resolve the connect identifier specified.
After that I tried sqlplus "/ as sysdba"
it returns as ORA-01031: insufficient privileges
and then i set ORACLE_SID and then try sqlplus "/ as sysdba"
it returns ORA-12560: TNS:protocol adapter error.
Replication :: Create Materialized View With Refresh On Commit?
Jul 7, 2010Create Materialized view with Refresh on commit example. with create Log file example.
View 6 Replies View RelatedError / ORA-01031 / Insufficient Privileges
Sep 27, 2013I am facing problems during login through sqlplus prompt.
It occurs in all the Linux boxes, with 10g and 11g databases, whereas in Windows it works good.
Sqlplus / as sysdba works good ( So there is no problem in OS Authentication)
Sqlplus sys/xxxx@SID as sysdba works good( Ther is no problem with password file)
But Sqlplus /@SID as sysdba is not working, It gives me an error message whereas in my windows box it works without any problems.
Error: ORA-01031 : Insufficient priveleges
I verified the service id entry in tnsnames.ora, i did not find any problem.
SQL & PL/SQL :: Getting ORA-01031 / Insufficient Privileges Error
Aug 2, 2012I've some metadata tables on one schema (abcref@db1) and users updates this table everyday. I need to replicate these tables to other schemas on same database (defdev@db1) and different database (deftest@db2) everyday. So i created materialized views on target schemas and refresh these through a pl/sql procedure. Now i'm getting error while refreshing the materialized view from defdev@db1 and the materialized view refreshes successfully from deftest@db2.
Granted select access on abcref@db1 tables to defdev@db1 and deftest@db2 tables via role.
Getting below error from defdev@db1 schema.
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SNAPSHOT_UTL", line 361
ORA-06512: at "SYS.DBMS_SNAPSHOT_UTL", line 443
ORA-06512: at line 1
Table on abcref@db1 schema
===========================
SQL> create table REF_PROD_CD
2 (
3 prod_cd VARCHAR2(5),
4 division_cd VARCHAR2(1),
5 brand_cd VARCHAR2(2)
6 );
[code]....
Now Using below procedure to refresh materialized views from Target schemas.
SQL> CREATE OR REPLACE PROCEDURE PR_REFRESH_MV IS
2 BEGIN
3 DBMS_MVIEW.REFRESH('REF_PROD_CD','C');
4 END;
5 /
Procedure created
Login To Oracle 10g - Insufficient Privileges For Sys?
Jun 29, 2012i am unable to login to oracle 10g as 'sys' as sysdba it is throwing an error like ORA-01031 insufficient privilidges for sys.but when i connect to database with username=system and password = oracle i am able to connect to database.
i have installed oracle 10g database on windows.so how should i resolve this error so that i can connect to the database as 'sys as sysdba'.
SQL & PL/SQL :: Create Materialized View With Clob Column Based On Varchar2?
Feb 9, 2011I need to create a materialized view with a clob column based on a varchar2 column of a table.This is because in the mv the clob column data gets appended one after another.
View 2 Replies View RelatedSQL & PL/SQL :: Unable To Query Dba_tab_cols / Insufficient Privileges?
Oct 29, 2010I have installed oracle 10g express edition, and created 2 users
1. DBA
2. HR -- is the normal application developement user.
When i issue the following query from DBA user, it works.
select * from dba_tab_cols; -- works fine for DBA user
select * from dba_tab_cols; -- doesnot work for HR user - invalid table name
When i try to issue the previage from DBA user like,
grant select on DBA_TAB_COLS to hr; -- DBA user says like, insufficient privileges.
Server Administration :: ORA-01031 - Insufficient Privileges?
Feb 7, 2012I can not login my db using tnsname,how can i do?
SQL> connect / as sysdba
Connected.
SQL> connect sys/sys@dup_oracl as sysdba
ERROR:
ORA-01031: insufficient privileges
Server Administration :: Insufficient Privileges To Other User
May 21, 2011WHEN I CONNECT TO SCOTT OR ANY KIND OF USER (EXCLUDE SYS)I CONNECT EASILY BUT PROBLEM WITH SCOTT OR OTHER THEY CANNOT BE
SHUTDOWN THE DATABASE AT THAT TIME THE ERROR ARISES ARE
ORA-01031: insufficient privileges SO OUR DATABASE CANT BE SHUT IT DOWN.
SQL & PL/SQL :: Insufficient Privileges When Creating Tables Using Execute Immediate?
Apr 30, 2010SQL> connect sys as sysdba
Enter password:
Connected.
SQL> grant dba to temp;
Grant succeeded.
SQL> connect temp
Enter password:
Connected.
SQL>
SQL> create or replace procedure tempproc
2 is
3 begin
4 execute immediate 'create table temp_table(a varchar2(10))';
5 end;
6 /
Procedure created.
SQL> exec tempproc;
BEGIN tempproc; END;
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "TEMP.TEMPPROC", line 4
ORA-06512: at line 1
SQL>
SQL> declare
2 begin
3 execute immediate 'create table temp_table(a varchar2(10))';
4 end;
5 /
PL/SQL procedure successfully completed.
SQL> desc temp_table;
Name Null? Type
----------------------------------------- -------- ----------------------------
A VARCHAR2(10)
Why am I not able to create a table from procedure using execute immediate?
PL/SQL :: Getting ORA-01031 / Insufficient Privileges Error When Created MV?
Jun 21, 2012I am getting a strange error while trying to create an MV.
<b><p>
Error report:
SQL Error: ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SNAPSHOT_UTL", line 1613
ORA-06512: at line 1
01031. 00000 - "insufficient privileges"
*Cause: An attempt was made to change the current username or password without the appropriate privilege. This error also occurs if
[code]....
Execute GRANT SELECT ON Xyz - Insufficient Privileges?
Sep 30, 2011 have an automated process which runs on an Oracle 8i database server as user abc. This process creates views/tables in other schemas, on the same database server, which point to objects owned by the abc user.
The issue I'm getting is that when I try to execute GRANT SELECT ON xyz.view123 TO PUBLIC as the abc user, I get an insufficient privileges.I should add that the abc user created the xyz.view123 table/view.
What grants/priviliges or whatever do I have to do to the abc schema?
SQL & PL/SQL :: Procedure To Get Any DDL Done Against Sample HR Schema - Insufficient Privileges
Jul 8, 2012I am new to Dynamic SQL..I create a procedure to get any DDL done against sample HR schema as follows.it goes well! Now when i try to test my procedure with some DDL command passing to the procedure i've created..strange! oracle throws an error as in the /*ERROR!!!!*/ block..
I don't understand why i am facing such an error..
/* Product an Version on my machine */
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
[code]...
SQL & PL/SQL :: ORA-12015 / Cannot Create Fast Refresh Materialized View From Complex Query
Apr 27, 2011I'm trying to create materialized view, successfully create logs for all the tables involved.
CREATE MATERIALIZED VIEW LOG ON tbJournal
WITH ROWID, SEQUENCE (tabid, companyid, storelocid, tabstatus, linetype)
INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON TBTABS
WITH ROWID, SEQUENCE (TSTAMP, COMPANYID, NUMGUESTS, POSITIONID, STORELOCID, TABSSTATUS)
INCLUDING NEW VALUES;
[code]....
The result is ora-12015: cannot create a fast refresh materialized view from a complex query
It does work fine if I remove
AND EXISTS (SELECT 1 FROM tbJournal J
WHERE j.tabstatus NOT BETWEEN -6 AND -4
AND j.linetype = 1
AND T.tabid = J.tabid
AND T.companyid = J.companyid
AND T.storelocid = J.storelocid)
from the where clause
Insufficient Privileges Error While Updating Through Stored Procedure
Oct 2, 2008I have a update statement that works fine when its run in SQL window but when i try it as part of a Stored Procedure its giving me the Insufficient privileges error on that table.
If i dont have privilege to update that table wont it not let me update while doing it outside the stored procedure as well?
Server Administration :: Insufficient Privileges While Connecting (sys As Sysdba)
Apr 2, 2013SQL> conn sys as sysdba
Enter password:
ERROR:
ORA-01031: insufficient privileges
The oracle 11g installed in eucalyptus cloud..
In the same server I can connect as a different user 'd6' but not as sysdba.
PL/SQL :: Error While Compiling Procedure / ORA-01031 / Insufficient Privileges
Jan 22, 2013how to grant compile access for stored procedures..? I got execute access so that i can execute procedure from another user but not able to save/modify once i edited.
i am trying to compile using SQL developer. Error Message :Error: ORA-01031: insufficient privileges