Re: How to change default tablespace of self

Praveen, The Id now has the ability to do all functions included with ALTER USER such as lock, unlock, and change passwords for any user on the database including the DBA ID's.

When you want to selectively grant a system privilege you generally encapsulate it in a stored procedure created under a priviled ID and grant EXECUTE on the procedure to the target users. This was you can ensure that the routine only issues "alter user default tablespace x" commands and not "alter user x identified by zzz" commands. You can include logic or a query that controls what tablespaces a user is switched between.

Generally speaking all your file systems should be laid out accross you disks in such a manner that there is no IO performance difference for an IO to one file over another. As such there should be no benefit from separating your tables in the manner you describe unless what you really mean is that you are placing one set of heavy hit objects on one disk stripe. Then placing another set of heavy hit tables on a different disk stripe where the two stripe are made up of different physical drives so to spread database data IO for concurrent sessions out accross the available physical database disks. If both tablespaces are on the same logical devices then the separation for performance purposes is a waste of time since in fact no performance benefit exists.

HTH -- Mark D Powell -- Received on Thu Oct 20 2005 - 11:45:58 CDT