create or replace procedure pt is
l_count integer:=0;
v_sid varchar2(1000);
begin
SELECT name
INTO v_sid
FROM v$database;
dbms_output.put_line('SID='||v_sid);
end pt;
Warning: Procedure created with compilation errors.
SQL> sho err
Errors for PROCEDURE PT:
LINE/COL ERROR
-------- -----------------------------------------------------------
5/1 PL/SQL: SQL Statement ignored
7/9 PL/SQL: ORA-00942: table or view does not exist
SQL>
SQL> select name from v$database;
NAME
---------
ORCL
I can't access v$parameter,or v$database from a procedure?
How to know what object/tab privileged a user have ? without connecting that user and using USER_TAB_PRIVS by connecting on that user I mean on DBA table .
I have 2 users in my oracle DB. They both have very different privileges and they both have too many different privileges. Now I want to grant user 1 the same privilege that user2 has while user1 keeps his existing privilege. How can this be done without manually comparing their difference and manually grant user 1 each privilege that he doesn't have? Or second option, can I override user 1's privilege with user 2's privileges?
After successfully installing the 12c, how can I connect to pdb to use the scott or hr schemas?By default when I connect as sys or system from sqlplus it is connecting to orcl not to pdborcl.also how can we make scott connection in sql developer.
i am trying to use loops to retrieve the ename of emp table of scott schema , then under they are retrieved , all "sal" values appear under them , i do not want employee's salary under each employee , i need retrieve the 14 record of enames , then under them the 14 record of sal column appears , so i used this code :
declare cursor emp_cur is select ename from emp; emp_rec emp_cur%rowtype ; [code]....
how can i corrupt a block to mock up BMR (block media recovery) workaround , I want to corrupt some block of Oracle seeded table "emp" within scott schema.
BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod PL/SQL Release 10.2.0.1.0 - Production CORE 10.2.0.1.0 Production TNS for 32-bit Windows: Version 10.2.0.1.0 - Production NLSRTL Version 10.2.0.1.0 - ProductionOS version:
Windows 7 64bit I have schema(scott) export with schema level option and imported with different name as (scott1).At regular period of time i need to import the scott to scott1 without affecting existing records.such as *1. Need to append new created records.* *2. Need to append updated records.*
for the above requirement I did in the following way expdp xxxx/******** schemas=SCOTT directory=dumpdir dumpfile=SCOTT_28-SEP-2012.dmp logfile=exp_SCOTT_28-SEP-2012.log imported in the following way impdp xxxx/******** AS SYSDBA REMAP_SCHEMA=SCOTT:SCOTT1 directory=DUMPDIR dumpfile=SCOTT_28-SEP-2012.dmp logfile=imp_SCOTT2_28-09-2012.log TRANSFORM=SEGMENT_ATTRIBUTES:n TABLE_EXISTS_ACTION=APPEND.
The problem is i couldn'table to append the records to existing tables the log error show such ways.
ORA-31684: Object type USER:"SCOTT1" already exists Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA [code].....
A function returns the metadata of named objects (Directories, Users, Tablespace....) in the form of DDL. When i execute the function in the schema having the privileges of CONNECT, RESOURCE, DBA, SELECT ANY TABLE, UNDER ANY VIEW AND EXECUTE ANY PROCEDURE, function returns the empty clob without any error. But he same function created and executed in the User having SYSDBA privilege, we get output.how to get output without SYSDBA privilege ?
CREATE OR REPLACE FUNCTION SCHEMA.DBLINK RETURN CLOB AS v_meta_handle NUMBER; v_meta_handle_trans NUMBER; V_DOC CLOB; V_LOB CLOB; [code]....
I have a stored procedure which when executed creates a user and grants some roles to the user and also makes certain roles DEFAULT using "alter user"
The issue comes when i execute the procedure.
I have a User who has a role and this role has the "alter user" privilege, for example lets say that the user1 has the role ABC, and role ABC has the "alter user" privilege.
SQL> grant alter user to ABC; Grant succeeded. SQL> grant ABC to user1; Grant succeeded.
Now, when i run the stored procedure as the user1, it gives me an error on a line saying "insufficient privileges", when i check the line, its this line: alter user user1 DEFAULT ROLE "ROLE1", "ROLE2"
But as far as i know the user1 has the "alter user" privilege
I want to make those two roles ROLE1, ROLE2 DEFAULT because i don't want the other roles ROLE3,ROLE4 to be default, as you know if there are many roles and if we alter user with default for certain roles other roles become DEFAULT=NO.
So i get the error at "alter user" statement though the user has the "alter user" privilege, what do you think might be wrong?
OR is there anyway to grant roles to the user with default=NO option?
I have created a role, when i try to grant privilege to that role, it give me insufficient privilege error. After granting privileges to this role, i have to grant this role to other. what type of privileges should i have.
Create Role cb_select; Grant select on atable to cb_select; (Got error)
I logged in as system and I just execute below script, however Im getting error "SQL Error: ORA-00942: table or view does not exist 00942. 00000 - "table or view does not exist""
There two users a and b,and the table b.test_part.And one procedure under a ,text like below:
create or replace procedure a.sp_test is vs_sqls varchar2(32767); begin vs_sqls:='alter table b.test_part truncate partition p_day'; execute immediate vs_sqls; end;
now,i have to grant drop any table to a.but in fact,i prefer to drop the special one table "b.test_partany" rather than any other table.how ? no by trigger!
I am not able to select from the VIEW while connecting with the Schema who is the owner itself:
FX@db > select * from B_UTIL select * from B_UTIL * ERROR at line 1: ORA-01031: insufficient privileges ORA-02063: preceding line from FXDB2TST Describe works but select does not. Even I connected with SYSDBA and performed SELECT * FROM FX.B_UTL but it again gives the above errors. 11.2.0.2 on RHL.
what privilege is require for a user to execute explain plan? I get below error while try to execute explain plan.
SQL> explain plan for SELECT /*+ FULL(t) */ COUNT(*) FROM "DREAM"."CONSUMER.TAB" t WHERE ROWNUM <= 1000000; explain plan for SELECT /*+ FULL(t) */ COUNT(*) FROM "DREAM"."CONSUMER.TAB" t WHERE ROWNUM <= 1000000 * ERROR at line 1: ORA-01031: insufficient privileges
I connect to an Oracle-10g database using OEM, and if I click to see the list of users, roles, storage options, ... everything is ok, but if I click to see the list of schemas, I get "1031: insufficient privileges" error.
The user account I use has the SELECT_ANY_DICTIONARY privilege and SELECT_CATALOG_ROLE role granted, and if I try to do "select * from dba_tables" in the SQL*Plus, I get a result.
The matter is even more strange because, if I use the SYSTEM account, I get the same error (!!!).
Do I need any other privilege/role to see the user's schemas with OEM?
I want to create new database and i follow the following steps.
C:SET ORACLE_SID=ASIM ORAPWD FILE=ORAPWDASIM.ORA PASSWORD=ASIM ENTRIES=6 ORADIM -NEW -SID ASIM -STARTMODE AUTO THE TRY TO CONNECT TO SQLPLUS SQLPLUS /NOLOG AFTER THAT I WRITE THE CODE TO CONNECT THE SYS Connect sys/ASIM as SYSDBA BUT IT DISPLAY ERROR ORA-01030, INSUFICENT PREVILAGES.
I'm new to oracle DB,i've been given access to Oracle as a user. when i try to create a table under my default schema i get the insufficient privileges error.
How do i check from the system views if i have create table privilege under my own schema?
