Oracle-Database

Install FAQ

# ./runinstaller -ignoreSysPrereqs

# ./runInstaller -record -destinationFile <responsefilename>

Oracle Pre-Reqs doc

Oracle Support Document 169706.1 (Oracle Database (RDBMS) on Unix AIX,HP-UX,Linux,Mac OS X,Solaris,

Tru64 Unix Operating Systems Installation and Configuration Requirements Quick Reference (8.0.5 to 11.2))

can be found at: https://support.oracle.com/epmos/faces/DocumentDisplay?id=169706.1

To record sqlplus entries run the following

SQL> spool

SQL> spool off

SQL> spool < filename >

Dbas login

# ORACLE_SID=<SID>; export $ORACLE_SID

# echo $ORACLE_SID

# sqlplus /nolog

SQL> connect / as sysdba

# sqlplus / as sysdba

ORAENV

The oraenv script is installed in /usr/local/bin and takes values from the oratab file located in /etc.

Oratab fields entries are of the form:

$ORACLE_SID:$ORACLE_HOME:<N|Y>:

Oracle startup sequence

Oracle databases statuses sequence: SHUTDOWN -> NOMOUNT -> MOUNT -> OPEN

Check dbas status

SQL> set lines 200 pages 200

SQL> select instance_name,host_name,status from v$instance;

Display alert log for a database

SQL> set linesize 300

SQL> column value format a300

SQL> select value from v$parameter where name='background_dump_dest';

Oracle User info

SQL> select username from dba_users;

SQL> SELECT username, account_status FROM dba_users;

SQL> SELECT username, account_status FROM dba_users where username='GI_STUDY_CODE';

Expired Accounts

SQL> select username, account_status from dba_users where ACCOUNT_STATUS LIKE '%EXPIRED%';

SQL> ALTER USER scott IDENTIFIED BY password; <--unexpire an account by assigning it a new password

SQL> ALTER USER scott ACCOUNT UNLOCK; <-- unlock an account

SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED; <-- disable password expiry

How to lock or unlock Oracle database user accounts

SQL> ALTER USER <username> ACCOUNT LOCK;

SQL> ALTER USER <username> ACCOUNT UNLOCK;

SQL> ALTER USER <username> IDENTIFIED BY <new_password>; <-- Issue the following SQL command to change a user's password:

SQL> show user;

SQL> create user lkadmin identified by lkadm123; <-- create a user

SQL> grant sysdba, create session to lkadmin; <-- give rights to user

TABLES

In Oracle, User and Schema are same, provided user has created at least one object in it.

SQL> select username from dba_users;

Will give you list of all users but all users may not have created objects in it.

SQL> select distinct owner from dba_objects;

Will give you the list of schemas available.

This is a query to get all Oracle tables that can be viewed by the current user.

SQL> select TABLE_NAME, OWNER from SYS.ALL_TABLES order by OWNER, TABLE_NAME;

The query can be filtered to return tables for a given schema by adding a where OWNER = 'some_schema' clause to the query.

Schemas

This is a query to get all Oracle schemas in an Oracle database instance.

SQL> select USERNAME from SYS.ALL_USERS order by USERNAME;

OR this one

SQL> select distinct owner from dba_segments where owner in (select username from dba_users where default_tablespace not in ('SYSTEM','SYSAUX'));

Views

This is a query to get all Oracle views that can be viewed by the current user.

SQL> select VIEW_NAME, OWNER from SYS.ALL_VIEWS order by OWNER, VIEW_NAME;

The query can be filtered to return views for a specific schema by adding a where OWNER = 'some_schema' clause to the query.

Packages

This is a query to get all Oracle packages that can be viewed by the current user.

SQL> select OBJECT_NAME, OWNER from SYS.ALL_OBJECTS where UPPER(OBJECT_TYPE) = 'PACKAGE' order by OWNER, OBJECT_NAME

To query for package bodies, substitute PACKAGE BODY for PACKAGE.

The query can be filtered to return packages for a specific schema by adding a where OWNER = 'some_schema' clause to the query.

Procedures

This is a query to get all Oracle procedures that can be viewed by the current user.

SQL> select OBJECT_NAME, OWNER from SYS.ALL_OBJECTS where upper(OBJECT_TYPE) = upper('PROCEDURE') order by OWNER, OBJECT_NAME

The query can be filtered to return procedures for a specific schema by adding a where OWNER = 'some_schema' clause to the query.

Indexes

An index is a performance-tuning method of allowing faster retrieval of records.

An index creates an entry for each value that appears in the indexed columns.

By default, Oracle creates B-tree indexes.

you are not restricted to creating indexes on only columns. You can create function-based indexes.

This is a query to get all Oracle indexes.

SQL> select INDEX_NAME, TABLE_NAME, TABLE_OWNER from SYS.ALL_INDEXES order by TABLE_OWNER, TABLE_NAME, INDEX_NAME

