table için;
begin
dbms_stats.gather_table_stats(ownname=> 'SAPSR3', tabname=> 'MARG', partname=> NULL);
end;
tablespace için;
begin
dbms_stats.gather_schema_stats(ownname=> 'SAPSR3' , cascade=> TRUE);
end;
http://blog.csdn.net/guozhenblog/category/260868.aspx
5 Ağustos 2009 Çarşamba
Check Database
Oracle Database Administration
Oracle Database Information Page
* Oracle Database Recovery
* Oracle Database Backup
* Oracle Database Migration
* Oracle Database Monitoring Scripts:
o User
o Tablespaces
o Rollback Segment
o Redo Log
o Tables and Indexes
o Coalesce
o FreeList
o Database Buffer
o Extents
o Shared Pool Size
o Session Statstics
* Oracle Database Archiving
* Oracle Stored Procedures
* Y2K Inventory Database
* Other Issues
Oracle Database Recovery
Implementing a Restored and Recovery Strategy
Phase I - Steps for Diagnosing a Problem
1. Determine if the database instance is available and the database is open.
2. Attempt to start the instance and open the database.
3. Shut down the instance if problems occur while starting it or opening the database.
4. Check the trace files for possible problems.
5. Check the alert_SID.log file for the possible problems.
6. Determine the appropriate recovery method by asking the following questions for each scenario:
1. Which recovery operations are available?
1. Complete Recovery
Closed Database Recovery
Open Database, Offline Tablespace Recovery
Open Database, Offline Tablespace, Individual Datafile Recovery
2. Incomplete Media Recovery
Cancel Based Recovery
Time Based Recovery
Change Based Recovery
2. Which recovery operations are appropriate for the particular problem?
3. Are disaster recovery procedures in place?
4. What need to be restored to proceed with recovery?
Phase II - Restore Appropriate Files
1. Determine which file(s) to restore
2. Determine what state the instance and database must be in to perform the recovery.
Phase III - Recover Database
1. Perform the appropriate method of recovery
Phase IV - Backup Database
1. Determine if another full offline backup is required.
Time Based Recovery
This is used to recover the database up to a specific point in time:
* When data is lost; check the alert file for the approximate time of the error.
* When part of a non-mirrored online redo log becomes corrupt and the approximate time of the corruption is known
1. Shutdown the listener.
2. Shutdown the database.
3. Restored all datafiles from last night backup(except the control files and redo log files).
4. Goto svrmgrl
5. svrmgrl> connect internal
6. svrmgrl> startup mount
7. svrmgrl> recover database until time 'YYYY-MM-DD:24:MM:SS'
8. svrmgrl> alter database open resetlogs;
9. svrmgrl> shutdown
10. svrmgrl> startup
11. Do an offline backup
Recently, I had to recover a database using time based recovery but due to some corrupted nightly backup files, I was not able to do a normal recovery. The error message was:
SVRMGR> recover database until time 1998-06-03:10:18:00
ORA-00283: recovery session canceled due to errors
ORA-01122: database file 3 failed verification check
ORA-01110: data file 3: '/dbase/u04/oradata/PRD/temp01PRD.dbf'
ORA-01203: wrong incarnation of this file - wrong creation SCN
The way to get around this is:
1. Shutdown the listener.
2. Shutdown the database.
3. Restored all datafiles from last night backup (except the control files and redo log files).
4. Goto svrmgrl
5. svrmgrl> connect internal
6. svrmgrl> startup mount
7. svrmgrl> select * from v$recover_file: it displayed 3 datafile that are not recoverable:
* /dbase/u04/oradata/PRD/temp01PRD.dbf
* /dbase/u04/oradata/PRD/ndx01BUD.dbf
* /dbase/u04/oradata/PRD/ndx01HLP.dbf
8. svrmgrl> alter database datafile '/dbase/u04/oradata/PRD/temp01PRD.dbf' offline drop; repeat for the other 2 files.
9. svrmgrl> recover database until time 'YYYY-MM-DD:24:MM:SS'
10. svrmgrl> alter database open resetlogs;
11. svrmgrl> drop tablespace TEMP including contents; svrmgrl> drop tablespace BUD_NDX including contents cascade contraints;
12. Create new tablespaces.
13. Create new indexes.
14. Shutdown the database.
15. Startup the database.
16. Do an offline backup.
Oracle Database Migration
Two ways to move a database:
1. For a small database.
1. Do an Export.
2. Create a new empty database.
3. Do an Import.
2. For a large database (in the old system):
1. svrmgrl>alter database backup controlfile to trace.
2. Take a Cold Backup.
For a large database (in the new system):
3. Restore on the new system (all the datafiles and redo log files). Modified file name if necessary.
4. Create New initSID file.
5. Modify the trace file from step 1 to reflect changes in file names and change the database name if desired.
6. Set the ORACLE_SID to the new SID.
7. Run the sql from step 1
8. Open the database.
Oracle Database Backup
3 ways of doing database backup:
1. Full Offline(Cold) Backup
2. Full Online(Hot) Backup - archiving must be on
3. Export Utility Backup
Oracle Database Archiving
To Turn Archive Log On
1. Modified the initSID.ora file to uncomment these 3 lines:
log_archive_start = true
log_archive_dest = disk$rdbms:[oracle.archive]
log_archive_format = "%S.arc"
2. Goto svrmgrl
svrmgrl> connect internal
svrmgrl> startup mount pfile=initSID.ora
svrmgrl> alter database archivelog;
svrmgrl> alter database open
Oracle Stored Procedure
To Create a Procedure
in sqlplus
sql> create procedure scott.sal_incr
sql> (v_empno IN NUMBER,
sql> v_incr IN NUMBER)
sql> as begin
sql> update scott.emp
sql> set sal = sal + v_incr
sql> where empno = v_empno;
sql> end;
To Execute a Procedure
in sqlplus
sql> exec sal_incr(v_empno, v_incr);
To look for User Procedure
in sqlplus
sql> select * from user_objects where object_type = 'PROCEDURE';
To look at Procedure Code
in sqlplus
sql> select text from user_source where name = 'PROCEDURE_NAME';
Oracle Database Monitoring Scripts
Check for overextended tables or indexes
select substr(owner,1,10) "Owner",
substr(segment_name,1,10) "SegName",
segment_type "SegType",
substr(tablespace_name,1,10) "Tablespace",
substr(extents,1,4) "#Ext",
max_extents "Max",
initial_extent "InitExt",
next_extent "NextExt"
from sys.dba_segments
where extents > 5
and (segment_type = 'TABLE'
or segment_type = 'ROLLBACK'
or segment_type = 'INDEX')
and owner like '%DBA'
order by owner, extents desc, segment_name;
Check for full tablespace
select substr(owner,1,10) "Owner",
substr(segment_name,1,14) "SegName",
next_extent "NextExt",
substr(tablespace_name,1,14) "Tablespace"
from dba_segments ds
where next_extent >
(select max(bytes)
from dba_free_space
where tablespace_name=ds.tablespace_name);
Check for tablespace fragmentation
select TABLESPACE_NAME,
count(*) FREE_SEGMENTS
from dba_free_space
group by TABLESPACE_NAME
having count(*) > 10;
Check for free space by tablespace monitoring
select substr(b.file_id,1,3) "FID",
substr(b.file_name,23,16) "Filename",
substr(b.tablespace_name,1,10) "Tablespace",
b.bytes "# Bytes",
(b.bytes - sum(nvl(a.bytes,0))) "# Used",
sum(nvl(a.bytes,0)) "# Free",
(sum(nvl(a.bytes,0))/(b.bytes)) * 100 "% Free"
from sys.dba_free_space a, sys.dba_data_files b
where a.file_id(+) = b.file_id
group by b.tablespace_name, b.file_id, b.file_name, b.bytes
order by b.tablespace_name;
Tablespace Disk Usages
select tablespace_name,
sum(bytes) "Bytes Consumed",
count(*) "Number of Objects"
from dba_segments
group by tablespace_name;
select tablespace_name,
sum(bytes) "Bytes Alloc"
from dba_data_files
group by tablespace_name;
Check for redo log contention
select name,
gets,
misses,
sleeps,
immediate_gets,
immediate_misses
from v$latch
where name in ('redo allocation', 'redo copy');
Check for rollback segments contention
select substr(r.name,1,10) "Rollback",
s.gets,
s.waits,
round (100 * s.waits/s.gets) "%Cont"
from v$rollstat s, v$rollname r
where s.usn = r.usn
and round (100 * s.waits/s.gets) > 1;
Check for rollback segments monitoring
select substr(name,1,10) "Rollback",
optsize,
shrinks,
aveshrink,
wraps,
extends,
rssize,
writes
from v$rollstat s, v$rollname r
where s.usn = r.usn;
select substr(segment_name,1,14),
substr(name,1,36),
substr(file_id,1,6),
bytes,
substr(tablespace_name,1,10)
from dba_rollback_segs,
v$datafile
where v$datafile.file# = dba_rollback_segs.file_id;
Check for transaction per rollback segments monitoring
select r.name rr,
nvl(s.username,'no transaction') us,
s.osuser os,
s.terminal ts
from V$LOCK l, V$SESSION s, V$ROLLNAME r
where l.sid =s.sid(+)
and trunc(l.ID1/65536) = r.usn
and l.type = 'TX'
and l.lmode = 6
order by r.name;
Check for user that use SYSTEM tablespace for work area
select substr(username,1,14) "User",
user_id "User ID",
substr(default_tablespace,1,14) "Default TSpace",
substr(temporary_tablespace,1,14) "Temp TSpace"
from dba_users
where temporary_tablespace = 'SYSTEM';
Check user privileges
select *
from sys.dba_role_privs
where GRANTEE like '%';
select * from sys.dba_sys_privs
where GRANTEE like '%';
select * from sys.dba_tab_privs
where GRANTEE like '%';
select username,
program,
sid,
serial#
from v$session;
User Disk Usages
select owner,
sum(bytes) "Bytes Consumed",
count(*) "Number of Objects"
from dba_segments
group by owner;
Check for tablespace coalesce
select tablespace_name, percent_blocks_coalesced
from dba_free_space_coalesced
order by percent_blocks_coalesced;
alter tablespace XXX coalesce;
select * from dba_free_space
where tablespace_name = 'XXX';
Check for db_buffer
select name,
value
from v$sysstat
where name in ('consistent gets', 'db block gets', 'physical reads');
select * from dba_ts_quotas
where BYTES = MAX_BYTES;
Check for Freelist
select * from v$waitstat;
Check for segments running out of extents
select a.owner,
table_name "table",
a.tablespace_name "tablespace",
'T' "T/I",
a.max_extents max_exts,
b.extents curr_exts
from sys.dba_tables a,
sys.dba_segments b
where table_name = segment_name
and a.max_extents - b.extents <= 10
union
select a.owner,
index_name "index",
a.tablespace_name "tablespace",
'I' "T/I",
a.max_extents max_exts,
b.extents curr_exts
from sys.dba_indexes a,
sys.dba_segments b
where index_name = segment_name
and a.max_extents - b.extents <= 10;
Resize datafiles
alter database datafile 'XXX' resize 30M;
Collect session statstics
select username,
to_char(sysdate - (HSECS - s.value)/(3600*100*24),
'DD-MM-YYYY HH24:MI:SS')
from v$sesstst S,
v$session SE,
v$timer
where SE.SID = S.SID
and statistic#=14
and username is not null;
Shared Pool Information
select sum(pins) pins,
sum(reloads) reloads
from v$librarycache;
select sum(gets) gets,
sum(getmisses) getmisses
from v$rowcache;
Oracle Database Other Issues
Connection
To restrict other users to the database.
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM DISABLE RESTRICTED SESSION;
http://www.its.monash.edu.au/staff/systems/oracle/technical/ss_oracle.html
http://decipherinfosys.wordpress.com/2007/03/20/checking-database-parameters-in-oracle/
http://help.sap.com/saphelp_nw70/helpdata/EN/b0/a42100eb7ec5478ebf2c846e2912e9/frameset.htm
Oracle Database Information Page
* Oracle Database Recovery
* Oracle Database Backup
* Oracle Database Migration
* Oracle Database Monitoring Scripts:
o User
o Tablespaces
o Rollback Segment
o Redo Log
o Tables and Indexes
o Coalesce
o FreeList
o Database Buffer
o Extents
o Shared Pool Size
o Session Statstics
* Oracle Database Archiving
* Oracle Stored Procedures
* Y2K Inventory Database
* Other Issues
Oracle Database Recovery
Implementing a Restored and Recovery Strategy
Phase I - Steps for Diagnosing a Problem
1. Determine if the database instance is available and the database is open.
2. Attempt to start the instance and open the database.
3. Shut down the instance if problems occur while starting it or opening the database.
4. Check the trace files for possible problems.
5. Check the alert_SID.log file for the possible problems.
6. Determine the appropriate recovery method by asking the following questions for each scenario:
1. Which recovery operations are available?
1. Complete Recovery
Closed Database Recovery
Open Database, Offline Tablespace Recovery
Open Database, Offline Tablespace, Individual Datafile Recovery
2. Incomplete Media Recovery
Cancel Based Recovery
Time Based Recovery
Change Based Recovery
2. Which recovery operations are appropriate for the particular problem?
3. Are disaster recovery procedures in place?
4. What need to be restored to proceed with recovery?
Phase II - Restore Appropriate Files
1. Determine which file(s) to restore
2. Determine what state the instance and database must be in to perform the recovery.
Phase III - Recover Database
1. Perform the appropriate method of recovery
Phase IV - Backup Database
1. Determine if another full offline backup is required.
Time Based Recovery
This is used to recover the database up to a specific point in time:
* When data is lost; check the alert file for the approximate time of the error.
* When part of a non-mirrored online redo log becomes corrupt and the approximate time of the corruption is known
1. Shutdown the listener.
2. Shutdown the database.
3. Restored all datafiles from last night backup(except the control files and redo log files).
4. Goto svrmgrl
5. svrmgrl> connect internal
6. svrmgrl> startup mount
7. svrmgrl> recover database until time 'YYYY-MM-DD:24:MM:SS'
8. svrmgrl> alter database open resetlogs;
9. svrmgrl> shutdown
10. svrmgrl> startup
11. Do an offline backup
Recently, I had to recover a database using time based recovery but due to some corrupted nightly backup files, I was not able to do a normal recovery. The error message was:
SVRMGR> recover database until time 1998-06-03:10:18:00
ORA-00283: recovery session canceled due to errors
ORA-01122: database file 3 failed verification check
ORA-01110: data file 3: '/dbase/u04/oradata/PRD/temp01PRD.dbf'
ORA-01203: wrong incarnation of this file - wrong creation SCN
The way to get around this is:
1. Shutdown the listener.
2. Shutdown the database.
3. Restored all datafiles from last night backup (except the control files and redo log files).
4. Goto svrmgrl
5. svrmgrl> connect internal
6. svrmgrl> startup mount
7. svrmgrl> select * from v$recover_file: it displayed 3 datafile that are not recoverable:
* /dbase/u04/oradata/PRD/temp01PRD.dbf
* /dbase/u04/oradata/PRD/ndx01BUD.dbf
* /dbase/u04/oradata/PRD/ndx01HLP.dbf
8. svrmgrl> alter database datafile '/dbase/u04/oradata/PRD/temp01PRD.dbf' offline drop; repeat for the other 2 files.
9. svrmgrl> recover database until time 'YYYY-MM-DD:24:MM:SS'
10. svrmgrl> alter database open resetlogs;
11. svrmgrl> drop tablespace TEMP including contents; svrmgrl> drop tablespace BUD_NDX including contents cascade contraints;
12. Create new tablespaces.
13. Create new indexes.
14. Shutdown the database.
15. Startup the database.
16. Do an offline backup.
Oracle Database Migration
Two ways to move a database:
1. For a small database.
1. Do an Export.
2. Create a new empty database.
3. Do an Import.
2. For a large database (in the old system):
1. svrmgrl>alter database backup controlfile to trace.
2. Take a Cold Backup.
For a large database (in the new system):
3. Restore on the new system (all the datafiles and redo log files). Modified file name if necessary.
4. Create New initSID file.
5. Modify the trace file from step 1 to reflect changes in file names and change the database name if desired.
6. Set the ORACLE_SID to the new SID.
7. Run the sql from step 1
8. Open the database.
Oracle Database Backup
3 ways of doing database backup:
1. Full Offline(Cold) Backup
2. Full Online(Hot) Backup - archiving must be on
3. Export Utility Backup
Oracle Database Archiving
To Turn Archive Log On
1. Modified the initSID.ora file to uncomment these 3 lines:
log_archive_start = true
log_archive_dest = disk$rdbms:[oracle.archive]
log_archive_format = "%S.arc"
2. Goto svrmgrl
svrmgrl> connect internal
svrmgrl> startup mount pfile=initSID.ora
svrmgrl> alter database archivelog;
svrmgrl> alter database open
Oracle Stored Procedure
To Create a Procedure
in sqlplus
sql> create procedure scott.sal_incr
sql> (v_empno IN NUMBER,
sql> v_incr IN NUMBER)
sql> as begin
sql> update scott.emp
sql> set sal = sal + v_incr
sql> where empno = v_empno;
sql> end;
To Execute a Procedure
in sqlplus
sql> exec sal_incr(v_empno, v_incr);
To look for User Procedure
in sqlplus
sql> select * from user_objects where object_type = 'PROCEDURE';
To look at Procedure Code
in sqlplus
sql> select text from user_source where name = 'PROCEDURE_NAME';
Oracle Database Monitoring Scripts
Check for overextended tables or indexes
select substr(owner,1,10) "Owner",
substr(segment_name,1,10) "SegName",
segment_type "SegType",
substr(tablespace_name,1,10) "Tablespace",
substr(extents,1,4) "#Ext",
max_extents "Max",
initial_extent "InitExt",
next_extent "NextExt"
from sys.dba_segments
where extents > 5
and (segment_type = 'TABLE'
or segment_type = 'ROLLBACK'
or segment_type = 'INDEX')
and owner like '%DBA'
order by owner, extents desc, segment_name;
Check for full tablespace
select substr(owner,1,10) "Owner",
substr(segment_name,1,14) "SegName",
next_extent "NextExt",
substr(tablespace_name,1,14) "Tablespace"
from dba_segments ds
where next_extent >
(select max(bytes)
from dba_free_space
where tablespace_name=ds.tablespace_name);
Check for tablespace fragmentation
select TABLESPACE_NAME,
count(*) FREE_SEGMENTS
from dba_free_space
group by TABLESPACE_NAME
having count(*) > 10;
Check for free space by tablespace monitoring
select substr(b.file_id,1,3) "FID",
substr(b.file_name,23,16) "Filename",
substr(b.tablespace_name,1,10) "Tablespace",
b.bytes "# Bytes",
(b.bytes - sum(nvl(a.bytes,0))) "# Used",
sum(nvl(a.bytes,0)) "# Free",
(sum(nvl(a.bytes,0))/(b.bytes)) * 100 "% Free"
from sys.dba_free_space a, sys.dba_data_files b
where a.file_id(+) = b.file_id
group by b.tablespace_name, b.file_id, b.file_name, b.bytes
order by b.tablespace_name;
Tablespace Disk Usages
select tablespace_name,
sum(bytes) "Bytes Consumed",
count(*) "Number of Objects"
from dba_segments
group by tablespace_name;
select tablespace_name,
sum(bytes) "Bytes Alloc"
from dba_data_files
group by tablespace_name;
Check for redo log contention
select name,
gets,
misses,
sleeps,
immediate_gets,
immediate_misses
from v$latch
where name in ('redo allocation', 'redo copy');
Check for rollback segments contention
select substr(r.name,1,10) "Rollback",
s.gets,
s.waits,
round (100 * s.waits/s.gets) "%Cont"
from v$rollstat s, v$rollname r
where s.usn = r.usn
and round (100 * s.waits/s.gets) > 1;
Check for rollback segments monitoring
select substr(name,1,10) "Rollback",
optsize,
shrinks,
aveshrink,
wraps,
extends,
rssize,
writes
from v$rollstat s, v$rollname r
where s.usn = r.usn;
select substr(segment_name,1,14),
substr(name,1,36),
substr(file_id,1,6),
bytes,
substr(tablespace_name,1,10)
from dba_rollback_segs,
v$datafile
where v$datafile.file# = dba_rollback_segs.file_id;
Check for transaction per rollback segments monitoring
select r.name rr,
nvl(s.username,'no transaction') us,
s.osuser os,
s.terminal ts
from V$LOCK l, V$SESSION s, V$ROLLNAME r
where l.sid =s.sid(+)
and trunc(l.ID1/65536) = r.usn
and l.type = 'TX'
and l.lmode = 6
order by r.name;
Check for user that use SYSTEM tablespace for work area
select substr(username,1,14) "User",
user_id "User ID",
substr(default_tablespace,1,14) "Default TSpace",
substr(temporary_tablespace,1,14) "Temp TSpace"
from dba_users
where temporary_tablespace = 'SYSTEM';
Check user privileges
select *
from sys.dba_role_privs
where GRANTEE like '%';
select * from sys.dba_sys_privs
where GRANTEE like '%';
select * from sys.dba_tab_privs
where GRANTEE like '%';
select username,
program,
sid,
serial#
from v$session;
User Disk Usages
select owner,
sum(bytes) "Bytes Consumed",
count(*) "Number of Objects"
from dba_segments
group by owner;
Check for tablespace coalesce
select tablespace_name, percent_blocks_coalesced
from dba_free_space_coalesced
order by percent_blocks_coalesced;
alter tablespace XXX coalesce;
select * from dba_free_space
where tablespace_name = 'XXX';
Check for db_buffer
select name,
value
from v$sysstat
where name in ('consistent gets', 'db block gets', 'physical reads');
select * from dba_ts_quotas
where BYTES = MAX_BYTES;
Check for Freelist
select * from v$waitstat;
Check for segments running out of extents
select a.owner,
table_name "table",
a.tablespace_name "tablespace",
'T' "T/I",
a.max_extents max_exts,
b.extents curr_exts
from sys.dba_tables a,
sys.dba_segments b
where table_name = segment_name
and a.max_extents - b.extents <= 10
union
select a.owner,
index_name "index",
a.tablespace_name "tablespace",
'I' "T/I",
a.max_extents max_exts,
b.extents curr_exts
from sys.dba_indexes a,
sys.dba_segments b
where index_name = segment_name
and a.max_extents - b.extents <= 10;
Resize datafiles
alter database datafile 'XXX' resize 30M;
Collect session statstics
select username,
to_char(sysdate - (HSECS - s.value)/(3600*100*24),
'DD-MM-YYYY HH24:MI:SS')
from v$sesstst S,
v$session SE,
v$timer
where SE.SID = S.SID
and statistic#=14
and username is not null;
Shared Pool Information
select sum(pins) pins,
sum(reloads) reloads
from v$librarycache;
select sum(gets) gets,
sum(getmisses) getmisses
from v$rowcache;
Oracle Database Other Issues
Connection
To restrict other users to the database.
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM DISABLE RESTRICTED SESSION;
http://www.its.monash.edu.au/staff/systems/oracle/technical/ss_oracle.html
http://decipherinfosys.wordpress.com/2007/03/20/checking-database-parameters-in-oracle/
http://help.sap.com/saphelp_nw70/helpdata/EN/b0/a42100eb7ec5478ebf2c846e2912e9/frameset.htm
3 Ağustos 2009 Pazartesi
Patch Upgrade
Note 824432 - Current Patchset for Oracle 10.1.
http://hi.baidu.com/regards/blog/item/5a7abeec23a1234879f0552a.html/cmtid/c1974c2501df7e21d5074230
环境：AIX5.3L + Oracle 10G + SAP ECC6
1.查看当前的Oracle版本：
PRDT / # su - oraprt
PRDT:oraprt 1> sqlplus /nolog
SQL*Plus: Release 10.2.0.2.0 - Production on Fri Oct 17 13:25:29 2008
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
SQL> connect /as sysdba
Connected.
SQL> select * from V$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bi
PL/SQL Release 10.2.0.2.0 - Production
CORE 10.2.0.2.0 Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.2.0 - Productio
NLSRTL Version 10.2.0.2.0 – Production
2.查看oracle已经打过的patch：
PRDT / # su - oraprt
PRDT:oraprt 1> cd $ORACLE_HOME/OPatch
PRDT:oraprt 2> pwd
/oracle/PRT/102_64/OPatch
PRDT:oraprt 3> ./opatch lsinventory
Invoking OPatch 10.2.0.2.0
Oracle interim Patch Installer version 10.2.0.2.0
Copyright (c) 2005, Oracle Corporation. All rights reserved..
Oracle Home : /oracle/PRT/102_64
Central Inventory : /oracle/oraInventory
from : /oracle/PRT/102_64/oraInst.loc
OPatch version : 10.2.0.2.0
OUI version : 10.2.0.2.0
OUI location : /oracle/PRT/102_64/oui
Log file location : /oracle/PRT/102_64/cfgtoollogs/opatch/opatch-00_Oct_17_16-06-48-GMT+08_Fri.log
Lsinventory Output file location : /oracle/PRT/102_64/cfgtoollogs/opatch/lsinv/lsinventory-00_Oct_17_16-06-48-GMT+08_Fri.txt
--------------------------------------------------------------------------------
Installed Top-level Products (2):
Oracle Database 10g 10.2.0.1.0
Oracle Database 10g Release 2 Patch Set 1 10.2.0.2.0
There are 2 products installed in this Oracle Home.
There are no Interim patches installed in this Oracle Home.
--------------------------------------------------------------------------------
OPatch succeeded.
3. 上传并解压Oracle patch和Oracle Client文件
(由于下载的是3部分类似于WINRAR的分卷文件，需要在DOS下进行合并)
D:\SAP SOFT>copy /B Patchset_10204_AIX5Laa.bin + Patchset_10204_AIX5Lab.bin + Patchset_10204_AIX5Lac.bin + Patchset_10204_AIX5Lad.bin Patchset_10204_AIX5La.bin
Patchset_10204_AIX5Laa.bin
Patchset_10204_AIX5Lab.bin
Patchset_10204_AIX5Lac.bin
Patchset_10204_AIX5Lad.bin
已复制 1 个文件。
修改Disk1和Oracle client文件为777
PRDT /sapsoft/sapsoft/orapatch # chmod -R 777 Disk1
PRDT /sapsoft/sapsoft/orapatch # chmod 777 OCL10264.SAR
4．备份SAP所在的VG：(smitty savevg)
5.安装oracle patch：
确认SAP和数据库关闭
# ps -ef | grep sap
root 974922 573570 0 16:17:02 pts/4 0:00 grep sap
# ps -ef | rep ora
/usr/bin/ksh: rep: not found.
# ps -ef | grep ora
root 974926 573570 0 16:17:12 pts/4 0:00 grep ora
PRDT:oraprt 11> ./runInstaller
********************************************************************************
Your platform requires the root user to perform certain pre-installation
OS preparation. The root user should execute '/usr/sbin/slibclean' on all the nodes before
you proceed with Patchset Installation.
Answer 'y' if root has run '/usr/sbin/slibclean' so you can proceed with Oracle
Patchset installation.
Answer 'n' to abort installation and then ask root to run '/usr/sbin/slibclean'.
********************************************************************************
Has '/usr/sbin/slibclean' been run by root? [y/n] (n)
./runInstaller[29]: y: 0403-012 A test command parameter is not valid.
Patchset Installation stopped to run '/usr/sbin/slibclean' by root.
# /usr/sbin/slibclean
设置DISPLAY实现远程GUI的调用:
PRDT:oraprt 11> setenv DISPLAY 10.38.5.12:0.0
PRDT:oraprt 12> ./runInstaller
完成oracle patch的安装。
6.安装oracle client：
PRDT:oraprt 7>SAPCAR -xvf OCL10264.SAR
PRDT:oraprt 8> ls -l
total 121888
drwxrwxrwx 5 root system 256 Oct 17 16:03 Disk1
-rwxrwxrwx 1 root system 56047058 Oct 17 15:50 OCL10264.SAR
-rwxrwxrwx 1 prtadm sapsys 6349944 Aug 02 2007 SAPCAR
drwxr-xr-x 2 oraprt dba 4096 Aug 01 23:29 instantclient_10204_V1
PRDT:oraprt 9> cd /oracle/ client/10*
PRDT:oraprt 16> ls -l
total 8
-rwxrwxr-x 1 prtadm sapsys 101 Mar 27 2006 dbclient.lst
lrwxrwxrwx 1 root sapinst 41 Feb 28 2008 instantclient -> /oracle/client/10x_64/instantclient_10202
drwxrwxr-x 2 prtadm sapsys 256 Mar 27 2006 instantclient_10202
PRDT:oraprt 16>rm instantclient
PRDT:oraprt17>cp –pr /sapsoft/sapsoft/orapatch/ instantclient_10204_V1 instantclient_10204_V1
PRDT:oraprt18>ln -s instantclient_10204_V1 instantclient
7.完成oracle client 10.2.0.4.0的安装，通过测试:
PRDT:prtadm 1> R3trans -d
This is R3trans version 6.14 (release 700 - 28.07.08 - 15:52:00).
unicode enabled version
R3trans finished (0000).
8.更新数据字典视图：
升级以后数据字典的一些基表的内容修改了,catalog.sql和catproc.sql这2个脚本需要在运行一下把数据字典视图在更新一下.
PRDT / # su - oraprt
PRDT:oraprt 1> sqlplus /nolog
SQL*Plus: Release 10.2.0.4.0 - Production on Fri Oct 17 23:41:33 2008
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
SQL> connect /as sysdba
Connected.
SQL>STARTUP UPGRADE
SQL> @$ORACLE_HOME/rdbms/admin/catupgrd.sql
9.正常启动Sap，完成Oracle patch和oracle client的安装。
http://hi.baidu.com/regards/blog/item/5a7abeec23a1234879f0552a.html/cmtid/c1974c2501df7e21d5074230
环境：AIX5.3L + Oracle 10G + SAP ECC6
1.查看当前的Oracle版本：
PRDT / # su - oraprt
PRDT:oraprt 1> sqlplus /nolog
SQL*Plus: Release 10.2.0.2.0 - Production on Fri Oct 17 13:25:29 2008
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
SQL> connect /as sysdba
Connected.
SQL> select * from V$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bi
PL/SQL Release 10.2.0.2.0 - Production
CORE 10.2.0.2.0 Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.2.0 - Productio
NLSRTL Version 10.2.0.2.0 – Production
2.查看oracle已经打过的patch：
PRDT / # su - oraprt
PRDT:oraprt 1> cd $ORACLE_HOME/OPatch
PRDT:oraprt 2> pwd
/oracle/PRT/102_64/OPatch
PRDT:oraprt 3> ./opatch lsinventory
Invoking OPatch 10.2.0.2.0
Oracle interim Patch Installer version 10.2.0.2.0
Copyright (c) 2005, Oracle Corporation. All rights reserved..
Oracle Home : /oracle/PRT/102_64
Central Inventory : /oracle/oraInventory
from : /oracle/PRT/102_64/oraInst.loc
OPatch version : 10.2.0.2.0
OUI version : 10.2.0.2.0
OUI location : /oracle/PRT/102_64/oui
Log file location : /oracle/PRT/102_64/cfgtoollogs/opatch/opatch-00_Oct_17_16-06-48-GMT+08_Fri.log
Lsinventory Output file location : /oracle/PRT/102_64/cfgtoollogs/opatch/lsinv/lsinventory-00_Oct_17_16-06-48-GMT+08_Fri.txt
--------------------------------------------------------------------------------
Installed Top-level Products (2):
Oracle Database 10g 10.2.0.1.0
Oracle Database 10g Release 2 Patch Set 1 10.2.0.2.0
There are 2 products installed in this Oracle Home.
There are no Interim patches installed in this Oracle Home.
--------------------------------------------------------------------------------
OPatch succeeded.
3. 上传并解压Oracle patch和Oracle Client文件
(由于下载的是3部分类似于WINRAR的分卷文件，需要在DOS下进行合并)
D:\SAP SOFT>copy /B Patchset_10204_AIX5Laa.bin + Patchset_10204_AIX5Lab.bin + Patchset_10204_AIX5Lac.bin + Patchset_10204_AIX5Lad.bin Patchset_10204_AIX5La.bin
Patchset_10204_AIX5Laa.bin
Patchset_10204_AIX5Lab.bin
Patchset_10204_AIX5Lac.bin
Patchset_10204_AIX5Lad.bin
已复制 1 个文件。
修改Disk1和Oracle client文件为777
PRDT /sapsoft/sapsoft/orapatch # chmod -R 777 Disk1
PRDT /sapsoft/sapsoft/orapatch # chmod 777 OCL10264.SAR
4．备份SAP所在的VG：(smitty savevg)
5.安装oracle patch：
确认SAP和数据库关闭
# ps -ef | grep sap
root 974922 573570 0 16:17:02 pts/4 0:00 grep sap
# ps -ef | rep ora
/usr/bin/ksh: rep: not found.
# ps -ef | grep ora
root 974926 573570 0 16:17:12 pts/4 0:00 grep ora
PRDT:oraprt 11> ./runInstaller
********************************************************************************
Your platform requires the root user to perform certain pre-installation
OS preparation. The root user should execute '/usr/sbin/slibclean' on all the nodes before
you proceed with Patchset Installation.
Answer 'y' if root has run '/usr/sbin/slibclean' so you can proceed with Oracle
Patchset installation.
Answer 'n' to abort installation and then ask root to run '/usr/sbin/slibclean'.
********************************************************************************
Has '/usr/sbin/slibclean' been run by root? [y/n] (n)
./runInstaller[29]: y: 0403-012 A test command parameter is not valid.
Patchset Installation stopped to run '/usr/sbin/slibclean' by root.
# /usr/sbin/slibclean
设置DISPLAY实现远程GUI的调用:
PRDT:oraprt 11> setenv DISPLAY 10.38.5.12:0.0
PRDT:oraprt 12> ./runInstaller
完成oracle patch的安装。
6.安装oracle client：
PRDT:oraprt 7>SAPCAR -xvf OCL10264.SAR
PRDT:oraprt 8> ls -l
total 121888
drwxrwxrwx 5 root system 256 Oct 17 16:03 Disk1
-rwxrwxrwx 1 root system 56047058 Oct 17 15:50 OCL10264.SAR
-rwxrwxrwx 1 prtadm sapsys 6349944 Aug 02 2007 SAPCAR
drwxr-xr-x 2 oraprt dba 4096 Aug 01 23:29 instantclient_10204_V1
PRDT:oraprt 9> cd /oracle/ client/10*
PRDT:oraprt 16> ls -l
total 8
-rwxrwxr-x 1 prtadm sapsys 101 Mar 27 2006 dbclient.lst
lrwxrwxrwx 1 root sapinst 41 Feb 28 2008 instantclient -> /oracle/client/10x_64/instantclient_10202
drwxrwxr-x 2 prtadm sapsys 256 Mar 27 2006 instantclient_10202
PRDT:oraprt 16>rm instantclient
PRDT:oraprt17>cp –pr /sapsoft/sapsoft/orapatch/ instantclient_10204_V1 instantclient_10204_V1
PRDT:oraprt18>ln -s instantclient_10204_V1 instantclient
7.完成oracle client 10.2.0.4.0的安装，通过测试:
PRDT:prtadm 1> R3trans -d
This is R3trans version 6.14 (release 700 - 28.07.08 - 15:52:00).
unicode enabled version
R3trans finished (0000).
8.更新数据字典视图：
升级以后数据字典的一些基表的内容修改了,catalog.sql和catproc.sql这2个脚本需要在运行一下把数据字典视图在更新一下.
PRDT / # su - oraprt
PRDT:oraprt 1> sqlplus /nolog
SQL*Plus: Release 10.2.0.4.0 - Production on Fri Oct 17 23:41:33 2008
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
SQL> connect /as sysdba
Connected.
SQL>STARTUP UPGRADE
SQL> @$ORACLE_HOME/rdbms/admin/catupgrd.sql
9.正常启动Sap，完成Oracle patch和oracle client的安装。
Data File
SELECT owner || '.' || object_name, SUM (VALUE)
FROM v$segment_statistics
WHERE statistic_name IN ('physical reads', 'physical writes')
AND tablespace_name NOT IN ('SYSTEM', 'SYSAUX')
GROUP BY owner || '.' || object_name
ORDER BY 2 DESC;
-----------------------------
SELECT name "Datafile", phyrds "Okuma", phywrts "Yazma", phyrds + phywrts "Toplam IO" FROM V$DATAFILE, V$FILESTAT WHERE V$DATAFILE.file# = V$FILESTAT.file#;
http://www.gokhanatil.com/oracle-datafile-veri-dosyalari-istatistikleri/
FROM v$segment_statistics
WHERE statistic_name IN ('physical reads', 'physical writes')
AND tablespace_name NOT IN ('SYSTEM', 'SYSAUX')
GROUP BY owner || '.' || object_name
ORDER BY 2 DESC;
-----------------------------
SELECT name "Datafile", phyrds "Okuma", phywrts "Yazma", phyrds + phywrts "Toplam IO" FROM V$DATAFILE, V$FILESTAT WHERE V$DATAFILE.file# = V$FILESTAT.file#;
http://www.gokhanatil.com/oracle-datafile-veri-dosyalari-istatistikleri/
Tablespace Monitoring -3
Step by step: How to extend tablespace for DB2 on AIX
The following steps are used to resolve full table space issue without having to re-create your database:
* 1. Find out which file containers are used for that tablespace (fully used)
* 2. Do you have enough space on the current file systems?
* 3. Increase the size for volume group
* 4. Increase size for logical volumes
* 5. Increase size for file systems
* 6. And finally, increase DB tablespace!
As your database tables grow, your originally planned disk space may simply run out. This is seen when you get the following error from DB2 db2diag.log (for both DB2 V8 and V9):
2007-08-29-13.02.27.267969-420 E14615A1054 LEVEL: Error
PID : 3743818 TID : 1 PROC : db2agent (PWTEST01) 0
INSTANCE: db2inst1 NODE : 000 DB : PWTEST01
APPHDL : 0-662 APPID: 10.0.4.50.54736.070829194050
AUTHID : FNRUN
FUNCTION: DB2 UDB, buffer pool services, sqlbAllocateExtent, probe:830
MESSAGE : ADM6044E The DMS table space "L002" (ID "105") is full. If this is
an autoresize or automatic storage DMS tablespace, the maximum table
space size may have been reached or the existing containers or
storage paths cannot grow any more. Additional space can be added to
the table space by either adding new containers or extending existing
ones using the ALTER TABLESPACE SQL statement. If this is an
autoresize or automatic storage DMS table space, additional space can
be added by adding containers to an autoresize table space or by
adding new storage paths to an automatic storage database.
You can alter tablespace (as recommended by the error message) to increase its size, but you may simply run out of disk space that the tablespace resides on. Many volume managers and file systems allow you to extend disk space as well, and that is what you have to do. The following steps apply to DB2 and AIX, but the process can work for other databases and operating systems as well. We expect you to replace commands used when working on other platforms.
1. Find out which file containers are used for that tablespace (which is full):
Normally this is done by search for the name of the tablespace in your
database definition script. Or if you don't have that (because the application
created it during installation), you can use the db2look command to dump
that to a file. Then do a grep on the name of the tablespace:
bash-3.00# grep L002 *
dbcreate.ddl:CREATE LARGE TABLESPACE L002 IN DATABASE PARTITION
dbcreate.ddl: USING (FILE '/db/a33/pwtest01/L002' 250M,
dbcreate.ddl: FILE '/db/a34/pwtest01/L002' 250M,
dbcreate.ddl: FILE '/db/a35/pwtest01/L002' 250M,
dbcreate.ddl: FILE '/db/a36/pwtest01/L002' 250M)
so we found the files used.
2. Do you have enough space on the current file systems?
# df -k
/dev/lv33 10256384 2387712 77% 27 1% /db/a33
/dev/lv34 10256384 2387704 77% 28 1% /db/a34
/dev/lv35 10256384 2387712 77% 27 1% /db/a35
/dev/lv36 10256384 2387704 77% 28 1% /db/a36
77% used. 10GB per file system, leaving about 230MB free space per.
You can check what else is on each of the file system:
bash-3.00# ls -l /db/a33/pwtest01/
total 15654912
-rw-rw-rw- 1 db2inst1 db2admi 0 Jul 24 02:42 .SQLCRT.FLG
-rw------- 1 db2inst1 db2admi 262144000 Aug 29 13:13 L001
-rw------- 1 db2inst1 db2admi 262144000 Aug 29 13:13 L002
-rw------- 1 db2inst1 db2admi 262144000 Aug 29 13:13 L003
-rw------- 1 db2inst1 db2admi 262144000 Aug 29 13:13 L004
-rw------- 1 db2inst1 db2admi 262144000 Aug 29 13:13 L005
-rw------- 1 db2inst1 db2admi 262144000 Aug 29 13:13 L006
-rw------- 1 db2inst1 db2admi 3221225472 Aug 29 13:13 U027
-rw------- 1 db2inst1 db2admi 3221225472 Aug 29 13:13 U029
3. Increase the size for volume group
If you think you need to extend tablespace size beyond 1GB (250Mx4), then you must increase
the file system (77% used already). But first, you need to extend size for logical volume,
but before you can do that, you need to extend size for the volume group that the logical
volume resides in ( we will use lv33 as an example, you just repeat the same for lv34,35,36).
First, find out the sizes for the volume group vg33:
bash-3.00# lsvg vg33
VOLUME GROUP: vg33 VG IDENTIFIER: 00ce3a8a00004c0000000113f77641d1
VG STATE: active PP SIZE: 32 megabyte(s)
VG PERMISSION: read/write TOTAL PPs: 319 (10208 megabytes)
MAX LVs: 256 FREE PPs: 5 (160 megabytes)
LVs: 2 USED PPs: 314 (10048 megabytes)
OPEN LVs: 2 QUORUM: 2
TOTAL PVs: 1 VG DESCRIPTORS: 2
STALE PVs: 0 STALE PPs: 0
ACTIVE PVs: 1 AUTO ON: yes
MAX PPs per VG: 32512
MAX PPs per PV: 1016 MAX PVs: 32
LTG size (Dynamic): 256 kilobyte(s) AUTO SYNC: no
HOT SPARE: no BB POLICY: relocatable
So vg33 is only 10GB. needs to extend that; Let's see what hdisks are in vg33:
# datapath query device
...
DEV#: 33 DEVICE NAME: vpath33 TYPE: 2107900 POLICY: Optimized
SERIAL: 75FABV1103D
===========================================================================
Path# Adapter/Hard Disk State Mode Select Errors
0 fscsi0/hdisk39 OPEN NORMAL 6735259 0
1 fscsi1/hdisk79 OPEN NORMAL 6743800 0
So if vpath33 offers 10GB space, then we need to add more vpath to the vg so
that it gets more hdisks.
Performce vg extend task in smitty (assume you run on AIX):
___________________________________________________________________________
Force the creation of a volume group? no
* VOLUME GROUP name [vg33]
* PHYSICAL VOLUME names []
PHYSICAL VOLUME names
Move cursor to desired item and press F7.
ONE OR MORE items can be selected.
Press Enter AFTER making all selections.
> vpath40
> vpath41
> vpath42
___________________________________________________________________________
This adds 3 more vpaths (here we used the same size: 10GB each vpath) to get
an additional 30GB more disk space.
4. Increase size for logical volumes
lv33 on vg33 now can be extended. Note: if your logical volume (lv) has a
MAX LOGICAL PARTITIONS setting, you may need to increase it so that addtional
partitions can be created on the new disks. Otherwise, you will be capped
at the old size. For example, you may need to change MAX LOGICAL PARTITIONS from
512 to 1024 (done in smitty).
To increase lv size, simply do in smitty the following:
Increase the Size of a Logical Volume
Type or select values in entry fields.
Press Enter AFTER making all desired changes.
[Entry Fields]
* LOGICAL VOLUME name lv34
* Number of ADDITIONAL logical partitions [939] <=== new size!
PHYSICAL VOLUME names [vpath43 vpath44 vpath45] <== new disks!
POSITION on physical volume middle
RANGE of physical volumes minimum
MAXIMUM NUMBER of PHYSICAL VOLUMES [32]
to use for allocation
Allocate each logical partition copy yes
on a SEPARATE physical volume?
File containing ALLOCATION MAP []
Note: 939 is "additional" partitions. The final number of partitions will be 313+939.
This is now 40GB.
5. Increase size for file systems
Now that lv33 is increased, it is time to increase the file system size. Also do this in
smitty.
Type or select values in entry fields.
Press Enter AFTER making all desired changes.
[Entry Fields]
File system name /db/a33
NEW mount point [/db/a33]
SIZE of file system
Unit Size 512bytes
Number of units [20512768]
Mount GROUP []
Mount AUTOMATICALLY at system restart? yes
PERMISSIONS read/write
Mount OPTIONS []
Start Disk Accounting? no
Fragment Size (bytes) 4096
Number of bytes per inode 32768
Compression algorithm no
Large File Enabled true
Allocation Group Size (MBytes) 16
Some calculations here for clarity:
Number of unites, 20512768*512 = 10G
4 times that yields 20512768*4 = 82051072 (this should be 40GB).
Command: OK stdout: yes stderr: no
Before command completion, additional instructions may appear below.
Filesystem size changed to 82051072
After the above commands runs successfully, check the size of the file system:
$ df -k
/dev/lv33 41025536 33034296 20% 27 1% /db/a33
/dev/lv34 10256384 2387704 77% 28 1% /db/a34
/dev/lv35 10256384 2387712 77% 27 1% /db/a35
/dev/lv36 10256384 2387704 77% 28 1% /db/a36
Now /db/a33 is 40GB!
Repeat for 34,35 & 36. You should get to:
/dev/lv33 41025536 33034296 20% 27 1% /db/a33
/dev/lv34 41025536 33034288 20% 28 1% /db/a34
/dev/lv35 41025536 33034296 20% 27 1% /db/a35
/dev/lv36 41025536 33034288 20% 28 1% /db/a36
6. And finally, increase DB tablespace!
. ~db2inst1/sqllib/db2profile
db2 connect to pwtest01 user pwtest using pwtest1
db2 "ALTER TABLESPACE L002 RESIZE (ALL 2000 M)"
(this increases L002 from 1GB to 8GB, because ALL extends all 4 file containers)
Now, take a look at the new file containers for L002:
bash-3.00# ls -l /db/a33/pwtest01/
total 19238912
-rw-rw-rw- 1 db2inst1 db2admi 0 Jul 24 02:42 .SQLCRT.FLG
-rw------- 1 db2inst1 db2admi 262144000 Aug 29 13:13 L001
-rw------- 1 db2inst1 db2admi 2097152000 Aug 29 15:58 L002 <== new size!!
-rw------- 1 db2inst1 db2admi 262144000 Aug 29 13:13 L003
-rw------- 1 db2inst1 db2admi 262144000 Aug 29 13:13 L004
-rw------- 1 db2inst1 db2admi 262144000 Aug 29 13:13 L005
-rw------- 1 db2inst1 db2admi 262144000 Aug 29 13:13 L006
-rw------- 1 db2inst1 db2admi 3221225472 Aug 29 15:57 U027
-rw------- 1 db2inst1 db2admi 3221225472 Aug 29 13:13 U029
Your database should be happy now!
http://www.performancewiki.com/extend-db-tablespace.html
The following steps are used to resolve full table space issue without having to re-create your database:
* 1. Find out which file containers are used for that tablespace (fully used)
* 2. Do you have enough space on the current file systems?
* 3. Increase the size for volume group
* 4. Increase size for logical volumes
* 5. Increase size for file systems
* 6. And finally, increase DB tablespace!
As your database tables grow, your originally planned disk space may simply run out. This is seen when you get the following error from DB2 db2diag.log (for both DB2 V8 and V9):
2007-08-29-13.02.27.267969-420 E14615A1054 LEVEL: Error
PID : 3743818 TID : 1 PROC : db2agent (PWTEST01) 0
INSTANCE: db2inst1 NODE : 000 DB : PWTEST01
APPHDL : 0-662 APPID: 10.0.4.50.54736.070829194050
AUTHID : FNRUN
FUNCTION: DB2 UDB, buffer pool services, sqlbAllocateExtent, probe:830
MESSAGE : ADM6044E The DMS table space "L002" (ID "105") is full. If this is
an autoresize or automatic storage DMS tablespace, the maximum table
space size may have been reached or the existing containers or
storage paths cannot grow any more. Additional space can be added to
the table space by either adding new containers or extending existing
ones using the ALTER TABLESPACE SQL statement. If this is an
autoresize or automatic storage DMS table space, additional space can
be added by adding containers to an autoresize table space or by
adding new storage paths to an automatic storage database.
You can alter tablespace (as recommended by the error message) to increase its size, but you may simply run out of disk space that the tablespace resides on. Many volume managers and file systems allow you to extend disk space as well, and that is what you have to do. The following steps apply to DB2 and AIX, but the process can work for other databases and operating systems as well. We expect you to replace commands used when working on other platforms.
1. Find out which file containers are used for that tablespace (which is full):
Normally this is done by search for the name of the tablespace in your
database definition script. Or if you don't have that (because the application
created it during installation), you can use the db2look command to dump
that to a file. Then do a grep on the name of the tablespace:
bash-3.00# grep L002 *
dbcreate.ddl:CREATE LARGE TABLESPACE L002 IN DATABASE PARTITION
dbcreate.ddl: USING (FILE '/db/a33/pwtest01/L002' 250M,
dbcreate.ddl: FILE '/db/a34/pwtest01/L002' 250M,
dbcreate.ddl: FILE '/db/a35/pwtest01/L002' 250M,
dbcreate.ddl: FILE '/db/a36/pwtest01/L002' 250M)
so we found the files used.
2. Do you have enough space on the current file systems?
# df -k
/dev/lv33 10256384 2387712 77% 27 1% /db/a33
/dev/lv34 10256384 2387704 77% 28 1% /db/a34
/dev/lv35 10256384 2387712 77% 27 1% /db/a35
/dev/lv36 10256384 2387704 77% 28 1% /db/a36
77% used. 10GB per file system, leaving about 230MB free space per.
You can check what else is on each of the file system:
bash-3.00# ls -l /db/a33/pwtest01/
total 15654912
-rw-rw-rw- 1 db2inst1 db2admi 0 Jul 24 02:42 .SQLCRT.FLG
-rw------- 1 db2inst1 db2admi 262144000 Aug 29 13:13 L001
-rw------- 1 db2inst1 db2admi 262144000 Aug 29 13:13 L002
-rw------- 1 db2inst1 db2admi 262144000 Aug 29 13:13 L003
-rw------- 1 db2inst1 db2admi 262144000 Aug 29 13:13 L004
-rw------- 1 db2inst1 db2admi 262144000 Aug 29 13:13 L005
-rw------- 1 db2inst1 db2admi 262144000 Aug 29 13:13 L006
-rw------- 1 db2inst1 db2admi 3221225472 Aug 29 13:13 U027
-rw------- 1 db2inst1 db2admi 3221225472 Aug 29 13:13 U029
3. Increase the size for volume group
If you think you need to extend tablespace size beyond 1GB (250Mx4), then you must increase
the file system (77% used already). But first, you need to extend size for logical volume,
but before you can do that, you need to extend size for the volume group that the logical
volume resides in ( we will use lv33 as an example, you just repeat the same for lv34,35,36).
First, find out the sizes for the volume group vg33:
bash-3.00# lsvg vg33
VOLUME GROUP: vg33 VG IDENTIFIER: 00ce3a8a00004c0000000113f77641d1
VG STATE: active PP SIZE: 32 megabyte(s)
VG PERMISSION: read/write TOTAL PPs: 319 (10208 megabytes)
MAX LVs: 256 FREE PPs: 5 (160 megabytes)
LVs: 2 USED PPs: 314 (10048 megabytes)
OPEN LVs: 2 QUORUM: 2
TOTAL PVs: 1 VG DESCRIPTORS: 2
STALE PVs: 0 STALE PPs: 0
ACTIVE PVs: 1 AUTO ON: yes
MAX PPs per VG: 32512
MAX PPs per PV: 1016 MAX PVs: 32
LTG size (Dynamic): 256 kilobyte(s) AUTO SYNC: no
HOT SPARE: no BB POLICY: relocatable
So vg33 is only 10GB. needs to extend that; Let's see what hdisks are in vg33:
# datapath query device
...
DEV#: 33 DEVICE NAME: vpath33 TYPE: 2107900 POLICY: Optimized
SERIAL: 75FABV1103D
===========================================================================
Path# Adapter/Hard Disk State Mode Select Errors
0 fscsi0/hdisk39 OPEN NORMAL 6735259 0
1 fscsi1/hdisk79 OPEN NORMAL 6743800 0
So if vpath33 offers 10GB space, then we need to add more vpath to the vg so
that it gets more hdisks.
Performce vg extend task in smitty (assume you run on AIX):
___________________________________________________________________________
Force the creation of a volume group? no
* VOLUME GROUP name [vg33]
* PHYSICAL VOLUME names []
PHYSICAL VOLUME names
Move cursor to desired item and press F7.
ONE OR MORE items can be selected.
Press Enter AFTER making all selections.
> vpath40
> vpath41
> vpath42
___________________________________________________________________________
This adds 3 more vpaths (here we used the same size: 10GB each vpath) to get
an additional 30GB more disk space.
4. Increase size for logical volumes
lv33 on vg33 now can be extended. Note: if your logical volume (lv) has a
MAX LOGICAL PARTITIONS setting, you may need to increase it so that addtional
partitions can be created on the new disks. Otherwise, you will be capped
at the old size. For example, you may need to change MAX LOGICAL PARTITIONS from
512 to 1024 (done in smitty).
To increase lv size, simply do in smitty the following:
Increase the Size of a Logical Volume
Type or select values in entry fields.
Press Enter AFTER making all desired changes.
[Entry Fields]
* LOGICAL VOLUME name lv34
* Number of ADDITIONAL logical partitions [939] <=== new size!
PHYSICAL VOLUME names [vpath43 vpath44 vpath45] <== new disks!
POSITION on physical volume middle
RANGE of physical volumes minimum
MAXIMUM NUMBER of PHYSICAL VOLUMES [32]
to use for allocation
Allocate each logical partition copy yes
on a SEPARATE physical volume?
File containing ALLOCATION MAP []
Note: 939 is "additional" partitions. The final number of partitions will be 313+939.
This is now 40GB.
5. Increase size for file systems
Now that lv33 is increased, it is time to increase the file system size. Also do this in
smitty.
Type or select values in entry fields.
Press Enter AFTER making all desired changes.
[Entry Fields]
File system name /db/a33
NEW mount point [/db/a33]
SIZE of file system
Unit Size 512bytes
Number of units [20512768]
Mount GROUP []
Mount AUTOMATICALLY at system restart? yes
PERMISSIONS read/write
Mount OPTIONS []
Start Disk Accounting? no
Fragment Size (bytes) 4096
Number of bytes per inode 32768
Compression algorithm no
Large File Enabled true
Allocation Group Size (MBytes) 16
Some calculations here for clarity:
Number of unites, 20512768*512 = 10G
4 times that yields 20512768*4 = 82051072 (this should be 40GB).
Command: OK stdout: yes stderr: no
Before command completion, additional instructions may appear below.
Filesystem size changed to 82051072
After the above commands runs successfully, check the size of the file system:
$ df -k
/dev/lv33 41025536 33034296 20% 27 1% /db/a33
/dev/lv34 10256384 2387704 77% 28 1% /db/a34
/dev/lv35 10256384 2387712 77% 27 1% /db/a35
/dev/lv36 10256384 2387704 77% 28 1% /db/a36
Now /db/a33 is 40GB!
Repeat for 34,35 & 36. You should get to:
/dev/lv33 41025536 33034296 20% 27 1% /db/a33
/dev/lv34 41025536 33034288 20% 28 1% /db/a34
/dev/lv35 41025536 33034296 20% 27 1% /db/a35
/dev/lv36 41025536 33034288 20% 28 1% /db/a36
6. And finally, increase DB tablespace!
. ~db2inst1/sqllib/db2profile
db2 connect to pwtest01 user pwtest using pwtest1
db2 "ALTER TABLESPACE L002 RESIZE (ALL 2000 M)"
(this increases L002 from 1GB to 8GB, because ALL extends all 4 file containers)
Now, take a look at the new file containers for L002:
bash-3.00# ls -l /db/a33/pwtest01/
total 19238912
-rw-rw-rw- 1 db2inst1 db2admi 0 Jul 24 02:42 .SQLCRT.FLG
-rw------- 1 db2inst1 db2admi 262144000 Aug 29 13:13 L001
-rw------- 1 db2inst1 db2admi 2097152000 Aug 29 15:58 L002 <== new size!!
-rw------- 1 db2inst1 db2admi 262144000 Aug 29 13:13 L003
-rw------- 1 db2inst1 db2admi 262144000 Aug 29 13:13 L004
-rw------- 1 db2inst1 db2admi 262144000 Aug 29 13:13 L005
-rw------- 1 db2inst1 db2admi 262144000 Aug 29 13:13 L006
-rw------- 1 db2inst1 db2admi 3221225472 Aug 29 15:57 U027
-rw------- 1 db2inst1 db2admi 3221225472 Aug 29 13:13 U029
Your database should be happy now!
http://www.performancewiki.com/extend-db-tablespace.html
Tablespace Monitoring -2
select b.tablespace, b.segfile#, b.segblk#,
round(((b.blocks*p.value)/1024/1024),2) size_mb,
a.sid, a.serial#, a.username, a.osuser, a.program, a.status
from v$session a, v$sort
_usage b, v$process c, v$parameter p
where p.name='db_block_size' and a.saddr = b.session_addr
and a.paddr=c.addr
order by b.tablespace,b.segfile#,b.segblk#,b.blocks;
-----------------------------------------------------
http://www.olympos.org/belgeler/veritabani/oracle-veritabaninda-veri-ve-sistem-guvenligi-5348.html
--------------------------------------------------------
Oracle'da Hangi Session Ne Kadar Temp Kullanıyor
Aşağıdaki sorgu TEMPORARY tablespace'i kullanan sessionları ve bu sessionların ne kadar temporary alan kullandığının bilgisini verir(MB cinsinden)
select b.tablespace, b.segfile#, b.segblk#,
round(((b.blocks*p.value)/1024/1024),2) size_mb,
a.sid, a.serial#, a.username, a.osuser, a.program, a.status
from v$session a, v$sort
_usage b, v$process c, v$parameter p
where p.name='db_block_size' and a.saddr = b.session_addr
and a.paddr=c.addr
order by b.tablespace,b.segfile#,b.segblk#,b.blocks;
Aşağıdaki sorgu da temporary tablespace'in her bir seferde kaç Kilobayt extent aldığını, toplam alacağı extent sayısını, o an alınmış extent sayısını, boştaki extent sayısını,kullanılmış maximum extent sayısını verir
SELECT tablespace_name,extent
_size, total_extents, used_extents,free_extents, max_used_size FROM v$sort_segment;
-------------------------------------------------------
Oracle Tablespaces
Version 11.1
General
Data Dictionary Objects
Tablespaces ts$
dba_tablespaces
user_tablespaces
Tablespace Quotas tsq$
dba_ts_quotas
user_ts_quotas
Data Files dba_data_files
v_$backup_datafile
v_$datafile
v_$datafile_copy
v_$datafile_header
Free Space dba_free_space
Segments dba_segments
v_$segment_statistics
Extents dba_extents
Blocks v_$database_block_corruption
Groups dba_tablespace_groups
SYSAUX Tablespace v_$sysaux_occupants
Temp Tablespace dba_temp_files
Undo Tablespace dba_rollback_segs
dba_undo_extents
v_$rollstat
v_$undostat
Transportable Tablespaces transport_set_violations
Dictionary Management fet$
uet$
System Privileges
alter tablespace drop tablespace unlimited tablespace
create tablespace manage tablespace
GRANT create tablespace TO uwclass;
GRANT alter tablespace TO uwclass;
GRANT drop tablespace TO uwclass;
GRANT manage tablespace TO uwclass;
GRANT unlimited tablespace TO uwclass;
Permanent Tablespace
Permanent Tablespace On A File System Without Auto-extend CREATE [] TABLESPACE
DATAFILE ''
SIZE [REUSE] AUTOEXTEND
BLOCKSIZE
[]
[FORCE LOGGING]
[ENCRYPTION USING ''
IDENTIFIED BY [NO] SALT]
[DEFAULT < COMPRESS [FOR OPERATIONS] | NOCOMPRESS>]
[]
EXTENT MANAGEMENT LOCAL < AUTOALLOCATE | UNIFORM SIZE>
[SEGMENT SPACE MANAGEMENT]
[FLASHBACK];
CREATE TABLESPACE uwdata DATAFILE
'c:\oracle\oradata\orabase\uwdata01.dbf' SIZE 150M,
'c:\oracle\oradata\orabase\uwdata02.dbf' SIZE 100M
AUTOEXTEND OFF
BLOCKSIZE 8192
FORCE LOGGING
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K
FLASHBACK ON;
desc dba_tablespaces
set linesize 121
col tablespace_name format a20
SELECT tablespace_name, block_size, status, contents, retention, extent_management, allocation_type, plugged_in, segment_space_management
FROM dba_tablespaces;
desc dba_data_files
col file_name format a45
SELECT file_name, tablespace_name, bytes, blocks, autoextensible, increment_by
FROM dba_data_files;
SELECT dbms_metadata.get_ddl('TABLESPACE', 'UWDATA')
FROM dual;
Permanent Tablespace On A Raw Device CREATE TABLESPACE
DATAFILE ''
SIZE
BLOCKSIZE
AUTOEXTEND OFF
EXTENT MANAGEMENT UNIFORM LOCAL SIZE
SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE tools LOGGING
DATAFILE '/u01/oradata/' SIZE 1024M
BLOCKSIZE 4096
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
SEGMENT SPACE MANAGEMENT AUTO;
Oracle Managed Permanent Auto-extendable Tablespace CREATE TABLESPACE;
ALTER SYSTEM SET DB_CREATE_FILE_DEST = $ORACLE_BASE/oradata';
CREATE TABLESPACE user_data;
Oracle Managed Permanent Fixed Size Tablespace CREATE TABLESPACE
DATAFILE AUTOEXTEND OFF;
ALTER SYSTEM
SET DB_CREATE_FILE_DEST = $ORACLE_BASE/oradata';
CREATE TABLESPACE user_data DATAFILE AUTOEXTEND OFF;
Set default tablespace type ALTER DATABASE SET DEFAULT TABLESPACE;
ALTER DATABASE DEFAULT BIGFILE TABLESPACE;
set linesize 121
col value$ format a20
col comment$ format a60
SELECT *
FROM props$
WHERE name LIKE '%DEF%'
ORDER BY by name;
Set tablespace as the default ALTER DATABASE DEFAULT TABLESPACE;
ALTER DATABASE DEFAULT TABLESPACE uwdata;
set linesize 121
col value$ format a20
col comment$ format a60
SELECT *
FROM props$
WHERE name LIKE '%DEF%'
ORDER BY by name;
SYSAUX Tablespace
Create SYSAUX Tablespace SELECT tablespace_name
FROM dba_tablespaces;
CREATE TABLESPACE sysaux
DATAFILE '/u01/oradata/sysaux01.dbf' SIZE 700M
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
Move SYSAUX Contents -- Move must be done using the indicated procedure
col occupant_name format a30
col schema_name format a30
col move_procedure format a50
SELECT occupant_name, schema_name, move_procedure
FROM v_$sysaux_occupants
ORDER BY 1;
Undo Tablespace
Create An UNDO Tablespace CREATE UNDO TABLESPACE
DATAFILE ''
SIZE
AUTOEXTEND
RETENTION;
CREATE UNDO TABLESPACE undotbs02
DATAFILE '/u01/oradata/undotbs02.dbf
SIZE 50000M REUSE AUTOEXTEND ON
RETENTION NOGUARANTEE;
desc dba_undo_extents
SELECT segment_name, tablespace_name, status, SUM(bytes)
FROM dba_undo_extents
GROUP BY segment_name, tablespace_name, status;
ALTER SYSTEM SET undo_tablespace='UNDOTBS2' scope=BOTH;
Change The Current UNDO Tablespace -- identify existing tablespaces
SELECT tablespace_name
FROM dba_tablespaces
ORDER BY 1;
-- format SQL*Plus display
col name format a30
col value format a30
-- check current undo related parameters
SELECT name, value
FROM gv$parameter
WHERE name LIKE '%undo%';
-- create the new undo tablespace
CREATE UNDO TABLESPACE testundo
DATAFILE '/u03/oradata/testundo.dbf'
SIZE 100M REUSE AUTOEXTEND ON
RETENTION NOGUARANTEE;
-- note that while it was created it is not being used
SELECT name, value
FROM gv$parameter
WHERE name LIKE '%undo%';
-- switch undo tablespaces
ALTER SYSTEM SET undo_tablespace = TESTUNDO SCOPE=BOTH;
-- verify the change has been performed
SELECT name, value
FROM gv$parameter
WHERE name LIKE '%undo%';
-- remove the old file
DROP TABLESPACE < old_tablespace's_name> INCLUDING CONTENTS AND DATAFILES;
Temporary Tablespaces
Create Temporary Tablespace CREATE TEMPORARY TABLESPACE
TEMPFILE ''
SIZE
AUTOEXTEND
TABLESPACE GROUP
EXTENT MANAGEMENT LOCAL UNIFORM SIZE;
Note:
You cannot use the ALTER TABLESPACE statement, with the TEMPORARY keyword, to change a locally managed permanent tablespace into a locally managed temporary tablespace. You must use the CREATE TEMPORARY TABLESPACE statement to create a locally managed temporary tablespace.
CREATE TEMPORARY TABLESPACE temp_new
TEMPFILE '/u01/oradata/tempnew01.dbf'
SIZE 1G AUTOEXTEND OFF
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K;
SELECT tablespace_name, block_size, status, contents, retention, extent_management, allocation_type, plugged_in
FROM dba_tablespaces;
desc dba_temp_files
SELECT file_name, tablespace_name, bytes, blocks, autoextensible, increment_by
FROM dba_temp_files;
Add Tempfile ALTER TABLESPACE
ADD TEMPFILE '' SIZE M;
ALTER TABLESPACE temp_new
ADD TEMPFILE '/u02/oradata/tempnew02.dbf' SIZE 200M;
Resize Tempfile ALTER DATABASE TEMPFILE ''
RESIZEM;
ALTER DATABASE TEMPFILE '/u02/oradata/tempnew02.dbf' RESIZE 250M;
Drop Tempfile ALTER DATABASE TEMPFILE '' DROP;
ALTER DATABASE TEMPFILE '/u02/oradata/tempnew02.dbf' DROP;
Take Temporary Tablespace Off-line ALTER DATABASE TEMPFILE '' OFFLINE;
ALTER DATABASE TEMPFILE '/u02/oradata/tempnew02.dbf' OFFLINE;
Place Temporary Tablespace On-line ALTER DATABASE TEMPFILE '' ONLINE;
ALTER DATABASE TEMPFILE '/u02/oradata/tempnew02.dbf' ONLINE;
Changing the Default Temporary Tablespace ALTER DATABASE DEFAULT TEMPORARY TABLESPACE;
col property_value format a30
col description format a55
SELECT *
FROM database_properties
WHERE property_name = 'DEFAULT_TEMP_TABLESPACE';
SELECT file_name, tablespace_name
FROM dba_temp_files;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE tempnew;
DROP TABLESPACE temp;
Transportable Tablespaces
Determine Transportability dbms_tts.transport_set_check(
tablespace_name IN VARCHAR2, TRUE);
dbms_tts.transport_set_check('uwdata', TRUE);
View Violations; If Any SELECT * FROM TRANSPORT_SET_VIOLATIONS;
Generate A Transportable Set ALTER TABLESPACE READ ONLY;
ALTER TABLESPACE tools READ ONLY;
Export Tablespace
Although the Export utility is used, only data dictionary structural information (metadata) for the tablespaces is exported. EXP TRANSPORT_TABLESPACE=Y
TABLESPACES=()
TRIGGERS=Y CONSTRAINTS=N GRANTS=N FILE=
EXP TRANSPORT_TABLESPACE=y TABLESPACES=(sales_1,sales_2)
TRIGGERS=y CONSTRAINTS=n GRANTS=n FILE=expdat.dmp
Import Tablespace IMP TRANSPORT_TABLESPACE= FILE=
DATAFILES=(')
TABLESPACES=()
TTS_OWNERS=()
FROMUSER=(dcranney,jfee) TOUSER=(smith,williams)
IMP TRANSPORT_TABLESPACE=y FILE=expdat.dmp
DATAFILES=('/db/sales_jan','/db/sales_feb')
TABLESPACES=(sales_1,sales_2) TTS_OWNERS=(dcranney,jfee)
FROMUSER=(dcranney,jfee) TOUSER=(smith,williams)
Import Parameter File TRANSPORT_TABLESPACE=y
FILE=expdat.dmp
DATAFILES=('/db/sales_jan','/db/sales_feb')
TABLESPACES=(sales_1, sales_2)
TTS_OWNERS=(dcranney, jfee)
FROMUSER=(dcranney, jfee)
TOUSER=(smith, williams)
Alter Permanent Tablespace
Add Datafile ALTER TABLESPACE ADD DATAFILE,
[REUSE]
NEXT
MAXSIZE
ALTER TABLESPACE tools ADD DATAFILE
'/u02/oracle/oradata/uwdata02.dbf' SIZE 25M
AUTOEXTEND OFF;
ALTER TABLESPACE tools ADD ADD DATAFILE
'c:\oracle\product\oradata\tools99.xxx' SIZE 10M
AUTOEXTEND ON;
Take Off-line ALTER TABLESPACE OFFLINE;
ALTER TABLESPACE tools OFFLINE;
Place On-line ALTER TABLESPACE ONLINE;
ALTER TABLESPACE tools ONLINE;
Make Read Only ALTER TABLESPACE READ ONLY;
ALTER TABLESPACE tools READ ONLY;
SELECT tablespace_name, status
FROM dba_tablespaces;
-- READ ONLY prevents DML ... not DDL including DROP and TRUNCATE.
Make A Tablespace Read Write ALTER TABLESPACE READ WRITE;
ALTER TABLESPACE tools READ WRITE;
SELECT tablespace_name, status
FROM dba_tablespaces;
Prepare Tablespace For Backup (archive logging must be active) ALTER TABLESPACE BEGIN BACKUP;
ALTER TABLESPACE tools BEGIN BACKUP;
End Tablespace Backup ALTER TABLESPACE END BACKUP;
ALTER TABLESPACE tools END BACKUP;
Rename ALTER TABLESPACE RENAME TO ;
SELECT tablespace_name
FROM dba_tablespaces;
SELECT table_name
FROM dba_tables
WHERE tablespace_name = 'USERS';
ALTER TABLESPACE users RENAME TO user_data;
SELECT tablespace_name
FROM dba_tablespaces;
SELECT table_name
FROM dba_tables
WHERE tablespace_name = 'USER_DATA';
Alter Undo Tablespace
Retention Guarantee ALTER TABLESPACE RETENTION ;
SELECT tablespace_name, retention
FROM dba_tablespaces;
ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;
select tablespace_name, retention
from dba_tablespaces;
ALTER TABLESPACE undotbs1 RETENTION NOGUARANTEE;
select tablespace_name, retention
from dba_tablespaces;
Drop Tablespace
Drop Tablespace DROP TABLESPACE;
DROP TABLESPACE tools;
Drop Tablespace Including Contents DROP TABLESPACE
INCLUDING CONTENTS;
DROP TABLESPACE tools INCLUDING CONTENTS;
Drop Tablespace Including Contents & Datafiles DROP TABLESPACE
INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE tools INCLUDING CONTENTS AND DATAFILES;
Drop Tablespace Including Contents & Datafiles When There Are Referential Constraints DROP TABLESPACE INCLUDING CONTENTS AND DATAFILES
CASCADE CONSTRAINTS;
DROP TABLESPACE tools INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;
Drop tablespace after datafile was accidentally dropped conn / as sysdba
CREATE TABLESPACE badidea
DATAFILE 'c:\temp\badidea.dbf' SIZE 10M
BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K
SEGMENT SPACE MANAGEMENT AUTO
ONLINE;
SELECT tablespace_name
FROM dba_tablespaces;
SELECT file_name
FROM dba_data_files;
shutdown immediate;
-- in an operating system window drop the file c:\temp\badidea.dbf
startup
-- record the error message
shutdown immediate;
startup mount;
alter database datafile 'c:\temp\badidea.dbf' offline drop;
alter database open;
SELECT tablespace_name
FROM dba_tablespaces;
SELECT file_name
FROM dba_data_files;
drop tablespace badidea including contents;
SELECT tablespace_name
FROM dba_tablespaces;
SELECT file_name
FROM dba_data_files;
Mandatory Tablespaces
System (must be named SYSTEM) - all versions
Always named system every Oracle instance must have one, and only one system tablespace. This tablespace contains the Oracle data dictionary tables and views. It is also where Oracle stores SQL programs such as stored procedures, functions, packages, and Java.
Undo (any name: the default is UNDOTSP1) - version 9i or above
Every Oracle instance must have one, and only one UNDO tablespace. The undo tablespace is a single large space into which Oracle stores and manager information for undo (rollback) and multi-versioning for all users and all transactions.
Temporary (any name but usually TEMP)
Every Oracle instance must have at least one temp tablespace and it can have any name. The default name is TEMP. The temp tablespace is used by Oracle to create temporary tables which it uses during processing of a request and for storing information for views and global temporary tables. Examples of transactions in which Oracle uses temp space are sorts and groupings.
One or more tablespaces for tables and indexes.
Every Oracle instance may have at least one and usually many tablespaces reserved for holding tables and indexes. These tablespaces can have any name but the default names are often like DATA01 and USERS.
It is advisable to spread I/O equally across multiple disks. And one way to accomplish this is to create separate tablespaces for tables and indexes and to store them on separate hard-drives if at all possible to improve system performance. In large systems it is usual to find each application stored in a separate tablespace and where tables of vastly different sizes are required to have tablespaces created to hold tables with small, medium, and large extents. By segregating tables by extent size into separate tablespaces it is possible to eliminate tablespace fragmentation which improves system performance and eliminates the wasting of disk space.
Definitions
The Number Of Extents - Dictionary vs. Locally Managed Tablespaces The number of extents has never been an issue for a segment. A table couldn't care less whether it is contained in 5 extents or 5000. But what does care is the data dictionary, since 5000 extents means 5000 rows in a data dictionary cluster that's been sized to expect no more than 5. That introduces cluster chaining in the data dictionary, and if the chaining is bad enough, then performance impacts involving dictionary access will be measurable.
Since LMTs don't touch the data dictionary for the purposes of recording extent acquisition, they do not suffer from cluster chaining, and do not experience a resulting performance degradation.
The performance issue also relates to the reading of the extent map in a single I/O instead of multiple I/Os ... since the extent map is stored inside an Oracle block, which is of finite size, too many extents mean that the map has to be stored in multiple blocks ... and any operation that needs to consult the extent map would therefore need multiple I/O operations to do so. You would really need thousands of extents, though, before that became a major issue).
The key advantage of LMTs is that they avoid a potential single point of contention on the data dictionary (but you'd have to have dozens of segments all simultaneously extending before that was an issue in the first place). And (better) they mean fragmentation is a thing of the past. And (the real killer) concern about a reasonable number of extents is wasted concern (reasonable being in the high hundreds to low thousands).
Logging Specify the default logging attributes of all tables, indexes, materialized views, materialized view logs, and partitions within the tablespace. LOGGING is the default. This clause is not valid for a temporary or undo tablespace.
The tablespace-level logging attribute can be overridden by logging specifications at the table, index, materialized view, materialized view log, and partition levels.
The force logging clause is used to force logging and to disregard no-logging instructions issued during object creation. You cannot specify FORCE LOGGING for an undo or temporary tablespace.
This clause only affects the logging of object creation and has no effect on logging of DML statements.
Segment Space Management When you create a locally managed tablespace using the CREATE TABLESPACE statement, the SEGMENT SPACE MANAGEMENT clause allows you to specify how free and used space within a segment is to be managed. Your choices are:
MANUAL
Specifying MANUAL tells Oracle that you want to use free lists for managing free space within segments. Free lists are lists of data blocks that have space available for inserting rows. This form of managing space within segments is called manual segment-space management because of the need to specify and tune the PCTUSED, FREELISTS, and FREELISTS GROUPS storage parameters for schema objects created in the tablespace.
MANUAL is the default.
AUTO
This keyword tells Oracle that you want to use bitmaps to manage the free space within segments. A bitmap, in this case, is a map that describes the status of each data block within a segment with respect to the amount of space in the block available for inserting rows. As more or less space becomes available in a data block, its new state is reflected in the bitmap. Bitmaps allow Oracle to manage free space more automatically, and thus, this form of space management is called automatic segment-space management.
Automatic segment-space management is a simpler and more efficient way of managing space within a segment. It completely eliminates any need to specify and tune the PCTUSED, FREELISTS, and FREELISTS GROUPS storage parameters for schema objects created in the tablespace. If such attributes should be specified, they are ignored.
Automatic segment-space management delivers better space utilization than manual segment-space management, and it is self tuning in that it scales with increasing the number of users, as well as instances. For a Real Application Clusters environment, automatic segment-space management allows for a dynamic affinity of space to instances, thus avoiding the hard partitioning of space inherent with using free list groups.
For many standard workloads, application performance when using automatic segment space management is better than the performance of a well tuned application using manual segment-space management.
Related Queries
List tablespaces, their files, allocated space, free space, and next free extent clear breaks
set linesize 132
set pagesize 60
break on tablespace_name skip 1
col tablespace_name format a15
col file_name format a50
col tablespace_kb heading 'TABLESPACE|TOTAL KB'
col kbytes_free heading 'TOTAL FREE|KBYTES'
SELECT dd.tablespace_name tablespace_name, dd.file_name file_name, dd.bytes/1024 TABLESPACE_KB, SUM(fs.bytes)/1024 KBYTES_FREE, MAX(fs.bytes)/1024 NEXT_FREE
FROM sys.dba_free_space fs, sys.dba_data_files dd
WHERE dd.tablespace_name = fs.tablespace_name
AND dd.file_id = fs.file_id
GROUP BY dd.tablespace_name, dd.file_name, dd.bytes/1024
ORDER BY dd.tablespace_name, dd.file_name;
List datafiles, tablespace names, and size in MB col file_name format a50
col tablespace_name format a10
SELECT file_name, tablespace_name, ROUND(bytes/1024000) MB
FROM dba_data_files
ORDER BY 1;
List tablespaces, size, free space, and percent free
Thanks to Michael Lehmann for this query SELECT df.tablespace_name TABLESPACE, df.total_space TOTAL_SPACE,
fs.free_space FREE_SPACE, df.total_space_mb TOTAL_SPACE_MB,
(df.total_space_mb - fs.free_space_mb) USED_SPACE_MB,
fs.free_space_mb FREE_SPACE_MB,
ROUND(100 * (fs.free_space / df.total_space),2) PCT_FREE
FROM (SELECT tablespace_name, SUM(bytes) TOTAL_SPACE,
ROUND(SUM(bytes) / 1048576) TOTAL_SPACE_MB
FROM dba_data_files
GROUP BY tablespace_name) df,
(SELECT tablespace_name, SUM(bytes) FREE_SPACE,
ROUND(SUM(bytes) / 1048576) FREE_SPACE_MB
FROM dba_free_space
GROUP BY tablespace_name) fs
WHERE df.tablespace_name = fs.tablespace_name(+)
ORDER BY fs.tablespace_name;
View For Schema Owner To Monitoring Free Space CREATE OR REPLACE VIEW freespace_view AS
SELECT tablespace_name, SUM(bytes/1024/1024) AVAILABLE
FROM user_free_space
GROUP BY tablespace_name;
Another Statement For
Tablespace Management set linesize 121
SELECT tablespace_name, ROUND(SUM(total_mb)-SUM(free_mb)) CUR_USE_MB, ROUND(SUM(total_mb)) CUR_SZ_MB,
ROUND((SUM(total_mb)-SUM(free_mb))/SUM(total_mb)*100) CUR_PCT_FULL, ROUND(SUM(max_mb) - (SUM(total_mb)-SUM(free_mb))) FREE_SPACE_MB,
ROUND(SUM(max_mb)) MAX_SZ_MB, ROUND((SUM(total_mb)-SUM(free_mb))/SUM(max_mb)*100) PCT_FULL
FROM (
SELECT tablespace_name, SUM(bytes)/1024/1024 FREE_MB,
0 TOTAL_MB, 0 MAX_MB
FROM dba_free_space
GROUP BY tablespace_name
UNION
SELECT tablespace_name, 0 CURRENT_MB,
SUM(bytes)/1024/1024 TOTAL_MB,
SUM(DECODE(maxbytes,0,bytes, maxbytes))/1024/1024 MAX_MB
FROM dba_data_files
GROUP BY tablespace_name)
GROUP BY tablespace_name;
Yet Another Statement For
Tablespace Management col tablespace_name format a15
col alloc_size format 999.999
col pct_used format 999.999
col free_space format 999.999
col maxnext format 999.999
col definitsz format 999.999
col defnextsz format 999.999
SELECT a.tablespace_name, a.datafile_sz, b.alloc_size,
(b.alloc_size)/a.datafile_sz*100 PCT_USED,
(a.datafile_sz-b.alloc_size) FREE_SPACE,
b.next_extent/1024/1024 MAXNEXT,
a.initial_extent/1024/1024 DEFINITSZ,
a.next_extent/1024/1024 DEFNEXTSZ
FROM (
SELECT a.tablespace_name, sum(b.bytes)/1024/1024
DATAFILE_SZ, a.initial_extent, a.next_extent
FROM dba_tablespaces a, dba_data_files b
WHERE a.tablespace_name = b.tablespace_name
GROUP BY a.tablespace_name, a.initial_extent, a.next_extent) A,
(
SELECT a.tablespace_name, sum(c.bytes)/1024/1024
ALLOC_SIZE, MAX(c.next_extent) NEXT_EXTENT
FROM dba_tablespaces a, dba_segments c
WHERE a.tablespace_name = c.tablespace_name
GROUP BY a.tablespace_name) B
WHERE a.tablespace_name = b.tablespace_name (+)
ORDER BY 1;
And Yet Another Statement For Tablespace Management SELECT dfs.tablespace_name, ddf.total_size,
ddf.total_size - dfs.total_free TOTAL_USED,
dfs.total_free,
(ddf.total_size-dfs.total_free)/ddf.total_size * 100 CAP,
dfs.total_chunks, dfs.largest_chunk
FROM (
SELECT a.tablespace_name,
SUM(a.bytes)/1024/1024 TOTAL_FREE,
COUNT(a.bytes) TOTAL_CHUNKS,
MAX(a.bytes)/1024/1024 LARGEST_CHUNK
FROM dba_free_space a
GROUP BY a.tablespace_name) dfs,
(
SELECT b.tablespace_name,
SUM(b.bytes)/1024/1024 TOTAL_SIZE
FROM dba_data_files b
GROUP BY b.tablespace_name) ddf
WHERE dfs.tablespace_name = ddf.tablespace_name
ORDER BY dfs.tablespace_name;
Calculation Of Minimum Tablespace Size (this takes a long time to run) SELECT SUBSTR(f.file_name,1,70) FILENAME,
MAX(e.block_id*(e.bytes/e.blocks)+e.bytes)/1024 MIN_SIZE
FROM dba_extents e, dba_data_files f
WHERE e.file_id = f.file_id
GROUP BY f.file_name;
Schemas In The SYSAUX Tablespace col occupant_name format a25
col schema_name format a20
col move_procedure format a30
col move_procedure_desc format a40
set linesize 131
SELECT occupant_name, schema_name, move_procedure, move_procedure_desc
FROM v_$sysaux_occupants;
Contiguous Space create table t_contig_space (
tablespace_name VARCHAR2(30),
file_id NUMBER,
block_id NUMBER,
starting_file_id NUMBER,
starting_block_id NUMBER,
blocks NUMBER,
bytes NUMBER)
tablespace uwdata;
CREATE OR REPLACE VIEW v_contig_space AS
SELECT SUBSTR(tablespace_name,1,20) TABLESPACE_NAME,
starting_file_id, starting_block_id, SUM(blocks) sum_blocks,
COUNT(blocks) count_blocks, MAX(blocks) max_blocks,
SUM(bytes)/1024/1024 SUM_MB
FROM tl_contig_space
GROUP BY tablespace_name, starting_file_id, starting_block_id;
DECLARE
CURSOR query IS
SELECT *
FROM dba_free_space
ORDER BY tablespace_name, file_id, block_id;
this_row query%ROWTYPE;
previous_row query%ROWTYPE;
old_file_id PLS_INTEGER;
old_block_id PLS_INTEGER;
BEGIN
OPEN query;
FETCH query INTO this_row;
previous_row := this_row;
old_file_id := previous_row.file_id;
old_block_id := previous_row.block_id;
WHILE query%FOUND loop
IF this_row.file_id = previous_row.file_id AND
this_row.block_id = previous_row.block_id+previous_row.blocks
THEN
INSERT INTO tl_contig_space
(tablespace_name, file_id, block_id, starting_file_id,
starting_block_id, blocks, bytes)
VALUES
(previous_row.tablespace_name, previous_row.file_id,
this_row.block_id, old_file_id, old_block_id, this_row.blocks,
this_row.bytes);
ELSE
INSERT INTO tl_contig_space
(tablespace_name, file_id, block_id, starting_file_id,
starting_block_id, blocks, bytes)
VALUES
(this_row.tablespace_name, this_row.file_id,
this_row.block_id, this_row.file_id, this_row.block_id,
this_row.blocks, this_row.bytes);
old_file_id := this_row.file_id;
old_block_id := this_row.block_id;
END IF;
previous_row := this_row;
FETCH query INTO this_row;
END LOOP;
COMMIT;
END;
/
col tablespace_name format a20
col sum_mb format 999.999
SELECT * FROM v_contig_space;
http://www.psoug.org/reference/tablespaces.html
round(((b.blocks*p.value)/1024/1024),2) size_mb,
a.sid, a.serial#, a.username, a.osuser, a.program, a.status
from v$session a, v$sort
_usage b, v$process c, v$parameter p
where p.name='db_block_size' and a.saddr = b.session_addr
and a.paddr=c.addr
order by b.tablespace,b.segfile#,b.segblk#,b.blocks;
-----------------------------------------------------
http://www.olympos.org/belgeler/veritabani/oracle-veritabaninda-veri-ve-sistem-guvenligi-5348.html
--------------------------------------------------------
Oracle'da Hangi Session Ne Kadar Temp Kullanıyor
Aşağıdaki sorgu TEMPORARY tablespace'i kullanan sessionları ve bu sessionların ne kadar temporary alan kullandığının bilgisini verir(MB cinsinden)
select b.tablespace, b.segfile#, b.segblk#,
round(((b.blocks*p.value)/1024/1024),2) size_mb,
a.sid, a.serial#, a.username, a.osuser, a.program, a.status
from v$session a, v$sort
_usage b, v$process c, v$parameter p
where p.name='db_block_size' and a.saddr = b.session_addr
and a.paddr=c.addr
order by b.tablespace,b.segfile#,b.segblk#,b.blocks;
Aşağıdaki sorgu da temporary tablespace'in her bir seferde kaç Kilobayt extent aldığını, toplam alacağı extent sayısını, o an alınmış extent sayısını, boştaki extent sayısını,kullanılmış maximum extent sayısını verir
SELECT tablespace_name,extent
_size, total_extents, used_extents,free_extents, max_used_size FROM v$sort_segment;
-------------------------------------------------------
Oracle Tablespaces
Version 11.1
General
Data Dictionary Objects
Tablespaces ts$
dba_tablespaces
user_tablespaces
Tablespace Quotas tsq$
dba_ts_quotas
user_ts_quotas
Data Files dba_data_files
v_$backup_datafile
v_$datafile
v_$datafile_copy
v_$datafile_header
Free Space dba_free_space
Segments dba_segments
v_$segment_statistics
Extents dba_extents
Blocks v_$database_block_corruption
Groups dba_tablespace_groups
SYSAUX Tablespace v_$sysaux_occupants
Temp Tablespace dba_temp_files
Undo Tablespace dba_rollback_segs
dba_undo_extents
v_$rollstat
v_$undostat
Transportable Tablespaces transport_set_violations
Dictionary Management fet$
uet$
System Privileges
alter tablespace drop tablespace unlimited tablespace
create tablespace manage tablespace
GRANT create tablespace TO uwclass;
GRANT alter tablespace TO uwclass;
GRANT drop tablespace TO uwclass;
GRANT manage tablespace TO uwclass;
GRANT unlimited tablespace TO uwclass;
Permanent Tablespace
Permanent Tablespace On A File System Without Auto-extend CREATE [
DATAFILE '
SIZE
BLOCKSIZE
[
[FORCE LOGGING]
[ENCRYPTION USING '
IDENTIFIED BY
[DEFAULT < COMPRESS [FOR
[
EXTENT MANAGEMENT LOCAL < AUTOALLOCATE | UNIFORM SIZE
[SEGMENT SPACE MANAGEMENT
[FLASHBACK
CREATE TABLESPACE uwdata DATAFILE
'c:\oracle\oradata\orabase\uwdata01.dbf' SIZE 150M,
'c:\oracle\oradata\orabase\uwdata02.dbf' SIZE 100M
AUTOEXTEND OFF
BLOCKSIZE 8192
FORCE LOGGING
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K
FLASHBACK ON;
desc dba_tablespaces
set linesize 121
col tablespace_name format a20
SELECT tablespace_name, block_size, status, contents, retention, extent_management, allocation_type, plugged_in, segment_space_management
FROM dba_tablespaces;
desc dba_data_files
col file_name format a45
SELECT file_name, tablespace_name, bytes, blocks, autoextensible, increment_by
FROM dba_data_files;
SELECT dbms_metadata.get_ddl('TABLESPACE', 'UWDATA')
FROM dual;
Permanent Tablespace On A Raw Device CREATE TABLESPACE
DATAFILE '
SIZE
BLOCKSIZE
AUTOEXTEND OFF
EXTENT MANAGEMENT UNIFORM LOCAL SIZE
SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE tools LOGGING
DATAFILE '/u01/oradata/' SIZE 1024M
BLOCKSIZE 4096
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
SEGMENT SPACE MANAGEMENT AUTO;
Oracle Managed Permanent Auto-extendable Tablespace CREATE TABLESPACE
ALTER SYSTEM SET DB_CREATE_FILE_DEST = $ORACLE_BASE/oradata';
CREATE TABLESPACE user_data;
Oracle Managed Permanent Fixed Size Tablespace CREATE TABLESPACE
DATAFILE AUTOEXTEND OFF;
ALTER SYSTEM
SET DB_CREATE_FILE_DEST = $ORACLE_BASE/oradata';
CREATE TABLESPACE user_data DATAFILE AUTOEXTEND OFF;
Set default tablespace type ALTER DATABASE SET DEFAULT
ALTER DATABASE DEFAULT BIGFILE TABLESPACE;
set linesize 121
col value$ format a20
col comment$ format a60
SELECT *
FROM props$
WHERE name LIKE '%DEF%'
ORDER BY by name;
Set tablespace as the default ALTER DATABASE DEFAULT TABLESPACE
ALTER DATABASE DEFAULT TABLESPACE uwdata;
set linesize 121
col value$ format a20
col comment$ format a60
SELECT *
FROM props$
WHERE name LIKE '%DEF%'
ORDER BY by name;
SYSAUX Tablespace
Create SYSAUX Tablespace SELECT tablespace_name
FROM dba_tablespaces;
CREATE TABLESPACE sysaux
DATAFILE '/u01/oradata/sysaux01.dbf' SIZE 700M
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
Move SYSAUX Contents -- Move must be done using the indicated procedure
col occupant_name format a30
col schema_name format a30
col move_procedure format a50
SELECT occupant_name, schema_name, move_procedure
FROM v_$sysaux_occupants
ORDER BY 1;
Undo Tablespace
Create An UNDO Tablespace CREATE UNDO TABLESPACE
DATAFILE '
SIZE
AUTOEXTEND
RETENTION
CREATE UNDO TABLESPACE undotbs02
DATAFILE '/u01/oradata/undotbs02.dbf
SIZE 50000M REUSE AUTOEXTEND ON
RETENTION NOGUARANTEE;
desc dba_undo_extents
SELECT segment_name, tablespace_name, status, SUM(bytes)
FROM dba_undo_extents
GROUP BY segment_name, tablespace_name, status;
ALTER SYSTEM SET undo_tablespace='UNDOTBS2' scope=BOTH;
Change The Current UNDO Tablespace -- identify existing tablespaces
SELECT tablespace_name
FROM dba_tablespaces
ORDER BY 1;
-- format SQL*Plus display
col name format a30
col value format a30
-- check current undo related parameters
SELECT name, value
FROM gv$parameter
WHERE name LIKE '%undo%';
-- create the new undo tablespace
CREATE UNDO TABLESPACE testundo
DATAFILE '/u03/oradata/testundo.dbf'
SIZE 100M REUSE AUTOEXTEND ON
RETENTION NOGUARANTEE;
-- note that while it was created it is not being used
SELECT name, value
FROM gv$parameter
WHERE name LIKE '%undo%';
-- switch undo tablespaces
ALTER SYSTEM SET undo_tablespace = TESTUNDO SCOPE=BOTH;
-- verify the change has been performed
SELECT name, value
FROM gv$parameter
WHERE name LIKE '%undo%';
-- remove the old file
DROP TABLESPACE < old_tablespace's_name> INCLUDING CONTENTS AND DATAFILES;
Temporary Tablespaces
Create Temporary Tablespace CREATE TEMPORARY TABLESPACE
TEMPFILE '
SIZE
AUTOEXTEND
TABLESPACE GROUP
EXTENT MANAGEMENT LOCAL UNIFORM SIZE
Note:
You cannot use the ALTER TABLESPACE statement, with the TEMPORARY keyword, to change a locally managed permanent tablespace into a locally managed temporary tablespace. You must use the CREATE TEMPORARY TABLESPACE statement to create a locally managed temporary tablespace.
CREATE TEMPORARY TABLESPACE temp_new
TEMPFILE '/u01/oradata/tempnew01.dbf'
SIZE 1G AUTOEXTEND OFF
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K;
SELECT tablespace_name, block_size, status, contents, retention, extent_management, allocation_type, plugged_in
FROM dba_tablespaces;
desc dba_temp_files
SELECT file_name, tablespace_name, bytes, blocks, autoextensible, increment_by
FROM dba_temp_files;
Add Tempfile ALTER TABLESPACE
ADD TEMPFILE '
ALTER TABLESPACE temp_new
ADD TEMPFILE '/u02/oradata/tempnew02.dbf' SIZE 200M;
Resize Tempfile ALTER DATABASE TEMPFILE '
RESIZE
ALTER DATABASE TEMPFILE '/u02/oradata/tempnew02.dbf' RESIZE 250M;
Drop Tempfile ALTER DATABASE TEMPFILE '
ALTER DATABASE TEMPFILE '/u02/oradata/tempnew02.dbf' DROP;
Take Temporary Tablespace Off-line ALTER DATABASE TEMPFILE '
ALTER DATABASE TEMPFILE '/u02/oradata/tempnew02.dbf' OFFLINE;
Place Temporary Tablespace On-line ALTER DATABASE TEMPFILE '
ALTER DATABASE TEMPFILE '/u02/oradata/tempnew02.dbf' ONLINE;
Changing the Default Temporary Tablespace ALTER DATABASE DEFAULT TEMPORARY TABLESPACE
col property_value format a30
col description format a55
SELECT *
FROM database_properties
WHERE property_name = 'DEFAULT_TEMP_TABLESPACE';
SELECT file_name, tablespace_name
FROM dba_temp_files;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE tempnew;
DROP TABLESPACE temp;
Transportable Tablespaces
Determine Transportability dbms_tts.transport_set_check(
tablespace_name IN VARCHAR2, TRUE);
dbms_tts.transport_set_check('uwdata', TRUE);
View Violations; If Any SELECT * FROM TRANSPORT_SET_VIOLATIONS;
Generate A Transportable Set ALTER TABLESPACE
ALTER TABLESPACE tools READ ONLY;
Export Tablespace
Although the Export utility is used, only data dictionary structural information (metadata) for the tablespaces is exported. EXP TRANSPORT_TABLESPACE=Y
TABLESPACES=(
TRIGGERS=Y CONSTRAINTS=N GRANTS=N FILE=
EXP TRANSPORT_TABLESPACE=y TABLESPACES=(sales_1,sales_2)
TRIGGERS=y CONSTRAINTS=n GRANTS=n FILE=expdat.dmp
Import Tablespace IMP TRANSPORT_TABLESPACE=
DATAFILES=('
TABLESPACES=(
TTS_OWNERS=(
FROMUSER=(dcranney,jfee) TOUSER=(smith,williams)
IMP TRANSPORT_TABLESPACE=y FILE=expdat.dmp
DATAFILES=('/db/sales_jan','/db/sales_feb')
TABLESPACES=(sales_1,sales_2) TTS_OWNERS=(dcranney,jfee)
FROMUSER=(dcranney,jfee) TOUSER=(smith,williams)
Import Parameter File TRANSPORT_TABLESPACE=y
FILE=expdat.dmp
DATAFILES=('/db/sales_jan','/db/sales_feb')
TABLESPACES=(sales_1, sales_2)
TTS_OWNERS=(dcranney, jfee)
FROMUSER=(dcranney, jfee)
TOUSER=(smith, williams)
Alter Permanent Tablespace
Add Datafile ALTER TABLESPACE ADD DATAFILE
[REUSE]
NEXT
MAXSIZE
ALTER TABLESPACE tools ADD DATAFILE
'/u02/oracle/oradata/uwdata02.dbf' SIZE 25M
AUTOEXTEND OFF;
ALTER TABLESPACE tools ADD ADD DATAFILE
'c:\oracle\product\oradata\tools99.xxx' SIZE 10M
AUTOEXTEND ON;
Take Off-line ALTER TABLESPACE
ALTER TABLESPACE tools OFFLINE;
Place On-line ALTER TABLESPACE
ALTER TABLESPACE tools ONLINE;
Make Read Only ALTER TABLESPACE
ALTER TABLESPACE tools READ ONLY;
SELECT tablespace_name, status
FROM dba_tablespaces;
-- READ ONLY prevents DML ... not DDL including DROP and TRUNCATE.
Make A Tablespace Read Write ALTER TABLESPACE
ALTER TABLESPACE tools READ WRITE;
SELECT tablespace_name, status
FROM dba_tablespaces;
Prepare Tablespace For Backup (archive logging must be active) ALTER TABLESPACE
ALTER TABLESPACE tools BEGIN BACKUP;
End Tablespace Backup ALTER TABLESPACE
ALTER TABLESPACE tools END BACKUP;
Rename ALTER TABLESPACE
SELECT tablespace_name
FROM dba_tablespaces;
SELECT table_name
FROM dba_tables
WHERE tablespace_name = 'USERS';
ALTER TABLESPACE users RENAME TO user_data;
SELECT tablespace_name
FROM dba_tablespaces;
SELECT table_name
FROM dba_tables
WHERE tablespace_name = 'USER_DATA';
Alter Undo Tablespace
Retention Guarantee ALTER TABLESPACE
SELECT tablespace_name, retention
FROM dba_tablespaces;
ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;
select tablespace_name, retention
from dba_tablespaces;
ALTER TABLESPACE undotbs1 RETENTION NOGUARANTEE;
select tablespace_name, retention
from dba_tablespaces;
Drop Tablespace
Drop Tablespace DROP TABLESPACE
DROP TABLESPACE tools;
Drop Tablespace Including Contents DROP TABLESPACE
INCLUDING CONTENTS;
DROP TABLESPACE tools INCLUDING CONTENTS;
Drop Tablespace Including Contents & Datafiles DROP TABLESPACE
INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE tools INCLUDING CONTENTS AND DATAFILES;
Drop Tablespace Including Contents & Datafiles When There Are Referential Constraints DROP TABLESPACE
CASCADE CONSTRAINTS;
DROP TABLESPACE tools INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;
Drop tablespace after datafile was accidentally dropped conn / as sysdba
CREATE TABLESPACE badidea
DATAFILE 'c:\temp\badidea.dbf' SIZE 10M
BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K
SEGMENT SPACE MANAGEMENT AUTO
ONLINE;
SELECT tablespace_name
FROM dba_tablespaces;
SELECT file_name
FROM dba_data_files;
shutdown immediate;
-- in an operating system window drop the file c:\temp\badidea.dbf
startup
-- record the error message
shutdown immediate;
startup mount;
alter database datafile 'c:\temp\badidea.dbf' offline drop;
alter database open;
SELECT tablespace_name
FROM dba_tablespaces;
SELECT file_name
FROM dba_data_files;
drop tablespace badidea including contents;
SELECT tablespace_name
FROM dba_tablespaces;
SELECT file_name
FROM dba_data_files;
Mandatory Tablespaces
System (must be named SYSTEM) - all versions
Always named system every Oracle instance must have one, and only one system tablespace. This tablespace contains the Oracle data dictionary tables and views. It is also where Oracle stores SQL programs such as stored procedures, functions, packages, and Java.
Undo (any name: the default is UNDOTSP1) - version 9i or above
Every Oracle instance must have one, and only one UNDO tablespace. The undo tablespace is a single large space into which Oracle stores and manager information for undo (rollback) and multi-versioning for all users and all transactions.
Temporary (any name but usually TEMP)
Every Oracle instance must have at least one temp tablespace and it can have any name. The default name is TEMP. The temp tablespace is used by Oracle to create temporary tables which it uses during processing of a request and for storing information for views and global temporary tables. Examples of transactions in which Oracle uses temp space are sorts and groupings.
One or more tablespaces for tables and indexes.
Every Oracle instance may have at least one and usually many tablespaces reserved for holding tables and indexes. These tablespaces can have any name but the default names are often like DATA01 and USERS.
It is advisable to spread I/O equally across multiple disks. And one way to accomplish this is to create separate tablespaces for tables and indexes and to store them on separate hard-drives if at all possible to improve system performance. In large systems it is usual to find each application stored in a separate tablespace and where tables of vastly different sizes are required to have tablespaces created to hold tables with small, medium, and large extents. By segregating tables by extent size into separate tablespaces it is possible to eliminate tablespace fragmentation which improves system performance and eliminates the wasting of disk space.
Definitions
The Number Of Extents - Dictionary vs. Locally Managed Tablespaces The number of extents has never been an issue for a segment. A table couldn't care less whether it is contained in 5 extents or 5000. But what does care is the data dictionary, since 5000 extents means 5000 rows in a data dictionary cluster that's been sized to expect no more than 5. That introduces cluster chaining in the data dictionary, and if the chaining is bad enough, then performance impacts involving dictionary access will be measurable.
Since LMTs don't touch the data dictionary for the purposes of recording extent acquisition, they do not suffer from cluster chaining, and do not experience a resulting performance degradation.
The performance issue also relates to the reading of the extent map in a single I/O instead of multiple I/Os ... since the extent map is stored inside an Oracle block, which is of finite size, too many extents mean that the map has to be stored in multiple blocks ... and any operation that needs to consult the extent map would therefore need multiple I/O operations to do so. You would really need thousands of extents, though, before that became a major issue).
The key advantage of LMTs is that they avoid a potential single point of contention on the data dictionary (but you'd have to have dozens of segments all simultaneously extending before that was an issue in the first place). And (better) they mean fragmentation is a thing of the past. And (the real killer) concern about a reasonable number of extents is wasted concern (reasonable being in the high hundreds to low thousands).
Logging Specify the default logging attributes of all tables, indexes, materialized views, materialized view logs, and partitions within the tablespace. LOGGING is the default. This clause is not valid for a temporary or undo tablespace.
The tablespace-level logging attribute can be overridden by logging specifications at the table, index, materialized view, materialized view log, and partition levels.
The force logging clause is used to force logging and to disregard no-logging instructions issued during object creation. You cannot specify FORCE LOGGING for an undo or temporary tablespace.
This clause only affects the logging of object creation and has no effect on logging of DML statements.
Segment Space Management When you create a locally managed tablespace using the CREATE TABLESPACE statement, the SEGMENT SPACE MANAGEMENT clause allows you to specify how free and used space within a segment is to be managed. Your choices are:
MANUAL
Specifying MANUAL tells Oracle that you want to use free lists for managing free space within segments. Free lists are lists of data blocks that have space available for inserting rows. This form of managing space within segments is called manual segment-space management because of the need to specify and tune the PCTUSED, FREELISTS, and FREELISTS GROUPS storage parameters for schema objects created in the tablespace.
MANUAL is the default.
AUTO
This keyword tells Oracle that you want to use bitmaps to manage the free space within segments. A bitmap, in this case, is a map that describes the status of each data block within a segment with respect to the amount of space in the block available for inserting rows. As more or less space becomes available in a data block, its new state is reflected in the bitmap. Bitmaps allow Oracle to manage free space more automatically, and thus, this form of space management is called automatic segment-space management.
Automatic segment-space management is a simpler and more efficient way of managing space within a segment. It completely eliminates any need to specify and tune the PCTUSED, FREELISTS, and FREELISTS GROUPS storage parameters for schema objects created in the tablespace. If such attributes should be specified, they are ignored.
Automatic segment-space management delivers better space utilization than manual segment-space management, and it is self tuning in that it scales with increasing the number of users, as well as instances. For a Real Application Clusters environment, automatic segment-space management allows for a dynamic affinity of space to instances, thus avoiding the hard partitioning of space inherent with using free list groups.
For many standard workloads, application performance when using automatic segment space management is better than the performance of a well tuned application using manual segment-space management.
Related Queries
List tablespaces, their files, allocated space, free space, and next free extent clear breaks
set linesize 132
set pagesize 60
break on tablespace_name skip 1
col tablespace_name format a15
col file_name format a50
col tablespace_kb heading 'TABLESPACE|TOTAL KB'
col kbytes_free heading 'TOTAL FREE|KBYTES'
SELECT dd.tablespace_name tablespace_name, dd.file_name file_name, dd.bytes/1024 TABLESPACE_KB, SUM(fs.bytes)/1024 KBYTES_FREE, MAX(fs.bytes)/1024 NEXT_FREE
FROM sys.dba_free_space fs, sys.dba_data_files dd
WHERE dd.tablespace_name = fs.tablespace_name
AND dd.file_id = fs.file_id
GROUP BY dd.tablespace_name, dd.file_name, dd.bytes/1024
ORDER BY dd.tablespace_name, dd.file_name;
List datafiles, tablespace names, and size in MB col file_name format a50
col tablespace_name format a10
SELECT file_name, tablespace_name, ROUND(bytes/1024000) MB
FROM dba_data_files
ORDER BY 1;
List tablespaces, size, free space, and percent free
Thanks to Michael Lehmann for this query SELECT df.tablespace_name TABLESPACE, df.total_space TOTAL_SPACE,
fs.free_space FREE_SPACE, df.total_space_mb TOTAL_SPACE_MB,
(df.total_space_mb - fs.free_space_mb) USED_SPACE_MB,
fs.free_space_mb FREE_SPACE_MB,
ROUND(100 * (fs.free_space / df.total_space),2) PCT_FREE
FROM (SELECT tablespace_name, SUM(bytes) TOTAL_SPACE,
ROUND(SUM(bytes) / 1048576) TOTAL_SPACE_MB
FROM dba_data_files
GROUP BY tablespace_name) df,
(SELECT tablespace_name, SUM(bytes) FREE_SPACE,
ROUND(SUM(bytes) / 1048576) FREE_SPACE_MB
FROM dba_free_space
GROUP BY tablespace_name) fs
WHERE df.tablespace_name = fs.tablespace_name(+)
ORDER BY fs.tablespace_name;
View For Schema Owner To Monitoring Free Space CREATE OR REPLACE VIEW freespace_view AS
SELECT tablespace_name, SUM(bytes/1024/1024) AVAILABLE
FROM user_free_space
GROUP BY tablespace_name;
Another Statement For
Tablespace Management set linesize 121
SELECT tablespace_name, ROUND(SUM(total_mb)-SUM(free_mb)) CUR_USE_MB, ROUND(SUM(total_mb)) CUR_SZ_MB,
ROUND((SUM(total_mb)-SUM(free_mb))/SUM(total_mb)*100) CUR_PCT_FULL, ROUND(SUM(max_mb) - (SUM(total_mb)-SUM(free_mb))) FREE_SPACE_MB,
ROUND(SUM(max_mb)) MAX_SZ_MB, ROUND((SUM(total_mb)-SUM(free_mb))/SUM(max_mb)*100) PCT_FULL
FROM (
SELECT tablespace_name, SUM(bytes)/1024/1024 FREE_MB,
0 TOTAL_MB, 0 MAX_MB
FROM dba_free_space
GROUP BY tablespace_name
UNION
SELECT tablespace_name, 0 CURRENT_MB,
SUM(bytes)/1024/1024 TOTAL_MB,
SUM(DECODE(maxbytes,0,bytes, maxbytes))/1024/1024 MAX_MB
FROM dba_data_files
GROUP BY tablespace_name)
GROUP BY tablespace_name;
Yet Another Statement For
Tablespace Management col tablespace_name format a15
col alloc_size format 999.999
col pct_used format 999.999
col free_space format 999.999
col maxnext format 999.999
col definitsz format 999.999
col defnextsz format 999.999
SELECT a.tablespace_name, a.datafile_sz, b.alloc_size,
(b.alloc_size)/a.datafile_sz*100 PCT_USED,
(a.datafile_sz-b.alloc_size) FREE_SPACE,
b.next_extent/1024/1024 MAXNEXT,
a.initial_extent/1024/1024 DEFINITSZ,
a.next_extent/1024/1024 DEFNEXTSZ
FROM (
SELECT a.tablespace_name, sum(b.bytes)/1024/1024
DATAFILE_SZ, a.initial_extent, a.next_extent
FROM dba_tablespaces a, dba_data_files b
WHERE a.tablespace_name = b.tablespace_name
GROUP BY a.tablespace_name, a.initial_extent, a.next_extent) A,
(
SELECT a.tablespace_name, sum(c.bytes)/1024/1024
ALLOC_SIZE, MAX(c.next_extent) NEXT_EXTENT
FROM dba_tablespaces a, dba_segments c
WHERE a.tablespace_name = c.tablespace_name
GROUP BY a.tablespace_name) B
WHERE a.tablespace_name = b.tablespace_name (+)
ORDER BY 1;
And Yet Another Statement For Tablespace Management SELECT dfs.tablespace_name, ddf.total_size,
ddf.total_size - dfs.total_free TOTAL_USED,
dfs.total_free,
(ddf.total_size-dfs.total_free)/ddf.total_size * 100 CAP,
dfs.total_chunks, dfs.largest_chunk
FROM (
SELECT a.tablespace_name,
SUM(a.bytes)/1024/1024 TOTAL_FREE,
COUNT(a.bytes) TOTAL_CHUNKS,
MAX(a.bytes)/1024/1024 LARGEST_CHUNK
FROM dba_free_space a
GROUP BY a.tablespace_name) dfs,
(
SELECT b.tablespace_name,
SUM(b.bytes)/1024/1024 TOTAL_SIZE
FROM dba_data_files b
GROUP BY b.tablespace_name) ddf
WHERE dfs.tablespace_name = ddf.tablespace_name
ORDER BY dfs.tablespace_name;
Calculation Of Minimum Tablespace Size (this takes a long time to run) SELECT SUBSTR(f.file_name,1,70) FILENAME,
MAX(e.block_id*(e.bytes/e.blocks)+e.bytes)/1024 MIN_SIZE
FROM dba_extents e, dba_data_files f
WHERE e.file_id = f.file_id
GROUP BY f.file_name;
Schemas In The SYSAUX Tablespace col occupant_name format a25
col schema_name format a20
col move_procedure format a30
col move_procedure_desc format a40
set linesize 131
SELECT occupant_name, schema_name, move_procedure, move_procedure_desc
FROM v_$sysaux_occupants;
Contiguous Space create table t_contig_space (
tablespace_name VARCHAR2(30),
file_id NUMBER,
block_id NUMBER,
starting_file_id NUMBER,
starting_block_id NUMBER,
blocks NUMBER,
bytes NUMBER)
tablespace uwdata;
CREATE OR REPLACE VIEW v_contig_space AS
SELECT SUBSTR(tablespace_name,1,20) TABLESPACE_NAME,
starting_file_id, starting_block_id, SUM(blocks) sum_blocks,
COUNT(blocks) count_blocks, MAX(blocks) max_blocks,
SUM(bytes)/1024/1024 SUM_MB
FROM tl_contig_space
GROUP BY tablespace_name, starting_file_id, starting_block_id;
DECLARE
CURSOR query IS
SELECT *
FROM dba_free_space
ORDER BY tablespace_name, file_id, block_id;
this_row query%ROWTYPE;
previous_row query%ROWTYPE;
old_file_id PLS_INTEGER;
old_block_id PLS_INTEGER;
BEGIN
OPEN query;
FETCH query INTO this_row;
previous_row := this_row;
old_file_id := previous_row.file_id;
old_block_id := previous_row.block_id;
WHILE query%FOUND loop
IF this_row.file_id = previous_row.file_id AND
this_row.block_id = previous_row.block_id+previous_row.blocks
THEN
INSERT INTO tl_contig_space
(tablespace_name, file_id, block_id, starting_file_id,
starting_block_id, blocks, bytes)
VALUES
(previous_row.tablespace_name, previous_row.file_id,
this_row.block_id, old_file_id, old_block_id, this_row.blocks,
this_row.bytes);
ELSE
INSERT INTO tl_contig_space
(tablespace_name, file_id, block_id, starting_file_id,
starting_block_id, blocks, bytes)
VALUES
(this_row.tablespace_name, this_row.file_id,
this_row.block_id, this_row.file_id, this_row.block_id,
this_row.blocks, this_row.bytes);
old_file_id := this_row.file_id;
old_block_id := this_row.block_id;
END IF;
previous_row := this_row;
FETCH query INTO this_row;
END LOOP;
COMMIT;
END;
/
col tablespace_name format a20
col sum_mb format 999.999
SELECT * FROM v_contig_space;
http://www.psoug.org/reference/tablespaces.html
2 Ağustos 2009 Pazar
Tablespace Monitoring
AUTOEXTEND ON Next Size
In a CREATE TABLESPACE command, the DATAFILE clause is the Physical Specification component.
In pre- 9i/10g OMF manner, the DATAFILE must be specified. A (initial) filesize must also be specified. However, Autoextend's NEXT size is not mandatory and Oracle can "default". Very unfortunately, the default AUTOEXTEND ON NEXT size is 1 Database block (based on the blocksize of the tablespace).
But if you create your Tablespace using OMF (i.e. where "db_create_file_dest" is configured), then Oracle defaults the initial size to 100MB and *also* defaults the AUTOEXTEND to ON with a of 100MB ! That is much neater.
Why is the default 1 Database block bad ? Because when the datafile is full, Oracle will extend it 1 block at-a-time, making a call to the OS to add 1 block on each occassion. Obviously, even if you are extending a table or index with an Extent of 64KB, Oracle has to make 8 calls to the OS (with a datafile block size of 8KB). That is where you will see "data file init write" waits.
In the example below, the (automatic) datafile for Tablespace AN_OMF_TBS get's created with both the initial and increment at 100MB and AutoExtend ON. However, for Tablespace REGULAR_TBS, I have to specify the initial size for the datafile. If I do not specify AutoExtend, the file is created with AutoExtend OFF. For the third tablespace, called ANOTHER_TBS, when I designate AutoExtend ON but do not specify the incremental size, Oracle defaults it to 1 Oracle Block.
SQL> select tablespace_name, file_name, bytes/1048576 File_Size_MB, autoextensible, increment_by from dba_data_files order by file_id;
SQL> alter system set db_create_file_dest='/var/tmp';
SQL> create tablespace an_omf_tbs;
SQL> select tablespace_name, file_name, bytes/1048576 File_Size_MB, autoextensible, increment_by from dba_data_files order by file_id;
SQL> create tablespace REGULAR_TBS datafile '/oracle_fs/Databases/ORT24FS/regular_tbs.dbf';
create tablespace REGULAR_TBS datafile '/oracle_fs/Databases/ORT24FS/regular_tbs.dbf'
SQL> create tablespace REGULAR_TBS datafile '/oracle_fs/Databases/ORT24FS/regular_tbs.dbf' size 100M;
SQL> select tablespace_name, file_name, bytes/1048576 File_Size_MB, autoextensible, increment_by from dba_data_files order by file_id;
SQL> create tablespace ANOTHER_TBS datafile '/oracle_fs/Databases/ORT24FS/another_tbs.dbf' size 100M autoextend on ;
SQL> select tablespace_name, file_name, bytes/1048576 File_Size_MB, autoextensible, increment_by from dba_data_files order by file_id;
You would be well-adviced to remember that when you create or add a non-OMF datafile, you should specify the Increment size with the AutoExtend ON. Else, you might suffer the overheads of Oracle having to make multiple calls to the OS whenever extending the datafile (imagine extending a datafile 1 block at each call for an extent of 64MB !)
SQL> create tablespace LAST_TBS datafile '/oracle_fs/Databases/ORT24FS/last_tbs.dbf' size 100M autoextend on next 100M;
Tablespace created.
SQL> select tablespace_name, file_name, bytes/1048576 File_Size_MB, autoextensible, increment_by from dba_data_files order by file_id;
Tablespace LAST_TBS's datafile is created with a meaningful Increment for AutoExtend.
otes on Specifying Datafiles and Tempfiles
*
For operating systems that support raw devices, the REUSE keyword of datafile_tempfile_spec has no meaning when specifying a raw device as a datafile. Such a CREATE TABLESPACE statement will succeed whether or not you specify REUSE.
*
You can create a tablespace within an Automatic Storage Management disk group by providing only the disk group name in the datafile_tempfile_spec. In this case, Automatic Storage Management creates a datafile in the specified disk group with a system-generated filename. The datafile is auto-extensible with an unlimited maximum size and a default size of 100 MB. You can use the autoextend_clause to override the default size.
*
If you use one of the reference forms of the ASM_filename, which refers to an existing file, then you must also specify REUSE.
http://www.sc.ehu.es/siwebso/KZCC/Oracle_10g_Documentacion/server.101/b10759/statements_7003.htm
---------------------------------------------------------------------------
CREATE TABLESPACE
DATAFILE SIZE sayı K|M [AUTOEXTEND OFF | {AUTIOEXTEND
ON | NEXT sayı K|M | MAXSIZE UNLIMITED | sayi K|M |}
[ONLINE | OFFLINE ]
[PERMANENT | TEMPORARY ]
Kullanılan ifadelerin açıklamaları :
Tablespace ismi : Oluşturulacak tablespace verilecek addır.
DATAFILE : Tablespace’i oluşturan veri dosyasının tanımlandığı bölümdür.
Dosya ismi : DATAFILE’ın path’ini belirtildiği bölümdür.
AUTOEXTEND OFF : Datafile otomatik artışını iptal etmekte kullanılan komuttur.
AUTOEXTEND ON : Datafile otomatik artışını sağlayan komuttur.
NEXT : Datafile’ ın genişleme miktarı.
MAXSIZE : Datafile’ın genişleyebileceği en son miktardır.
UNLIMITED : Datafile’ın sınırsız büyüyebileceğini göstermektedir. Belirtilmese dahi standart olarak seçilidir.
ONLINE : Tablespace’i oluşturduktan hemen sonra aktif olacağını belirten bölümdür. Standart olarak seçilidir.
OFFLINE : Tablespace’i oluşturduktan hemen sonra pasif olacağını belirten bölümdür.
PERMANENT : Tablespace içinde tutulacak olan nesnelerin kalıcı olarak saklanacağını belirten bölümdür. Standart olarak seçilidir.
TEMPORARY : Tablespace’te geçici nesnelerin tutulacağının belirtildiği bölümdür.
NOT : Tablespace oluşturmak ve yönetmek için STORAGE MANAGER kullanılmasını tavsiye ederiz.
In a CREATE TABLESPACE command, the DATAFILE clause is the Physical Specification component.
In pre- 9i/10g OMF manner, the DATAFILE must be specified. A (initial) filesize must also be specified. However, Autoextend's NEXT size is not mandatory and Oracle can "default". Very unfortunately, the default AUTOEXTEND ON NEXT size is 1 Database block (based on the blocksize of the tablespace).
But if you create your Tablespace using OMF (i.e. where "db_create_file_dest" is configured), then Oracle defaults the initial size to 100MB and *also* defaults the AUTOEXTEND to ON with a of 100MB ! That is much neater.
Why is the default 1 Database block bad ? Because when the datafile is full, Oracle will extend it 1 block at-a-time, making a call to the OS to add 1 block on each occassion. Obviously, even if you are extending a table or index with an Extent of 64KB, Oracle has to make 8 calls to the OS (with a datafile block size of 8KB). That is where you will see "data file init write" waits.
In the example below, the (automatic) datafile for Tablespace AN_OMF_TBS get's created with both the initial and increment at 100MB and AutoExtend ON. However, for Tablespace REGULAR_TBS, I have to specify the initial size for the datafile. If I do not specify AutoExtend, the file is created with AutoExtend OFF. For the third tablespace, called ANOTHER_TBS, when I designate AutoExtend ON but do not specify the incremental size, Oracle defaults it to 1 Oracle Block.
SQL> select tablespace_name, file_name, bytes/1048576 File_Size_MB, autoextensible, increment_by from dba_data_files order by file_id;
SQL> alter system set db_create_file_dest='/var/tmp';
SQL> create tablespace an_omf_tbs;
SQL> select tablespace_name, file_name, bytes/1048576 File_Size_MB, autoextensible, increment_by from dba_data_files order by file_id;
SQL> create tablespace REGULAR_TBS datafile '/oracle_fs/Databases/ORT24FS/regular_tbs.dbf';
create tablespace REGULAR_TBS datafile '/oracle_fs/Databases/ORT24FS/regular_tbs.dbf'
SQL> create tablespace REGULAR_TBS datafile '/oracle_fs/Databases/ORT24FS/regular_tbs.dbf' size 100M;
SQL> select tablespace_name, file_name, bytes/1048576 File_Size_MB, autoextensible, increment_by from dba_data_files order by file_id;
SQL> create tablespace ANOTHER_TBS datafile '/oracle_fs/Databases/ORT24FS/another_tbs.dbf' size 100M autoextend on ;
SQL> select tablespace_name, file_name, bytes/1048576 File_Size_MB, autoextensible, increment_by from dba_data_files order by file_id;
You would be well-adviced to remember that when you create or add a non-OMF datafile, you should specify the Increment size with the AutoExtend ON. Else, you might suffer the overheads of Oracle having to make multiple calls to the OS whenever extending the datafile (imagine extending a datafile 1 block at each call for an extent of 64MB !)
SQL> create tablespace LAST_TBS datafile '/oracle_fs/Databases/ORT24FS/last_tbs.dbf' size 100M autoextend on next 100M;
Tablespace created.
SQL> select tablespace_name, file_name, bytes/1048576 File_Size_MB, autoextensible, increment_by from dba_data_files order by file_id;
Tablespace LAST_TBS's datafile is created with a meaningful Increment for AutoExtend.
otes on Specifying Datafiles and Tempfiles
*
For operating systems that support raw devices, the REUSE keyword of datafile_tempfile_spec has no meaning when specifying a raw device as a datafile. Such a CREATE TABLESPACE statement will succeed whether or not you specify REUSE.
*
You can create a tablespace within an Automatic Storage Management disk group by providing only the disk group name in the datafile_tempfile_spec. In this case, Automatic Storage Management creates a datafile in the specified disk group with a system-generated filename. The datafile is auto-extensible with an unlimited maximum size and a default size of 100 MB. You can use the autoextend_clause to override the default size.
*
If you use one of the reference forms of the ASM_filename, which refers to an existing file, then you must also specify REUSE.
http://www.sc.ehu.es/siwebso/KZCC/Oracle_10g_Documentacion/server.101/b10759/statements_7003.htm
---------------------------------------------------------------------------
CREATE TABLESPACE
DATAFILE
ON | NEXT sayı K|M | MAXSIZE UNLIMITED | sayi K|M |}
[ONLINE | OFFLINE ]
[PERMANENT | TEMPORARY ]
Kullanılan ifadelerin açıklamaları :
Tablespace ismi : Oluşturulacak tablespace verilecek addır.
DATAFILE : Tablespace’i oluşturan veri dosyasının tanımlandığı bölümdür.
Dosya ismi : DATAFILE’ın path’ini belirtildiği bölümdür.
AUTOEXTEND OFF : Datafile otomatik artışını iptal etmekte kullanılan komuttur.
AUTOEXTEND ON : Datafile otomatik artışını sağlayan komuttur.
NEXT : Datafile’ ın genişleme miktarı.
MAXSIZE : Datafile’ın genişleyebileceği en son miktardır.
UNLIMITED : Datafile’ın sınırsız büyüyebileceğini göstermektedir. Belirtilmese dahi standart olarak seçilidir.
ONLINE : Tablespace’i oluşturduktan hemen sonra aktif olacağını belirten bölümdür. Standart olarak seçilidir.
OFFLINE : Tablespace’i oluşturduktan hemen sonra pasif olacağını belirten bölümdür.
PERMANENT : Tablespace içinde tutulacak olan nesnelerin kalıcı olarak saklanacağını belirten bölümdür. Standart olarak seçilidir.
TEMPORARY : Tablespace’te geçici nesnelerin tutulacağının belirtildiği bölümdür.
NOT : Tablespace oluşturmak ve yönetmek için STORAGE MANAGER kullanılmasını tavsiye ederiz.
Kaydol:
Kayıtlar (Atom)