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


ADVERTISEMENT

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 :: Grant User Schema To Another User

Feb 9, 2011

I have two users say A and B. I have all the tables,views,indexes, types,procedures,packages etc. User B wants to access all the objects from user A.

View 4 Replies View Related

SQL & PL/SQL :: Rename User Or Schema

Jul 12, 2012

I have one use "A" . I would like to rename user "A" to "B". how its possible ?

View 11 Replies View Related

XE :: How To Drop User And Schema

Aug 31, 2012

I want to free up some space , as i have created some testing users and schema.

View 3 Replies View Related

PL/SQL :: Index Name By Schema User

Nov 12, 2013

I want to find out  all index of schema user X. what will be the query? t will be like below schema name index name

View 8 Replies View Related

Security :: How To Create User Under Schema

Dec 5, 2012

To make users under one schema.

1) user should have access of all objects with limited privileges.

How can i make it under schema?

View 3 Replies View Related

How To Create A User (Owner Of Coeus Schema)

Dec 9, 2010

On the database instance where you want to install Coeus, create a user who will be the owner of Coeus schema. This user should have at least the following privileges. CREATE TABLE, CREATE VIEW, CREATE PROCEDURE, CREATE PUBLIC SYNONYM, CREATE USER, CREATE ROLE, CREATE SEQUENCE, CREATE SESSION CREATE TRIGGER, AND CREATE TYPE. If you choose, you can grant DBA role to this user.

Note: Coeus Schema owner should have CREATE USER and ALTER USER right explicitly granted to it, not through a role like DBA.

View 3 Replies View Related

PL/SQL :: How To Copy A User Defined Type From Other Schema

Aug 25, 2012

Through sql*plus, how can I copy a user-defined type from other schema to mine?

I've granted the execute object privilege over the user-defined type to my user, so I can use the type.

I know I can manually write and create the type, but I wanna learn (if exist) a statement to copy the user-defined type, something like:
CREATE TABLE x2 AS
(SELECT *
FROM x1);

I'm testing ORACLE DATABASE 11g R2 over Windows O. S.

View 3 Replies View Related

Export All Schema And User Permission - Then Import To New Server?

Nov 2, 2010

I have database in 9.2.0.3 on windows 2003 R2 one server and i have serve with 10.2.0.4 64 bit on windows 2008 R2 64 bits. I want to move database from 9.2.0.3 to new server on 10.2.0.4.

!)should i do the cold backup of 9.2.0.3 and then create db instance on new server and then use dimutility to create new instance and then run the patch upgrade.

!!) I would like to do export all schema and user permision (is if possible?)export and then import to new server?

View 2 Replies View Related

Server Administration :: How To See Schema User Password In Text

Oct 1, 2012

I need to see all schema users password in a text.I am database admin,but it difficult to remember the password of all users created in single database So we also dont want to change password of all time,table to view password in text. Also we all know we can see using dba_users, but it was showing in hash value i need it in a text

View 3 Replies View Related

SQL & PL/SQL :: Finding User Who Made Changes In Table Structure Or Schema

Sep 21, 2011

i want to find the name of user who make changes in the table structure or create any index or constraint or unique key or alter the column? Is there any way to find in Oracle. in which table what change has been done as well?

following Output needed

userid, username, schemaname, schemachangetime, "what_change_has_been_made", IP address or Computername

View 11 Replies View Related

Server Administration :: Difference Between A User And Schema On Oracle?

Apr 26, 2010

difference between a user and schema on Oracle.By difference, I do not mean what is a user and what is a schema.

I understand that if we create a user, the schema gets created by default for that user. Does that mean that we cannot create a user/schema alone.

What if I just want to create a user and grant him some read access on an existing schema or just want to create a schema.

View 7 Replies View Related

SQL & PL/SQL :: How To Restrict User (Schema) From Deleting Data From Table

Nov 2, 2012

I want to know how to restrict a user(Schema) from deleting the values from a table created in the same schema.

Below is the example.

I have created a table employee in abc schema which has two values.

EMPLOYEE
ABC
XYZ

In the above scenario the abc user can only fire select query on the EMPLOYEE table.

SELECT * FROM EMPLOYEE;

He should not be able to use any other DML commands on that table. If he uses then Insufficient privileges error should be thrown.

View 10 Replies View Related

PL/SQL :: User Copied Table Data From Prod To Other Schema?

Apr 22, 2013

has copied one table data from Production to any other schema , know which user has done this ?

View 1 Replies View Related

