Oracle-Database

Från wiki.soltec.se
Hoppa till: navigering, sök

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