How to get PASSWORD changed time for an Oracle user??

SELECT name, ctime, ptime
FROM sys.user$
WHERE name = 'TEST_SUH';

How to check if your Oracle client is 32 bit or 64 bit?

SOLUTION 1:
If logged into SQL Plus, the banner will tell you 64-bit if the 64-bit version is installed. If it does not specify 64-bit then it is 32-bit (even though it does not explicitly say so).

SOLUTION 2:
You can specifically query the info by running the following:
SELECT * FROM V$VERSION;

The banner will tell you 64-bit if the 64-bit version is installed. If it does not specify 64-bit then it is 32-bit.
SOLUTION 3:
Run the query to check the following:
select distinct address from v$sql where rownum<2;
If the address returned is 16 characters long, it is 64 bit.
If it is 32 bit you will get an 8 character address.

SOLUTION 4:
If the two directories $ORACLE_HOME/lib32 and $ORACLE_HOME/lib are existing then it is 64 bit. If there is only an ORACLE_HOME/lib directory then it is 32 bit client.
*Headsup: In newer versions of the client, the library is not included and this directory may not exist.

How to find total number of Oracle schema objects and its size??

select obj.owner "Owner", obj_cnt "Objects", decode(seg_size, NULL, 0, seg_size) "size MB" from (select owner, count(*) obj_cnt from dba_objects group by owner) obj, (select owner, ceil(sum(bytes)/1024/1024) seg_size
from dba_segments group by owner) seg where obj.owner = seg.owner(+)
order by 3 desc ,2 desc, 1;

How to run SQL Tuning Adviser manually??

Step 1: Replace SQL_ID and TASK_NAME (Unique):

DECLARE

  l_sql_tune_task_id  VARCHAR2(100);

BEGIN

  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (

                          sql_id      => 'XXXXX',

                          scope       => DBMS_SQLTUNE.scope_comprehensive,

                          time_limit  => 1800,

                          task_name   => 'XXXXX_A',

                          description => 'Tuning task for statement 8p9qh5xwgc3fx_B in AWR.');

  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);

END;

/

Step 2: Validate if SQL Tuning Task is created or not (Status should be INITIAL):

select * from dba_advisor_log  where task_name='XXXXX_A' order by task_id desc;

Step 3: Run the Advisor

EXEC DBMS_SQLTUNE.execute_tuning_task('XXXXX_A');

Step 4: Check the status in other session using command below: (It should show EXECUTING)

select * from dba_advisor_log  where task_name='XXXXX_A' order by task_id desc;

Step 5: Once the Advisor task is completed. Generate report using command below:

SELECT DBMS_SQLTUNE.report_tuning_task(‘XXXXX_A') AS recommendations FROM dual;

Step 6: Analyze the report and implement the solution if necessary.

Source: Thanks Arbind!!!

How to configure RMAN??

Here i am using ORCL as primary database and CATDB as catalog database.
Step # 1: Connect to Target database(Target DB: The database on which Backup & Recovery to be performed) as sysdba.

Step # 2: Ensure the database has been configured with ARCHIVELOG mode or not?

Step # 3: If the database has been configured with ARCHIVELOG mode then skip the Step number 3 to 6, If not then Shutdown the database.

Step # 4: Startup the database in mount state.

 Step # 5: Configure database in ARCHIVELOG mode.

Step # 6: Alter database to open state.

Step # 7: Ensure ARCHIVELOG destination.




Step # 8: Connect to RMAN prompt with target database.

Step # 9: Configure RMAN with controlfile auto-backup feature that will be auto-backup controlfile in case of major changes done in database.

Step # 10: To enable backup optimization run the following command, by default backup optimization has been configured OFF.

Step # 11: Configure retention policy for backup.

Step # 12: Connect to the recovery catalog database(RMAN Repository) & Create a tablespace to store RMAN catalog database objects. Create a RMAN user, assign RMAN tablespace to RMAN user as a default & grant recovery catalog owner,connect & resource privileges to RMAN user.

Step # 13: Connect to RMAN on target and recovery catalog database. Create catalog by issuing the following command in RMAN prompt. And then register database with recovery catalog

Step # 14: Check whether registration was successful.

Oracle Support: Coverage for Oracle Database Releases

Extended Support Fee for Oracle 11.2.0.4 waived until May 31, 2017 - Extended Support until Dec 2020

Friday, Oct 16, 2015, Oracle announced that the Extended Support for Oracle Database 11.2.0.4 will be waived until May 31, 2017. After this period of Waived Extendend Support,Extended Support for Oracle Database 11.2.0.4 will be offered until end of December 2020.

How to Prevent a User to Drop Own Objects???

CREATE OR REPLACE TRIGGER trigger_prevent_drop BEFORE DROP ON DATABASE
BEGIN
IF ora_dict_obj_type = 'VIEW' 
AND ora_dict_obj_owner = 'QO112_1616' 
AND ora_login_user = 'QO112_1616' 
AND ora_dict_obj_name='SAMPLE_NEW'
THEN
raise_application_error (-20000, 'YOU CAN NOT DROP SAMPLE_NEW VIEW!');
END IF;
END;

We will get an erorr message when trying to delete object in my case VIEW