Basic queries often used for Oracle Administration

The below are the basic queries which can be used for DB administration:

To check space of tablespaces:

select tablespace_name, used_percent from dba_tablespace_usage_metrics where used_percent > 85;

To delete archivelogs :

delete noprompt archivelog until time 'SYSDATE-1';

DELETE noprompt archivelog until time "to_date('2014-07-14:01:00:00','YYYY-MM-DD:hh24:mi:ss')";

To check archivelog generation per hour :

alter session set NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';

select trunc(COMPLETION_TIME,'HH') Hour,thread# , round(sum(BLOCKS*BLOCK_SIZE)/1048576) MB,count(*) Archives from v$archived_log group by trunc(COMPLETION_TIME,'HH'),thread# order by 1 ;

select JOB,WHAT,BROKEN,FAILURES,INTERVAL from dba_jobs where what like ‘%INFI%’;

select job,what,interval from dba_jobs where what like '%xxxx%';

select * from dba_mview_refresh_times where name like '%xxxx%';

To check whether rman backup completed :

set pages 2000 lines 200
COL in_sec FORMAT a10
COL out_sec FORMAT a10
COL TIME_TAKEN_DISPLAY FORMAT a10
col output_bytes_display for a10
select session_key,
       input_type,
       status,
       to_char(start_time,'yyyy-mm-dd hh24:mi') start_time,
       to_char(end_time,'yyyy-mm-dd hh24:mi')   end_time,
       output_bytes_display,
       time_taken_display
from v$rman_backup_job_details
order by session_key asc;

To know the version of oracle apps:

select release_name from apps.fnd_product_groups;

To check space of tablespaces:

select tablespace_name, used_percent from dba_tablespace_usage_metrics where used_percent > 85;

To know the version of oracle apps :

select release_name from apps.fnd_product_groups;

If password expire or locked:

alter user username identified by passwrd account unlock;
alter user username account unlock;


NOTE: As of 11g the DBA_USERS table no longer contains encrypted passwords. Use the SYS.USER$ table and the NAME, PASSWORD columns.

select name, password, ltime from user$ order by name, exptime;


To change password of sys: (This has to be executed on comand prompt at the path orapwd file location)
 $ orapwd file=filename  password=password entries=max_users

To create controlfile :
alter database backup controlfile to trace as 'location';

Create table in oracle:

CREATE TABLE table_name(
   column1 datatype,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
   PRIMARY KEY( one or more columns )
);

Insert into table:

INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)]
VALUES (value1, value2, value3,...valueN);

Update table oracle:

UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];


To create a service in windows:

sc [<ServerName>] create [<ServiceName>] [type= {own | share | kernel | filesys | rec | interact type= {own | share}}] [start= {boot | system | auto | demand | disabled}] [error= {normal | severe | critical | ignore}] [binpath= <BinaryPathName>] [group= <LoadOrderGroup>] [tag= {yes | no}] [depend= <dependencies>] [obj= {<AccountName> | <ObjectName>}] [displayname= <DisplayName>] [password= <Password>]

To delete service in windows:

sc "\\<servername>" delete <service-name>


To change passwrd encrypted:

set pagesize 0
set long 90000
set feedback off
set echo off
select dbms_metadata.get_ddl('USER', 'xyz') from dual;

To know the tablespaces:

To view all tablespaces:

SELECT TABLESPACE_NAME,SUM(BYTES)/1024/1024/1024 "FREE SPACE(GB)" FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME;

-------------------------------

To view all tablespaces in % :

select tablespace_name, used_percent from dba_tablespace_usage_metrics where used_percent > 85;

-------------------------------

To view all tablespaces with free space :

column "Tablespace" format a13
column "Used MB"    format 99,999,999
column "Free MB"    format 99,999,999
column "Total MB"   format 99,999,999
select
   fs.tablespace_name                          "Tablespace",
   (df.totalspace - fs.freespace)              "Used MB",
   fs.freespace                                "Free MB",
   df.totalspace                               "Total MB",
   round(100 * (fs.freespace / df.totalspace)) "Pct. Free"
from
   (select
      tablespace_name,
      round(sum(bytes) / 1048576) TotalSpace
   from
      dba_data_files
   group by
      tablespace_name
   ) df,
   (select
      tablespace_name,
      round(sum(bytes) / 1048576) FreeSpace
   from
      dba_free_space
   group by
      tablespace_name
   ) fs
where
   df.tablespace_name = fs.tablespace_name;

**************

select distinct TEMPORARY_TABLESPACE from dba_users;
**************
create temporary tablespace TEMP_01 tempfile 'C:\app\oracle\oradata\devidw\xxx.DBF size 500M autoextend on maxsize 8024M;
**************
alter database default temporary tablespace TEMP_01;


To view the user which is connected:

select s.sid, s.serial#, s.status, p.spid
from v$session s, v$process p
where s.username = 'myuser'
and p.addr (+) = s.paddr;

-- alter system kill session '<sid>,<serial#>';

To know the privilege of user:

select privilege
from dba_sys_privs
where grantee='HYPHSS' order by 1;


To create user / schema:

create user <username> identified by <password>;

To grant privileges :

grant <privileges> to <username>;


To create a DB_Link:

create public database link DBlink connect to user identified by password using 'Dblink';

create public database link Link_name connect to user identified by password using 'dblink';

And to test the dblink:

select * from dual@urdblink;

To know the schema size : 

select
   sum(bytes)/1024/1024/1024 as size_in_gig,
   segment_type
from
   dba_segments
where
   owner='SCOTT'
group by
   segment_type;