The Value Added Tax rate we invoice at drops from 17.5% to 15% on 1st December 2008. If, like me, you are on the small business flat rate VAT scheme for IT consultants, then the rate we pay VAT out to HM Revenue and Customs drops from 13% to 11.5% at the same time.
Full details in http://www.hmrc.gov.uk/pbr2008/vat-guide-det.pdf, Page 43, Section 16, Annex E “Flat Rate Scheme – New Percentage Rates”
Adding more memory to PCs is a cheap and easy upgrade method.
But there are different types of PC memory modules available – SDRAM, DDR, DDR2 – and computers are limited to being compatible with one type only. So you have to know which type you want before buying, especially if buying online.
1) To see how much memory a windows PC has, either run:
Start -> Control Panel -> System
or right click on ‘My Computer’ and select ‘Properties’
The general tab shows the amount of memory:
In this case, 192 MB of memory (RAM = random access memory).
2) Look at the existing memory module, which means removing the outer case of the computer, normally held on by a few small Phillips head screws. Power off at the mains beforehand.
Inside there will be a memory module looking like one of these:
Which is held in the motherboard with white edge clips like this:
It’s easy to take that in and out, but for full instructions see Morris Rosenthal’s illustrated guide.
3) The memory module should be labelled with which type it is, but you can tell even without that by looking at the number of small metal pins at the base of the memory module:
SDRAM – 168 pins – 2 notches
DDR – 184 pins – 1 notch
DDR2 – 240 pins – 1 notch
The pins run on both sides of the memory module, so a DDR module has pins 1-92 on one side, and pins 93-184 on the other. Their numbers should be printed beside the first and last pin on each side to save you counting.
I 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 PUBLIC;
GRANT all ON plsql_profiler_units TO PUBLIC;
GRANT all ON plsql_profiler_runs TO PUBLIC;
CREATE PUBLIC SYNONYM plsql_profiler_runs FOR plsql_profiler_runs;
CREATE PUBLIC SYNONYM plsql_profiler_units FOR plsql_profiler_units;
CREATE PUBLIC SYNONYM plsql_profiler_data FOR plsql_profiler_data;
CREATE PUBLIC SYNONYM plsql_profiler_runnumber FOR plsql_profiler_runnumber;
alter user system default tablespace system ;
Based on Tim Hall’s post.
My rule of thumb:
And here’s why:
9i and below
Without a listener password set, any ‘oracle’ account on any machine (even a client) can stop and change listeners on any other machine on your network, including production servers.
So, running a command like this from a dev box as the oracle userid:
$ lsnrctl stop liveserver
will stop the listener running on server “liveserver“. Which you probably don’t want.
And its not enough to make every “oracle” account on every server secured to DBAs: people can and will create their own clients or virtual clients with oracle userids. As long as they are plugged into your network, they control all your listeners. And there’s nothing you can do about it – except set a listener password.
With a listener password, nothing can be done to any of your listeners except by someone who knows your password.
To set a listener password, use the change_password command in listener control:
$ lsnrctl
LSNRCTL> change_password
LSNRCTL> save config
The fist time you do this, no password is set, so just press return when you are asked for the existing password.
Once that has been done, you have to specify the password before attempting most listener operations. Use the “set password” command for that:
$ lsnrctl
LSNRCTL> set password
“lsnrctl start” still works without the password being specified, but pretty much everything (even status!) requires the password set first.
That makes system shutdown commands a little more complex, see this post for details.
10g and above
The above is such a glaring security hole that oracle changed the default behaviour with 10g and made everything secure. More secure even than the above setup of a password protected listener. Because with 10g, the default is to refuse lsnrctl operations from other machines.
If you actually wanted lsnrctl operations from other machines (very unusual in my experience), then setting a listener password will allow that.
So notice the behaviour has switched:
Setting a password doesn’t make anything more sucure – passwords are ignored if you are the oracle userid/member of dba group on the local machine. While on 9i and below the password, if set, would always be required.
Oracle Support have some carefully worded (“customers may wish to consider“) advice on that:
For database releases prior to Oracle Database 10g Release 1: you should set an administrative password for the TNS listener. For Oracle Database 10g Release 1 and later releases, the default authentication mode for the TNS listener is local OS authentication, which requires the TNS listener administrator to be a member of the local dba group. Setting a password for the TNS listener in Oracle Database 10g Release 1 and higher simplifies administration. However, setting a password requires good password management to prevent unauthorized users from guessing the password and potentially gaining access to privileged listener operations. Because the listener acts as the database’s gateway to and from the network, it is important to secure it. Customers may wish to consider not setting a password for the TNS listener starting with Oracle Database 10g Release 1, because in the interest of increasing security and reducing risks, the default behavior changed in this and later releases.
[Metalink note 340009.1]
That is: for 10g, don’t set a listener password, unless you really need/want to control listeners from different machines.
Thanks to Beth, Howard Rogers, Mark Ashby for the info on 10g behaviour.
In 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 automatically. It has to be created manually by running script utlxplan.sql in $ORACLE_HOME/rdbms/admin. That should be done both at creation time and after each upgrade (since plan_table structure is changed/improved with each new release).
Another difference from 10g: in 9i and below, plan_table is a real table rather than a global temporary table.
It is possible to allow each user to have their own personal plan_table, but I think it is better to mimic the 10g behaviour and have a single central plan_table used by all users. To do that, run SQL like:
conn / as sysdba
@?/rdbms/admin/utlxplan
grant select, insert, update, delete on sys.plan_table to public ;
create public synonym plan_table for sys.plan_table ;
One thing to watch is that copies of old plan_table’s can be left lying around in users schemas. That applies even to 10g, because a users’ table will be accessed in preference to a public synonym of the same name.
It’s not just upgrades that can leave those in place – export/import can bring users’ old plan_table’s across as well. Old versions of plan_table tend to work without reporting an error, but do not generate as much detail as new versions, so should be avoided.
Utlxplan.sql is still shipped with 10g, but there is no reason to continue using it at that version.
For extra confusion, TOAD by default will attempt to use its own version of a plan table. See previous post for details.
TOAD can report error “ORA-02404: specified plan table not found” when trying to display execution plans:
Two options to fix:
Plan_table itself ought to be in every database, although in versions 9i and earlier that required DBA intervention, was not created by default. See next post for details on that.
TOAD error fix from:
Short SQL Server Training course uploaded in MS-Word format.
It’s a bit dated now – is for SQL Server version 7.0 – but still relevant. I’ll update for version 2005 and convert to html format when I get the chance.
I produced that for a 1.5 day training course to give Oracle DBAs the knowledge they needed to work on SQL Server. They are similar enough relational database management systems for a quick conversion course like this to be enough to make the switch. And they are both common enough that a good DBA ought to be comfortable with both.
Thanks to Chris Carr for pointing out this:
If you are upgrading or patching to 10.2.0.3 and the word size is different to the word size when the database was created the upgrade will fail with an ora-600. This applies even if the wordsize has been changed in an interim upgrade i.e. 8.1.7 32bit upgraded to 9.2.0 64bit will not upgrade to 10.2.0.3 64 bit, (but will upgrade to 10.2.0.3 32 bit).
To identify whether a database was created as 32-bit or 64-bit, execute the following SQL statement:
SQL> select metadata from sys.kopm$ ;
If the output references the string 'B023' then it indicates that the database was created as 32-bit If the output references the string 'B047' then it indicates that the database was created as 64-bit
Metalink note on this is Note 412271.1.
There is a patch to fix this before applying 10.2.0.3, although it is available on Linux, Solaris, HPUX and AIX only so far, not on Windows for some reason.
This would seem to be pretty authoritative:
“And on the seventh day God ended his work which He had made; and he rested on the seventh day from all his work which He had made. And God blessed the seventh day, and sanctified it; because that in it he had rested from all his work which God created and made” (Genesis 2:2-3).
But, only in America.*
In many other NLS territories, Saturday is the sixth day of the week, not the seventh:
SQL> alter session set NLS_TERRITORY = 'AMERICA';
Session altered.
SQL> select to_char(sysdate+3,'D Dy') from dual;
TO_CH
-----
7 Sat
SQL> alter session set NLS_TERRITORY='UNITED KINGDOM';
Session altered.
SQL> select to_char(sysdate+3,'D Dy') from dual;
TO_CH
-----
6 Sat
Now that’s a real pain, because different Windows PCs will inevitably end up having different registry/environment variable settings for NLS_LANG (NLS_TERRITORY forms the middle part of the NLS_LANG variable). And NLS_LANG also gets set a lot in unix .profile’s. And those all override the databases settings.
So this week when I had users reporting different results from different PCs, I had initially assumed tnsnames.ora differences were pointing them off to different databases. But really it was because they were running code like this:
select address, 'These customers want visited Saturdays'
from customer
where to_char(workdate,'D') = 6 ;
The fix is to name the day of the week rather than count to it:
select address, 'These customers want visited Saturdays'
from customer
where to_char(workdate,'Dy') = 'Sat' ;
Essentially this issue makes the “D” date format unusable.
If code like the above absolutely couldn’t be changed, then a logon trigger would be the only way to set NLS_TERRITORY the same for all users:
create or replace trigger my_logon_trigger after logon on database begin execute immediate 'alter session set nls_territory = ''UNITED KINGDOM'' ' ; end ; /