You have to use the GRANT command to grant roles (with admin option if required), then use ALTER USER to set roles to default (or not). So, the order of commands is,
- grant roles (with admin option as needed)
- alter user to set all roles to non-default
- alter user to set only required roles to default.
A way to generate sql using sql is as follows, you can run this sql in instanceA, and actually execute the generated script in instanceB by replacing username if required. Make sure to test though,
with x as (select 'USER1' as usr from dual)
select cmd from (
select 1 as ord, 'grant ' || granted_role || ' to ' || grantee || case when admin_option = 'YES' then ' with admin option' end || ';' as cmd
from dba_role_privs, x where grantee = x.usr
union all
select distinct 2 as ord, 'alter user ' || grantee || ' default role none;' as cmd from dba_role_privs, x where grantee = x.usr
union all
select 3 as ord, 'alter user ' || grantee || ' default role ' || granted_role || ';' as cmd from dba_role_privs, x where grantee = x.usr and default_role = 'YES')
order by ord
In reality, on the modern devices the impact on performance is likely to be minimal.
The old "tablespace for index, tablespace for data" mantra comes from a time when storage tended to be made up of sets of single devices. I.E. if you said that you wanted to put a tablespace on a certain disk, it went onto a particular physical disk.
Having both indexes and data on the same disk increases disk contention as Oracle can't read both index and data at the same time.
However, in most reasonably specc'd servers these days a 'disk' as far as Oracle is concerned is not necessarily a single disk. Often they are striped disk arrays. In this case there often isn't an increase in disk contention as there are multiple physical disks and IO cards in the server and the indexes and data don't always clash anyway.
So, in short - it depends on your hardware set-up.
Though, to be honest, unless you're really on top of monitoring, responding to your "top SQL" reports, have good data model designers and are building the right indexes you are much more likely to find performance improvements by learning about those things first. I'm not saying you don't do those things, but if you don't you should look to improve there first.
If you think the tablespaces issue is a problem, then (as others have said) you can just move the tables and indexes around to put them back in the right place.
Once done, a good plan would be to implement some kind of standard for how you implement table and index creates, probably using substitution variables for the names of tablespaces, most certainly supported by a scripted test that runs as part of your commit / build process to check that people aren't putting things in the wrong place.
You can then follow that up with a regular process on your demo / test server that checks for things in the wrong place and sends an e-mail to the team when it discovers something. That way you stand a chance of discovering problems before they hit the live environment.
I'd use this approach for any standard that is broken more than once or twice.
As for non-performance related implications, it can have an impact on the maintainability of your tablespaces and ensuring that the datafiles are properly configured. Even that is probably negligible on modern servers.
Is should say though - I'm not a DBA, I've just worked as a developer with Oracle a LONG time - so when it comes to maintainability, I'm no expert...
Best Answer
A tablespace is a logical entity in Oracle database which consists of physical data files stored on the underlying filesystem.
We provide a quota of a certain size on the tablespace to a user. Which allows the user to create objects on it. And the user can explicitly defines on which tablespace he/she wants to create the object.
Here is a simple example which represents(as far as I understand) your scenario.