PL/SQL :: How To Restrict User (Schema) From Deleting Data From A Table

Nov 2, 2012

I have scenario here.

I want to know how to restrict a user(Schema) from deleting the values from a table created in the same schema.

Below is the example.

I have created a table employee in abc schema which has two values.

EMPLOYEE
ABC
XYZ

In the above scenario the abc user can only fire select query on the EMPLOYEE table.

SELECT * FROM EMPLOYEE;

He should not be able to use any other DML commands on that table. If he uses then Insufficient privileges error should be thrown.

View 6 Replies View Related

Server Utilities :: Import Schema Into A Newly Created User?

Jun 6, 2012

I need a clarification on the below query:

1) DROP USER MK CASCADE;

2) Created user

3) Created objects like procedure,index... and granted privileges.

4) Now i am performing the import as below.

impdp system/.... SCHEMAS=MK DIRECTORY=EXPBKUP DUMPFILE=ABC_Export.dmp LOGFILE=ABC_imp.log INCLUDE=TABLE TABLE_EXISTS_ACTION=REPLACE

But nothing is imported.

Is this the problem of the parameter "INCLUDE=TABLE TABLE_EXISTS_ACTION=REPLACE"? as the user is new.

View 5 Replies View Related

SQL & PL/SQL :: Create Trigger That Will Fire Whenever Any User Will Access Schema Of Oracle

Oct 31, 2011

I would like to create a trigger that will fire whenever any user will access to a Schema of Oracle DB (for each and every login). regardless the access will be through an application or SQLPLUS and this trigger must insert the below information into a table.

1) IP address
2) Machine Name
3) login time
4) logout time
5) name of accessed schema

writing this trigger and creating the table that will hold the required data.

View 6 Replies View Related

Backup & Recovery :: Remap Schema From Dump File To New User?

Jan 18, 2012

I am trying to remap schema from dump file to new user but my import fails and giving me error.

-----------------------------------
Using username "oracle".
Last login: Tue Jan 17 17:41:30 2012 from 192.168.100.11
[oracle@cvs ~]$ dbstart
Processing Database instance "cvsdbm": log file /home/oracle/oracle/product/10.2.0/db_1/startup.log

[code]....

View 1 Replies View Related

SQL & PL/SQL :: Writing Procedure In User 2 Schema Accessing Table Product - Not Compiled?

Dec 17, 2010

I have got 2 users as user1 and user2.I have used the following statements from user 'user1':

create role GENEVAOBJECTS;
grant select, insert, update, delete on PRODUCT to GENEVAOBJECTS;
grant GENEVAOBJECTS to user2;

In the above statements, product is a table. Now, I could able to access this table from user 'user2'. But however if I write a procedure in user2 schema accessing the table product, then the procedure is not getting compiled.

create or replace procedure test_prc as
v_test number(9);
begin
select product_id into v_test
from PRODUCT where rownum=1;

[code]...

why I cannot access that table from procedure?

View 8 Replies View Related

Server Utilities :: Schema Level Import By Dropping Existing User That Contain Tables

Jan 20, 2011

Our Testing DB is running in No archive log mode. I did a schema level import by dropping the existing user that contain tables, recreate the user and finished the import. Now they want the old tables back.Is there is any way to recover the old tables?

View 5 Replies View Related

Server Administration :: Create / Drop User / Schema In Another Oracle Database Through Apex

Jul 7, 2012

Through an Oracle Apex application I need to create/drop a user/schema in another Oracle database. i.e., create/drop user remotely using an Oracle Apex application.

View 12 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 Utilities :: Exported Data Of One User Importing Into Another Schema At Another Server

Sep 15, 2010

I have exported data of one user an importing into another schema at another server. when i am trying to imoport it is working fine for quite no of imports into tables, but after some time it starts giving me below mention error...

IMP-00008: unrecognized statement in the export file:
<
IMP-00008: unrecognized statement in the export file:
<
IMP-00008: unrecognized statement in the export file:
<‏ے
IMP-00008: unrecognized statement in the export file:
+A
IMP-00008: unrecognized statement in the export file:
[code]...

View 6 Replies View Related

Export/Import/SQL Loader :: Schema Level Export For User Sample 1

Jul 11, 2012

I have schema level export for user SAMPLE1(Default tablespace USERS) on oracle 9.2.0.1 production database. I want to import into another 9i database on another server, so do i nneed to Create SAMPLE1 user and USERS tablespace in new database again.

View 5 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

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







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