SQL> select INDEX_NAME, TABLE_NAME from SYS.ALL_INDEXES order by INDEX_NAME

SQL> SELECT * FROM user_indexes;

Searching for index 'all_struct_mol_idx1'

SQL> select INDEX_NAME, TABLE_NAME, TABLE_OWNER from SYS.ALL_INDEXES where index_name = 'all_struct_mol_idx1'

SQL> select table_name from all_indexes where index_name = 'all_struct_mol_idx1';

If you forgot to collect statistics on the index when you first created it or you want to update the statistics,

you can always use the ALTER INDEX command to collect statistics at a later date.

How to collect statistics for an index in Oracle/PLSQL.

SQL> ALTER INDEX supplier_idx REBUILD COMPUTE STATISTICS;

Rename an index in Oracle/PLSQL:

SQL> ALTER INDEX index_name RENAME TO new_index_name;

Dropping an index in Oracle/PLSQL:

SQL> DROP INDEX index_name;

SQL> select dbms_metadata.get_ddl('INDEX','all_struct_mol_idx1') from dual;

SQL> select index_name, num_rows, blevel, leaf_blocks, distinct_keys from dba_indexes where index_name='THIN_WHITE_DUKE1_CODE_I';

SQL> select table_name, num_rows, blocks, avg_space, avg_row_len, last_analyzed from dba_tables where table_name='THIN_WHITE_DUKE1';

Display tablespace info

SQL> select t.tablespace_name name, d.allocated, u.used, f.free, t.status, d.cnt, contents, t.extent_management extman,

t.segment_space_management segman from dba_tablespaces t, (select sum(bytes) allocated, count(file_id) cnt from dba_data_files

where tablespace_name='EXAMPLE') d, (select sum(bytes) free from dba_free_space where tablespace_name='EXAMPLE') f,

(select sum(bytes) used from dba_segments where tablespace_name='EXAMPLE') u where t.tablespace_name='EXAMPLE';

Display size of database

SQL> select a.data_size+b.temp_size+c.redo_size "total_size in MB"

from ( select sum(bytes)/1024/1024 data_size

from dba_data_files ) a,

( select nvl(sum(bytes)/1024/1024,0) temp_size

from dba_temp_files ) b,

( select sum(bytes)/1024/1024 redo_size

from sys.v_$log ) c;

Display size of database, nr2

col "Database Size" format a20

col "Free space" format a20

col "Used space" format a20

select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"

, round(sum(used.bytes) / 1024 / 1024 / 1024 ) -

round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"

, round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"

from (select bytes

from v$datafile

union all

select bytes

from v$tempfile

union all

select bytes

from v$log) used

, (select sum(bytes) as p

from dba_free_space) free

group by free.p

/

Show the ten largest objects in the database

col owner format a15

col segment_name format a30

col segment_type format a15

col mb format 999,999,999

select owner

, segment_name

, segment_type

, mb

from (

select owner

, segment_name

, segment_type

, bytes / 1024 / 1024 "MB"

from dba_segments

order by bytes desc

)

where rownum < 11

To see where Oracle believes the Flash Recovery Area is

SQL> select substr(Name,1,30) Name,(SPACE_LIMIT/1024/1024/1024)

Space_Limit_GB, SPACE_USED/1024/1024/1024

Space_Used_GB, SPACE_RECLAIMABLE, NUMBER_OF_FILES from v$recovery_file_dest;

SQL> show parameter DB_RECOVERY_FILE_DEST;

how to make a change in $ORACLE_HOME/dbs/spfile<sid>

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='/oracledb/oracle/flash_recovery_area' scope=both;

SQL> commit;

SQL SELECT cmds, one liners

Gather Dbas Info commands

SQL> select * from v$tablespace;

SQL> select * from v$sgainfo; <- Display SGA info

SQL> select log_mode FROM v$database; <-- display if in archivelog mode (dgmgrl)

SQL> select logins from v$instance; <-- display if database is in restricted mode

SQL> select username from all_users;

SQL> select * from v$pwfile_users; with passwds

SQL> select distinct machine from v$session;

SQL> SELECT SID, SERIAL#, INST_ID FROM GV$SESSION WHERE USERNAME='SCOTT'; <-- Display open sessions

SQL> select name from v$database;

SQL> select * from all_users;

SQL> select * from dba_users;

SQL> select * from DBA_ROLES

SQL> select file_name from dba_data_files;

SQL> select tablespace_name from dba_tablespaces;

SQL> select FILE_NAME, BLOCKS, TABLESPACE_NAME from DBA_DATA_FILES; <-- To list the names, sizes, and associated tablespaces of a database

SQL> select * from V$OPTION where PARAMETER = 'Partitioning'; <--- Display whether or not partition has truly been installed.

SQL> select * from v$version; <--retrieve all version information

