Jonathan Lewis - FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Oracle (tm) Users' Co-operative FAQ - Index Mon, 30 May 2011 02:28:31 -0400 240 Ponyfish - http://www.ponyfish.com I'm trying to make a tablespace read only, but it seems to hang forever. What's wrong ? http://feedproxy.google.com/~r/JonathanLewisFaq/~3/qMziW1Mzu2I/ts_readonly.html http://www.jlcomp.demon.co.uk/faq/ts_readonly.html http://www.jlcomp.demon.co.uk/faq/ts_readonly.html What bitwise operations does Oracle support ? http://feedproxy.google.com/~r/JonathanLewisFaq/~3/DcenCT6FAc0/bitwise.html http://www.jlcomp.demon.co.uk/faq/bitwise.html http://www.jlcomp.demon.co.uk/faq/bitwise.html How can I tune a tablescan? http://feedproxy.google.com/~r/JonathanLewisFaq/~3/G20y4JAlAFY/tablescan_tune.html http://www.jlcomp.demon.co.uk/faq/tablescan_tune.html http://www.jlcomp.demon.co.uk/faq/tablescan_tune.html Why is dbms_stats so much slower than Analyze? http://feedproxy.google.com/~r/JonathanLewisFaq/~3/15rYzI23eVE/dbms_or_analyze.html http://www.jlcomp.demon.co.uk/faq/dbms_or_analyze.html http://www.jlcomp.demon.co.uk/faq/dbms_or_analyze.html Can Bind Variable Peeking Cause Problems even with no Histograms on the Predicate Columns http://feedproxy.google.com/~r/JonathanLewisFaq/~3/eZXKPtT1kRQ/bind_peek.html http://www.jlcomp.demon.co.uk/faq/bind_peek.html http://www.jlcomp.demon.co.uk/faq/bind_peek.html How to generate an Execution Plan with Bind Variable Peeking? http://feedproxy.google.com/~r/JonathanLewisFaq/~3/Mk_ePvCm7Vs/how_to_explain.html http://www.jlcomp.demon.co.uk/faq/how_to_explain.html http://www.jlcomp.demon.co.uk/faq/how_to_explain.html Why do I keep seeing tables with garbage names like BIN${something} http://feedproxy.google.com/~r/JonathanLewisFaq/~3/jl8gzAmNzts/recyclebin.html http://www.jlcomp.demon.co.uk/faq/recyclebin.html http://www.jlcomp.demon.co.uk/faq/recyclebin.html How do I pin a table in memory so that it is permenently in the cache ? http://feedproxy.google.com/~r/JonathanLewisFaq/~3/aRgIkdEpOac/pin_table.html http://www.jlcomp.demon.co.uk/faq/pin_table.html http://www.jlcomp.demon.co.uk/faq/pin_table.html How do you create an 'auto number' or 'auto increment' column similar to the offering from Microsoft's SQL Server ? http://feedproxy.google.com/~r/JonathanLewisFaq/~3/JVEce6g3Gj0/autonumb.html http://www.jlcomp.demon.co.uk/faq/autonumb.html http://www.jlcomp.demon.co.uk/faq/autonumb.html How does Oracle handle PGA memory ? http://feedproxy.google.com/~r/JonathanLewisFaq/~3/h70qIRE4c5U/pat_presentation.html http://www.jlcomp.demon.co.uk/faq/pat_presentation.html http://www.jlcomp.demon.co.uk/faq/pat_presentation.html What is the relationship between Oracle's buffer cache and UNIX's buffer cache ?  http://feedproxy.google.com/~r/JonathanLewisFaq/~3/Hr2gz8AKeLw/ou_bufcache.html http://www.jlcomp.demon.co.uk/faq/ou_bufcache.html http://www.jlcomp.demon.co.uk/faq/ou_bufcache.html Queries against dba_free_space are slow - is there any way to speed them up? http://feedproxy.google.com/~r/JonathanLewisFaq/~3/3HZSxm0S5Og/freespace3.html http://www.jlcomp.demon.co.uk/faq/freespace3.html http://www.jlcomp.demon.co.uk/faq/freespace3.html How do I change the default password of user internal ? http://feedproxy.google.com/~r/JonathanLewisFaq/~3/ss4Dwx-lWYw/internal.html http://www.jlcomp.demon.co.uk/faq/internal.html http://www.jlcomp.demon.co.uk/faq/internal.html How do you use sql_trace ? http://feedproxy.google.com/~r/JonathanLewisFaq/~3/fYc-RaatVmo/sql_trace.html http://www.jlcomp.demon.co.uk/faq/sql_trace.html http://www.jlcomp.demon.co.uk/faq/sql_trace.html Is it possible to run several different versions of Oracle on the same server machine? http://feedproxy.google.com/~r/JonathanLewisFaq/~3/cINFk-9L3dA/multiple_oracle.html http://www.jlcomp.demon.co.uk/faq/multiple_oracle.html http://www.jlcomp.demon.co.uk/faq/multiple_oracle.html I have deleted a trace file with the session still live. I now want the session to resume tracing, but a new file doesn't appear http://feedproxy.google.com/~r/JonathanLewisFaq/~3/z0inbavLiaA/sql_trace_2.html http://www.jlcomp.demon.co.uk/faq/sql_trace_2.html http://www.jlcomp.demon.co.uk/faq/sql_trace_2.html Is there a Windows version of the oerr program? http://feedproxy.google.com/~r/JonathanLewisFaq/~3/-LApTpYsI8s/windowsoerr.html http://www.jlcomp.demon.co.uk/faq/windowsoerr.html http://www.jlcomp.demon.co.uk/faq/windowsoerr.html Why does it seem that a SELECT over a db_link requires a commit after execution ? http://feedproxy.google.com/~r/JonathanLewisFaq/~3/8fBeyLuF6yA/dblink_commit.html http://www.jlcomp.demon.co.uk/faq/dblink_commit.html http://www.jlcomp.demon.co.uk/faq/dblink_commit.html How can I tell what events are set ? http://feedproxy.google.com/~r/JonathanLewisFaq/~3/SE6fuxDAmvk/what_events.html http://www.jlcomp.demon.co.uk/faq/what_events.html http://www.jlcomp.demon.co.uk/faq/what_events.html How can I select a random collection of rows from a table ? http://feedproxy.google.com/~r/JonathanLewisFaq/~3/NYO5K8D_zJU/random.html http://www.jlcomp.demon.co.uk/faq/random.html http://www.jlcomp.demon.co.uk/faq/random.html How can I maintain a history of user logins ? http://feedproxy.google.com/~r/JonathanLewisFaq/~3/_H7jTPAcJ_4/audit.html http://www.jlcomp.demon.co.uk/faq/audit.html http://www.jlcomp.demon.co.uk/faq/audit.html According to autotrace, I am not doing partition elimination - how do I fix this ? http://feedproxy.google.com/~r/JonathanLewisFaq/~3/_XD9vVfdjI8/autotrace_pruning.html http://www.jlcomp.demon.co.uk/faq/autotrace_pruning.html http://www.jlcomp.demon.co.uk/faq/autotrace_pruning.html When I rebuild an index, I see Oracle doing a sort. Why should this be necessary, why doesn't it simply read the existing index ? http://feedproxy.google.com/~r/JonathanLewisFaq/~3/ENaBHxMYZlE/ind_rebuild.html http://www.jlcomp.demon.co.uk/faq/ind_rebuild.html http://www.jlcomp.demon.co.uk/faq/ind_rebuild.html I lock my table before truncating it, but I still get ORA-00054: resource busy ... Why ? http://feedproxy.google.com/~r/JonathanLewisFaq/~3/0RYTUy0O1nE/truncate_busy.html http://www.jlcomp.demon.co.uk/faq/truncate_busy.html http://www.jlcomp.demon.co.uk/faq/truncate_busy.html Can I see all the tables that are in the database if I have the DBA privilege ? http://feedproxy.google.com/~r/JonathanLewisFaq/~3/rfP2DDBT4aQ/see_tables.html http://www.jlcomp.demon.co.uk/faq/see_tables.html http://www.jlcomp.demon.co.uk/faq/see_tables.html How do you purge old statspack snapshots automatically http://feedproxy.google.com/~r/JonathanLewisFaq/~3/6LU-ngEw8ms/statspack_purge.html http://www.jlcomp.demon.co.uk/faq/statspack_purge.html http://www.jlcomp.demon.co.uk/faq/statspack_purge.html How do I find out who is currently using the space in the temporary tablespace ? http://feedproxy.google.com/~r/JonathanLewisFaq/~3/5WltRT8q6EM/sort_user.html http://www.jlcomp.demon.co.uk/faq/sort_user.html http://www.jlcomp.demon.co.uk/faq/sort_user.html Is there any reason why the tablespace_name in user_tables and all_tables is blank (empty) for an Index Organized Table. http://feedproxy.google.com/~r/JonathanLewisFaq/~3/ksUyrgoR-9Y/iot_ts.html http://www.jlcomp.demon.co.uk/faq/iot_ts.html http://www.jlcomp.demon.co.uk/faq/iot_ts.html Our PL/SQL loop to load 20M rows into the database slows down after the first 1M or so, why ? http://feedproxy.google.com/~r/JonathanLewisFaq/~3/Y4ENLVkUTb8/slowdown.html http://www.jlcomp.demon.co.uk/faq/slowdown.html http://www.jlcomp.demon.co.uk/faq/slowdown.html Queries against dba_free_space with locally managed tablespaces are slow - is there any way to speed them up ? http://feedproxy.google.com/~r/JonathanLewisFaq/~3/ttGNRbWIEBU/free_space.html http://www.jlcomp.demon.co.uk/faq/free_space.html http://www.jlcomp.demon.co.uk/faq/free_space.html How can an end-user see whether a row is locked, and who is locking it, before they try to lock it themselves ? http://feedproxy.google.com/~r/JonathanLewisFaq/~3/mn7T1BBB420/locked_rows2.html http://www.jlcomp.demon.co.uk/faq/locked_rows2.html http://www.jlcomp.demon.co.uk/faq/locked_rows2.html I have deleted 6M rows from a table - how do I reclaim the free space ? http://feedproxy.google.com/~r/JonathanLewisFaq/~3/hrOy89ulS6U/mass_delete.html http://www.jlcomp.demon.co.uk/faq/mass_delete.html http://www.jlcomp.demon.co.uk/faq/mass_delete.html How do I clone a database from one machine to another - when the filesystems are different ? http://feedproxy.google.com/~r/JonathanLewisFaq/~3/0onLjkoj3_M/clone_db.html http://www.jlcomp.demon.co.uk/faq/clone_db.html http://www.jlcomp.demon.co.uk/faq/clone_db.html I have a two-column partition key, and my data keeps going in the wrong partition. Why ? http://feedproxy.google.com/~r/JonathanLewisFaq/~3/Mfb3Tqod0Rg/partition_key.html http://www.jlcomp.demon.co.uk/faq/partition_key.html http://www.jlcomp.demon.co.uk/faq/partition_key.html dba_segments and dba_tables show different numbers for blocks. What has gone wrong ? http://feedproxy.google.com/~r/JonathanLewisFaq/~3/bfYGVmV2jsk/block_counts.html http://www.jlcomp.demon.co.uk/faq/block_counts.html http://www.jlcomp.demon.co.uk/faq/block_counts.html What is the unit used in the time-related columns of the v$views http://feedproxy.google.com/~r/JonathanLewisFaq/~3/AGjy-aX-yAw/time_unit.html http://www.jlcomp.demon.co.uk/faq/time_unit.html http://www.jlcomp.demon.co.uk/faq/time_unit.html My locally managed tablespaces seem to have lost some space according to dba_free_space. Where has it gone ? http://feedproxy.google.com/~r/JonathanLewisFaq/~3/ca8deBrtV7c/lmt_losses.html http://www.jlcomp.demon.co.uk/faq/lmt_losses.html http://www.jlcomp.demon.co.uk/faq/lmt_losses.html What is the difference between cpu_costing in Oracle 9 and the old costing method of Oracle 8 ? http://feedproxy.google.com/~r/JonathanLewisFaq/~3/8pSok0HcBAI/cost_9.html http://www.jlcomp.demon.co.uk/faq/cost_9.html http://www.jlcomp.demon.co.uk/faq/cost_9.html How do I find distributed queries / transactions (either issued from or connecting to this instance)? http://feedproxy.google.com/~r/JonathanLewisFaq/~3/8EpOHhRIYHU/find_dist.html http://www.jlcomp.demon.co.uk/faq/find_dist.html http://www.jlcomp.demon.co.uk/faq/find_dist.html What happens if I drop a partition from a partitioned table whilst a query is running ? http://feedproxy.google.com/~r/JonathanLewisFaq/~3/xgXnV0fohlg/pt_drop_query.html http://www.jlcomp.demon.co.uk/faq/pt_drop_query.html http://www.jlcomp.demon.co.uk/faq/pt_drop_query.html Why does a global index on my partitioned table work faster than a local index ? http://feedproxy.google.com/~r/JonathanLewisFaq/~3/85x3VuoEu68/slow_local.html http://www.jlcomp.demon.co.uk/faq/slow_local.html http://www.jlcomp.demon.co.uk/faq/slow_local.html When I update one table from another, lots of my data turns to nulls - what have I done wrong ? http://feedproxy.google.com/~r/JonathanLewisFaq/~3/GZa1Ybb_CAk/lost_update.html http://www.jlcomp.demon.co.uk/faq/lost_update.html http://www.jlcomp.demon.co.uk/faq/lost_update.html What are all these tables with names like EVT% and SMP% that have recently appeared in my system ? http://feedproxy.google.com/~r/JonathanLewisFaq/~3/XXzq_AA9n70/oem_tables.html http://www.jlcomp.demon.co.uk/faq/oem_tables.html http://www.jlcomp.demon.co.uk/faq/oem_tables.html How can I track ddl changes in my database ? http://feedproxy.google.com/~r/JonathanLewisFaq/~3/Tugo2InSSe4/DDL_tracker.html http://www.jlcomp.demon.co.uk/faq/DDL_tracker.html http://www.jlcomp.demon.co.uk/faq/DDL_tracker.html What's my best strategy for laying out an Oracle database across my available I/O devices ? http://feedproxy.google.com/~r/JonathanLewisFaq/~3/CBV6YB5QQcI/layout.html http://www.jlcomp.demon.co.uk/faq/layout.html http://www.jlcomp.demon.co.uk/faq/layout.html Is it possible to re-set a sequence without dropping and recreating it ? http://feedproxy.google.com/~r/JonathanLewisFaq/~3/u-RqZ-i_EoM/resetseq.html http://www.jlcomp.demon.co.uk/faq/resetseq.html http://www.jlcomp.demon.co.uk/faq/resetseq.html How do I retrieve information about partitioned tables from the data dictionary ? http://feedproxy.google.com/~r/JonathanLewisFaq/~3/AuzpSf8xQcY/pt_info.html http://www.jlcomp.demon.co.uk/faq/pt_info.html http://www.jlcomp.demon.co.uk/faq/pt_info.html Why is my import so much slower than my export ? http://feedproxy.google.com/~r/JonathanLewisFaq/~3/R4eOAcGMb6Q/imp_slow.html http://www.jlcomp.demon.co.uk/faq/imp_slow.html http://www.jlcomp.demon.co.uk/faq/imp_slow.html What is an object of type 'Non-existent' ? http://feedproxy.google.com/~r/JonathanLewisFaq/~3/Nc4goDODS5Y/non_exist.html http://www.jlcomp.demon.co.uk/faq/non_exist.html http://www.jlcomp.demon.co.uk/faq/non_exist.html I am getting a database startup error about being unable to start "with new and and old " values for parameters. (9.0.1) http://feedproxy.google.com/~r/JonathanLewisFaq/~3/qIFCLMX-68A/spfile_01.html http://www.jlcomp.demon.co.uk/faq/spfile_01.html http://www.jlcomp.demon.co.uk/faq/spfile_01.html