Life Cycle of SQL Tuning - SQL Profile

 

 

 

How to: when did the given database schema,last refresh using impdp or import or SQL Loader?

 

Option 1:

If schema refresh is carried out by dropping schema then Created column in DBA_USERS will give the answer.

Select username,created from dba_users where username='USR40'; USERNAME                       CREATED ------------------------------          -------------------- USR40                          28-APR-2013 17:28:16

Option 2:

If schema refresh is carried out by dropping the tables, then we can check the created and last_ddl_time in DBA_OBJECTS

SELECT OWNER,OBJECT_NAME,CREATED,LAST_DDL_TIME FROM DBA_OBJECTS WHERE OWNER='USR40' OWNER                          OBJECT_NAME                              CREATED              LAST_DDL_TIME ------------------------------ ---------------------------------------- -                       ------------------- -------------------- USR40                          XPK_ASE_USR                               28-APR-2013 18:42:09 28-APR-2013 18:42:09 USR40                          XPK_ASE_USR_ACC                       28-APR-2013 18:42:09 28-APR-2013 18:42:09 USR40                          XPK_ASE_USR_ACC_K                   28-APR-2013 18:42:09 28-APR-2013 18:42:10

How to find top 10 largest sized tables in Oracle

col    owner format a15 col    segment_name format a30 col    segment_type format a15 col    gb format 999,999,999 select  owner ,    segment_name ,    segment_type ,    gb from    (     select    owner     ,    segment_name     ,    segment_type     ,    bytes / 1024 / 1024 / 1024 "gb"     from    dba_segments     where segment_type = 'TABLE'     order    by bytes desc     ) where    rownum < 10

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired ORA-06512: at

 

Error:

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired ORA-06512: at "SIV.PKG1_CHG_DB_CNFG",

Investigation:
Find the locking sessions

select do.owner,do.object_name,do.object_type,dl.session_id,vs.serial#, vs.program,vs.machine,vs.osuser from dba_locks dl,dba_objects do,v$session vs where do.object_name ='SIV.PKG1_CHG_DB_CNFG' and do.object_type='TABLE' and dl.lock_id1 =do.object_id and vs.sid = dl.session_id;

Solution:
Kill the session locking the object then resubmit

alter system kill sessions 'sid,serial#' immediate;

Query to check the database backup details

 

The query will return the backup status and timings of database backups -

Run the query run in the database, not on the catalog.

Login as sysdba -

This script will report on all backups – full, incremental and archivelog backups -

col STATUS format a9 col hrs format 999.99 select SESSION_KEY, INPUT_TYPE, STATUS, to_char(START_TIME,'mm/dd/yy hh24:mi') start_time, to_char(END_TIME,'mm/dd/yy hh24:mi')   end_time, elapsed_seconds/3600                   hrs from V$RMAN_BACKUP_JOB_DETAILS order by session_key;

Generate DDL to create user – reverse engineer

 

Query

SQL> SET long 200000000 SQL> SELECT dbms_metadata.get_ddl('USER','SIVA') from dual;

Output

DBMS_METADATA.GET_DDL('USER','SIVA') --------------------------------------------------------------------------------    CREATE USER "SIVA" IDENTIFIED BY VALUES 'S:1A6727A1D45324587FCFCFEAD63BBD96B 8DBBE0C6D1754259FE954EB9459;0FE66EDD5C4FF3D5'       DEFAULT TABLESPACE "DATA01"       TEMPORARY TABLESPACE "TEMP"

Query to determine which privileges, grants, roles, and tablespace quotas are granted to the users

 

SET lines 120 pages 100 select * from DBA_ROLE_PRIVS where GRANTEE = 'SIVA'; select * from DBA_SYS_PRIVS where GRANTEE = 'SIVA'; select * from DBA_TAB_PRIVS where GRANTEE = ‘SIVA’'; select * from  dba_ts_quotas where username = 'SIVA';

Query to find list of users not schemas

 

select username from dba_users minus select distinct owner from dba_objects ;

Oracle Query to find listing of Schema Owners

select distinct owner from dba_objects

In Oracle Schema defined as;

A schema is a collection of logical structures of data, or schema objects.
A schema is owned by a database user and has the same name as that user.

How to find Primary Key of a table

 

SELECT cols.table_name, cols.column_name, cols.position, cons.status, cons.owner FROM all_constraints cons, all_cons_columns cols WHERE cols.table_name = 'TABLENAME' AND cons.constraint_type = 'P' AND cons.constraint_name = cols.constraint_name AND cons.owner = cols.owner AND cols.owner = 'sivaora' AND cons.owner = 'sivaora' ORDER BY cols.table_name, cols.position

This can be useful when getting error
ORA-00001: unique constraint (constraint_name) violated

How to find the open mode status of database?

Option 1:

select status from v$instance;

Option 2:

select open_mode from v$database;

Option 3:

Search Alert Log for string of “Database” or “alter database”.

How to generate AWR Report?

Method 1:  By Querying

1. Query dba_hist_snapshot to find the snap id for the interval you are looking for.

SELECT dbid, snap_id, begin_interval_time, end_interval_time from dba_hist_snapshot order by begin_interval_time;

2.

spool siv_awr_201109.lst SELECT output FROM TABLE (dbms_workload_repository.awr_report_text(3463084831, 1, 18048, 18050)); spool off

Method 2:

@?/rdbms/admin/awrrpt.sql

It is an interactive script that can be used to generate HTML or text reports. The script prompts for type of report
requested and prompts for number of days of snapshots to choose from so based on that it displays snap ids to
choose the start and end snapshot it.

@?/rdbms/admin/awrrpti.sql	Script can be used for the specified database and instance so the script in addition will prompt for dbid and instance number.
@?/rdbms/admin/awrddrpt.sql	Script can be used to generate a combine report of 2 different snapshots for comparison of performance in one report so it will prompt for two pairs of snapshot snapshots to compare against.
@?/rdbms/admin/awrinfo.sql	Script print AWR information like space usage by AWR, subset of AWR snap ids, Automatic Database Diagnostic Monitor (ADDM), Active Session History (ASH) information.

ORA-00980: synonym translation is no longer valid

 

Error:  ORA-00980: synonym translation is no longer valid

Cause:

1. Synonym created on non-existing object by mistake.

2. The synonyms referencing the object is dropped but synonym is not dropped

3. Dropped a user, but the synonyms referencing the objects owned by that dropped user.

Query to find the invalid synonyms

select * from dba_synonyms s where table_owner not in('SYSTEM','SYS') and db_link is null and not exists (select 1 from dba_objects o where s.table_owner=o.owner and s.table_name=o.object_name);

select 'drop '||decode (s.owner,'PUBLIC','PUBLIC SYNONYM ','SYNONYM'||s.owner||'.')||s.synonym_name||';' from dba_synonyms s where table_owner not in('SYSTEM','SYS') and db_link is null and not exists (select 1 from dba_objects o where s.table_owner=o.owner and s.table_name=o.object_name)

The query above generate the script to drop the synonyms whose translation is no longer valid

 

Basic Oracle Queries

1. How to check Unix O.S Version?

uname -a

2. How to check Oracle Version?

select * from v$version;

3. How to find the Start up time of Oracle Instance?

SELECT instance_name,host_name,
TO_CHAR(startup_time,'DD-MON-YYY HH:MM:SS AM')
FROM v$instance;