how to show sql statements in pl/sql

donnie_xu 2003-08-21 10:39:54

hi,
I am debuging a shell in unix, which contains pl/sql used to purge some data in oracle db.
I want show a sql statemnet (a select statement) in it when executing it?
How can i?
Thanks in advance

...全文

28 3 打赏收藏转发到动态举报

写回复

用AI写文章

3 条回复

切换为时间正序

请发表友善的回复…

发表回复

LGQDUCKY 2003-08-21

打赏
举报

或者用PLSQL Developer工具
可以看到当前执行的SQL语句。。。

LGQDUCKY 2003-08-21

打赏
举报

用DBMS_OUTPUT.PUT_LINE 例如：

DBMS_OUTPUT.PUT_LINE('PK_TAB_NAME0='||pk_table_name||',ret='||ret);

jametong 2003-08-21

打赏
举报

in sqlplus 执行命令set termout on echo on;

32位版本的 PLSQL 正式版。安装中文包时请注意安装路径是否为PLSQL程序的路径。安装请查看说明。 PL/SQL Developer 13.0 - New Features Load Tester With the new Load Tester tool you can perform stress tests and concurrency tests for your Oracle data and programs. This allows you to find performance issues and locking issues with multi-user scenarios, and to test the results of optimizations. You can define steps, sub steps and loops to perform specific SQL statements or PL/SQL blocks to mimic the behavior of users of a specific application. For each step you can define execution properties such as the SQL text, repeats, and delays, as well as variables to generate random data or to get data from previous steps: You can specify for how many concurrent threads these steps will be executed and which Oracle accounts should be used. The performance results can be viewed in real-time as text, bar chart, or line chart: Editor Enhancements After making a column selection with Alt-select, you can now type new text for the column. The newly typed text will be repeated on each line of the column selection. You can now increase or decrease the editor font size by pressing Ctrl +/- on the numeric keypad. The "Go to line" function now has an option to move relative from the start of the text, from the cursor position, or from SQL start position: The editor now highlights corresponding value expressions when the cursor is in an insert column list. Previously this only worked when the cursor was in the insert values list. The editor now highlights all table.column occurrences in a select statement when clicking on the table name in the statement: Editor now highlights all field occurrences in a select statement when clicking on a field in the statement: Function keys have been added for fold and unfold operations. A new "Duplicate line" function key has been added. A new "Editor: First Character on Line" function key has been added. A new "Delete unindents" preference has been added. When enabled, pressing the Delete key will delete all tabs and spaces to the right of the cursor position. Code Assistant Enhancements A new "Filter method" option has been added. It can be set to "Contains" to display all items that contain the filter text, or it can be set to "Starts with" to display all items that start with the filter text (the previous behavior): The Code Assistant will now show a directory listing after typing @ in the Command Window: Find & Replace Enhancements Support had been added for special characters (e.g. \t) and subexpressions (e.g. $1) in the replacement text when using regular expressions. There is now a separate recall history for regular expressions. There is now a syntax selection list for the find text and replacement text when using regular expressions: SQL Window Enhancements You can now enter a column filter for a result set to limit the visible columns based on the column name and data type, or include only mandatory columns or include only primary/unique/foreign key columns: You can manually enter the filter [removed]such as "owner, name" above, to show all columns with these 2 words in the name), or you can build a column filter by pressing the [...] button: A "Compare to..." popup menu item has been added to compare the window contents to other file contents. You can now update invisible columns in a result set. The "Save Windows state" preference now also works for SQL Windows. The vertical splitter position is now preserved if manually moved. The EXCEL=filename and CSV=filename comment directives now also support

