SQL & PL/SQL :: How To Check System Views When Create Table Privilege

Jan 4, 2013

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?

View 2 Replies


ADVERTISEMENT

SQL & PL/SQL :: How To Check If User Has (create Table) Privilege

Jun 29, 2011

1.How to check a user has 'create table' privilege?

2.how to check a user has privilege to grant 'create table' privilege to other user ?

View 6 Replies View Related

SQL & PL/SQL :: Check If Table Exist In Scheme - If Not Create As Original DDL

Aug 13, 2013

I have 20 or so tables, partitioned by range, indexed etc...soon these tables will be gone and i have to recreate them with the same definitions. I have to write a procedure(or script, it's up to me) which must:

1- check if table A exist in the scheme
2- if not create the table as the original ddl.

for 1 i've used smth like
-------------------------
SELECT count(*)
INTO a
FROM user_objects
WHERE object_name='A';

[code]....

so my question is how and what is the cleanest and fastest way to do this.? it's a lot DDLs,

View 17 Replies View Related

SQL & PL/SQL :: ORA-30506 - System Triggers Cannot Be Based On Tables Or Views

Jan 24, 2012

i'm trying to audit ddl actions(a strange "disappearing of a table every couple of days) on a specific table.

i want to trigger those actions, but not on a database level, but a table's level only(or at least a user schema's).

when i execute

CREATE OR REPLACE TRIGGER system.audit_trg BEFORE DROP ON tu2.d
BEGIN
INSERT INTO

[Code].....

i get an error
ORA-30506: system triggers cannot be based on tables or views

and if it is - how do i adjust my plsql?

View 29 Replies View Related

PL/SQL :: Get List Of Materialized Views / Views Using Column Of Table

Oct 17, 2013

I am removing sal column from table tab_emp; i want to check whether any materialized view or view using this column by  querying using data dictionary :- if i use like condition against query column of all_mviews it is throwing error sicne it is long data type. is there a way to search it without creating any function and use it in a query.

View 3 Replies View Related

PL/SQL :: How To Grant Privilege On User1 Based On Privilege Of User2

Jun 20, 2013

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?

View 2 Replies View Related

SQL & PL/SQL :: Direct Privilege Vs Privilege Through Roles

May 30, 2010

As we all know, privileges granted to a user through role are not visible from within a stored procedure. What is the reasoning behind this design?

Moreover, privileges granted through role are visible from anonymous PL/SQL block. Why such discrimination between anonymous PL/SQL and stored PL/SQL?

View 3 Replies View Related

SQL & PL/SQL :: How To Create Dynamic Views

Jun 26, 2010

How to create dynamic views? I need literature on dynamic views.

View 5 Replies View Related

SQL & PL/SQL :: Create Materialized Views On A Database

Jan 5, 2011

I'm trying to create a materialized view on a database on my local laptop, and when I execute the create it fails with the following error: ORA-06550: line 1, column 60:

