m spooling the results of some performance tests to a text file, the test script im using calls various sql statements in other scripts and spools to the results, stats and time to the same file...Howver I dont want to spool all the results... i only want the stats and time.. how can i do this?
my script looks like:
=====================================================
SET TERMOUT OFF
SET AUTOTRACE ON STAT
SPOOL f:/testresults.txt
I need to do the following via the sql plus command prompt :
1. Execute a large ".pls" script file which does various inserts and updates to a table. 2. Spool the output of "select * from updatedtable;" to a text file to see the changes made. 3. Rollback all the updates and inserts in the script.
I want to ask if we could create a sub folder using the SPOOL command. I tried the following but it gave "Cannot create Spool file" error.
SPOOL C:TESTABCABC.sql
where TEST is already present in the C drive whereas I want SPOOL command to create a sub-directory named 'ABC' and then create the spool file 'ABC.sql' isnside it.
To add application name in a spool file, I am using the below way.
sqlplus username/pwd@tns @xyz.sql APP0115
SQL> define appname="'&1'" Enter value for 1: APP0115 SQL> prompt &appname 'APP0115' SQL> spool &appname._html_jobs.csv; SP2-0768: Illegal SPOOL command Usage: SPOOL { <file> | OFF | OUT } where <file> is file_name[.ext] [CRE[ATE]|REP[LACE]|APP[END]]
But I am getting the above error in the spool file clause because of single quote printing infront of the spool file. But the method of defining a character is "'&1'". So I cannot avoid this single quote in the define clause.
'APP0115'
print the appname like APP0115 instead 'APP0115'.then only I can use this in the spool file clause?
I have a problem spooling flat files in utf 8. I have 3 different sql that needs to be spooled regulary in utf8 format.the first two containing special chars is spooled without any problem, but the third that is containg only numbers and pipe-s is spooled in ansi format.
i have set nls_lang=.utf8 in my shell script, but that only worked at the first two sql the last doesnt work.
I have also tried to convert file using iconv command, but the output is still in ansi (only works if i put a special char in the ansi file then it converts to utf8 without problem) if i open the file in notepad and save as utf8 then it works, but i cannnot reach the same from unix script.
I am looking forward for getting the output of audit records in a non-editable format as in pdf or something of that sort. Is it possible from Oracle 10g Rel-2?Can i spool output as PDF format?
I have a master table A that has 10+ columns along with data and a configuration table B that has only 2 columns i.e table name and column name. For ex:-
Table_Name Column_Name --------- ---------- A C1 A C2 A C3
Now I need to write a sql script that should take the table A Columns defined in Table B and then based on the columns it should spool the columns output from Table A.
For above ex:- I need to spool the C1,C2,C3 columns data from Table A in to some Unix path using SQL SCRIPT.
how to create a file in a folder based on todays date. i need to know how to define a variable in sqlplus and assign a value to it.Here is the code below. The code gets executed without creating a spool file.
DEFINE _DATE = replace('C:\_sysdate_EU001.csv', '_sysdate_', TO_CHAR(SYSDATE, 'DD-MON-YYYY')) spool _DATE set serveroutput on size 100000 select * from dual; spool off
I use sqplus in oracle to output the command output to text file .
I use below set environment varialble.
SQL> set echo off; SQL> set linesize 3999; SQL> set feedback off; SQL> set feedback off; SQL> set termout off; SQL> set pagesize 0; SQL> spool mapping.txt select C_SIM_MSISDN,C_SIM_IMSI from RCA_SMART_CARD order by C_SIM_MSISDN;
In ouput file , it look like
SQL> select C_SIM_MSISDN,C_SIM_IMSI from RCA_SMART_CARD order by C_SIM_MSISDN; 060010007 10007 : : : SQL> spool off;
any setting or command that allow me to remove the first sql command line" SQL>select XXXX" and the last command "SQL>spool off" after start up the "spool mapping.txt"
My SQLPULS script below generates 2 million records and it works fine without any errors, which I run from my batch file. (sqlplus -S %CONNECT% @"SCRIPTSmysql.sql"
The script itself runs 2 hours and exits via sqlplus.exe properly without any errors based on errorlevel from my batch and proper generated data. However, I have noticed that spool output file stops generating any records after 1 hour. It appears that client sqlplus.exe is sleeping or verifying something after spooling completed exactly the same period of time that takes to generate my out file. Is there a way to configure my script to avoid this 1 hour sleep/verification process?
whenever sqlerror exit sql.sqlcode
alter session set current_schema=blah; / set linesize 1000 set feedback off
I would like to spool a clob column to a flag file, however some of the clob are greater than 32k, and I have to have the same record in a single line in the file. Is there any way to achieve this through spooling?
set heading off set feedback off set term off set long 1000000 set longchunksize 500000 set line 32767 set trimspool on set pagesize 50000 spool file.txt @--this is my select statement. spool off exit
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.
I would like to use the Spool command to export data for other purposes within the application. We would like to use tab delimiter to seperate the fields but the client wants to know if the text datatype fields can be wrapped in double quotes along with the tab delimiter..
SQL>create table test (id number(2), first_name varchar2(15), last_name varchar2(15),var_no number(4), type varchar2(1),type_no number(12));
Table created.
SQL> insert into test values(1,'mary','ross',132,'S',12);
1 row created.
SQL> insert into test values(3,'Sue','Bill',432,'S',12);
1 row created.
I tried the below spool command to use tab delimited for all the fields but not sure how to wrap double quotes for only the text fields and also would to have the column names in the 1st row but don't seem to get the full column name in the csv file.
set echo off set feedback off set linesize 1000 set pagesize 4000 set trim on set headsep off set colsep '' (used tab between the quote)
spool test.csv select id,first_name,last_name,var_no,type,type_no from test; spool off
In my database auto optimizer stats collection job scheduled. It is successfully running,i am confirming this by viewing DBA_AUTOTASK_JOB_HISTORY. My doubt is whether stats gather job collect statistics when 10%(default value) of data modified in a hole table or table partitions.
Below is the output for user_tab_modification.I am able to see two entry for same table.
SQL> select TABLE_NAME,PARTITION_NAME,INSERTS,UPDATES,DELETES,TIMESTAMP,TRUNCATED from USER_tab_modifications where table_name='DLM_PERFORMANCE_DATA';
TABLE_NAME PARTITION_NAME INSERTS UPDATES DELETES TIMESTAMP TRU ------------------------------ ------------------------------ ---------- ---------- ---------- ----------- --- DLM_PERFORMANCE_DATA 169812174 0 0 20-SEP-2012 NO DLM_PERFORMANCE_DATA SYS_P2663580 4946409 0 0 20-SEP-2012 NO
I have a little problem when debugging in pl/sql developer. All date variables are always displayed DD-MON-RR. I already changed the values for Date and Time to YYYY-MM-DD HH24:MI:SS in
Tools => Preferences => User Interface / NLS Options.
But when I start debugging and watch a variable I still get eg. 17-OCT-12 without the time. I already restarted pl/sql developer. No change.
I have a stored procedure running in which there is a cursor which fetches around 1500000 records and then query another table using the fetched record values.
I cannot modify the procedure as its on production. I want to know which cursor record is currently being processed by the procedure, and how many are still remaining ? How to check the cursor stats at runtime. I want to check up to which record the cursor has been fetched and how many are still remaining. I have cursor name. Is there some dynamic view to check cursor stats at runtime ?
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net message to client 1 0.00 0.00 db file sequential read 85704 0.31 460.55 latch free 1 0.00 0.00 SQL*Net message from client 1 14.98 14.98
[code]...
Why the elasped time changed when data and plan hasn't changed at all? Also why the plan has different stats for round 1 and 2 on db1 and db2?
I ran it 2 times each round each database so hard parsing shall not be issue.Also why the number of rows accessed are different in db1,db2 and db3,db4 especially for step1 when count of crt.qtn_cun_id is similar?
In fact when the query was taking long I was the only user on the system Also I used hard coded value (no bind variables at all)
I checked num_rows, distinct keys as well which are quite similar across all 4 databases Also no stats where gather during the query execution
I have used the above to get a copy of schema stats and gather new stats for specific tables into a STATS TABLE in my personal schema. What I want to do now is use this stats table to generate plans for queries where I believe stats are off. Is it even possible? To be clear, I do not want to import stats because this replaces the stats currently there. I just want to point the CBO to my stats table for generating plans.
there was a session parameter I could set to tell oracle to use my stats table when generating plans, or an explain plan clause I could use or a DBMS_XPLAN paramter I could provide that would tell these tools to use my stats table when generating a plan, or even some way to tell autotrace. But I have found none of this.
During STATS gather running for the table, unknowingly i deleted the old stats using EXEC DBMS_STATS.DELETE_TABLE_STATS. I would like to know will it affect the stats gather job currently running for the table and whether my stats will be gathered successfully.
1) A reverse engineering tool that I can point to an Oracle schema and get a "baseline" script to re-create that schema from scratch, with decently formatted DDL files (1 per object) neatly organized in a directory tree (by object type) and called in the correct order. Icing on the cake would be an option to pass the tool a list of tables containing static data and get DMLs to populate (insert) those tables as part of the script.
2) a diff tool that I can point to a pair of Oracle instances (source and target) containing a given schema and get a "delta" script to alter the target schema so that it becomes identical to the source schema. If data loss occurs on the target instance (i.e. drop a column) I would like to find a warning comment inserted in the script (e.g. "-- Attention: data migration DML needed here?"). Icing on the cake would be an option to pass the tool a list of tables containing static data and get DMLs to update (delete, update and insert) the data in the target tables to become identical to the contents in the source tables *without* deleting and re-inserting all rows (or dropping, recreating and repopulating the table).
3) I would like the above two tools (that, as you will have recognized, are basic to putting your database design under version control) to be open-source, with a command-line interface and a vibrant community backing them.
The problem is that this connection errors when I try to connect using my SQuirreL client. My developers want to connecting using the SID or the SERVICE_NAME and by using the "New Method" syntax, not the "Old Method" syntax. According to the documentation the "New Method" syntax works with the SERVICE_NAME or the SID. The "Old Method" only works with the SID.
# Host name is vmwwin7634.na.SAS.com # SID is sting2 # SERVICE_NAME is sting2.na.sas.com
PROBLEM using SID errors ==> jdbc:oracle:thin:@vmwwin7634.na.SAS.com:1521/sting2 THIS WORKS using SERVICE_NAME ==> jdbc:oracle:thin:@vmwwin7634.na.SAS.com:1521/sting2.na.sas.com
I am using SQuirreL Client version 3.4.0
# Here is the tnsnames.ora entry on the server. STING2 = (DESCRIPTION =
[code]...
# In this documentation it says, "On new syntax SERVICE may be a oracle service name or a SID."You can find this on the orafax wiki under JDBC#Thin_driver.
# I was able to connect with the "old method" using the SID only, SERVICE_NAME errors jdbc:oracle:thin:@vmwwin7634.na.SAS.com:1521:sting2.na.sas.com (SQuirreL client error, "Unexpected Error occurred attempting to open an SQL connection.") jdbc:oracle:thin:@vmwwin7634.na.SAS.com:1521:sting2 (this works)
# "New Method" jdbc:oracle:thin:@vmwwin7634.na.SAS.com:1521/sting2.na.sas.com (this works) jdbc:oracle:thin:@vmwwin7634.na.SAS.com:1521/sting2 (this errors) (SQuirreL client error, "Unexpected Error occurred attempting to open an SQL connection.")
I have new virtual UNIX machine and I installed oracle client on /usr/lib/oracle. Also I have a oracle database and I am able to connect to this database from my desktop sql developer.
So now I am trying to connect from new UNIX machine. Where I created tnsnames.ora file under /usr/lib/oracle/network/admin and before connecting did export the following
ORA-12545: Connect failed because target host or object does not exist. Not sure what I missed here. using same tns file I am able to connect from sql developer on windows.
I need to install Oracle client to get latest sql plus working on client pc as the problem is, i am not able to find out where i can download this , i just need the sql plus as i am not able use many commands because when i installed oracle forms 6i client tool , sql plus 8.0 got installed.
I want to use BR*Tools to administer an Oracle database. I have installed BrGUI local, set the JAVA Home variable and edited the brgui.properties file accordingly.I am able to login, but the menues are mising. The login screen is incomplete.
