No.

原文：http://www.dizwell.com/oracle/articles/read-only_tables.html

No.

Well, if you want more, you’ll have to define what you mean a little more fully! But putting it simply, a table in an Oracle database, of whatever version, and on whatever platform can not be made read-only. That is, it cannot have some switch flipped whereby it then intrinsically and infallibly knows not to permit DML on its records. But if what you are really asking is, ‘is it possible to prevent DML from happening to a table temporarily’, then the answer becomes ‘Yes, there are lots of ways of doing that’. But the real point is, you’ve got to define why you want to prevent DML from happening for me to supply the right answer.

Well, again, the short answer is No. But let me see if I can demonstrate some of those ‘Yes’ options to you, and then we can decide why they may or may not be useful, and why the underlying reasons for your request are not a mater of irrelevance, but form a core part of the answer.

First, you could do this:

SQL> connect / as sysdba
Connected.

SQL> lock table scott.emp in exclusive mode;
Table(s) Locked.

Anyone connecting from another session attempting a piece of DML would be faced with this:

SQL> connect scott/tiger
Connected.

SQL> select sum(sal) from emp;

SUM(SAL)
----------
29025

SQL> update emp set sal=sal*2;
[session hangs]

So not even the owner of the table is able to update the rows of EMP. Scott’s session just sits there for an eternity, unable to obtain the shared lock on the EMP table that all DML needs to take before it can actually be implemented. Is that read-only enough for you?

The second he ends his session, the exclusive lock on EMP is removed, and DML by others is permitted.

Well, there’s my point: this table was undoubtedly ‘shielded’ from DML, but the implementation was too temporary for you. So knowing how permanent and trouble-free you want read-only-ness to be is an important part of getting the right answer. Taking an exclusive lock like that is certainly quick and simple, and would be fine if all you wanted was to prevent all DML for an hour or two. But for much longer no-DML periods, something else is called for. Try this, for example:

create or replace function lockme
(object_schema in varchar2, object_name in varchar2)
return varchar2
is
predicate varchar2(20);
begin
predicate := '1=2';
return predicate;
end;
/

exec dbms_rls.add_policy('SCOTT','EMP','LCKPOL',-
'SCOTT','LOCKME',’INSERT, UPDATE, DELETE’)

This creates a function which returns a never-true predicate (that is, 1 never equals 2), and uses Oracle’s Virtual Private Database functionality to attach a policy to the table which causes the function to fire and to append that predicate to whatever DML-style SQL is issued against the EMP table. Selects should be fine, however:

SQL> connect scott/tiger
Connected.

SQL> select sum(sal) from emp;

SUM(SAL)
----------
29025

SQL> update emp set sal=sal+sal;
0 rows updated.

SQL> commit;
Commit complete.

SQL> select sum(sal) from emp;

SUM(SAL)
----------
29025

The clue was in the “0 rows updated” line! You can try the same thing for inserts and deletes as well:

SQL> delete from emp;
0 rows deleted.

Hence the table is now protected against all DML, and by a mechanism which doesn’t cease working just because someone logs off.

Because I don’t know that it is really what you wanted. How about this to dampen your enthusiasm a little?

SQL> connect / as sysdba
Connected.

SQL> delete from scott.emp;
14 rows deleted.

SYS is exempt from Virtual Private Database policies. Your table really isn’t made read-only by this technique -merely protected from casual DML issued by ordinary users. Which might genuinely be all that you neded. But if the reason you wanted to make the table read-only was to prevent any modification of this table’s data, then this mechanism (clever and convenient as it is) fails that test. The DBA can always by-pass the mechanism.

On the same lines, you could try something like this:

SQL> connect / as sysdba
Connected.

SQL> exec dbms_rls.drop_policy('SCOTT','EMP','LCKPOL')

That just tidies things up by getting rid of the VPD policy previously created. Now for the alternative approach:

SQL> create or replace trigger lock_emp
2 before insert or update or delete on scott.emp
3 begin
4 raise_application_error(-20001, 'EMP is now Read-Only');
5 end;
6 /

Trigger created.

SQL> connect scott/tiger
Connected.

SQL> delete from emp;
delete from emp
*
ERROR at line 1:
ORA-20001: EMP is now Read-Only
ORA-06512: at "SYS.LOCK_EMP", line 2
ORA-04088: error during execution of trigger 'SYS.LOCK_EMP'

So here, I have a trigger which raises an error any time anyone attempts DML. And I do mean anyone:

SQL> connect / as sysdba
Connected.

SQL> delete from scott.emp;
delete from scott.emp
*
ERROR at line 1:
ORA-20001: EMP is now Read-Only
ORA-06512: at "SYS.LOCK_EMP", line 2
ORA-04088: error during execution of trigger 'SYS.LOCK_EMP'

Even SYS can’t get passed the effects of that trigger. Except of course for this minor detail:

SQL> drop trigger lock_emp;
Trigger dropped.

SQL> delete from scott.emp;
14 rows deleted.

Since the table is not really read-only, but only appears to be as a result of the trigger ‘intercepting’ attempts at DML, disabling or dropping the trigger means the table is open for business as usual. So here we have a mechanism which is mostly set-and-forget, and is mostly long-ish term in effect, but ultimately falls down on the ‘can it be bypassed’ test. But if all you were after was a quick-and-dirty ‘make it look read-only, and I don’t care too much that factually it really isn’t and the mechanism might be by-passed’, then this might indeed be the technique for you.

