SQL & PL/SQL :: Using Dbms_lock In Schema?

Nov 24, 2010

When I log into 'SYSTEM' schema I am able to use the dbms_lock command as followed:

declare
begin
dbms_lock.sleep(3);
end;

But i need to call it from my own 'INT' schema

declare
begin
system.dbms_lock.sleep(5);
end;

ORA-06550: line 3, column 5:
PLS-00201: identifier 'SYSTEM.DBMS_LOCK' must be declared
ORA-06550: line 3, column 5:
PL/SQL: Statement ignored

And also from the 'SYSTEM' schema i tried to grant privledges to public

grant execute on dbms_lock to PUBLIC;

but that gives me ORA-01031: insufficient privileges

View 4 Replies


ADVERTISEMENT

SQL & PL/SQL :: PLS-00201 / Identifier DBMS_LOCK Must Be Declared

Dec 9, 2011

i m getting this DBMS_LOCK error, i have DBA privilege

Declare
l_count integer := 0;
l_count_t integer := 0;
error_msg varchar2(4000);
begin
select count(*) into l_count from user_objects where object_name='PAKAGE_NAME' and object_type='PACKAGE';
if l_count=0 then
DBMS_OUTPUT.PUT_LINE('ERROR: Package PAKAGE_NAME does not exist in the database');

[code].....

ERROR: Package PAKAGE_NAME or its body is in INVALID state
PLS-00201: identifier 'DBMS_LOCK' must be declared

PL/SQL procedure successfully completed.

View 3 Replies View Related

Server Utilities :: Import A Schema From One Database Schema To Another Schema B?

Aug 10, 2010

I want to import a schema from one database schema to another schema b from db STBTST to STATST and from schema CMSSTAGINGB to CMSSTAGINGA

I first want to test this to my own schema (mvanmannekes) CMSSTAGINGA is filled at the moment.

So i've created a dump from STBTST-CMSTAGINGB For importing im using this statement:

impdp mvanmannekes/password schemas=cmsstagingb remap_tablespace=cmsliveb_data:cmslivea_data
remap_tablespace=cmsliveb_index:cmslivea_index
remap_schema=cmsstagingb:mvanmannekes directory=expdp_dir dumpfile=cmstagingb.dmp

I'm getting this:

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "MVANMANNEKES"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "MVANMANNEKES"."SYS_IMPORT_SCHEMA_01": mvanmannekes/********
schemas=cmsstagingb remap_tablespace=cmsliveb_data:cmslivea_data

[code]....

View 4 Replies View Related

Server Administration :: Accessing Single Schema From Multiple Schema Logging?

May 16, 2011

A single master schema where many developers are accessing. all share same password.

now i would like to trace all the changes made by each users. so i create a individual users for all and grant permission to access that schema.do i have a possibility of auditing the changes did by each user for that particular schema

View 2 Replies View Related

Security :: Schema Consolidation And User Schema Mapping Based On Service

Jul 24, 2011

We have an application with many separate databases (one per customer). Given they share the same business requirements (service hours, change mgmt etc), we're interested in potentially consolidating the separate DBs (which are relatively small) into separate schemas within a fewer no of databases to reduce the overhead.

Our issue is that the application is hard-coded to use a specific administrator and application connection user name. Changing this is unfortunately not an option.

Given this limitation, is there any possibility to map a generic user into a customer-specific schema based on the database service that they connect to? Each customer connects to different database services but may use the same user name. We considered using private synonyms but this seems to acheive the opposite (i.e. many different users could connect and map to a single users schema). One thing to point out is that where there is a single user name, it is acceptable for a single password to be used across the different customer DBs as they will be a single admin/user.

View 5 Replies View Related

Client Tools :: How To Create A Table In Another Schema As In Existing Schema

Apr 26, 2010

I would like to create a table in another schema(CBF) as already exist in my schema(TLC) without data but related indexes,synonyms and grants should be include.

How could I do this without using export import. I am using TOAD 9.0.1.

View 10 Replies View Related

Server Utilities :: How To Take All Schema Metadata Export Except One Schema (scott)

Jul 5, 2012

how to take all schema metadata export except one schema (scott)

can i use like EXCLUDE=schema:"IN('SCOTT')

View 4 Replies View Related

Export/Import/SQL Loader :: Imp Records Of One Schema Into Another Schema Of Same Table

Nov 3, 2012

I had done following steps,

schemas(toy,toys)

1) i open the session of toy schema

First i taken backup of table

create table bck20121103_himan as select * from himan;

Backup table is created.

After taking the Backup table

delete himan;(deleting the records)

2) i log in to another session(toys)

exp toys/toys@orcl file=20121103TOYs.DMP TABLES=(HIMAN) /* Particular table is taken*/

3) i log in to toy schema

