It’s often a pain getting X windows GUIs like the Oracle Installer and DBCA to run on Micrsoft Windows PC clients. Emulators like Reflection-X, KEA-Term, and Exceed work ok, but they cost money to licence and require source media and local admin rights to install. Those things can take an age to organise in large […]
Read More Free X windows emulator for running Oracle InstallerToday had noticed that redo logs were not being applied to standby. Checking live database alert log showed errors like:
Read More Data Guard todayWouldn’t it be nice if sql code produced the same result, regardless of whether it is inside a stored procedure or is a standalone plsql block? But that’s not what happens when the all_ views are used. Run this code to see the anomaly for yourself: set serverout on declare var1 number ; begin select […]
Read More Bug? with wrong results from all_objects in stored plsql proceduresTo 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 sessionThe below SQL generates a SQL script that can be used to clone a database, putting tablespaces into hot backup mode one at a time. Run it on the source database, and edit the output to specify new target file and directory names, and change cp to rcp or scp if cloning to another server. […]
Read More Clone a databaseLists 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 reportIt’s not always easy to get X windows properly, or to get hold of X windows emulator software for Windows PCs. So it was a bit of a step backwards when the installer moved away from the ugly but effective clunky text display that it had in version 7 and below. Running the installer in […]
Read More Silent installer on unix still needs X windows until 10gUpdate 13-Feb-07: Setting the datapump table_exists_action to replace does not overwrite views, sequences, plsql objects. They have to be dropped separately before datapump is called, see gotchas and code below. Update 02-Mar-07: dba_ views rather than all_ views have to be used to identify what objects to drop prior to datapump, for reasons explained in […]
Read More Using 10g datapump and scheduler to copy schemasSQL Server 2005 doesn’t prompt during install for locations of datafiles and logfiles, meaning they will end up on C:, which you won’t normally want. You can alter the locations during install, but it’s not especially intuitive. Or at least not intuitive enough for me 🙁 To fix them after the event, see http://www.campus.ncl.ac.uk/databases/sqlser/SQL2005/setupsql2005.html and […]
Read More SQL Server 2005