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