imp toy/toy@orcl file=<dump file name> TABLES=(HIMAN) INDEXES=N IGNORE=Y

i tried the above statement it taken so much of time..

Later i tried

I log in to toy session

i rename the table with other name.

later i imported

imp toy/toy@orcl file=<dump file name> TABLES=(HIMAN) IGNORE=Y FULL=Y

it's successfully imported.

View 3 Replies View Related

Server Utilities :: How To Move Schema To Another Schema In Same Database

Jan 5, 2009

move the tables with data present in the user scott(full) to another schema named test. In my case scott is in user tablespace and for test schema i have created different tablespace named test_tbs.

View 14 Replies View Related

Sequence Name In Oracle Database Schema As Well As Timesten Schema?

Jan 13, 2011

How to use same oracle sequence name in Oracle Database schema as well as timesten schema?

View 1 Replies View Related

Server Administration :: One Schema Versus Multiple Schema

Feb 1, 2010

single schema setup or multiple schema setups for an application development. Which option is recommended and pros and cons of these methods?

View 4 Replies View Related

SQL & PL/SQL :: Accessing Schema Object From Another Schema?

Apr 20, 2010

I have create one procedure under my user schema. In that procedure , I am selecting data from another schema's table.

While compiling that I am getting following error->

PL/SQL: ORA-00942: table or view does not exist
PL/SQL: Statement ignored

how I grant access of one schema object to another schema. Currently I am using oracle 10g

View 2 Replies View Related

How To Fire A Schema Trigger From Outside Schema

Sep 24, 2012

A user is using an ad hoc tool similar to SQL Developer called PeopleSoft Application Designer.

He creates a connection to the db, then issues an alter session set current_schema = 'restricted_schema'. The connected user does not have direct privileges on the "restricted_schema" which they call SYSADM.

After changing the schema context in that manner he creates objects in SYSADM. A schema trigger is then fired and grants privileges on the new objects created in SYSADM. Doing the same in either SQL Plus or SQL Developer does not fire the schema trigger.

I think SQL Plus and SQL Dev are working as they should. Altering the session like that does not change your identity - just the schema context. But, when you examine v_$session, the connection with this other tool looks exactly the same as one from SQL Plus or SQL Dev when changing the schema context in the session.

Instead of trying to figure out what this other tool is doing, is there any way for that schema trigger to fire when using this process from one of our tools?

View 6 Replies View Related

Server Utilities :: Compare Column Name Of Schema With Other Schema's Column Name

Dec 15, 2012

I have a standard schema named ABC and 600 more schema's over there in my database.They all has same table name and column name as on standard schema. But in some tables number of columns varying. So I need to compare all schemas with my standard schemas column name. I create below script but it is generating output in infinite loop.

SET SERVEROUTPUT ON
DECLARE
V_COLS VARCHAR2(20);

BEGIN
FOR CUR_CCD IN(SELECT DISTINCT TABLE_NAME,OWNER FROM ALL_TABLES
WHERE OWNER LIKE 'CCD_MAIN'
[code]....

View 16 Replies View Related

Same Column Name Across Schema

Apr 25, 2011

I would like to find out same column_name from different table across the schema, which means if two tables or more are having the same column name but is not bound by foreign keys, I want to find out and list. for detailed report if we required small anonymous block also fine.provide the info for the above.

View 1 Replies View Related

One Schema For Several People?

Jan 28, 2008

In our Oracle 9i Database we have a Project that uses one Schema called ProjectOne where several people (working together) need to be able to create tables, create,edit and delete records, create and delete triggers and sequences in the ProjectOne schema.

Our DBA creates a user account for each person so they have a different username and password with priviledges to the ProjectOne Schema. The DBA doesnt want us to use the ProjectOne Schema username and password. Is this a standard practice with Oracle where each person needs their own username and password to login into the Database and view and edit one Schema (ProjectOne)?

View 1 Replies View Related

SQL & PL/SQL :: Merging Two Schema In One?

Feb 5, 2012

Requirement of merging two schema's into one. I have a client in two diffrent location . intially we setup the application with two diffrent instances of database for them for a smooth opration as they were not having any connectivity between the branches. Now they are moving their both branches together as one organisation. My application database table structure is same in both places.

View 21 Replies View Related

SQL & PL/SQL :: Extract One Schema's DDL

Apr 22, 2011

Is there any option available in DBMS_METADATA.GET_DDL in such a way that I can extract the script (user creation+grants)only for that particular schema?

View 5 Replies View Related

SQL & PL/SQL :: Schema Vs Tablespace?

May 2, 2010

what is the data dictionary table to find out the default table space and temporary table space for a user.Let say i have a user called 'Pointers'.How to find out the default table space and temporary table space for this.I have checked in 'dict' table but no luck.

SELECT *
FROM dict
WHERE table_name LIKE '%TABLESPACE%'

View 4 Replies View Related

SQL & PL/SQL :: Same Object In Different Schema

Oct 11, 2011

I have 3 schemas (master, baby and web)- master has two pakages , PkgA which has user access and package PkgB with definer access. It also has a table called Tab1. Both pakages are inserting data into table Tab1. Baby schema also has table Tab1. When we call pakage PkgA where data will insert (mater or baby table) and when we call pakage PkgB where data will insert (mater or baby table). What will happen when we call pakages from web and how can we make sure data is inserting baby Tab1 table?

View 4 Replies View Related

HR Schema And Jsf Web Design?

Jul 13, 2013

how can ı reach hr schema I watched alot of video and have read so much writing from oracle but my data base haven't HR schema table like
emlployees
countries
jobs

how can ı add these hr schema
2) how can ı create web page with adf web applicaiton jsf