PLS-00103: Encountered the symbol ".16" when expecting one of the following: . ( @ ; with the table name highlighted.

CREATE MATERIALIZED VIEW Schema.MV (col1,
col2,)
BUILD IMMEDIATE
REFRESH Complete ON DEMAND
WITH PRIMARY KEY
AS
select EXTRACTVALUE (rawxml, '/Test.class/@ID') AS col1,
EXTRACTVALUE (rawxml, '/test.class/test.attribute.name') AS col2,
from RAWXML
where Type = 'test.classvalue';

The query on its own though executes fine.

select EXTRACTVALUE (rawxml, '/Test.class/@ID') AS col1,
EXTRACTVALUE (rawxml, '/test.class/test.attribute.name') AS col2,
from RAWXML
where Type = 'test.classvalue';

Is there something wrong with the table setup? I have tried creating a public synonym and still the error comes up.

View 11 Replies View Related

SQL & PL/SQL :: Privilege - Just Drop Special One Table?

Jul 3, 2013

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!

View 5 Replies View Related

SQL & PL/SQL :: Privilege Required To Gather Table Statistics?

May 14, 2011

What privilege is required to gather table statistics using dbms_stats ?

View 2 Replies View Related

SQL & PL/SQL :: Create Oracle Objects (Views) Using Conditional Compilation?

Jun 6, 2011

it is possible to create Oracle objects(Views) using Conditional compilation?

View 5 Replies View Related

SQL & PL/SQL :: Create Materialized Views Depending On Sysdate With Enable Query Rewrite Option

Mar 28, 2011

I want to create a materialized view for the last 10 days with the enable query rewrite option.

e.g. i want to create a view with the list of employees who joined the company in the last 10 days.

create materialized view M_Employee
refresh fast on commit
enable query rewrite as
select joining_date , name
from
employee
where joining_datde < TRUNC(sysdate) - 10

I seem to get the error SQL Error: ORA-30353: expression not supported for query rewrite

30353. 00000 - "expression not supported for query rewrite"
*Cause: The select clause referenced UID, USER, ROWNUM, SYSDATE.

This error is self explanatory , but is there any work around to have a query liek this to list the employees based on sysdate.

View 23 Replies View Related

Create System Generated Values?

May 14, 2007

Lets say you're making upa school database and you awnted to give the students each a unique student number generated by thesystem.

View 2 Replies View Related

How To Create Check Constraint That Act As Foreign Key

Jul 10, 2012

I have table EMP & DEPT.

Can you give me sample "check" constraint so that when I enter an EMP (with status='active') without matching DEPT entry, the process will fail?

It will allow to enter EMP with status='inactive' only even if no matching DEPT entry. So foreign_key will not apply.

View 21 Replies View Related

Mandatory To Create A Local System Administrator Account?

May 25, 2012

Is it possible to install Oracle SOA 11 in a Windows2008R2 as a power user or it is mandatory to create a Local system administrator account?

View 2 Replies View Related

Windows :: Installation Process Trying To Create Database On System

Dec 7, 2010

I'm trying to install Oracle10g in win7 Home premium. It is installing but problem is when installation process trying to create database on the system, a message is appearing which telling that ORA-12546:TNS:permission denied.

View 1 Replies View Related

RAC & Failsafe :: Create Shared File System Between Two Desktop PC?

May 14, 2013

We are setting RAC between two machines(Desktop PC connected wirth LAN ....have storage Local to them and no Shared storage..Only NFS).We have shared the mount point by NFS.We do not have any shared storage but we need to share the RAW files to setup the OCR and VOTING DISKS.

The problem is:

1. We need to keep the RAW devices for OCR and voting disk common to the machines (Desktop PC..both Linux OELU5). I am able to create the raw file systems but that is local to one machine. I am not able to understand how to share the raw devices between two machines.

2. Tried to use ISCSI utility but that did not work. (How_to_use_iSCSI_Targets_on_Linux)

Scenario : One Machine has 500 GB Storage. Second machine has 80 GB Storage. Made private and public networks(Used two Network cards in LAN). All are communicating.

Problem : Need to make the storage 300GB (Out of 500 GB) of Machine 1 as shared storage. How can we do that? (Do we need any virtual software...ex .. vmware for that?)

View 3 Replies View Related

Server Administration :: Graphical Analysis Of Dynamic Performance Views (V$ Views)?

Nov 5, 2012

is there some open source or free tool which can graphical display V$ Views. Can TOAD do that in a good maner?

in UNIX there is the "sar" command, but a Java tool "ksar" for displaying the statistics in user friendly fashion.

View 2 Replies View Related

PL/SQL :: Create A Trigger To Check If Synonym Already Exists In DB

Mar 29, 2013

How can I create a trigger to check if synonym already exists in db and if exists then don't create synonym.

my work: ( this is just like an outline i prepared)

select * from all_synonyms;
declare
s_exists number;
begin
-- check whether the synonym exists
select 1 into s_exists from all_synonyms;
-- an error gets raise if it doesn't
exception when no_data_found then
-- DDL has to be done inside
execute immediate ' create or replace synonym';
end;
/

View 16 Replies View Related

SQL & PL/SQL :: Oracle - Create Procedure To Check Username And Password?

Aug 23, 2011

how can i create procedure to check username and password.

View 10 Replies View Related

Refresh Materialized Views Based On Remote Views?

Aug 4, 2011

I have a created a materialized view which is based on a view on remote database. Now how do I refresh the view.

Materialized view is created by

CREATE MATERIALIZED VIEW mv_employee_name
AS SELECT EMPLID, EMPL_NAME
FROM VEMPDATA@REMOTEDB
WHERE REGION = 'US';

I am wondering how the refersh happens or how do I specify the refresh clause.REFRESH FAST option is looking for VIEW LOG on the master table but in this case its a remote view, so I cannot create any object on remote db.

View 1 Replies View Related

Server Administration :: Create SPFILE - Failure In Processing System Parameters?

Oct 11, 2004

I have a small problem with creating the SPFILE,

These are the commands i had issued :-

1. startup nomount
2. create SPFILE from PFILE ;

Then i got an error :-

ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file 'C:ORACLEORA92DATABASEINITORCL.ORA'

Ever since this happened i am unable to connect to ORACLE under any schema !

I get the following error : -

SQL> conn scott/tiger
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist

Warning: You are no longer connected to ORACLE.

WHY DOES THIS HAPPEN AND HOW CAN I OVERCOME IT ?

View 8 Replies View Related

SQL & PL/SQL :: Materialized Views - Take Statistics For Table Again?

Oct 26, 2012

I have created materialized view which hold few million records.Should i have to analyse the view and compute the statistics after i create the materialized view?

Also,just in case i need further indexing,should i have to take the statistics for the table again??

View 5 Replies View Related

Enterprise Manager :: What Is The Difference Between Create External Table Vs Create Table

Apr 29, 2011

What is the difference between CREATE EXTERNAL TABLE Vs CREATE TABLE .?

Is CREATE EXTERNAL TABLE included in CREATE TABLE?

View 3 Replies View Related

Replication :: Replicate One Table In Two Materialized Views At Same Time

Nov 11, 2008

I have a table in one database and I want to replicate it in another two databases with materialized views. The refresh may be fast. Is this possible?

View 1 Replies View Related

Convert Some Existing Materialized Views (fast Refresh) To Partition Materialized Views

Jul 7, 2010

I have to convert some existing materialized views (fast refresh) to partition materialized views.

Database version is oracle 10.1.0.4. I have decided to use on prebuilt table option to do the partitioning as it minimizes the time to transfer from the master site.

1) stop replication
1) create interim tables with similar structure as the materialized views
2) transfer all data from the materialized views to the interim tables
4) script out the materialized views structure and add in on prebuilt table option in the scripts
5) drop the materialized views
6) rename the interim tables with the same name as the materialized views
7) run the scripts to create the materialized views with on prebuilt table option
8) refresh the newly created materialized views -> it should take a short time since I am using on prebuilt table option

But I am facing one major issue. That is if I drop the materialized views, the materialized view logs of the master tables are purged. When the materialized views are refreshed fast, there are some data missing. the data that are purged out when the materialized view are dropped.

Do you happen to know other ways that existing materialized views can be converted to partitioned materialized views? Do you have any workaround to prevent the materialized view logs from being purged?

View 3 Replies View Related

Forms :: Put One Check Box To Check All The Check Boxes?

Jul 30, 2011

I want to put one check box to check all the check boxes.how can i do this?

View 5 Replies View Related

PL/SQL :: Use Of Views And Synonyms If Changes Made To Them Affects Original Table Contents

Mar 28, 2013

what is the the use of views and synonyms if changes made to them affects the original table contents?It is data insecurity know?

View 2 Replies View Related

Forms :: Difference Between System.current_item And System.cursor_item?

Apr 1, 2012

We have system.current_item and system.cursor_item.....

both represent the same....

like this we have few other system variables also .....

what is the main difference between current_item and cursor_item?

View 2 Replies View Related







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