Here’s an example of how to use partition exchange to move partitions or even entire unpartitioned tables from one schema to another. Is mean to be very fast and generate very little redo. Even more so if the partitions and tables are kept in the same tablespace. Process for doing partition exchange is like this: […]
Read More schema moves by the magic of partition exchangeHere is a demo example. 1) Set up example tables: CREATE TABLE parent ( parent_id number(10) not null, CONSTRAINT parent_pk PRIMARY KEY (parent_id) ); CREATE TABLE child ( child_id numeric(10) not null, parent_id numeric(10) not null, CONSTRAINT fk_child FOREIGN KEY (parent_id) REFERENCES parent(parent_id) ); insert into parent values (1) ; insert into child values (1,1) […]
Read More Use Foreign Key constraints with “on delete cascade” option for fast automated delete of child recordsLOBs generally(*) perform better if cached – that is, stored in the database buffer cache. However, that is not switched on by default. To change a lob to be cached: alter table mytable modify lob (mycolumn) (cache) ; To set it back to being uncached: alter table mytable modify lob (mycolumn) (nocache) ; And to […]
Read More Cache LOBs for Better PerformanceI used the following to install dbms_profiler, using a central set of system owned tables rather than each user having its own tables: conn / as sysdba @?/rdbms/admin/profload.sql conn system/password alter user system default tablespace users ; — [or any other reasonable tablespace] @?/rdbms/admin/proftab.sql GRANT all ON plsql_profiler_runnumber TO PUBLIC; GRANT all ON plsql_profiler_data TO […]
Read More Installing dbms_profilerIn versions 10g, plan_table exists as a public synonym of the global temporary table sys.plan_table$. That is all created automatically at database create time and when a database is upgraded to 10g (creation is in catplan.sql, which is called from catproc.sql and u*.sql scripts in $ORACLE_HOME/rdbms/admin). In versions 9i and below, plan_table is not created […]
Read More plan_table changesTOAD can report error “ORA-02404: specified plan table not found” when trying to display execution plans: Two options to fix: Run script toadprep.sql. This creates the table toad_plan_table, among other things, and is meant to be run by a user with DBA privileges. or Change TOAD to use the same plan table (= plan_table) that […]
Read More Fix for TOAD error ORA-02404: specified plan table not foundGUIs like TOAD and Enterprise Manager can show this better, but they’re not always to hand. Every session active now or within the last minute is shown. set pages 9999 lines 132 col username form a20 col osuser form a20 col program form a50 col minutes form 999 alter session set nls_date_format = ‘Dy DD-Mon-YYYY […]
Read More What SQL and sessions are running?To switch 10046 tracing on in another session (first setting on timed statistics and making max_dump_file_size very large): set pages 9999 verify off col serial new_value serial noprint select serial# serial from v$session where sid = &1 ; exec sys.dbms_system.set_bool_param_in_session(&1,&serial,’timed_statistics’,true) exec sys.dbms_system.set_int_param_in_session(&1,&serial,’max_dump_file_size’,999999999) exec sys.dbms_system.set_ev(&1,&serial,10046,8,”) And to switch it off: set pages 9999 verify off col […]
Read More 10046 tracing in another sessionLists slow SQL in library cache for tuning investigations: set pages 9999 spool c.lst select elapsed_time/1000000 secs, executions, elapsed_time/1000000/greatest(executions,1) secs_per_exec, sql_text from v$sql where executions > 50 and elapsed_time/1000000/greatest(executions,1) > 1 order by 3 desc / spool off ed c.lst For older versions of oracle, use buffer_gets or disk_reads in place of elapsed_time.
Read More Slow SQL reportThis shell script runs against every database that is up and running (has an entry in the ps list). Which I think is better than searching through oratab (since not all of those may be up and running, esp. on dev machines). You can run any SQL within it, but this particular version prints out […]
Read More Script run against every running database, listing total oracle memory (sga+pga)