1) adding a search button
2) search fieldand I need to list my search results

View 1 Replies View Related

XML DB :: Register One XML Schema

Apr 18, 2013

I am new to XML Db.I want to Register one XML schema,but i am getting error like

ORA-30937: No schema definition for 'Project' (namespace '') in parent '/'

BEGIN
DBMS_XMLSCHEMA.registerSchema(
SCHEMAURL => 'Schema.xml',
SCHEMADOC => bfilename('DIR','Schema.xml'),
CSID => nls_charset_id('AL32UTF8'));
END;
[code]....

View 6 Replies View Related

Schema Level DML

Jun 3, 2013

I have about 200 tables, and each table has two columns : ( table_name_ID, local_id ).

example :

COST ( cost_id, local_id )
RATES ( rates_id, local_id )
SALARY ( salary_id, local_id )

I want the column local_id to be set to same value as table_id. ( local_id = cost_id for COST table; local_id = SALARY_ID in SALARY table etc.)

The code for trigger on these table will be generic and can be automtically generated.

But we are talking about 200 tables - means huge amount of testing with existing triggers on all these tables.

Is there any simpler method.

BTW - I purused following thread, and it talks about using AUDITING.

Re: Schema level Database triggers

But I wonder how AUDITING can be used in place of trigger to modify data ?

View 9 Replies View Related

SQL & PL/SQL :: Non Schema User

Feb 18, 2010

does a non schema user have to append schema prefix for every database object ?

View 7 Replies View Related

Using Oracle XE For RCU Schema

May 2, 2013

I have to install obiee on my new linux server.

two question:

-it's a good idea to use oracleXe as database for rcu schemas (mds and biplatform)? Can Xe reduce the impact on server resources?
-in terms of performance of obiee solution, the rcu database have to be on the same machine of obiee or it's better to have this database on a separate machine?

View 1 Replies View Related

How To Retrieve Table Schema

May 22, 2007

I'd like to know which SQL command (if there is any) I can use to display the current schema of a table. I'm particular interested in knowing which columns are indexes.My privileges to the DB are quite basic (I think that I'nm only allowed to run SELECT statements

This is the Oracle version that I'm using:

Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
PL/SQL Release 9.2.0.6.0 - Production
CORE 9.2.0.6.0 Production
TNS for HPUX: Version 9.2.0.6.0 - Production
NLSRTL Version 9.2.0.6.0 - Production

View 2 Replies View Related

Select All Views From Schema

Apr 27, 2010

I want to list out all the views in a schema. What table can I query?

View 2 Replies View Related

Merge Over Multiple Schema

Aug 31, 2007

Procedure A and Table A are located in Schema A. Procedure A performs a merge between View B of Schema B and Table A. Procedure A is giving a ORA-00942(table or view does not exist) for the following line ... USING (SELECT * FROM B.VIEWB) D

Code excerpt
---------------------
MERGE INTO A.TableA C
USING (SELECT * FROM B.ViewB) D
ON (C.dealerid = D.di_dealer_id)
WHEN MATCHED THEN UPDATE SET

C.dealername = D.di_dealer_name,
C.dealerno = D.di_dealer_no,
C.fedid = D.di_fed_id,
C.oldate = D.di_online_dte, ...

Permissions given are as follows

Schema A
----------------
ProcedureA - execute on User A
TableA- Insert, Delete, Update, Select on User A

Schema B
----------------
ViewB - Select on User A, Select on UserARole

I believe appropriate permissions have been given.

View 1 Replies View Related

DB Migration Into Schema Or Instance?

May 17, 2011

I have a large Sybase db that is using Documentum as the front end tool to create apps. Total DB size is around 12 GB.

I would like to migrate into Oracle 10 RAC. Should I add to our current RAC instance with a new Schema or ask our DBA for a new instance?

View 3 Replies View Related

Generating Schema From XSD Files?

Jul 7, 2010

generating oracle table definitions from XML .xsd files?

View 5 Replies View Related







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