I am creating database instance from template. I have specified the location of redo log files. When I run the dbca utility it does creates the redo log files in specified directory. But the installation fails . When I checked the trace file. it says unable to locate the specified file(redo.log). But when i check in directory they are created.
Oracle 10g. I want to find which sql statement was blocking others.
I know the sid of the blocker but I am unable to find the sql statement of the blocker.Also, when I check enterprise manager top activity only shows the waiter.
I tried to run the queries from below link, but it only shows the information about the waiting session.
[URL]
How can I find the sql statement of the particular sid from history ?
We are getting below error....Our one package called orawpcom.dll library file.
ERROR at line 1: ORA-06520: PL/SQL: Error loading external library ORA-06522: Unable to find library '/oracle9i/app/product/11.1.0.7.0inorawpcom.dll'. ORA-06512: at "GFSAM.OAINVOKEDOUBLE", line 1 ORA-06512: at "GFSAM.ORDCOM", line 229 ORA-06512: at "GFSAM.ORDEXCELSB", line 450 ORA-06512: at line 43 ORA-06520: PL/SQL: Error loading external library ORA-06522: Unable to find library
I am getting below ORA-01555 error in alert log everyday.
ORA-01555 caused by SQL statement below (SCN: 0x09ad.86a4562a): Sat Jan 21 08:39:45 2012 SELECT (NVL(MAX(BLOCK_ID + BLOCKS ),0) * :b1 ) / 1024 FROM DBA_EXTENTS WHERE TABLESPACE_NAME = :b2 AND FILE_ID = :b3
I cannot able to find the sql_id here. So how can I find from which process or session this query is firing? before increasing the undo size, I need to analyze as why it is occuring?
1. On the Home Page, click the "Settings" wheel in the header of the Team Development region on the right side. 2. On the Home Page Preferences popup set "Show" to something else as "All" and click [Apply Changes] 3. Back on the Home Page, in the Team Development region on the right side, click the number next to "Bugs" 4. ERR-1002 Unable to find item ID for item "P3500_RELEASE" in application "4800".
I am trying to find the unix process for one of my application in the database but I am unable to view the same. To simulate, I did the following.
1. My database runs on different server. 2. I invoked "sqlplus" from another unix box to login to the database. 3. I found that the process id (ps -ef |grep sqlplus). 4. When I execute the below mentioned query it does not display the process id that I am looking for. But the osuser, username, program and machine details are correct. How can I know the process details from the database?
SELECT SYS.GV_$SESSION.OSUSER, SYS.GV_$SESSION.USERNAME, SYS.GV_$PROCESS.SPID, SYS.GV_$SESSION.MACHINE, SYS.GV_$SESSION.PROGRAM, SYS.GV_$PROCESS.PROGRAM ,SYS.GV_$SESSION.SQL_ID FROM SYS.GV_$PROCESS, SYS.GV_$SESSION WHERE SYS.GV_$PROCESS.ADDR=SYS.GV_$SESSION.PADDR and SYS.GV_$SESSION.USERNAME='TEST' and SYS.GV_$SESSION.MACHINE like '%hostname%'
i am facing a small problem (!!) while inserting into a table by selecting from another table.
let me simulate.
while execute the select query, i am able to view the records. but when the data inserted into the new table, empty column value is getting inserted. this problem with only one column.
Table 1 : data to be selected. CREATE TABLE DRI_TEST2 ( FM_COMPANY VARCHAR2(4 BYTE) )
[Code]....
if we select the select clause we will be able to see the records. but while inserting empty values are getting inserted.
I tried to create a backup table as select * from the existing table but still no partition information seems to get transferred. The new table has NO partitions.
i have table like TWRCHAIN_WS_SUM. it is regularly updated by another object like procedure, function. how can i find which object is updating the particular table across the database.
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.
I want to know constraint name of a particular table if it exists.Here are the queries that i tried to find the constraint name of a particular table but the results were 'no rows selected'.
Queries:
select constraint_name from user_cons_columns where table_name='x'; select constraint_name from user_constraints where table_name='x';
I want to know that How to find which table got last updated and how to find last DDL and DML operation obtained in which table? here I know the table name
SQL> SELECT LAST_DDL_TIME FROM DBA_OBJECTS WHERE OBJECT_NAME='PREM';
LAST_DDL_ --------- 20-MAR-12
SQL> TRUNCATE TABLE PREM;
Table truncated.
SQL> SELECT LAST_DDL_TIME FROM DBA_OBJECTS WHERE OBJECT_NAME='PREM';
LAST_DDL_ --------- 10-APR-12
Note: With out enable the auditing I want to know that .
i have a employee table .i want to find out duplicate name from this table.so i write following query
select empname from employee having count(empname) >1 group by empname
i find the result but problem is how i can find out the same name if it have any space like ramkumar ram kumar both are same but above query did not.give this data. how i can solve it
i'm just trying to find out DML Statements, that where executed on a special table at a defined time. By googeling i just found a statement to find out the last DML, that was executed on a table or something else. But i want to look into the past. How can i do this?
I am trying to Truncate the table but it doesnt get truncated. When I issue the command it even doesnt throw the error. I also tried to drop the table but cant even able to drop the table. I thought table might be locked. But it allows me delete a row from the table.
Then I issue the following query to retrieve data based on a specific criteria (Actually I have condensed the table and data because in Production, I have large number of rows; so for simplicity I am using 8 rows with one table)
criteria : I then want to retrieve the following:
for srs = 62, all values that is greater than 10,000
Answer: select decode(srs,62,ab.amt)temp1 ,decode(srs,65,ab.amt)temp2 from ct_temp ab
where decode(srs,62,ab.amt)>10000
Works like a charm and retrives the results.
Now comes the big issue
I want to retrieve the values for srs = 62 which is greater than 10,000 and at the same time I also want to retrieve the values for srs = 65 which is less than srs = 62.
Typically I wrote the query as:
select decode(srs,62,ab.amt)temp1 ,decode(srs,65,ab.amt)temp2 from ct_temp ab where decode(srs,62,ab.amt)>10000 and decode(srs,65,ab.amt)<decode(srs,62,ab.amt)
I should atleast get one row for srs = 65 which is id # 8 but it displays blank rows or "no rows returned".
I am actually preparing a ad-hoc report for the business analyst and stuck at this step due to which I am unable to proceed any further. I have used DECODE function because of the requirement specified by Business Analyst.
I have tried the following in the office:
using EXISTS operator = no luck using INLINE VIEW = no luck
I am unable to insert the rows into table after creation of trigger because Mutating error was getting.
SQL> desc test; Name Null? Type ----------------------------------------- -------- ---------------------------- EMP_NAME VARCHAR2(10) EMP_NO VARCHAR2(10)
SQL> select * from test;
EMP_NAME EMP_NO ---------- ---------- ORACLE PC729 JAVA PC047 C PC0729
SQL> create or replace trigger trig_test 2 after insert on test 3 for each row 4 declare 5 ename varchar2(10); 6 eno varchar2(10); 7 begin 8 select emp_name,emp_no into ename,eno from test 9 where emp_no ='1'; 10 insert into test2(emp_name,emp_no) values( 11 ename,eno); 12 end; 13 /
Trigger created.
SQL> insert into test values ('PRO','1'); insert into test values ('PRO','1') * ERROR at line 1: ORA-04091: table APPS.TEST is mutating, trigger/function may not see it ORA-06512: at "APPS.TRIG_TEST", line 5 ORA-04088: error during execution of trigger 'APPS.TRIG_TEST' SQL> spool off;
I have a table where there is no sequence maintained and there is no time stamp column to track the inserted record.How do I find the latest inserted record in the table.
I want to know the DDL statements executed on a table. Example:
i have a table test1 with structure as below: test1 (aa number, bb varchar2(10))
After some period of time, one DDL statement to alter the column is issued.
alter table test1 modify (bb varchar2(30));
so new table structure is: test1(aa number, bb varchar2(30))
Basically i would like to find these 2 DDL statements executed on this table - test1, so that i compare which column get modified and what modification done.
Is this information stored in any data dictionary table?
note - audit, flashback option not enabled in this database.
1) how to find a primary key of a table in oracle, for example if I want to drop a index in table , how do I find what is the primary key in a table so that I can issue that command.
P/s: I don't have OEM installed so I must use SQL command
Example of dropping the index ALTER TABLE table_name drop CONSTRAINT constraint_name;
I need the column name and table name which is having the particular value in the whole database.
E.g. :We have 'Scott' value in emp table emp_name column. and we have lot of tables. here we need emp_name and emp as emp table is having scott value in emP_name column.
