Oracle-Database - Versionshistorik

Js146669: /* ORAENV */

2017-09-05T20:41:05Z

‎ORAENV

Js146669: /* SQL SELECT cmds, one liners */

2017-02-04T10:55:52Z

‎SQL SELECT cmds, one liners

Js146669: /* Schemas */

2017-02-04T10:49:05Z

‎Schemas

Js146669 den 3 februari 2017 kl. 18.01

2017-02-03T18:01:33Z

Js146669 den 3 februari 2017 kl. 17.54

2017-02-03T17:54:08Z

Js146669 den 3 februari 2017 kl. 17.38

2017-02-03T17:38:06Z

Js146669 den 3 februari 2017 kl. 17.10

2017-02-03T17:10:21Z

Js146669 den 3 februari 2017 kl. 16.59

2017-02-03T16:59:57Z

Js146669: Skapade sidan med ''''Install FAQ''' : # ./runinstaller -ignoreSysPrereqs : # ./runInstaller -record -destinationFile : '''Oracle Pre-Reqs doc'''
: Oracle Support Docum...'

2017-02-03T16:44:06Z

Skapade sidan med ''''Install FAQ''' : # ./runinstaller -ignoreSysPrereqs : # ./runInstaller -record -destinationFile <responsefilename> : '''Oracle Pre-Reqs doc'''<br/> : Oracle Support Docum...'

Ny sida

'''Install FAQ'''

: # ./runinstaller -ignoreSysPrereqs
: # ./runInstaller -record -destinationFile <responsefilename>

: '''Oracle Pre-Reqs doc'''<br/>
: 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.<br/>
: 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';

@@ Rad 192: / Rad 192: @@
 == SQL SELECT cmds, one liners==
-Users
+ Gather Dbas Info commands
 :: SQL> select * from v$tablespace;
 :: SQL> select * from v$sgainfo; <- Display SGA info
@@ Rad 245: / Rad 245: @@
 :: SQL> select sum(bytes)/1024/1024 from dba_segments;
 :: SQL> select sum(bytes)/1024/1024 from dba_data_files;

@@ Rad 72: / Rad 72: @@
 :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==

← Äldre version		Versionen från 3 februari 2017 kl. 18.01
Rad 176:		Rad 176:
	:: )		:: )
	::where rownum < 11		::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==
		+	Users
		+	:: 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;

← Äldre version		Versionen från 3 februari 2017 kl. 17.54
Rad 157:		Rad 157:
	::group by free.p		::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

← Äldre version		Versionen från 3 februari 2017 kl. 17.10
Rad 121:		Rad 121:
	:: SQL> select index_name, num_rows, blevel, leaf_blocks, distinct_keys from dba_indexes where index_name='THIN_WHITE_DUKE1_CODE_I';		:: 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';		:: 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
		+	/

@@ Rad 22: / Rad 22: @@
 ==ORAENV==
-: The oraenv scriptis installed in /usr/local/bin and takes values from the oratab file located in /etc.<br/>
+: The oraenv script is installed in /usr/local/bin and takes values from the oratab file located in /etc.<br/>
 : Oratab fields entries are of the form:
 :: $ORACLE_SID:$ORACLE_HOME:<N|Y>:

@@ Rad 1: / Rad 1: @@
-'''Install FAQ'''
+==Install FAQ==
 : # ./runinstaller -ignoreSysPrereqs
@@ Rad 9: / Rad 9: @@
 : can be found at: https://support.oracle.com/epmos/faces/DocumentDisplay?id=169706.1
-'''To record sqlplus entries run the following'''
+==To record sqlplus entries run the following==
 : SQL> spool
 : SQL> spool off
 : SQL> spool < filename >
-'''Dbas login'''
+==Dbas login==
 : # ORACLE_SID=<SID>; export $ORACLE_SID
 : # echo $ORACLE_SID
@@ Rad 21: / Rad 21: @@
 : # sqlplus / as sysdba
-'''ORAENV'''
+==ORAENV==
 : The oraenv scriptis installed in /usr/local/bin and takes values from the oratab file located in /etc.<br/>
 : Oratab fields entries are of the form:
 :: $ORACLE_SID:$ORACLE_HOME:<N|Y>:
-'''Oracle startup sequence'''
+==Oracle startup sequence==
 : Oracle databases statuses sequence: SHUTDOWN -> NOMOUNT -> MOUNT -> OPEN
-'''Check dbas status'''
+==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'''
+==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'''
+==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'''
+==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
@@ Rad 49: / Rad 49: @@
 : SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED; <-- disable password expiry
-'''How to lock or unlock Oracle database user accounts'''
+==How to lock or unlock Oracle database user accounts==
 : SQL> ALTER USER <username> ACCOUNT LOCK;
 : SQL> ALTER USER <username> ACCOUNT UNLOCK;
@@ Rad 57: / Rad 57: @@
 : SQL> grant sysdba, create session to lkadmin; <-- give rights to user
-'''TABLES'''
+==TABLES==
 : In Oracle, User and Schema are same, provided user has created at least one object in it.
 :: SQL> select username from dba_users;
@@ Rad 69: / Rad 69: @@
 : The query can be filtered to return tables for a given schema by adding a where OWNER = 'some_schema' clause to the query.
-'''Schemas'''
+==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'''
+==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'''
+==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
@@ Rad 85: / Rad 85: @@
 : The query can be filtered to return packages for a specific schema by adding a where OWNER = 'some_schema' clause to the query.
-'''Procedures'''
+==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
@@ Rad 91: / Rad 91: @@
 : The query can be filtered to return procedures for a specific schema by adding a where OWNER = 'some_schema' clause to the query.
-'''Indexes'''
+==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.
@@ Rad 122: / Rad 122: @@
 :: 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'''
+==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
@@ Rad 128: / Rad 128: @@
 :: (select sum(bytes) used from dba_segments where tablespace_name='EXAMPLE') u where t.tablespace_name='EXAMPLE';
-'''Display size of database'''
+==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
@@ Rad 137: / Rad 137: @@
 ::    from sys.v_$log ) c;
-'''Display size of database, nr2'''
+==Display size of database, nr2==
 :: col "Database Size" format a20
 :: col "Free space" format a20

← Äldre version		Versionen från 3 februari 2017 kl. 16.59
Rad 37:		Rad 37:
	: SQL> column value format a300		: SQL> column value format a300
	: SQL> select value from v$parameter where name='background_dump_dest';		: 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';