I am trying to write an update statement which updates the User IDs in one table with the User IDs in another table. However I need to update statement to ignore any duplicates that are in the tables.
View 4 RepliesI'd like to know if it is possible to track DML actions issued on a specific table by a specific user, for example , i tried :
AUDIT SELECT on SCOTT.DEPT by HR by ACCESS;
I get an error, where is my syntax error ?
i want to know if it's possible to do it without trigger ?
I stumbled about some weird 11gR2 behavior (running on AIX).When I performed a join between a table with user based content (parts belonging to an sourcing scope) and a base table (parts available) whereas the parts have to fulfill a special regular expression, it showed that the same query is faster when using outer join than inner join (about 0.7sec vs. 20sec; which makes me believe that regexp_like works wrong when involved in an inner join).
i tried the same statement with a standard like (but not fulfilling the same condition).This time performance was as expected (inner join outperforming outer join).
Oracle version information
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
[code]...
I can see it, the execution plan for the "inner join" doesn't show so much more costs than the one for the outer (but why at all is does an inner join cost more?) ...The execution plan for both "not like" is the same and (surprisingly ;-) ) similar to "outer-regexp".
I hope sample data are not needed as there would be needed a lot...this is the second time I came across the "plan worse but execution time better" phenomenon.
i need to grant a user to see all table into another table and not to use this option (grant any table)
View 9 Replies View RelatedI have a table TEMP_TEST_1 to be loaded with multiple rows which contains the available items for a store with their sequence numbers. I also have another table TEMP_INV_1 which holds a column for inventory count of the items, and this column is to be updated after loading the first table TEMP_TEST_1.
The table TEMP_TEST_1 is updated through all possible channels in OLTP. So while updating the inventory, which is the best way. My update should either add the total number of inserts per item into the TEMP_TEST_1 table's INVENTORY_CNT column i.e. existing value + count of new rows OR a full update by taking the complete row count (Grouped by item id) and update the INVENTORY_CNT column
--------------------------------------------------------------------------------
-- Initial setup SQL's, TEMP_TEST_1 is updates on OLTP through multiple channels
--------------------------------------------------------------------------------
CREATE TABLE TEMP_TEST_1 ( ITEMID NUMBER ( 4 ),
INVENTORY_CNT NUMBER ( 2 ) );
CREATE TABLE TEMP_INV_1 ( ITEMID NUMBER ( 4 ),
ITEMSEQ NUMBER ( 5 ) );
INSERT INTO
[code].......
--------------------------------------------------------------------------------
-- Inserts for new items in the store
--------------------------------------------------------------------------------
INSERT INTO
TEMP_INV_1
VALUES
( 1,
11 );
INSERT INTO
TEMP_INV_1
[code].......
I am using pay user tables to store the balance values.
Example:
IF rec.balance_name = 'Gross Earnings'
THEN
l_gross := bal_function(g_assg_id,rec.balance_name,'_ASG_GRE_YTD',p_effct_date);
END IF;
from the above I should not hardcode ('Gross Earnings ) value, so I placed this value in pay user table that can be changed by different clients. how do i refer user table values for the right earnings/net/overtime pay values that can be placed in user table.
I have two user a and b. I have to Grant select update insert Delete Permission to all tables of user b To User a. how can it possible?
View 5 Replies View RelatedIm using oracle forms 10g. i want to take backup of user's table.
i.e, scott user has 4 tables. if i login as scott user and select the tables what i have to take backup using check box and click a backup button,the selected tables backup and stored in .MDB(MS Access).
How to achive this?
How to Insert all user names into a table from sql for example when i use select * from all_users command it displays all user names i want that how we transfer these user names into a table.
View 11 Replies View RelatedI have an issue on selecting data from a nested table owned by other user.
I have given select access on stc_irb_usr.stc_loy_settlebillpay_map_main to anthr user adminofs.
But when I try to query, it says insufficient privileges. adminofs is able to select other columns which are not nested.
I have given "grant execute on STC_IRB_USR.STC_BILLPAY_MAP_TAB_TYPE to adminofs;" but it did not
select * from stc_irb_usr.stc_loy_settlebillpay_map_main where rownum<3;
ERROR: OCI-21700: object does not exist or is marked for delete
SQL> desc stc_irb_usr.stc_loy_settlebillpay_map_main
Name Null? Type
----------------------------------------- -------- ----------------------------
BATCH_NUM NOT NULL NUMBER
ACCOUNT_NUM NOT NULL VARCHAR2(40)
PAY_BILL_SEQ_MAP STC_IRB_USR.STC_BILLPAY_MAP_
[Code]....
Name Null? Type
----------------------------------------- -------- ----------------------------
ALLOCATING_CREDIT_SEQ NUMBER
ALLOCATION_ENTITY_SEQ NUMBER
ALLOCATING_CREDIT_DATE DATE
ALLOCATING_CREDIT_TYPE VARCHAR2(20)
ALLOCATION_ENTITY_DATE DATE
[Code]...
I have a large table and want to calculate just a few values. Therefore, I don't want to create a new table, I want to update the table. Here an example:
I want to calculate the VALUE_LAG with ID = 4 only (-> two values).
create table zTEST
( PRODUCT number,
ID number,
VALUE number,
VALUE_L1 number );
[Code]..
I tried this, but obviously, windows functions are not allowed in the update statement.
update zTEST
set VALUE_L1 = lag(VALUE) over (partition by PRODUCT, order by ID)
where ID = 4
How can I do this?
i got a prob in executing a query in my oracle.TRANSACTIONLIST is the table and my query is..
select * from TRANSACTIONLIST where USER_ID = '07751A1247'the table has 2 records with that user id.
But it is given result as "no data found".
I've created a type as
CODETYPE TEST_DWH_PROD AS OBJECT
(WO_RELEASE_DATE DATE,
SITE_CODE__MFG CHAR(5),
SITE_CODE__PARENT CHAR(5),
ORDER_TYPE CHAR(3),
MFG_TYPE VARCHAR2(10),
[code]....
the above query is giving the value of current QTD(on the basis of V_REPORT_DATE)..I need to integrate the same as user want the output as table type and he can also give some filter conditions.
That is I have created the User Defined Data Type as following. CREATE OR REPLACE TYPE Bit_Type AS OBJECT(Bit NUMBER(1,0));
After completing this creation of new UDT, I am trying to create the table with this UDT as follows, CREATE OR REPLACE TABLE Sample_Bit ( RegID Bit_Type);
I received an Error Message like:
SQL Error: ORA-22913: must specify table name for nested table column or attribute
22913. 00000 - "must specify table name for nested table column or attribute"
*Cause: The storage clause is not specified for a nested table column or attribute.
*Action: Specify the nested table storage clause for the nested table column or attribute.
a table structure is modified every now and then because of which the few packages get uncompiled. is there any way to monitor which user has changed table structure.
View 2 Replies View Related1.How to check a user has 'create table' privilege?
2.how to check a user has privilege to grant 'create table' privilege to other user ?
I have build a sw containing 20 forms & 35 reports. I have created users table.
Now I want to assign different rights to different users like update, view , update, print, delete etc etc. Enable disable add/delete/update buttons on basis of user rights.
I have granted select privs on below tables to an exisiting user.grant select on WEB.ALMSTAT to nms...I would like to view the privs that i have granted to nms user?
*"In production, nms user will need the ability to read the following tables:*
*WEB.ALMSTAT*
*NET.ASSET*
*NET.LOCATIONNAME"*
I have a small question is it possible to find the details of a user who modified the structure of a table, including what command he ran to change the structure of the table?
View 2 Replies View Relatedi 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
I have a table with table_ID, date_created, user_id.
I have sequence, and a BEFORE INSERT trigger, which uses the seq to increment the table_ID by 1, everytime the webpage is saved.
ques:
(1).If i have date_creted defaulted to sysdate in the table, do I need to have it in the trigger?
-- Update create_date field to current system date
:NEW.DATE_CREATED := sysdate;
(2).How can I insert the user_id in the table, each time user SAVE the page ? web page procedure is getting the user info at the beginning. can i add it in the trigger
DECLARE
v_username varchar2(10);
BEGIN
[Code]....
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.
How can i find the last user analyzed particular table?
I can able to find last analyzed date, but can't able find the which user has done it.
Flashback table operations are not supported for the SYS user . I have found the above line in one of the article.
I don't know why the SYS user can not able to flash back the table.
has copied one table data from Production to any other schema , know which user has done this ?
View 1 Replies View Relatedwe have every day full export backup in eacly morning. but some tables's data has been delete unforutnaltely & structure of these tables intact. how do i import only some tables of a user from
daily full export backup? . this has to be done immdediately.
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.
I want to find the constraint name from User_Constraints table using the following query:
Select * From User_Constraints Where Table_Name='CHARGECODE' and Constraint_Type='C' And Search_Condition = '"PERCENTAGE" IS NOT NULL';
Then it shows "ORA-00997: illegal use of LONG datatype" error.
Is there any way to compare with long type value.
Need to access data in a table base on user parameter input where the data is stored like a spreadsheet with column headings JAN, FEB, MAR... and the rows are the years. Is there a way to create a generic SQL statement so that I don't have to have 12 if statements in the procedure?
View 5 Replies View Related