When check a datafiles location tablespace in schema, you need to verify the existing tablespace availability. This query will show you what’s there and how much space are free to use :
Query Check
SELECT file_name, bytes/1024/1024/1024 from dba_data_files where tablespace_name=‘TABLESPACE_NAME’;
When creating a new users in Oracle database (new schema), you need to verify the existing tablespace availability. This query will show you what’s there and how much space are free to use :
Query Check
select df.tablespace_name "Tablespace",totalusedspace "Used MB",(df.totalspace - tu.totalusedspace) "Free MB",df.totalspace "Total MB",round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace)) "Pct. Free" from (select tablespace_name, round(sum(bytes) / 1048576) TotalSpace from dba_data_files group by tablespace_name) df, (select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name from dba_segments group by tablespace_name) tu where df.tablespace_name = tu.tablespace_name ;
This encryption process works so that users who take data by way of database backup. This encryption works when the user restore the database on another server it will fail because it does not have a wallet that has been created. For more details follow these steps :
Set Master Key
alter system set "_db_discard_lost_masterkey" = true;
Create Key Management
administer key management create keystore '/u01/app/oracle/product/12.1.0/db_1/owm/wallets/oracle' identified by "passw0rd";