SQL & PL/SQL :: Mirroring A Table With Exclusive Column?
Sep 16, 2010
there's a table with 3 columns oid, value, status in a scheme. Now i want this table to be used in an other scheme. So i used grant select to the other scheme so it could read the data. But now i want the column status for independent use of updates in the 2nd scheme. So i want the columns oid & value in one place (table in 1st scheme) and 2 places for status (in original table and in the 2nd scheme. I could create a status table in scheme 2 with oid and status and create an updatable view over table from scheme 1 and this status table - joined over oid. Sounds not too tough but if data is inserted in scheme 1 (all 3 colums) the status table in scheme 2 has no idea of this new data.I want to prevent using triggers or notifications.
I read that the value EXCLUSIVE (REMOTE_LOGIN_PASSWORDFILE parameter) is supported for backward compatibility. It now has the same behavior as the value SHARED.So is the EXCLUSIVE value deprecated? Oracle Database Reference doesn't mention that.
I have a job which runs the procedure below concurrently in 4 different sessions; It updates two tables.
PROCEDURE UPDATE_TAB_1_AND_TAB_2( traceIdTab IN pl_sql table, individualIdTab IN pl_sql table, fileSeqIdTab IN pl_sql table,
[Code].....
Each session is passed a sequence generated file ID; so the records processed in each session are completely different.
However, everytime this job runs, without failure, ONLY TWO sessions process concurrently, while the other two sessions are blocked. Once the first two have finished, then the remaining two sessions start work.
I have being monitoring the sessions on each run daily, and realized that the first two sessions processing are each holding a transaction exclusive lock on objects; hence blocking the other two sessions.
When I tried to find the objects that the two running sessions have a lock on, I cant seem to find anything by searching DBA_OBJECTS.object_id. However, when I look for the objects that are being waited on, its either a partitioned table or a partitioned index on either table_1 or table_2.
At first, I thought it was an ITL lock and after much analysis, I did not find anything which definitely pointed to an ITL lock. However, just to make sure, I increased the INITTRANS value from 2 to 5, but the problem did not go away.
At this point, I have applied all I know to understand the origin of the TX lock and how I can work around this situation, and decided to ask higher powers in Oracle for advise.
@ the end of the day means After date changes i need to populate the new row in Some of my tables with the previous date values with the new date.. In that time some other Transaction will Occur on those tables so we need to lock all the tables in that time..Even i tried by doing the Exclusive lock on the tables...
execute immediate 'LOCK TABLE CTR_SALES IN EXCLUSIVE MODE NOWAIT'; execute immediate 'LOCK TABLE CTR_STK_ASSIGN IN EXCLUSIVE MODE NOWAIT';
And how to run this function @ 12:00 in the midnight by scheduling.... And 1 more thing how to pick the recent (Transaction) row on that table. I tried from the rowid it's not working
How i create Standby Database in Same HOST, we using SUN OS 5.8.
I read form Oracle DOC that i can create Standby Database in Same HOST and follow the tutorial , but when i try to mount standby database using ALTER DATABASE MOUNT STANDBY DATABASE EXCLUSIVE; i got a error message ORA-01102: cannot mount database in EXCLUSIVE mode
I performed Shutdown Immediate in Oracle 8I and now I am unable to mount the database.I'm getting the following message: ORA-01102 Cannot mount database in Exclusive mode/I only have one database running on this machine. It was automatically created by Oracle installer upon installation of Oracle software. I can no longer connect via SQL * Plus,Only through Server Manager (connect internal)
I'm trying to create a physical standby database on my Oracle9i DB runing on WinXP.
Note: I have both Primary and Standby on the same system.
Actually everything went well .... I did created the standby DB but the problem is I can not start my primary DB if my standby DB is mounted. I get this error:
ORA-01102: cannot mount database in EXCLUSIVE mode
And when I read about the error message I learnt that I should start my DB in compatible mode ...
(1) how can i fill some value in a table column based on some existing column value automatically without user intervention. my actual problem is i have 'expiry date' column and 'status'. the 'status' column should get filled automatically based on the current system date. ex: if expiry date is '25-Apr-2011' and current date is '14-May-2011', then status should be filled as 'EXPIRED'
(2)hOw can i build 'select' query in a report (report 6i) so that it will show me list of items 'EXPIRED' or 'NOT EXPIRED' or both expired and not expired separately in a single report based on user choice. 'EXPIRED' & 'NOT EXPIRED' can be taken from the above question no. 1.
if a table contains two columns and both are part of the primary key of that table (Kind of obvoius).
should i opt for a index organized tbale in this case ?Or should i opt for another running sequential colum which would serve as a primary key of this table and define the actual two columns of the system as unique keys.
there is a drawback if a most of the tables of a database contain composite primary keys?
I am trying to join column names from a table with data from a different table. I think i should be able to pass the parameter to a 'select list' in a query. Look at my sample data below. And the data in sales table can grow till 15 rows and similarly corresponding columns in saleshist.
I have two table country and state.Columns are given below
SQL> DESC Country
Name Null? Type ----------------- -------- -------------- PRIMARY_KEY NOT NULL NUMBER DISPLAY_ORDER NOT NULL NUMBER NAME NOT NULL VARCHAR2(50) TREE_POSITION NOT NULL VARCHAR2(250) PARENT_ID NUMBER
SQL> desc state Name Null? Type ----------------- -------- ------------------------- PRIMARY_KEY NOT NULL NUMBER DISPLAY_ORDER NOT NULL NUMBER NAME NOT NULL VARCHAR2(50 CHAR) VALUE NOT NULL VARCHAR2(50 CHAR) TREE_POSITION NOT NULL VARCHAR2(250 CHAR) PARENT_ID NUMBER
Now I have to insert the values in the state table taking from the country table.
EXAMPLE :
INSERT INTO STATE (PRIMARY_KEY, DISPLAY_ORDER, NAME, VALUE, PARENT_ID, TREE_POSITION) VALUES (251, 1, 'Alaska', 'AK', 233, 'STAT_USUS_AKAK');
but here the tree position is static it can be anything at the client side so I want to make it dynamic. So to make dynamic what I have used is mentioned below:
INSERT INTO STATE (PRIMARY_KEY,DISPLAY_ORDER,NAME, VALUE, PARENT_ID, TREE_POSITION) VALUES
[code].....
Now I want that Quote: select tree_position from L_STATE_ITEM where name ='United States')||'_ALAL' which i have used in the state table to put value in the tree_position, I want to make the other half part after the concatenation operator "_ALAL" also dynamic.
So I was just wondering If I can pic that value from the "VALUE COLUMN" of the same table.
How can I add new column at the specified location in a oracle table? I am having a flat file from which data is inserted to the table..But initially I don't need that column so I m not having this column in the table but if further i need that column at the particular location then how can I do this..?
I have some tables. TABLE1 contains columns called query_id and a column called List_name TABLE2 holds a list of data and the TABLE_NAME = value in TABLE1 held under LIST_NAME TABLE3 contains column with Query_ID and other info.
Is it possible to grab the entry in TABLE1 under LIST_NAME as a TABLE_NAME in a data source?
i'v a problem regarding dual table,i logged to oracle as sys as sysdba and i add a column to dual table and then want to delete that added column but oracle gives "ORA-12988: cannot drop column from table owned by SYS"
i'm not able to drop any user oracle gives "ORA-00604: error occurred at recursive SQL level 1" "ORA-01031: insufficient privileges"
another error:: SP2-0575: Use of Oracle SQL feature not in SQL92 Entry Level oracle gives error when i want to switch user all problems starts when i add that column to dual table
I have one table which has 90 columns all has varchar2 datatype except one Column[primary key (Number)]. In this Table we have 1000 records, I want to fetch those records from Table which has value in all 90 columns means there is no null value in any column.
I know simple method Like this :-
column_name1 IS NOT NULL AND Column_name2 IS NOT NULL.
Like this we can write IS NOT NULL condition for all column.Is there any other way to write this Query because it makes Query very longer and it is very tedious job to write this Condition for all Columns.
With Data in a table, is it possible to find out the table name and column name? Means i know the account number (data) of the table and with this data i want to find out column name and table name.
