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

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

/