How to check Tablespace on Oracle

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 ;

Continue reading

How to Getting Started With Transparent Data Encryption in Oracle 12c (Case Upgrade DB 10g))

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";

Continue reading

Transparent Data Encryption Oracle 10g Release 2

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 :

Edit sqlnet.ora ,First a wallet needs to be created that can store the encryption key . To setup TDE the location for the wallet needs to be set, directory sqlnet.ora : $ORACLE_HOME/network/admin/. Create directory encryption_wallet in your database server. example directory : /u01/app/oracle/admin/diarycoding/

Create Directory in example directory

MKDIR encryption_wallet

 

Edit sqlnet.ora

ENCRYPTION_WALLET_LOCATION=
  (SOURCE=(METHOD=FILE)(METHOD_DATA=
    (DIRECTORY=/u01/app/oracle/admin/diarycoding/encryption_wallet/)))

Continue reading