My need is to view sql statement and its explain plan generated by client (Business Objects). How do I perform it? Since it's developer's server I have sufficient rights, I just don't know particular table name.
View 2 RepliesAs we know that, MV is generating more redo logs during the FAST refresh. but i need more clarifications on that.
see the below examples:
exec dbms_mview.REFRESH ( LIST => 'mv_test', method=>'F');
PL/SQL procedure successfully completed.
select a.name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and a.name = 'redo size';
NAME VALUE
---------------------------------------------------------------- ----------
redo size 147144
see the redo size is 147144 bytes. Immediately, i refreshed in MV view. now there is no update or insert or delete stats happened in source tables. but i do see redo log generation is high for NO DATA refresh.
select a.name, b.value-147144
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and a.name = 'redo size';
NAME VALUE
---------------------------------------------------------------- ----------
redo size 42352
For no rows refresh, it takes 42352 bytes.. why oracle generated redo logs when there is no DML operations happened in source table.
I executed the following statements
SQL> CREATE OR REPLACE Function test
2 ( name_in IN varchar2 )
3 RETURN number
4 IS
5 cnumber number;
6
7 cursor c1 is
[code]....
how I can view the full statement of creating the function.
currently i m going through some dumps for my OCA-11g prep.I came across one sentence :A view cannot have an ORDER BY clause in the SELECT statement.well this statement is false and the explanation given was :
Query operations containing ORDER BY clause are also permitted, so long as the ORDER BY clause appears outside the parentheses.
The following is an example of what I mean: CREATE VIEW my_view AS (SELECT*FROM emp) ORDER BYempno.
but when i tried running the query like this :CREATE VIEW my_view AS SELECT*FROM emp ORDER BYempno ,it worked w/o giving parentheses.
Need a trigger in view with select statement that means
CREATE OR REPLACE VIEW TEST_VIEW AS SELECT * FROM TEST_TABLE;
CREATE OR REPLACE TRIGGER TEST_VIEW_TRG1
INSTEAD OF DELETE ON TEST_VIEW DECLARE
BEGIN
Dbms_Output.Put_Line('STATEMENT TRIGGER.');
END;
i wanted to use select statement instead of delete.How can i get that
I'm using Oracle 9i Enterprise edition, Is there a select statement to view transaction log for specific date?
View 1 Replies View RelatedWe have a materialized view showing red cross in oralce sql developer. But it functions well, we can select on it, no errors.
I searched on web, and some tells if underlying table changed, it may be broken. and need to refresh.
So I think I need to refresh it, using alter materialized view ... compile, but it told me the table or view doesnot exist, but it exists.
I have this following select statement
Select name
from names_list
where name like ('A%')
The output returns a list of names beginning with 'A'...What I want is for the output to repeat that list of names multiple times e.g. if the only two names returned are 'Andrews' and 'Apple', I want to have the output show
Andrews
Andrews
Andrews
Apple
Apple
Apple
I have problem viewing 2 procedures at the same time... e.g. i open procedure A, edit it, not close it. then i open procedure B from the left pane, and then i cannot see the procedure A... If i look at the File Menu on top left, it has the option of SAVE ALL, and not just SAVE.. that means both the procedures are open. but then why am i not able to see them?
View 6 Replies View RelatedIn toad 9.1.0.62 version, when i open a package in schema browser. It shows table or view doesn't exist.
But the package seems to be in valid state.
I also used other schema's to open that schema packages it opens with no problems.
I am executing the SQL statement in a unix korn shell script. and the output of this SQL is stored in a file.
`sqlplus -S $OraUserID@$OraInstance/$OraUserPW <<endofsql >result
set heading off;
SELECT * from emp;
exit;
endofsql`
cat result
this code working fine and the output i see in file is as below.
SQL*Plus: Release 10.2.0.3.0 - Production on Wed Jun 9 23:09:33 2010
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release <number> -
and Real Application Testing options
SQL> SQL>
30-APR-10
01-MAY-10
02-MAY-10
03-MAY-10
04-MAY-10
05-MAY-10
06-MAY-10
07-MAY-10
8 rows selected.
SQL> Disconnected from Oracle Database 11g Enterprise Edition Release and Real Application Testing options
How can to get only the SQL output in the file but not all the other oracle connecting messages.
I have a PLSQL script.
sqlplus
$DB_ACCT << EOSQL
set heading off
set termout off
set pagesize 0
[code]....
My output in FILE.txt looks like this
SQL>
SQL> Select col_with_a||col_with_b||col_with_c from
2 alphabet_table;
abc
[code]....
I tried several options by putting set echo off, etc.What do I include in the sql script to get the output as :
abc
abc
abc
without the SQL prompt and the statement, and the spool off command?
i'm working on sql developer in that i'm able to access tables if particular table name is given..
i don't understand is there any grant role problem .
1. In previous version of Toad (9.7.2) during executing a statement I was able to click on 'Explain plan current statement'. In this version of Toad (10.6.0. 42) is not a possible to do it.In the Toad options I've filled the sign: "use a separate connection when Toad itself is generating transactions"
2. During executing a statement I still see 'clock' cursor.Is it a possible to disable to see it?
I would like to store my sql query output into text file.Like for example:
select name from emp where emp_id=101;
Here output should be in text file as
swapna.
I dont want to use spool statement here,since If I use it,spool statement will also be printed in text file which is not my requirement.I just want to take only output.
CREATE OR REPLACE PROCEDURE test
IS
CURSOR cusers IS SELECT user_name, user_date FROM users;
uname users.user_name%TYPE;
udate users.user_date%TYPE;
BEGIN
OPEN cusers;
[code].....
When I try to execute this procedure I get following error:
ORA-00900: invalid SQL statement
Compilation of procedure is successful.
SQL code for creating the USERS table is here:
CREATE TABLE "USERS"
("USER_ID" NUMBER(10,0) NOT NULL ENABLE,
"USER_NAME" VARCHAR2(50) NOT NULL ENABLE,
"USER_EMAIL" VARCHAR2(50) NOT NULL ENABLE,
"USER_PASS" VARCHAR2(50) NOT NULL ENABLE,
"USER_DATE" DATE NOT NULL ENABLE,
CONSTRAINT "USERS_PK" PRIMARY KEY ("USER_ID") ENABLE
)
I executed the following PL/SQL block in SqlDeveloper :
VARIABLE max_dept_no NUMBER
DECLARE
v_dept_name VARCHAR2(30) := '&p_dept_name';
v_max NUMBER(4,0);
BEGIN
SELECT MAX(department_id) INTO v_max FROM departments;
:max_dept_no := v_max + 20;
INSERT INTO departments VALUES (:max_dept_no,v_dept_name, NULL,NULL) ;
END;
/
And it gave the error : Quote:Error report:
ORA-01400: cannot insert NULL into ("HR"."DEPARTMENTS"."DEPARTMENT_ID")
ORA-06512: at line 7
01400. 00000 - "cannot insert NULL into (%s)"
The same code when executed in iSqlPlus gave no error.
I was wondering with dba_constraints and i found unexpected result shown as below
select constraints_name, constraint_type from dba_constraints
constraint_name constraint_type
SYS_C00141 c
SYS_C00142 c
SYS_C00143 c
SYS_C00144 c
SYS_C00145 c
SYS_C00146 c
SYS_C00147 c
SYS_C00148 c
SYS_C00149 c
i jst want to know that what is this system generated constraints ?
The trace directory is full of trc files with the text below:
--------Dumping Sorted Master Trigger List --------
Trigger Owner : INPUT
Trigger Name : CUS_TST
--------Dumping Trigger Sublists --------
There is like a file generated every minute, and i cant stop it from happening.I have tried setting the trace_enabled parameter to FALSE but no success.
i just noticed on my 11g database - no AWR snapshots are generated.
View 4 Replies View RelatedForms 10g is insatalled recently on my machine. New form is developed,when we run the form it generates FMX file for the same but output is not dispalyed. The output window get closed automatically.
View 3 Replies View Relatedin eache record we are receiving information from differente city, we pretend to get an output where we have a row for each city (delimited by comma) that we have in column CITY
Input data
SELECT '1001001' as CLIENT_ID, 'LONDON, PARIS' as CITY, TO_DATE('20101105', 'YYYYMMDD') as DT_REG FROM DUAL UNION
SELECT '1022201' as CLIENT_ID, 'MADRID, OSLO' as CITY, TO_DATE('20101105', 'YYYYMMDD') as DT_REG FROM DUAL UNION
SELECT '1033001' as CLIENT_ID, 'PARIS' as CITY, TO_DATE('20101105', 'YYYYMMDD') as DT_REG FROM DUAL UNION
SELECT '1033004' as CLIENT_ID, 'MADRID, OSLO, PARIS' as CITY, TO_DATE('20101105', 'YYYYMMDD') as DT_REG FROM DUAL
Output expected
CLIENT_IDDT_REGCITY
100100105/11/2010 LONDON
100100105/11/2010 PARIS
102220105/11/2010 MADRID
[code]...
How to find out how much undo will be generated by a dml / ddl statement in Oracle 9i? With Oracle 10g we can use the famous mystat.sql and mystat2.sql with argument as 'undo change vector size'.
However with Oracle 9i there is no statname as 'undo change vector size'
Is there any way to get the amount of redo generated in last 2 hour. which has below chareteistic
1. redo generated by currently connected session from last 2 hour.
2. redo generated by session disconnected during last 2 hour.
total_redo = disconnected sessoin during last 2 hour + connected session generating redo during last 2 hour.
in our database 10.0.2.4 with RAC archive log generated each 4 min , did this increase the performance of database and how i can fix it
View 14 Replies View RelatedI am facing issue with my sequence number about sequence number generated..I want it to follow the sequence all the time, i.e. it should take next incremental number to last generated seauence, but it generates random one all the time, i.e
s.nextval() = 4
s.nextval() = 5
s.nextval() = 10
s.nextval() = 543
s.nextval() = 544
My sequence defination is as below
CREATE SEQUENCE s
START WITH 1
MAXVALUE 999999999999999999999999999
MINVALUE 1
NOCYCLE
CACHE 20
i have faced one problem with reports. i had created 4 reports. recently we are designed new application using oracle forms6i. we are created more than 10 forms and added to application they were working fine. but when i was added these reports they are not generated. but in server report are running. i mean the report genereted in server but not in local system.
View 4 Replies View RelatedLets say you're making upa school database and you awnted to give the students each a unique student number generated by thesystem.
View 2 Replies View RelatedRMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup plus archivelog command at 09/06/2010 12:36:47
ORA-19715: invalid format F for generated name
ORA-27302: failure occurred at: slgpn
I have found there is a wearied of email generated from my database. I have the data base server located in US (estern time) and the sysdate in the database is AUSTRALIA (AEST). But i am working from India (IST).I have one procedure, which will send email to some recipients once the job is done. It is using UTL_SMTP for sending the email from database.
Now problem is, the email i got from this procedure has a different timestamp which is NOT matching to any of the times:
Time in Mail : 2-Nov-2012 9.56 PM
IST Time : 2-Nov-2012 10.56 AM
Easter Time : 2-Nov-2012 01.30 AM
AEST Time : 2-Nov-2012 4.30 PM