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
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
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
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
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
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
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
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
Jul 4, 2008
Any tool or way to view the schema and relations between tables of an Oracle database?
View 1 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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
Jan 13, 2011
How to use same oracle sequence name in Oracle Database schema as well as timesten schema?
View 1 Replies
View Related
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
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
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
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
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
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
Jan 17, 2013
can we change the existing materialized view to normal view? if yes how?
View 2 Replies
View Related