set lines 256 set trimout on set tab off set pagesize 100 set colsep " | "
Initial configuration on a db_swonly installed system
Use dbca to create initial database as software was installed non-interactively with: oracle.install.option=INSTALL_DB_SWONLY
dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbname ORCL -sid ORCL -responseFile NO_VALUE -characterSet AL32UTF8 -memoryPercentage 30 -emConfiguration LOCAL
netca -silent -responsefile $ORACLE_HOME/assistants/netca/netca.rsp
Extend tablespace (useful for small)
alter tablespace DATA ADD datafile '/oracle/oradata/orcl/data02.dbf' size 1G autoextend on;
List all tablespaces with their associated files, the tablespace's allocated space, free space, and the next free extent
clear breaks
SET linesize 230
SET pagesize 60
break ON tablespace_name skip 1
col tablespace_name format a15
col file_name format a50
col tablespace_kb heading 'TABLESPACE|TOTAL KB'
col kbytes_free heading 'TOTAL FREE|KBYTES';
SELECT dd.tablespace_name tablespace_name, dd.file_name file_name, dd.bytes/1024/1024/1024 TABLESPACE_GB, SUM(fs.bytes)/1024/1024/1024 GBYTES_FREE, MAX(fs.bytes)/1024/1024/1024 NEXT_FREE
FROM sys.dba_free_space fs, sys.dba_data_files dd
WHERE dd.tablespace_name = fs.tablespace_name
AND dd.file_id = fs.file_id
GROUP BY dd.tablespace_name, dd.file_name, dd.bytes/1024ORDER BY dd.tablespace_name, dd.file_name;
List schema sizes
set linesize 3000
set wrap off
set termout off
set pagesize 0 embedded on
set trimspool on
select
owner,
sum(bytes)/1024/1024/1024 schema_size_gig
from
dba_segments
group by
owner;
List datafiles, tablespace names, and size in MB:
col file_name format a50 col tablespace_name format a10
SELECT file_name, tablespace_name, ROUND(bytes/1024/1024/1024) GB FROM dba_data_files ORDER BY 1;
List tablespaces, size, free space, and percent free, query originally developed by Michael Lehmann
SELECT df.tablespace_name TABLESPACE, df.total_space TOTAL_SPACE, fs.free_space FREE_SPACE, df.total_space_mb TOTAL_SPACE_MB,(df.total_space_mb - fs.free_space_mb) USED_SPACE_MB, fs.free_space_mb FREE_SPACE_MB,ROUND(100 * (fs.free_space / df.total_space),2) PCT_FREE
FROM (SELECT tablespace_name, SUM(bytes) TOTAL_SPACE,
ROUND(SUM(bytes) / 1048576) TOTAL_SPACE_MB
FROM dba_data_files
GROUP BY tablespace_name) df,
(SELECT tablespace_name, SUM(bytes) FREE_SPACE,
ROUND(SUM(bytes) / 1048576) FREE_SPACE_MB
FROM dba_free_space
GROUP BY tablespace_name) fs
WHERE df.tablespace_name = fs.tablespace_name(+)ORDER BY fs.tablespace_name;
Connections
SELECT 'Currently, ' || (SELECT COUNT(*) FROM V$SESSION) || ' out of ' || VP.VALUE || ' connections are used.' AS USAGE_MESSAGE FROM V$PARAMETER VP WHERE VP.NAME = 'sessions';
Both processes and sessions:
select resource_name, current_utilization, max_utilization, limit_value from v$resource_limit where resource_name in ('sessions', 'processes');
Tablespace sizes
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 ;
Max file size
SELECT round(4194303value/(10241024*1024),2) MaxFileSizeGByte from v$parameter where name='db_block_size';
To check if a given tablespace is a bigfile tablespace:
SELECT tablespace_name, bigfile FROM dba_tablespaces;
Check installed patches
go to ORACLE_HOME/Opatch and run opatch lsinventory
opatch lsinventory
Invoking OPatch 10.2.0.4.3
Oracle Interim Patch Installer version 10.2.0.4.3 Copyright (c) 2007, Oracle Corporation. All rights reserved.
Create tablespaces
small:
CREATE TABLESPACE data DATAFILE '/oracle/oradata/orcl/data.dbf' size 1G autoextend on;
CREATE temporary TABLESPACE temp TEMPFILE '/oracle/oradata/orcl/temp.dbf' size 10G;
big:
create bigfile tablespace DATA datafile '/oradata/data.dbf' SIZE 1M AUTOEXTEND ON EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K; ALTER DATABASE DEFAULT TABLESPACE DATA;
create bigfile tablespace INDX datafile '/oradata/indx.dbf' SIZE 1M AUTOEXTEND ON EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K; create bigfile tablespace STAGING datafile '/oradata/staging.dbf' SIZE 1M AUTOEXTEND ON EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
Create spfile from pfile
The spfile is created using the CREATE SPFILE statement; this requires connecting as SYSDBA. Connect system/manager as sysdba;
CREATE SPFILE FROM PFILE;
This command creates an spfile in a non-default location ($ORACLE_HOME/database). However, you can fully-qualify the path name is the ?create spfile? statement:
CREATE SPFILE='/u01/admin/prod/pfile/file_mydb.ora' FROM PFILE=/u01/admin/prod/pfile/initprod.ora';
Warning - After an spfile is created, when you bounce the database you may encounter an error. To get around this, you have to reconnect as SYSDBA and use the STARTUP command. The addition of the spfile has changed the search path for the Oracle startup deck. Oracle now uses the following path:
* Search for the spfile$ORACLE_SID.ora file in the default location,
* Look for the spfile.ora; and
* Search for the pfile by name init$ORACLE_SID.ora.
The number of sessions the database was configured to allow
SELECT name, value FROM v$parameter WHERE name = 'sessions' The number of sessions currently active SELECT COUNT(*) FROM v$session
Use single quote marks
SQL> create spfile from pfile="/oracle/app/product/12.1.0/dbhome_1/dbs/initORCL.ora"; create spfile from pfile="/oracle/app/product/12.1.0/dbhome_1/dbs/initORCL.ora" * ERROR at line 1: ORA-00972: identifier is too long
SQL> create spfile from pfile='/oracle/app/product/12.1.0/dbhome_1/dbs/initORCL.ora';
File created.
Processes I use the below query to find out in day what is the max limit i reach in terms of process and sessions.
col metric_unit for a30 set pagesize 100
Select trunc(end_time),max(maxval) as Maximum_Value,metric_unit from dba_hist_sysmetric_summary where metric_id in ( 2118,2119) group by trunc(end_time),metric_unit order by 1
login as sysdbasqlplus "/as sysdba" or sqlplus '/as sysdba'
show parameter sessions show parameter processes show parameter transactions
And if you are increasing sessions parameter you should consider increasing processes and transactions parameter as well. Here is the formula you can use to determine their values.
processes=x sessions=x1.1+5 transactions=sessions1.1
E.g. processes=500 sessions=555 transactions=610
alter system set processes=500 scope=both sid=''; alter system set sessions=555 scope=both sid=''; alter system set transactions=610 scope=both sid='*';
cpu_count integer 48
parallel_max_servers integer 960
parallel_min_servers integer 48
cpu_count integer 40 parallel_max_servers integer 500 =>800
parallel_min_servers integer 400 => 40
alter system set parallel_max_servers = 800 scope=both;
alter system set parallel_min_servers = 40 scope=both;
alter system set optimizer_features_enable='12.1.0.2' scope=both;
alter system set compatible='12.1.0.2' scope=spfile;
set linesize 120
set wrap off
Select name,value from v$parameter where name like 'comp%' or name like 'optimizer_f%';
Check for files in DBA_DATA_FILES or DBA_TEMP_FILES view.
alter database tempfile '/u01/oradata/TESTDB/temp01.dbf' resize 250M
alter database tempfile '/SID/oradata/data02/temp12.dbf' autoextend on maxsize 1800M;
alter tablespace TEMP add tempfile '/SID/oradata/data02/temp05.dbf' size 1800m reuse;
cpu_count integer 48
parallel_max_servers integer 960
parallel_min_servers integer 48
cpu_count integer 40 parallel_max_servers integer 500 =>800
parallel_min_servers integer 400 => 40
alter system set parallel_max_servers = 800 scope=both;
alter system set parallel_min_servers = 40 scope=both;
alter system set optimizer_features_enable='12.1.0.2' scope=both;
alter system set compatible='12.1.0.2' scope=spfile;
Select name,value from v$parameter where name like 'comp%' or name like 'optimizer_f%';