1. 11g의 $ORACLE_HOME/rdbms/admin/utlu112i.sql 을 10g의 $ORACLE_HOME/rdbms/admin/로 복사
2. 10g에서 실행
SQL> @$ORACLE_HOME/rdbms/admin/utlu112i.sql
Oracle Database 11.2 Pre-Upgrade Information Tool 02-20-2012 10:26:31
Script Version: 11.2.0.2.0 Build: 001
.
**********************************************************************
Database:
**********************************************************************
--> name: TESTDB
--> version: 10.2.0.5.0
--> compatible: 10.2.0.5.0
--> blocksize: 8192
--> platform: Linux IA (32-bit)
--> timezone file: V4
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 687 MB
--> UNDOTBS1 tablespace is adequate for the upgrade.
.... minimum required size: 472 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 442 MB
--> EXAMPLE tablespace is adequate for the upgrade.
.... minimum required size: 62 MB
--> TMP tablespace is adequate for the upgrade.
.... minimum required size: 61 MB
.
**********************************************************************
Flashback: OFF
**********************************************************************
**********************************************************************
Update Parameters: [Update Oracle Database 11.2 init.ora or spfile]
Note: Pre-upgrade tool was run on a lower version 32-bit database.
**********************************************************************
--> If Target Oracle is 32-Bit, refer here for Update Parameters:
WARNING: --> "sga_target" needs to be increased to at least 412 MB
.
--> If Target Oracle is 64-Bit, refer here for Update Parameters:
WARNING: --> "sga_target" needs to be increased to at least 596 MB
.
**********************************************************************
Renamed Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
-- No renamed parameters found. No changes are required.
.
**********************************************************************
Obsolete/Deprecated Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
--> background_dump_dest 11.1 DEPRECATED replaced by "diagnostic_dest"
--> user_dump_dest 11.1 DEPRECATED replaced by "diagnostic_dest"
.
**********************************************************************
Components: [The following database components will be upgraded or installed]
**********************************************************************
--> Oracle Catalog Views [upgrade] VALID
--> Oracle Packages and Types [upgrade] VALID
--> JServer JAVA Virtual Machine [upgrade] VALID
--> Oracle XDK for Java [upgrade] VALID
--> Oracle Workspace Manager [upgrade] VALID
--> OLAP Analytic Workspace [upgrade] VALID
--> OLAP Catalog [upgrade] VALID
--> EM Repository [upgrade] VALID
--> Oracle Text [upgrade] VALID
--> Oracle XML Database [upgrade] VALID
--> Oracle Java Packages [upgrade] VALID
--> Oracle interMedia [upgrade] VALID
--> Spatial [upgrade] VALID
--> Data Mining [upgrade] VALID
--> Expression Filter [upgrade] VALID
--> Rule Manager [upgrade] VALID
--> Oracle OLAP API [upgrade] VALID
.
**********************************************************************
Miscellaneous Warnings
**********************************************************************
WARNING: --> Database is using a timezone file older than version 14.
.... After the release migration, it is recommended that DBMS_DST package
.... be used to upgrade the 10.2.0.5.0 database timezone version
.... to the latest version which comes with the new release.
WARNING: --> Database contains INVALID objects prior to upgrade.
.... The list of invalid SYS/SYSTEM objects was written to
.... registry$sys_inv_objs.
.... The list of non-SYS/SYSTEM objects was written to
.... registry$nonsys_inv_objs.
.... Use utluiobj.sql after the upgrade to identify any new invalid
.... objects due to the upgrade.
.... USER PUBLIC has 7 INVALID objects.
.... USER SYSTEM has 1 INVALID objects.
.... USER SYSMAN has 17 INVALID objects.
.... USER OE has 5 INVALID objects.
.... USER BI has 8 INVALID objects.
.... USER SYS has 15 INVALID objects.
.... USER WMSYS has 5 INVALID objects.
WARNING: --> EM Database Control Repository exists in the database.
.... Direct downgrade of EM Database Control is not supported. Refer to the
.... Upgrade Guide for instructions to save the EM data prior to upgrade.
WARNING: --> Your recycle bin contains 3 object(s).
.... It is REQUIRED that the recycle bin is empty prior to upgrading
.... your database. The command:
PURGE DBA_RECYCLEBIN
.... must be executed immediately prior to executing your upgrade.
.
**********************************************************************
Recommendations
**********************************************************************
Oracle recommends gathering dictionary statistics prior to
upgrading the database.
To gather dictionary statistics execute the following command
while connected as SYSDBA:
EXECUTE dbms_stats.gather_dictionary_stats;
**********************************************************************
Oracle recommends reviewing any defined events prior to upgrading.
To view existing non-default events execute the following commands
while connected AS SYSDBA:
Events:
SELECT (translate(value,chr(13)||chr(10),' ')) FROM sys.v$parameter2
WHERE UPPER(name) ='EVENT' AND isdefault='FALSE'
Trace Events:
SELECT (translate(value,chr(13)||chr(10),' ')) from sys.v$parameter2
WHERE UPPER(name) = '_TRACE_EVENTS' AND isdefault='FALSE'
Changes will need to be made in the init.ora or spfile.
**********************************************************************
3. 위메시지를 토대로 tablespace size 수정
TABLESPACE_NAME FILE_NAME BYTES/1024/1024 AUTOEX
--------------- --------------------------------------------- --------------- ------
USERS /home/oracle/oradata/testdb/users01.dbf 8.75 YES
SYSAUX /home/oracle/oradata/testdb/sysaux01.dbf 240 YES
UNDOTBS1 /home/oracle/oradata/testdb/undotbs01.dbf 40 YES
SYSTEM /home/oracle/oradata/testdb/system01.dbf 460 YES
EXAMPLE /home/oracle/oradata/testdb/example01.dbf 100 YES
KOO /home/oracle/oradata/testdb/koo01.dbf 10 YES
UNDO /home/oracle/oradata/testdb/undo01.dbf 28.6875 YES
TABLESPACE_NAME FILE_NAME MB AUTOEX
--------------- --------------------------------------------- ---------- ------
TEMP /home/oracle/oradata/testdb/temp01.dbf 20 YES
KOO_TEMP /home/oracle/oradata/testdb/koo_temp.dbf 10 NO
TMP /home/oracle/oradata/testdb/tmp01.dbf 13 YES
원래사이즈
수정
SQL> alter database datafile '/home/oracle/oradata/testdb/system01.dbf' resize 700M;
Database altered.
SQL> alter database datafile '/home/oracle/oradata/testdb/undotbs01.dbf' resize 500M;
Database altered.
SQL> alter database datafile '/home/oracle/oradata/testdb/sysaux01.dbf' resize 450M;
Database altered.
SQL> alter database datafile '/home/oracle/oradata/testdb/tmp01.dbf' resize 65M;
Database altered.
4.
SQL> @?/rdbms/admin/utlrp.sql
COMP_TIMESTAMP UTLRP_BGN 2012-02-20 10:36:38
DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC> objects in the database. Recompilation time is proportional to the
DOC> number of invalid objects in the database, so this command may take
DOC> a long time to execute on a database with a large number of invalid
DOC> objects.
DOC>
DOC> Use the following queries to track recompilation progress:
DOC>
DOC> 1. Query returning the number of invalid objects remaining. This
DOC> number should decrease with time.
DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC> 2. Query returning the number of objects compiled so far. This number
DOC> should increase with time.
DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC> This script automatically chooses serial or parallel recompilation
DOC> based on the number of CPUs available (parameter cpu_count) multiplied
DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC> On RAC, this number is added across all RAC nodes.
DOC>
DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC> recompilation. Jobs are created without instance affinity so that they
DOC> can migrate across RAC nodes. Use the following queries to verify
DOC> whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC> 1. Query showing jobs created by UTL_RECOMP
DOC> SELECT job_name FROM dba_scheduler_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC> 2. Query showing UTL_RECOMP jobs that are running
DOC> SELECT job_name FROM dba_scheduler_running_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#
COMP_TIMESTAMP UTLRP_END 2012-02-20 10:36:52
DOC> The following query reports the number of objects that have compiled
DOC> with errors (objects that compile with errors have status set to 3 in
DOC> obj$). If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#
0
DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#
0
SQL> select substr(owner,1,12) owner,
substr(object_name,1,30) object,
substr(object_type,1,30) type, status from
dba_objects where status <> 'VALID';
no rows selected
5. Deprecated CONNECT role
SQL> l
1 SELECT grantee FROM dba_role_privs
2 WHERE granted_role = 'CONNECT' and
3 grantee NOT IN (
4 'SYS', 'OUTLN', 'SYSTEM', 'CTXSYS', 'DBSNMP',
5 'LOGSTDBY_ADMINISTRATOR', 'ORDSYS',
6 'ORDPLUGINS', 'OEM_MONITOR', 'WKSYS', 'WKPROXY',
7 'WK_TEST', 'WKUSER', 'MDSYS', 'LBACSYS', 'DMSYS',
8 'WMSYS', 'OLAPDBA', 'OLAPSVR', 'OLAP_USER',
9 'OLAPSYS', 'EXFSYS', 'SYSMAN', 'MDDATA',
10* 'SI_INFORMTN_SCHEMA', 'XDB', 'ODM')
SQL> /
PM
IX
KOO
SCOTT
SQL> revoke connect from pm;
SQL> revoke connect from ix;
SQL> revoke connect from koo;
SQL> revoke connect from scott;
6. DBLINK script 생성
SQL> SELECT 'CREATE '||DECODE(U.NAME,'PUBLIC','public ')||'DATABASE LINK '||CHR(10)
||DECODE(U.NAME,'PUBLIC',Null, 'SYS','',U.NAME||'.')|| L.NAME||chr(10)
||'CONNECT TO ' || L.USERID || ' IDENTIFIED BY "'||L.PASSWORD||'" USING '''||L.HOST||''''
||chr(10)||';' TEXT
FROM SYS.LINK$ L, SYS.USER$ U
WHERE L.OWNER# = U.USER#;
no rows selected
7. Check for TIMESTAMP WITH TIMEZONE Datatype
SQL> SELECT 'CREATE '||DECODE(U.NAME,'PUBLIC','public ')||'DATABASE LINK '||CHR(10)
||DECODE(U.NAME,'PUBLIC',Null, 'SYS','',U.NAME||'.')|| L.NAME||chr(10)
||'CONNECT TO ' || L.USERID || ' IDENTIFIED BY "'||L.PASSWORD||'" USING '''||L.HOST||''''
||chr(10)||';' TEXT
FROM SYS.LINK$ L, SYS.USER$ U
WHERE L.OWNER# = U.USER#;
no rows selected
SQL> select * from v$timezone_file;
FILENAME VERSION
------------ ----------
timezlrg.dat 4
SQL> SELECT CASE COUNT(DISTINCT(tzname))
WHEN 183 then 1
WHEN 355 then 1
WHEN 347 then 1
WHEN 377 then 2
WHEN 186 then CASE COUNT(tzname) WHEN 636 then 2 WHEN 626 then 3 ELSE 0 END
WHEN 185 then 3
WHEN 386 then 3
WHEN 387 then case COUNT(tzname) WHEN 1438 then 3 ELSE 0 end
WHEN 391 then case COUNT(tzname) WHEN 1457 then 4 ELSE 0 end
WHEN 392 then case COUNT(tzname) WHEN 1458 then 4 ELSE 0 end
WHEN 188 then case COUNT(tzname) WHEN 637 then 4 ELSE 0 end
WHEN 189 then case COUNT(tzname) WHEN 638 then 4 ELSE 0 end
ELSE 0 end VERSION
FROM v$timezone_names;
VERSION
----------
4
8. Optimizer Statistics (Dictionary 객체에 대한 통계 정보를 수집한다)
SQL> exec dbms_stats.gather_schema_stats('SYS', options=>'GATHER' -
> , estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt=>'FOR ALL COLUMNS SIZE AUTO', -
> cascade=>TRUE);
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_schema_stats('WMSYS', options=>'GATHER' -
> , estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt=>'FOR ALL COLUMNS SIZE AUTO', -
> cascade=>TRUE);
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_schema_stats('OLAPSYS', options=>'GATHER', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt=>'FOR ALL COLUMNS SIZE AUTO',cascade=>TRUE);
PL/SQL procedure successfully completed.
exec dbms_stats.gather_schema_stats('SYSMAN', options=>'GATHER', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt=>'FOR ALL COLUMNS SIZE AUTO',cascade=>TRUE);
exec dbms_stats.gather_schema_stats('CTXSYS', options=>'GATHER', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt=>'FOR ALL COLUMNS SIZE AUTO',cascade=>TRUE);
exec dbms_stats.gather_schema_stats('XDB', options=>'GATHER', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt=>'FOR ALL COLUMNS SIZE AUTO',cascade=>TRUE);
exec dbms_stats.gather_schema_stats('MDSYS', options=>'GATHER', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt=>'FOR ALL COLUMNS SIZE AUTO',cascade=>TRUE);
PL/SQL procedure successfully completed.
9. Disable Oracle Database Vault
10. XDB.MIGR9202STATUS가 존재한다면 삭제 요망
SQL> select * from XDB.MIGR9202STATUS;
N
----------
1000
SQL> drop table XDB.MIGR9202STATUS;
Table dropped.
11. corrupt된 Dictionary check
Set verify off
Set space 0
Set line 120
Set heading off
Set feedback off
Set pages 1000
Spool analyze.sql
SELECT 'Analyze cluster "'||cluster_name||'" validate structure cascade;'
FROM dba_clusters
WHERE owner='SYS'
UNION
SELECT 'Analyze table "'||table_name||'" validate structure cascade;'
FROM dba_tables
WHERE owner='SYS'
AND partitioned='NO'
AND (iot_type='IOT' OR iot_type is NULL)
UNION
SELECT 'Analyze table "'||table_name||'" validate structure cascade into invalid_rows;'
FROM dba_tables
WHERE owner='SYS'
AND partitioned= 'YES';
spool off
실행
SQL> @$ORACLE_HOME/rdbms/admin/utlvalid.sql
create table INVALID_ROWS (
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
SQL> @analyze.sql
SP2-0042: unknown command "SQL>" - rest of line ignored.
SP2-0734: unknown command beginning "SQL> SELEC..." - rest of line ignored.
SP2-0734: unknown command beginning "SQL> spool..." - rest of line ignored.
실행
12. 현재 사용중인 snapshot 존재 유무 check 있다면 정지시킴
SELECT DISTINCT(TRUNC(last_refresh))
FROM dba_snapshot_refresh_times;
13. 백업 및 복구중인 파일이 있는지 확인
SQL> SELECT * FROM v$recover_file;
no rows selected
SQL> SELECT * FROM v$backup WHERE status != 'NOT ACTIVE';
no rows selected
14. Listener stop
SQL> !lsnrctl stop
15. 현재 트랜잭션 check
SQL> select * from dba_2pc_pending;
만일 리턴되는 row가 있으면 아래와 같이 수행
SQL> SELECT local_tran_id
FROM dba_2pc_pending;
SQL> EXECUTE dbms_transaction.purge_lost_db_entry('');
SQL> COMMIT;
16. batch, cron job disable
17. sys와 system 유저의 default tablespace check (SYSTEM tablespace)
SELECT username, default_tablespace
FROM dba_users
WHERE username in ('SYS','SYSTEM');
(둘다 SYSTEM이어야함)
18. aud$ table이 system tablespace에 존재하는지 check
SQL> SELECT tablespace_name
FROM dba_tables
WHERE table_name= 'AUD$';
SYSTEM
19. Check whether database has any externally authenticated SSL users
SQL> SELECT name FROM sys.user$
WHERE ext_username IS NOT NULL
AND password = 'GLOBAL';
no rows selected
20. 파일들 위치 check
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/home/oracle/oradata/testdb/control01.ctl
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/home/oracle/oradata/testdb/system01.dbf
/home/oracle/oradata/testdb/undotbs01.dbf
/home/oracle/oradata/testdb/sysaux01.dbf
/home/oracle/oradata/testdb/users01.dbf
/home/oracle/oradata/testdb/example01.dbf
/home/oracle/oradata/testdb/koo01.dbf
/home/oracle/oradata/testdb/undo01.dbf
7 rows selected.
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/home/oracle/oradata/testdb/redo03.log
/home/oracle/oradata/testdb/redo02.log
/home/oracle/oradata/testdb/redo01.log
21. shutdown immediate
22. init<SID>.ora backup
23. 아카이브 모드라면 아카이브 파일 떨어질 공간 충분히 확보
24. .bash_profile 수정
export ORACLE_BASE=/app/oracle
#export ORACLE_BASE=/home/oracle
#export ORACLE_HOME=$ORACLE_BASE/product/10g
export ORACLE_HOME=$ORACLE_BASE/product/11g
25. /etc/oratab 수정
#testdb:/home/oracle/product/10g:N
testdb:/app/oracle/product/11g:N
----------------------------------------------------------------
업그래이드하기
1. 10g의 init file, password file, listener.ora, tnsnames.ora를 11g로 copy
2. 복사한 init 파일을 utlul112i.sql 실행 결과를 참조하여 수정
*.sga_target=432013312 수정
background_dump_dest, user_dump_dest 삭제 후
*.diagnostic_dest='/app/oracle' 추가
나머지는 다 그대로(컨트롤파일경로 등등)
3. sqlplus / as sysdba 접속 후 startup upgrade
4. open 되면 11g쪽 catupgrd.sql 수행
완료되면 위 스크립트에 의해 자동 shutdown database 됨.
flushes all caches, clears buffers, and performs other housekeeping activities를 위해...
5. DB startup
6. upgrade 된 componets 확인
@?/rdbms/admin/utlu112s.sql
SQL> @?/rdbms/admin/utlu112s.sql
.
Oracle Database 11.2 Post-Upgrade Status Tool 02-20-2012 21:04:35
.
Component Status Version HH:MM:SS
.
Oracle Server
. VALID 11.2.0.2.0 00:11:24
JServer JAVA Virtual Machine
. VALID 11.2.0.2.0 00:03:13
Oracle Workspace Manager
. ORA-00942: table or view does not exist
. ORA-06512: at "WMSYS.OWM_MIG_PKG", line 1579
. ORA-06512: at "WMSYS.OWM_MIG_PKG", line 1596
. ORA-06512: at line 1
. VALID 11.2.0.2.0 00:00:30
OLAP Analytic Workspace
. VALID 11.2.0.2.0 00:00:20
OLAP Catalog
. VALID 11.2.0.2.0 00:00:43
Oracle OLAP API
. VALID 11.2.0.2.0 00:00:34
Oracle Enterprise Manager
. VALID 11.2.0.2.0 00:07:52
Oracle XDK
. VALID 11.2.0.2.0 00:01:37
Oracle Text
. VALID 11.2.0.2.0 00:00:43
Oracle XML Database
. VALID 11.2.0.2.0 00:03:57
Oracle Database Java Packages
. VALID 11.2.0.2.0 00:00:22
Oracle Multimedia
. VALID 11.2.0.2.0 00:03:51
Spatial
. VALID 11.2.0.2.0 00:04:41
Oracle Expression Filter
. VALID 11.2.0.2.0 00:00:11
Oracle Rules Manager
. VALID 11.2.0.2.0 00:00:09
Gathering Statistics
. 00:02:45
Total Upgrade Time: 00:43:02
PL/SQL procedure successfully completed.
7.@?/rdbms/admin/catuppst.sql
8. invalid check (utlrp.sql)
SQL> select substr(owner,1,12) owner,
substr(object_name,1,30) object,
substr(object_type,1,30) type, status from
dba_objects where status <> 'VALID';
5903 rows selected.
SQL> @?/rdbms/admin/utlrp.sql
실행 후
select substr(owner,1,12) owner,
substr(object_name,1,30) object,
substr(object_type,1,30) type, status from
dba_objects where status <> 'VALID';
no rows selected << INVALID 가 모두 사라짐
업그레이드 완료!
9. upgrade statistics tables created by the DBMS_STATS packgage
DBMS_STATS.CREATE_STAT_TABLE procedure를 이용해 통계 테이블을 생성했다면 테이블 업그레이드 한다.
EXECUTE DBMS_STATS.UPGRADE_STAT_TABLE('SYS','dictstattab');
10. 파라미터파일수정
11. change password for Oracle-Supplied Accounts
SQL> SELECT username, account_status FROM dba_users ORDER BY username;
SQL> ALTER USER username PASSWORD EXPIRE ACCOUNT LOCK;
12. Upgrade the Oracle Cluster Registry (OCR) Configuration
If you are using Oracle Cluster Services, then you must upgrade the Oracle Cluster Registry (OCR) keys for the database.
Use one of the following options to upgrade the OCR configuration to 11g:
1. Use srvconfig from the Oracle Database 11g Release 1 (11.1) Oracle home.
For example:
srvconfig -upgrade -dbname db_name -orahome pre-11g_Oracle_home
2. Run srvctl.
For example:
From old Oracle_Home:
% $ORACLE_HOME/bin/srvctl remove database -d db_name
From 11g Oracle_Home:
% $ORACLE_HOME/bin/srvctl add database -d db_name -o <location of 11g home>
% $ORACLE_HOME/bin/srvctl add instance -d db_name -i instance -n node
13. Configure EM
14. 통계정보 생성
1. system 통계 직접 생성
System Statistics 는 System Hardware 의 I/O, CPU 의 특성을 분석하여 Optimizer 가 CPU Costing 을 계산할 때 사용하는 정보로써, 이를 기반으로 Optimizer 가 임의의 SQL에 대한 실행 계획을 수립할 때 이를 기반으로 계산하게 된다.
수행주기 - 초기 1회
시스템 자원의 변경이 생겼을 경우 ( Memory, CPU, I/O 등 )
SQL> exec dbms_stats.gather_system_stats(gathering_mode=>'INTERVAL', interval=>10);
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_system_stats;
PL/SQL procedure successfully completed.
2. Fixed Object 통계 직접 생성
Fixed Table에 대한 통계 정보를 수집한다. Oracle의 공식적인 가이드는 1) Oracle 최초(재) 설치나 업그레이드 후, 2) 적당한 일량이 발생하는 환경에서, 3) 단 한 번만 Fixed Table에 대한 통계 정보를 수집할 것을 권장한다.
SQL> exec DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
PL/SQL procedure successfully completed.
3. DICTIONARY OBJECT 통계 직접 생성
Dictionary 객체에 대한 통계 정보를 수집한다. Oracle은 주기적으로 Dictionary 객체에 대한 통계 정보를 수집할 것을 권장한다.
SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
PL/SQL procedure successfully completed.
4. User Objects 통계 생성
사용자의 Object 에 대한 통계치 수집은 10G 에서 기본적인 GATHER_STATS_JOB 을 이용한다. 이 JOB 은 기존 DATA의 10% 이상의 변경이 있거나, 오랫동안 통계치가 변경되지 않거나, 통계치가 없거나 한 Object 에 대한 Gathering 을 수행한다. 또한 분석하는 순서 역시 우선순위 순으로 수행한다. 만약 Object 에 많은 Data 가 Load 되거나 변경이 있는 경우에는 Manual 하게 수행한다.
Set head off
Set feedback off
Spool stats_user.sql
SELECT 'EXEC DBMS_STATS.GATHER_SCHEMA_STATS('|| 'OWNNAME=>' ||CHR(39)|| USERNAME || CHR(39) || CHR(44) || 'CASCADE=>TRUE, ESTIMATE_PERCENT=>25, DEGREE=>8);' AS STRING
FROM DBA_USERS
Where USERNAME NOT IN ('SYS','SYSTEM','OUTLN',’DBSNMP’)
Order by USERNAME
Spool off
'참고글 및 문제해결 > 관련, 참고글' 카테고리의 다른 글
| tablespace offline 의 종류 3가지 (2) | 2012.02.23 |
|---|---|
| VI에디터로 각줄의 처음 5글자 지우기(패턴에 관계없이) (0) | 2012.02.22 |
| Redo log 생성 및 기록 원리 (0) | 2012.02.09 |
| logmnr 간단명령어 (0) | 2012.02.08 |
| 3D업종을 그나마 2.5D로 바꿔주는 자동 script, 명령어들 (0) | 2012.02.08 |