SQL> select * FROM v$version WHERE banner LIKE 'Oracle%'; <--Display only version info

SQL> SELECT UNIQUE(TABLE_NAME) FROM USER_TAB_COLUMNS <-- show all tables in an Oracle tablespace

SQL> desc dba_tables; <-- Show options for dba_tables to build select statement from.

SQL> select CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME from user_constraints where constraint_name like '%FK%'; <- Display FK constraints

SQL> select value from v$parameter where name = 'background_dump_dest'; <-- Display location of alert log

SQL> select t.name,d.name,d.bytes from v$tablespace t join v$datafile d on t.ts#=d.ts# order by t.name; <-- datafiles and tablespaces and sizes.

SQL> select * from v$controlfile; <-- Location of all controlfile copies

SQL> select instance_name from v$instance; <-- Display instance name

SQL> select program from v$process order by program; <-- Display dbas processes that are running

SQL> select segment_type,count(1) from dba_segments group by segment_type order by segment_type; <-- Display segment types in database.

SQL> select name,block_size*file_size_blks bytes from v$controlfile; <-- Display size of control files in blocks.

SQL> select member,bytes from v$log join v$logfile using (group#); <-- Display size of online redo log files.

SQL> select group#,sequence#,members,status from v$log; <-- Display redo log file groups

SQL> select group#,status,member from v$logfile; <-- Display individual redo log files

SQL> select name,bytes from v$datafile union all select name,bytes from v$tempfile; <-- Display name and size of data- and tempfiles

SQL> select distinct status from v$datafile; <-- Display if datafile is online

SQL> select comp_id from dba_registry where comp_id='ODM' or comp_id='AMD'; <-- If the query does not return ODM, then you do not have Oracle Data Mining installed

SQL> @$ORACLE_HOME/rdbms/admin/dminst.sql SYSAUX TEMP <-- To install Data Mining

SQL> select comp_id from dba_registry where comp_id='ODM' or comp_id='AMD'; <-- If the query does not return AMD, then you do not have OLAP installed.

SQL> @$ORACLE_HOME/olap/admin/olap.sql SYSAUX TEMP <-- To install OLAP

SQL> select comp_name, status, version from dba_registry; <-- check for current db status following the upgrade is the dba_registry table.

SQL> SELECT LOG_MODE FROM SYS.V$DATABASE; <-- Display archivelog mode

SQL> select banner from v$version; <-- Display version

SQL> select * from v$timezone_file; <-- Check the Timezone version

SQL> select to_char(startup_time, 'HH24:MI DD-MON-YY') "Startup time" from v$instance; <-- Startup time

SQL> select status from v$instance;

SQL> SELECT name, value FROM v$parameter WHERE name = 'compatible';

SQL> select * from dba_tablespaces where tablespace_name = 'TEMP'; <-- Display TEMP tablespace info

SQL> select * from dba_tablespaces; <-- Display info on all tablespaces.

SQL> select * from registry$history; <-- Display commands log

SQL> set lines 500

SQL> select d.name, i.instance_name; i.host_name; d.open_mode from v$instance i, v$database d; <-- Display dbase status

SQL> select distinct owner from dba_segments where owner in (select username from dba_users where default_tablespace not in ('SYSTEM','SYSAUX')); <-- Display all Schemas

SQL> select sum(bytes)/1024/1024 from dba_segments;

SQL> select sum(bytes)/1024/1024 from dba_data_files;

ALTER Dbas commands

SQL> Alter SYSTEM

SQL> Alter database

SQL> Alter session

SQL> alter table <tablename> allocate extent storage (datafile '<filename>'); <-- Force ORA to allocate another extent to the segment.

SQL> alter tablespace <tablespacename> offline [normal | immediate | temporary]; <-- take a tablespace offline

SQL> alter tablespace <tablespacename> [read only | read write]; <-- make a tablespace read only or RW.

SQL> alter tablespace <tablespaceoldname> rename to <tablespacenewname>; <-- Rename a tablespace.

SQL> alter database datafile '/u02/oracle/10.2/CQ/data/CQGGSN11.DBF' online; <-- make a file online

SQL> ALTER USER <username> IDENTIFIED BY <new_password>; <-- Issue the following SQL command to change a user's password:

SQL> ALTER DATABASE OPEN UPGRADE;

SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION; <-- ORA-12526, TNS:listener: all appropriate instances are in restricted mode

Any users connected to the Oracle instance when going into restricted mode will remain connected;

they must be manually disconnected from the database by exiting gracefully or by the DBA with

the ìalter system kill sessionî command.

SQL> alter system enable restricted session;

SQL> alter system kill session 'session-id, session-serial';

The session-id and session-serial parameters are found in the v$session view (columns sid and serial#).

SQL> ALTER system switch logfile; <-- Activate new/different log file

SQL> ALTER system checkpoint; <-- force checkpoint position to update