Precisely. None of the methods discussed so far truly make the table read-only to the sort of stringent standards required by lawyers, accountants and taxmen (or, the three horsemen of the apocalypse as they are fondly known). For them, only a table which is genuinely read-only, and guaranteed to stay that way is good enough. And for that, the only possible technique is to move the table into a genuinely read-only tablespace:

SQL> create tablespace ro_tables
2 datafile 'c:oracleoradatawin92ro01.dbf' size 2m;

Tablespace created.

SQL> alter table scott.emp enable table lock;
Table altered.

SQL> alter table scott.emp move tablespace ro_tables;
Table altered.

SQL> alter tablespace ro_tables read only;
Tablespace altered.

SQL> update scott.emp set sal=900;
update scott.emp set sal=900
*
ERROR at line 1:
ORA-00372: file 6 cannot be modified at this time
ORA-01110: data file 6: 'C:ORACLEORADATAWIN92RO01.DBF'

SQL> delete from scott.emp;
delete from scott.emp
*
ERROR at line 1:
ORA-00372: file 6 cannot be modified at this time
ORA-01110: data file 6: 'C:ORACLEORADATAWIN92RO01.DBF'

And here you see SYS moving the table into the new tablespace, making the tablespace truly read-only, and then finding out that all DML is prohibited on the table -forever, and without possibility of sneaky reversal.

True. But you’re really up against things at this point, asking how to make sure that your system is immune from attempts at subversion and abuse from the very person whose job it is to administer the entire thing. That’s about as hard a security challenge as it’s possible to imagine. Nevertheless, as I’ll show you, this method of making a table read-only really is (or can be) permanent and irreversible. Even short of that gold standard of assurance, though, this method has distinct advantages over any other mentioned so far.

For a start, of course a read-only tablespace can be made read-write again:

SQL> alter tablespace ro_tables read write;
Tablespace altered.

But in doing that, look what appears in the alert log:

Thu Oct 21 21:54:07 2004
Completed: alter tablespace ro_tables read write

Not only do we know the change to read-write status took place, we know when precisely it happened, too. (The switch back to read-only mode would also be logged, and timestamped... so we’d know exactly how long the tablespace had been accessible for DML statements). That means this technique, unlike a quick drop-and-recreate of a trigger, is not capable of being circumvented invisibly.

Well, you may say, you can audit the dropping of triggers, too. True enough: but that sort of auditing means records are written into a database table, called AUD$. And the DBA could delete from that table, and the necessary audit records would be lost. The alert log, however, is written into an operating system file, and simple O/S security would prevent the DBA from tampering with it -unless, of course, your DBA is also your system administrator. But if that was the case, you’re probably not interested in truly top-notch non-dodgeable auditing anyway.

The only other way to circumvent the read-only restrictions on the table would be to move the table back into a regular read-write tablespace. That would not produce an alert log trail... but the redo logs would be stuffed full of change vectors recording the move. That’s certainly not an invisble circumvention technique, either!

So the read-only tablespace technique is the only one that is relatively simple to set up, extremely long-term in effect, and mostly incapable of being circumvented -not, at least, without leaving a very obvious audit trail. If that’s the sort of iron-clad security and assurance you’re after, the other techniques don’t even come close. On the other hand, precisely because moving a large table is not a trivial operation, and hence very visible and auditable, it is a much more expensive proposition than writing a simple trigger or applying a quick VPD policy. Once again, it comes down to why you want to make a table read only. The prevention of casual DML by ordinary users can be achieved simply enough using those other techniques. The true lock-down of a table is expensive, but is capable of locking down even DBA attempts to get around them.

Precisely! This is at the very end of the security spectrum I’ve been talking about. And there is only one way to achieve that in Oracle: you make the tablespace read-only as before and then you move the data files involved onto some form of read-only media. Once the data file is physically stored on truly read-only media, then all attempts at DML will fail, with no possibility of circumvention by anyone under any circumstances.

For a table to be housed in truly, physically read-only media, it is compulsory for it to be moved into a tablespace which is then flagged as read-only. You can’t leave a table where it is, and simply burn the datafile onto a DVD, for example, because that would cause endless errors later on (checkpoints will fail, for example, if the datafile is physically incapable of being checkpointed. And future startups will fail, because the database will look out of synch to SMON). So you have to make the tablespace read-only, and then move the physical files onto a read-only medium. At that point, you have total assurance that the data cannot be tampered with.

In summary, you have an entire spectrum of security choices at your disposal.

• You could lock the table in exclusive mode. But the lock disappears, and DML resumes, when the locking session terminates.
• You could simply revoke DML privileges on a table from all users, but risk forgetting one or two, or risk the possibility of a user hacking into the database with higher privileges.
• You could create a ‘no DML’ trigger, but risk the trigger being dropped or invalidated, and hence by-passed, by an ordinary user.
• You could apply a VPD policy to the table, but that never applies to SYS
• You could make the table’s tablespace read-only, and rely on auditing to let you know if an administrator tries to change that status (capturing the culprit after the event)
• You could truly lock down the table by moving it to read-only tablespace and burning the data files involved onto physically read-only media. That is un-circumventable by anyone.

And that’s only some of the possibilities! But to come back to your original question: is there an alter table X read only command? No. Instead you have a whole smorgasboard of options, but to make your selection, you need to understand why you want the table to be read-only, and decide how much assurance you need that it will be read-only for all time and for all people. You can’t make a sensible choice without knowing those fundamentals.