SQL & PL/SQL :: View Definition With Schema Name?

Nov 28, 2012

I have a view in schema 'sales', let say the view name is view_test.

This view sales.view_test uses two base tables, one from schema 'products' and other from its own schema 'sales'.

So in sales schema the view defination is,

create or replace view view_test
as
select * from t_test, products.t_products
where t_test.id=t_products.id

The view got created, I have granted select permisson with grant option on tables t_test, products.t_products and view_test to another user 'master'.But when i query the view from master schema I get an error saying that the view does not exists, upon analysis, I found one of the base table in the view defination has no schema name mentioned (select * from t_test, products.t_products --here only t_test mentioned rather sales.t_test), I feel because the table t_test belongs to the same schmea of the view, they did not mention the schema name. If not, does it mean that we need to always specify the schema names in the view defination for the base tables.

View 6 Replies


ADVERTISEMENT

SQL & PL/SQL :: Change View Definition When Select Is Running On That View

Apr 25, 2012

We wrote one data load process to load GZ files into Database.during process we will change client facing view definitions to backup table so that we can work on base tables.

This view definition changes are related to FROM and WHERE clause (not columns/type). during load process, client/user may connect to current server and accessing these views. My question is what will be the reflection of changing view definition while user is accessing view?

I created a scenario-
STEP1: Created a view-
create or replace view view_01 as
select object_name from dba_objects union all
select object_name from dba_objects union all
select object_name from dba_objects union all
[code]....

View definition is replaced by new definition while select is executing on that view. select returned number of records as per view definition one.

View 6 Replies View Related

Imbedded DB LINKS In View Definition

Sep 7, 2012

How can I determine what views have a dblink hard coded in them? I am talking about a large number of views so bringing up each view's DDL in a GUI data dictionary tool to look is not an option. I tried running the query below unfortunately "text" is a LONG and doesn't allow the use of the "like" statement.

select * from dba_views where text like '%@%' ;

View 4 Replies View Related

SQL & PL/SQL :: How To Retrieve A View Definition To Recreate

May 20, 2013

How to retrieve a view definition to re-create it somewhere when its size is more than 32767 bytes?Here are some constraints :

- We are in Oracle 9.2.0.8, meaning that we cannot use the DBMS_METADATA package.
- We cannot concatenate char and long : things like
set long 100000

select 'create or replace view my_view as ' || text from dba_views where view_name = 'MY_VIEW';
are not possible.

View 31 Replies View Related

ORA-01731 Circular View Definition Encountered

Feb 22, 2013

I created a view for emp1 table.

SQL> select   view_name , text   from  dba_views  where VIEW_NAME='EMP1_VIEW';          

VIEW_NAME      TEXT

EMP1_VEW       select  empid , ename , qual,  dept  from  emp1
where  empid  between  1000 and  1010

Now  i tried to  use create or replace option

SQL>create or replace  view emp1_view as
*2 select * from emp1_view with read only;*
create or replace  view emp1_view as
*+
ERROR at line 1:
ORA-01731: circular view definition encountered

Actually i want to make read only  view (emp1_view) ;  without  doping  ;

View 16 Replies View Related

PL/SQL :: Select On View Takes Long On Definition Short - No Difference In Exec-plans?

Aug 20, 2012

[URL]...

I have a quite complex view that selects from approx 10 long tables (approx 4M records each) and build one "customer sentence" pre customer id. I will be always getting just one row from this view, eg. select * from my_view where party_id = XYZ. I'll NEVER EVER select the whole view.

The problem is that running a query:
select * from my_view where party_id = XYZ takes really long time, while putting the party_id = XYZ condition directly into the view executes in 0.0 seconds.

After putting a ORDERED FIRST_ROWS(1) hint into a view the execution plans seems to be the same (or very similar) for both queries. Unfortunately, I can not transfer anything but screenshot from the environmnet - therefore I paste the exec plans as screenshots only - pls follow the link: [URL]...

View DDL:
create or replace view my_view as
select /*+ ORDERED FIRST_ROWS(1) */  pt.party_id
           pt.party_id as id_klienta_mdm,
           pt.master_reference_no as id_klienta_ref_mdm,
        
[code]...

View 6 Replies View Related

PL/SQL :: Renaming View From Another Schema?

Aug 1, 2013

I've 2 schemas  namely  department,  hrNow department schema is having one table EMP . And a view is created on that table with the name R_EMPSchema Name : 

departmentTable Name  :  EMPView Name   :  R_EMPGRANT SELECT ON R_EMP  TO  HR ;  In HR schema a synonym has been created on view R_EMP with the name R_EMP .CREATE OR REPLACE SYNONYM hr.R_EMP for department.R_EMPSchema Name :  hr Synonym Name:  R_EMP

Now I want to rename the view name R_EMP  to V_EMP  from  HR schema only So, I tried the folloiwng syntax in HR schema (All the operations should be done from this schema only )[code] RENAME department.R_EMP to department.V_EMP ;

