tag:blogger.com,1999:blog-46125588510918927002024-08-30T03:21:38.315-07:00Database StuffRepository of Database Related Information, Assets and Knowledge Base covering Oracle, SQLs, Scripts, Performance Tuning etcUnknownnoreply@blogger.comBlogger19125tag:blogger.com,1999:blog-4612558851091892700.post-15767944092950624412012-09-13T00:17:00.000-07:002012-09-13T00:17:58.703-07:00Oracle: Script to check for table locksScript to check for Oracle table locks: Display details on which table is being locked by which process/user select s1.username || '@' || s1.machine   || ' ( SID=' || s1.sid || ' )  is blocking '   || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status   from v$lock l1, v$session s1, v$lock l2, v$session s2   where s1.sid=l1.sid and s2.Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-4612558851091892700.post-1610370777205068352012-09-12T22:13:00.000-07:002012-09-12T22:16:20.410-07:00Oracle: Script to display current slow job detailsOracle Script to display slow jobs details column e_dttm format a15 column usr format a10 column hp format 9999999999 select TO_CHAR(m. end_time, ' DD-MON-YYYY HH24:MI: SS' ) e_dttm,      -- Interval End Time m.intsize_csec/100 ints,                       -- Interval size in sec s.username usr, m.session_id sid, Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-4612558851091892700.post-12503082131921710282012-09-12T21:55:00.001-07:002012-09-12T22:18:05.925-07:00Oracle: Script to display session detailsScript to display Oracle session details: column value format a40 column description format a100 SELECT 'AUDITED_CURSORID' AS Parameter, SYS_CONTEXT('USERENV','AUDITED_CURSORID') AS Value, 'Returns the cursor ID of the SQL that triggered the audit' AS Description FROM Dual UNION ALL SELECT 'AUTHENTICATION_DATA' AS Parameter, SYS_CONTEXT('USERENV','AUTHENTICATION_DATA') AS Value, 'AuthenticationUnknownnoreply@blogger.com0tag:blogger.com,1999:blog-4612558851091892700.post-84092146642174421932010-09-13T23:10:00.000-07:002010-11-15T15:04:20.823-08:00Oracle Hints (10G) Hints for Optimization Approaches and Goals ALL_ROWSThe ALL_ROWS hint explicitly chooses the cost-based approach to optimize a statement block with a goal of best throughput (that is, minimum total resource consumption). FIRST_ROWSThe FIRST_ROWS hint explicitly chooses the cost-based approach to optimize a statement block with a goal of best response time (minimum resource usage to return firstUnknownnoreply@blogger.com0tag:blogger.com,1999:blog-4612558851091892700.post-43661143314786872572010-05-17T21:38:00.000-07:002010-05-17T21:41:38.674-07:00Oracle: SqlPlus Useful Set CommandUseful SET commands for output formatting and SPOOL: SET TERM OFF -- TERM = ON will display on terminal screen (OFF = show in LOG only) SET ECHO ON -- ECHO = ON will Display the command on screen (+ spool) -- ECHO = OFF will Display the command on screen but not in spool files. -- Interactive commands are always echoed to screen/spool. SET TRIMOUT ON -- TRIMOUT = ON will Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-4612558851091892700.post-51763555865872861942010-05-12T18:45:00.000-07:002010-05-12T18:45:23.267-07:00Oracle: Monitoring progress of import jobsCode to monitor speed of import jobs: select    substr(sql_text,instr(sql_text,'INTO "'),30) table_name,    rows_processed,    round((sysdate-     to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60,1) minutes,    trunc(rows_processed/     ((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60)) rows_per_minuteUnknownnoreply@blogger.com0tag:blogger.com,1999:blog-4612558851091892700.post-75541012789344487332009-10-28T15:48:00.000-07:002009-10-28T15:48:21.541-07:00Oracle: Initialization Parameters (10g)Initialization parameters for Oracle 10g can be obtained from v$parameter; sample script to get values: set pagesize 1000 set linesize 150 column name format a40 column value format a40 column description format a40 select name, value, description from v$parameter order by name; useful links: http://www.dba-oracle.com/t_oracle10g_initialization_parms.htmUnknownnoreply@blogger.com0tag:blogger.com,1999:blog-4612558851091892700.post-87270589354796367122009-10-01T23:56:00.000-07:002009-10-01T23:56:33.037-07:00Oracle: Commonly Used System Tables Below is an alphabetical listing of commonly used Oracle system tables. System TableDescription ALL_ARGUMENTSArguments in object accessible to the user ALL_CATALOGAll tables, views, synonyms, sequences accessible to the user ALL_COL_COMMENTSComments on columns of accessible tables and views ALL_CONSTRAINTSConstraint definitions on accessible tables ALL_CONS_COLUMNSInformation about accessible Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-4612558851091892700.post-60259290724249055842009-09-28T00:20:00.000-07:002009-09-28T00:24:53.541-07:00Oracle: List freespace by TablespacesUse the following script to get sizing (free,used etc) for tablespaces: ------------------------------------------------------------------------------ -- This SQL Plus script lists freespace by tablespace ------------------------------------------------------------------------------ set linesize    200 set pages       100 set verify off set feedback off col stat &Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-4612558851091892700.post-8831778211219573692009-09-27T18:23:00.000-07:002012-09-13T00:25:46.434-07:00Oracle: SQL for displaying current processes, useridsuse the followings sql for displaying current running processes, userids: SET TERMOUT    ON SET HEADING    ON SET PAGESIZE   40 SET LINESIZE   180 SET NEWPAGE    0 SET VERIFY     OFF SET ECHO       OFF SET UNDERLINE  = SET FEEDBACK   ON SET LONG       1000 SET EMBED      ON COLUMN Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-4612558851091892700.post-64287053587624575542009-09-24T00:47:00.000-07:002009-09-24T00:47:19.582-07:00Oracle: Rename TableOracle rename table syntaxOracle provides a rename table syntax as follows: alter table    table_name rename to    new_table_name; For example, we could rename the customer table to old_customer with this syntax: alter table    customer rename to    old_customer; When you rename an Oracle table you must be aware that Oracle does not update Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-4612558851091892700.post-77098314680625519732009-09-23T22:28:00.000-07:002009-09-23T22:28:37.351-07:00Oracle: Column Statistics SQLGet column statistics via the following sql. change xxx to table name of interest. SELECT COLUMN_NAME, NUM_DISTINCT, NUM_NULLS, NUM_BUCKETS, DENSITY FROM DBA_TAB_COL_STATISTICS WHERE TABLE_NAME ='XXXX' ORDER BY COLUMN_NAME; Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-4612558851091892700.post-89164542852091721182009-09-23T21:42:00.000-07:002009-09-23T21:42:17.523-07:00Oracle: Optimizer settingsOracle OPTIMIZER settings alter session set "_optimizer_cost_model" =choose;  alter session set "_optimizer_cost_model"=io; alter session set "_optimizer_cost_model"=cpu; Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-4612558851091892700.post-33173835938677914312009-09-23T21:39:00.000-07:002009-09-23T21:39:30.872-07:00Oracle: Check which user is holding temp tablespaceCheck which user holding temp tablespace SELECT s.username, s.sid,  u.TABLESPACE, u.CONTENTS, u.extents, u.blocks   FROM v$session s, v$sort_usage u  WHERE s.saddr = u.session_addr; Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-4612558851091892700.post-68761035140884313202009-09-23T21:33:00.001-07:002009-09-24T01:08:22.051-07:00Oracle: SQLplus Prompt to show user, connection (10g)SQLplus Prompt to show user, connection (Oracle 10g) run the following in sqlplus: set sqlprompt "_USER'@'_CONNECT_IDENTIFIER _PRIVILEGE> " Display: user@DB (privilege if exist)> Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-4612558851091892700.post-73171446179310661772009-09-23T21:16:00.000-07:002009-09-23T21:19:01.652-07:00Oracle: Show current userShow current userShow user;orSelect user from dual;Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-4612558851091892700.post-86686896512386430082009-09-23T21:09:00.000-07:002009-09-28T00:17:05.696-07:00Oracle: Get database name using sqlplusGet database name using this sql: select ora_database_name from dual;Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-4612558851091892700.post-32823980852688783162008-12-01T21:52:00.000-08:002009-09-23T21:31:49.167-07:00SQL Stuffw3schools SQL Tutorial: http://www.w3schools.com/sql/default.asp SQL (orafaq): http://www.orafaq.com/wiki/SQL Oracle SQL FAQ: http://www.orafaq.com/wiki/SQL_FAQUnknownnoreply@blogger.com0tag:blogger.com,1999:blog-4612558851091892700.post-57634355248630556642008-12-01T21:45:00.000-08:002008-12-01T21:51:45.415-08:00Oracle Database StuffOracle FAQ - Oracle Wiki: http://www.orafaq.com/wiki/Main_PageOracle DBA FAQ - Oracle Basic Concepts: http://dev.fyicenter.com/faq/oracle/oracle_basic_concept.phpOracle SQL *Plus: An Introduction and Tutorial: http://cisnet.baruch.cuny.edu/holowczak/oracle/sqlplus/Unknownnoreply@blogger.com0