13 March 2006
Oracle Wish List
I started filling in the wishes on this list in '98 and has been changing/expanding it over the years. Some of the "wishes" are now possible.
- All Oracle parameters to be dynamic
- This has been my wish since the late 90s and Oracle is slowly getting there.
- Bind variable peeking for all bind variables per SQL statement
- If the application is created with bind variables, this would peek for every bind variable for a specific SQL statement
- Get running statisticsfor SQL statement's run without actually running it
- Some non-Oracle tuning tools already provide this feature
- ! Alert log errors logged into a table when the database is open
- Using 9i and 10g external tables, alert and listener logs can appear in a table format.
- Switching off redo logging for a database/tablespace/session/SQL
- NOLOGGING option provides only a partial solution
- Ability to ask for password before shutting down Oracle databases
- Similar to Oracle listeners' passwords
- A DBA should not always need to see the application data
- ! This became possible with the Database Vault
- Transfer ownership of the object to another user without dropping the object
- Dynamic aliases in SQL*PLus
- Example:
- SQL> define sl="select * from"
- SQL> sl test; will run "select * from test;"
- Same-type triggers firing in a pre-defined order
- More granular system roles
- Example:
- An user has ALTER DATABASE privilege; He/she can only manage datafiles, but not startup and recovery
- More control over sorting segments in a temporary tablespace
- Cleaning them/dropping them forcefully (for all those times when TEMP is 100% full and you can't do anything but bounce the instance)
- Statistics of redo size per each statement
- At the moment, only redo size per session exists, which is a problem if you want to find which SQL in that session is creating excessive redo entries
- Easy way of tracing the database jobs before they run
- This is possible with tracing all the current job processes + on-logon tracing trigger, but the amount of trace files is sometimes too hard to process
- I would like to see a command like this:
- dbms_support.trace_job(&job_id, bind=>TRUE, waits=>TRUE);
Comments:
<< Home
Hi Tanya,
Very interesting article.
Reading through the wishlist at the end of the article, the one about dynamic aliases... this can be done on Windoze using the DOSKEY program and sqlplus: -
C:\> doskey /exename=sqlplus.exe sl=select * from $*
C:\> sqlplus user/pwd
SQL> sl dual;
D
-
X
SQL>
I have this setup : -
DOSKEY /EXENAME=sqlplus.exe con+=conn $1@(description=(ADDRESS=(PROTOCOL=TCP)(HOST=$2)(PORT=$3))(connect_data=(service_name=$4)))
...so I can :-
SQL> conn+ user2/pwd mach01 1521 PROD1
Connected.
...to connect without having to use a tnsnames.ora, just the host, port, sid|servicename details.
You can have multiple "aliases defined by specifying multiple "doeskey exename=sqlplus.exe" definitions before logging into sqlplus.
Regards,
Gaz
Post a Comment
Very interesting article.
Reading through the wishlist at the end of the article, the one about dynamic aliases... this can be done on Windoze using the DOSKEY program and sqlplus: -
C:\> doskey /exename=sqlplus.exe sl=select * from $*
C:\> sqlplus user/pwd
SQL> sl dual;
D
-
X
SQL>
I have this setup : -
DOSKEY /EXENAME=sqlplus.exe con+=conn $1@(description=(ADDRESS=(PROTOCOL=TCP)(HOST=$2)(PORT=$3))(connect_data=(service_name=$4)))
...so I can :-
SQL> conn+ user2/pwd mach01 1521 PROD1
Connected.
...to connect without having to use a tnsnames.ora, just the host, port, sid|servicename details.
You can have multiple "aliases defined by specifying multiple "doeskey exename=sqlplus.exe" definitions before logging into sqlplus.
Regards,
Gaz
# posted by 
: 06 January, 2009
: 06 January, 2009 << Home
