Oracle 10g has a feature where it gathers optimizer statistics automatically at night. This is done through a scheduled job called GATHER_STATS_JOB. If you want to disable this, you have to issue : EXEC dbms_scheduler.disable(’GATHER_STATS_JOB’); To enable it again : EXEC dbms_scheduler.enable(’GATHER_STATS_JOB’); The following query shows when this job was run : SELECT * FROM dba_scheduler_job_run_details WHERE job_name = ’GATHER_STATS_JOB’; There are other tables, all starting with dba_scheduler, that show the schedules and so on.
To get the language setting of the current session : SELECT USERENV ('language') FROM DUAL; This returns the database character set, not the user’s though. The NLS settings can be found in the following list, in this order. If a value is specified in NLS_SESSION_PARAMETERS, it has preference over a value in NLS_INSTANCE_PARAMETERS. NLS_SESSION_PARAMETERS NLS_INSTANCE_PARAMETERS NLS_DATABASE_PARAMETERS
By default Oracle expects tnsnames.ora to be in the $ORACLE_HOME\network\admin directory. This is not always the most convenient place, especially because that directory is usually owned by the oracle user. To get around this, you can set the TNS_ADMIN variable. This points to the directory where you have put your own copy of tnsnames.ora . Like they say on TV : darn useful.
Every time you log into Oracle, you create a session. This is an easy way to kill these Oracle sessions, thanks to this article. First, select the session ID and serial number as such : SELECT s.sid, s.serial#, s.osuser, s.program FROM v$session s; The osuser and program field can be used to identify the session. Then you can kill the session using : ALTER SYSTEM KILL SESSION 'sid,serial#'; It’s also possible to kill the session immediately (but that’s considered rude) : ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;
If you want to select random rows from a table, you can order the rows using the dbms_random.value function : SELECT column FROM table ORDER BY dbms_random.value To get 20 random rows from the table, we can add a condition on rownum. This selects the first 20 rows : SELECT column FROM ( SELECT column FROM table ORDER BY dbms_random.value ) WHERE rownum < 21 With thanks to this site.
Copyright (c) 2024 Michel Hollands