Usage: ora [-u user] [-i instance#] [] General -u user/pass use USER/PASS to log in -i instance# append # to ORACLE_SID -sid set ORACLE_SID to sid -top # limit some large queries to on # rows - repeat Repeat an coomand time. Sleep between two calls Command are: - execute: cursors currently being executed - longops: run progression monitor - sessions: currently open sessions - stack get process stack using oradebug - cursors [all] : [all] parsed cursors - sharing : print why cursors are not shared - events [px]: events that someone is waiting for - events [read_by_other_session] events that someone is read by other session - ash [duration] [-f ] active session history for specified period e.g. 'ash 30' to display from [now - 30min] to [now] e.g. 'ash 30 10 -f foo.txt' to display a 10 minutes period from [now - 30min] and store the result in file foo.txt - ash_wait_graph [duration] [-f ] PQ event wait graph using ASH data Arguments are the same as for ash except that the output must be shown with the mxgraph tool - ash_sql Show all ash rows group by sampli_time and event for the specified sql_id - [-u ] degree degree of objects for a given user - [-u ] colstats stats for each table, column - [-u ] tabstats stats for each table - params []: view all parameters, even hidden ones - snap: view all snapshots status - bc: view contents of buffer cache - temp: view used space in temp tbs - asm: Show asm space/free space - space []: view used/free space in a given tbs - binds : display bind capture information for specified cursor - fulltext : display the entire SQL text of the specified statement - last_sql_hash []: hash value of the last styatement executed by the specified sid. If no sid speficied, return the last hash_value of user sessions - openv []: display optimizer env parameters for specified cursor - plan []: get explain plan of a particular cursor - pxplan : get explain plan of a particular cursor and all connected cursor slave SQL - wplan []: get explain plan with work area information - pxwplan : get explain plan with work area information of a particular cursor and all connected cursor slave SQL - eplan []: get explain plan with execution statistics - pxeplan : get explain plan with execution statistics of a particular cursor and all connected cursor slave SQL - gplan : get graphical explain plan of a particular cursor using dot specification - webplan get graphical explain plan of a particular [/] cursor using gdl specification []: optional: child_number, default is zero. optional: decorate to print further node information. default is 0, 1 => print further node information such as cost, filter_predicates etc. 2 => in addition to the above, print row vector information sample usage: # ora webplan 4019453623 print more information (decorate 1) # ora webplan 4019453623/1 1 more information, overload! (decorate 2) # ora webplan 4019453623/1 2 using sql_id along with child number instead of hash value # ora webplan aca4xvmz0rzup/3 1 - hash_to_sqlid : get the sql_id of the cursor given its hash value - sqlid_to_hash : get the hash value of the cursor given its (unquoted) sql_id - exptbs: generate export tablespace script - imptbs: generate import tablespace script - smm [limited]: SQL memory manager stats for active workareas - onepass: Run an ora wplan on all one-pass cursors - mpass: Run an ora wplan on all multi-pass cursors - pga: tell how much pga memory is used - pga_detail | -mem : Gives details on how PGA memory is consumed by a process (given its os PID) or by the set of precesses consuming more than MB of PGA memory (-mem option) - pgasnap [] Snapshot the pga advice stats - pgaadv [-s []] [-o graphfile] [-m min_size]: generate a graph from v and display it or store it in a file if the -o option is used. -s [] to diff with a previous snapshot (see pgasnap cmd) -o [graphfile] to store the result in a file instead of displaying it -m [min_size] only consider workareas with a minimum size - pgaadvhist [-f []] display the advice history for all factors or for factor between f_min and f_max - sga: tell how much sga memory is used - sga_stats: tell how sga is dynamically used - sort_usage: tell how temp tablespace is used in detail - sgasnap [] Snapshot the sga advice stats - sgaadv [-s []] [-o graphfile] generate a graph from v and v and store it in a file if the -o option is used. -s [] to diff with a previous snapshot (see sgasnap cmd) -o [graphfile] to store the result in a file instead of displaying it - process []: display process info with pga memory - version: display Oracle version number - cur_mem [ ] display the memory used for a given or all cursors - shared_mem [ ] detailed dump of cursor shared mem allocations - runtime_mem [ ] detailed dump of cursor runtime memory allocations - all_mem [ ] do all of the memory dumps - pstack |all [] run pstack on specified process (or all if 'all' specified) and store files in specified dir ( when not specified) - idxdesc [username] list all indexes for a given user or for a given user and table - segsize [username] list size of all objects(segments) for given user for a given user and object - tempu list temporary ts usage of all users or for a given user - sqlstats [ ] list sql execution stats (like buffer_gets, phy. reads etc) for a given sql_id/hash_value of statement - optstats [username] list optimizer stats for all tables stored in dictionary for a given user or for a given user and table - userVs list all user Views (user_tables, user_indexes etc) - fixedVs list all V$ Views - fixedXs list all X$ Views - px_processes list all px processes (QC and slaves) - cursor_summary summarize stats about (un)pinned cursors - rowcache summarizes row cache statistics - monitor_list lists all the statements that have been monitored - monitor [xml]: wraps dbms_sqltune.report_sql_monitor(). Directly passe the arguments to the PL/SQL procedure. Args are: sql_id, session_id, session_serial, sql_exec_start, sql_exec_id, inst_id, instance_id_filter, parallel_filter, report_level, type. Examples: - monitor xml shows XML report - monitor show last monitored stmt - monitor sql_id=>'8vz99cy9bydv8', session_id=>105 will show monitor info for sql_id 8vz99cy9bydv8 and session_id 105 Use simply ora monitor 8vz99cy9bydv8 to display monitoring information for sql_id 8vz99cy9bydv8. Syntax for parallel filters is: [qc][servers([,] [,] )] Use /*+ monitor */ to force monitoring. - monitor_old [ash_all] [] [qc| [ []]] Old version of SQL monitoring, use a SQL query versus the report_sql_monitor() package. Display monitoring info for the LAST execution of the specified cursor. Cursor response time needs to be at least 5s for monitoring to start (use the monitor hint to force monitoring). Without any parameter, will display monitoring info for the last cursor that was monitored - ash_all will aggregate ash data over all executions of the cursor (useful for short queries that are executed many times). If parallel: - qc to see only data for qc - slave_grp# to see only data for one parallelizer - slave_grp# + slave_set# to see only data for one slave set of one parallelizer, - slave_grp# + slave_set# + slave# to see data only for the specified slave - sql_task [progress | interrupt | history | report ] progress: progress monitoring for executing sql tasks interrupt: interrupt an executing sql task history: print a history of last n executions report: get a sql tune report - sql_use_temp_segment Find Who And What SQL Is Using Temp Segments. - sh Run a shell command. E.g. ora repeat 5 10 sh 'ps -edf | grep DESC' - awr_dbid Show AWR dbid - awr_dbtime [dbid] Show AWR dbtime - awr_dbtime [dbid] [inst] Show AWR dbtime - awr_dbtime_order [dbid] Show AWR dbtime order by desc - awr_sql_elaps_time [dbid] Show AWR SQL elapsed time - awr_sql_elaps_time [dbid] [inst] Show AWR SQL elapsed time - awr_sql_elaps_time_order [dbid] Show AWR SQL elapsed time order by desc - awr_logical_reads_order [dbid] - awr_logical_reads [dbid] Show AWR logical reads M Show AWR logical reads M order by desc - awr_physical_reads [dbid] Show AWR physical reads M - awr_physical_reads_order [dbid] Show AWR physical reads M order by desc - awr_db_cpu_per [dbid] [inst] Show AWR db_cpu_time cpu percent - awr_user_cpu_per [dbid] [inst] Show AWR oracle user_time cpu percent including backgroud process - awr_sql sql_id [dbid] Show AWR sql_id executions, per elapsed time. - awr_fulltext sql_id [dbid] Show AWR sql fulltext - awr_plan sql_id plan_hash [dbid] Show AWR sql plan, if plan_hash is null, show all plans. - awr_binds sql_id end_snap_id [dbid] Show AWR bind values in end_snap_id. - tab_frag owner [frag_percent] Show table fragment. - index_frag owner [frag_percent] Show index fragment. - rman_fullrestore_scripts dest_dbfile_dir Generate rman full database restore scripts - top_buffers_gets Top 10 by buffer gets > 10000 - top_physical_reads Top 10 by Physical Reads (disk_reads > 1000) - top_executions Top 10 by Executions > 100 - top_parse_calls Top 10 by Parse Calls > 1000 - top_sharable_memory Top 10 by Sharable Memory > 1M - top_version_count Top 10 by Version Count > 20 - top_cpu_usage Top 10 by CPU usage (cpu_time) - top_running_time Top 10 by Running Time (first_load_time desc) - create_tbs path size Create test database's tablespace script - create_tbs path size [dbid]Create dbid's test database's tablespace script - hold_txlock Show sessions holding a TX lock - wait_txlock Show sessions waiting a TX lock - rowid Display rowid's file_id, file_name, block info, object info, extent_id Memory: The detailed memory dumps need to have events set to work. The events bellow can be added to the init.ora file event="10277 trace name context forever, level 10" # mutable mem event="10235 trace name context forever, level 4" # shared mem NOTE ==== - Set environment variable ORA_USE_HASH to 1 to get SQL hash values instead of SQL ids - Set environment variable DBUSER to change default connect string which is "/ as sysdba" - Set environment variable ORA_TMP to the default temp directory (default if /tmp when not set)

SQL is not very flexible and it cannot be made to react differently to differing sutuations easily. In SQL queries we normally tell database what we want but not tell it how to do it. SQL : give com...

Developing PL/SQL Web Applications This chapter explains how to develop PL/SQL web applications, which let you make your database available on the intranet. Topics: Overview of PL/SQL Web Applica...

How to Identifying High-Load SQL Statements

Oracle

17,082

社区成员

55,236

社区内容

发帖

与我相关

我的任务

社区管理员

加入社区

近7日
近30日
至今

加载中

查看更多榜单

社区公告

暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章