Error: ORA-01765: specifying table's owner name is not allowed[/code] How to change the syntax to make the statement work from HR schema .

View 6 Replies View Related

Insufficient Privilege From Schema Own View?

Jul 19, 2013

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.

View 18 Replies View Related

Tool To View Schema And Relations Between Tables

Jul 4, 2008

Any tool or way to view the schema and relations between tables of an Oracle database?

View 1 Replies View Related

SQL & PL/SQL :: Creating And Refreshing Materialized View Group From Another Schema?

Nov 27, 2012

I have a list of materialized views in schema A. I want to create a refresh group and then refresh it from Schema B (Dynamically-run time based on some criteria). What Grants are necessary on schema B in order for it to be able to create and refresh the groups on Materialized views in Schema A.

I know that one of the Options is to, GRANT ALTER ANY MATERIALIZED VIEW as a SYS user. But I do not have any SYS privileges.

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

Display All Tables In DB (with Their Definition)?

Sep 19, 2007

I've tried using Select * from ALL_TABLES but this does not return me the column info for each table.

How do I get an output of all tables in a database along with column (e.g datatypes etc)?

I'm using TOAD v8.6.1.

View 3 Replies View Related

Performance Tuning :: Definition Between 2 Metrics Physical Reads

Nov 21, 2011

On 10gR2, I cannot find the exact definition of the 2 following metrics :

"Physical Read Total Bytes Per Sec"
and
"Physical Read Bytes Per Sec"

I would want to know what is the difference between them.

View 3 Replies View Related

Application Express :: Page Validation Against Table Definition?

Apr 29, 2013

I am trying to apply page validations on a tabular form. The form allows users to update data in a database table. I have created some validations such as "column x must not be null" etc and on submit, the error message appears and the relevant cells are highlighted in red. All ok so far.

However, for the primary key, I am relying on the table definitions in the Oracle database to not allow duplicate row entries. When a user tries to enter a duplicate row the error message appears but the relevant row / cells are not highlighted, just the row number is given. In a table with many rows this is a bit annoying.

Is there anyway to get the cells to highlight in red for such circumstances or do I need to create the primary keys within APEX itself?

APEX 4.2.1.00.08

View 0 Replies View Related

Server Utilities :: External Table Definition And Content Of CSV File

May 26, 2010

Below is the external table definition and the content of csv file, why the first record fails ?

0546-0*LB-CRP*16*"Tech", ZAO*29-DEC-2009*29-DEC-2010***A051453*RU*29-DEC-2009*0***21-MAY-2010*21-MAY-2010 --FAILED
0546-0*LB-CRP*16*ID"Tech", ZAO*29-DEC-2009*29-DEC-2010***A051453*RU*29-DEC-2009*0***21-MAY-2010*21-MAY-2010 -- SUCCESS
0546-0*LB-CRP*16*"Tech, ZAO"*29-DEC-2009*29-DEC-2010***A051453*RU*29-DEC-2009*0***21-MAY-2010*21-MAY-2010 -- SUCCESS
[code]....

View 8 Replies View Related

Server Utilities :: ORA-01466 - Unable To Read Data - Table Definition Has Changed

Apr 2, 2013

My job is running at 2 am and that time no application user is connected. Even though, my exp utility shows error on 3 tables (2 are temp tables), everyday. But when expdp is running without error, which was scheduled at 4 am.

Below are the error -

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 Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

. . exporting table DW_TEST_MOTOR
EXP-00056: ORACLE error 1466 encountered
ORA-01466: unable to read data - table definition has changed

[Code].....

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

Application Express :: 4.2.1 Dynamic Action Definition True Action Settings Not Visible

May 16, 2013

im working with apex 4.2.1 and when i try to define a Dynamic action on any item, if the action type is "Set Value" or execute SQL/Javascript code it should appear a text field that let me define the action taking place. However this wont do..

this is what happens:

And this is what should happen:

I know that patch 4.2.2 solves some issues regarding dynamic actions but in the bug report there's no reference to such a problem.

View 1 Replies View Related

Server Utilities :: User Creation Definition In User Mode Export

Mar 31, 2010

I would like to know if 'user creation definition' is exported in user mode export if export is done with DBA role..If it is Not, does it mean we always need to precreate the user before we import the dump created using 'user mode export'?

View 2 Replies View Related

Backup & Recovery :: Adding DB ID To RMAN Backup Definition

Oct 13, 2011

is there any way I can add the DBID to my rman backup name? I've already tried %F/%f as seen in the man page. but this option remains ignored.

It works for the rman backup of the control file. but no change to get this running proper for the datafiles.

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 Administration :: How To Change Existing Materialized View To Normal View

Jan 17, 2013

can we change the existing materialized view to normal view? if yes how?

View 2 Replies View Related







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