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.
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.
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.
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 ;
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,
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 .
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.
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.
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
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?
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
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
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.
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.
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.
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.
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'?
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?
