티스토리 뷰
출처 : http://www.psoug.org/reference/tablespaces.html
다음 내용은 최신 버전이 아닐 수 있습니다. 최신 버전은 위 사이트를 참조하세요.
다음 내용은 최신 버전이 아닐 수 있습니다. 최신 버전은 위 사이트를 참조하세요.
General | |||||||||||||||||||||||||||
Data Dictionary Objects |
|
||||||||||||||||||||||||||
System Privileges |
create tablespace alter tablespace drop tablespace manage tablespace unlimited 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 [BIGFILE | SMALLFILE] TABLESPACE <tablespace_name> DATAFILE '<path_and_file_name>' SIZE <integer><K | M | G | T | P | E> [REUSE] AUTOEXTEND <OFF | ON> BLOCKSIZE <bytes> [<LOGGING | NOLOGGING>] [FORCE LOGGING] [DEFAULT <COMPRESS | NOCOMPRESS>] EXTENT MANAGEMENT LOCAL UNIFORM SIZE <extent_size> [SEGMENT SPACE MANAGEMENT AUTO] <ONLINE | OFFLINE>; |
||||||||||||||||||||||||||
CREATE TABLESPACE
uwdata DATAFILE 'c:\oracle\product\oradata\orabase\uwdata01.dbf' SIZE 250M, 'c:\oracle\product\oradata\orabase\uwdata02.dbf' SIZE 100M AUTOEXTEND OFF BLOCKSIZE 8192 FORCE LOGGING DEFAULT NOCOMPRESS EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K FLASHBACK ON ONLINE; SELECT dbms_metadata.get_ddl('TABLESPACE', 'UWDATA') FROM dual; |
|||||||||||||||||||||||||||
Permanent Tablespace On A Raw Device |
CREATE TABLESPACE <tablespace_name> DATAFILE '<path_and_file_name>' SIZE <integer><K | M | G | T> BLOCKSIZE <bytes> AUTOEXTEND OFF EXTENT MANAGEMENT UNIFORM LOCAL SIZE <extent_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 <tablespace_name>; | ||||||||||||||||||||||||||
ALTER SYSTEM SET DB_CREATE_FILE_DEST = $ORACLE_BASE/oradata'; CREATE TABLESPACE user_data; |
|||||||||||||||||||||||||||
Oracle Managed Permanent Fixed Size Tablespace | CREATE TABLESPACE <tablespace_name> 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_type> TABLESPACE; | ||||||||||||||||||||||||||
ALTER DATABASE DEFAULT BIGFILE TABLESPACE; | |||||||||||||||||||||||||||
Set tablespace as the default | ALTER DATABASE DEFAULT TABLESPACE <tablespace_name>; | ||||||||||||||||||||||||||
ALTER DATABASE DEFAULT TABLESPACE uwdata; | |||||||||||||||||||||||||||
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 <tablespace_name> DATAFILE '<path_and_file_name>' SIZE <integer><K | M | G | T> AUTOEXTEND <ON | OFF> RETENTION <GUARANTEE | NOGUARANTEE>; |
||||||||||||||||||||||||||
CREATE UNDO TABLESPACE tspundo DATAFILE '/u01/oradata/undotbs01.dbf SIZE 50000M REUSE AUTOEXTEND ON RETENTION NOGUARANTEE; |
|||||||||||||||||||||||||||
Change The Current UNDO Tablespace | Create a second undo tablespace then alter the SPFILE | ||||||||||||||||||||||||||
Temporary Tablespaces | |||||||||||||||||||||||||||
Create Temporary Tablespace |
CREATE TEMPORARY TABLESPACE <tablespace_name> TEMPFILE '<path_and_file_name>' SIZE <integer><K | M | G | T> AUTOEXTEND <ON | OFF> TABLESPACE GROUP <group_name> EXTENT MANAGEMENT LOCAL UNIFORM SIZE <extent_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 TEMPFILE '/u01/oradata/temp01.dbf' SIZE 10000M AUTOEXTEND OFF EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K; |
|||||||||||||||||||||||||||
Add Tempfile | ALTER TABLESPACE <tablespace_name> ADD TEMPFILE '<path_and_file_name>' SIZE <n>M; |
||||||||||||||||||||||||||
ALTER TABLESPACE lmtemp ADD TEMPFILE '/u02/oradata/temp02.dbf' SIZE 200M; |
|||||||||||||||||||||||||||
Resize Tempfile | ALTER DATABASE TEMPFILE '<file_name>' RESIZE <mega_bytes_integer>M; |
||||||||||||||||||||||||||
ALTER DATABASE TEMPFILE '/u02/oradata/temp02.dbf' RESIZE 200M; | |||||||||||||||||||||||||||
Drop Tempfile | ALTER DATABASE TEMPFILE '<file_name>' DROP; | ||||||||||||||||||||||||||
ALTER DATABASE TEMPFILE '/u02/oradata/temp02.dbf' DROP; | |||||||||||||||||||||||||||
Take Temporary Tablespace Off-line | ALTER DATABASE TEMPFILE '<path_and_file_name>' OFFLINE; | ||||||||||||||||||||||||||
ALTER DATABASE TEMPFILE '/u02/oradata/temp02.dbf' OFFLINE; | |||||||||||||||||||||||||||
Place Temporary Tablespace On-line | ALTER DATABASE TEMPFILE '<path_and_file_name>' ONLINE; | ||||||||||||||||||||||||||
ALTER DATABASE TEMPFILE '/u02/oradata/temp02.dbf' ONLINE; | |||||||||||||||||||||||||||
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 <tablespace_name> 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=(<comma_delimited_list_of_tablespaces>) TRIGGERS=Y CONSTRAINTS=N GRANTS=N FILE=<file_name> |
||||||||||||||||||||||||||
EXP TRANSPORT_TABLESPACE=y TABLESPACES=(sales_1,sales_2) TRIGGERS=y CONSTRAINTS=n GRANTS=n FILE=expdat.dmp |
|||||||||||||||||||||||||||
Import Tablespace |
IMP TRANSPORT_TABLESPACE=<Y | N> FILE=<file_name> DATAFILES=('<comma_delimited_list_of_data_files>) TABLESPACES=(<comma_delimited_list_of_tablespaces>) TTS_OWNERS=(<comma_delimited_list_of_schema_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 <file_name>, <integer><K | M | G | T> [REUSE] <autoextend> <ON | OFF> NEXT <integer><K | M | G | T> MAXSIZE <integer><K | M | G | T | UNLIMITED> |
||||||||||||||||||||||||||
ALTER TABLESPACE tools ADD
DATAFILE '/u02/oracle/oradata/uwdata02.dbf' SIZE 250M AUTOEXTEND OFF; ALTER TABLESPACE tools ADD DATAFILE 'c:\oracle\product\oradata\tools99.xxx' SIZE 10M AUTOEXTEND ON; |
|||||||||||||||||||||||||||
Take Off-line | ALTER TABLESPACE <tablespace_name> OFFLINE; | ||||||||||||||||||||||||||
ALTER TABLESPACE tools OFFLINE; | |||||||||||||||||||||||||||
Place On-line | ALTER TABLESPACE <tablespace_name> ONLINE; | ||||||||||||||||||||||||||
ALTER TABLESPACE tools ONLINE; | |||||||||||||||||||||||||||
Make Read Only | ALTER TABLESPACE <tablespace_name> READ ONLY; | ||||||||||||||||||||||||||
ALTER TABLESPACE tools READ ONLY; -- READ ONLY prevents DML ... not DDL including DROP and TRUNCATE. |
|||||||||||||||||||||||||||
Make A Tablespace Read Write | ALTER TABLESPACE <tablespace_name> READ WRITE; | ||||||||||||||||||||||||||
ALTER TABLESPACE tools READ WRITE; | |||||||||||||||||||||||||||
Prepare Tablespace For Backup (archive logging must be active) | ALTER TABLESPACE <tablespace_name> BEGIN BACKUP; | ||||||||||||||||||||||||||
ALTER TABLESPACE tools BEGIN BACKUP; | |||||||||||||||||||||||||||
End Tablespace Backup | ALTER TABLESPACE <tablespace_name> END BACKUP; | ||||||||||||||||||||||||||
ALTER TABLESPACE tools END BACKUP; | |||||||||||||||||||||||||||
Rename |
ALTER TABLESPACE <tablespace_name> RENAME TO <new_tablespace_name>; | ||||||||||||||||||||||||||
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 <tablespace_name>; | ||||||||||||||||||||||||||
DROP TABLESPACE tools; | |||||||||||||||||||||||||||
Drop Tablespace Including Contents | DROP TABLESPACE <tablespace_name> INCLUDING CONTENTS; |
||||||||||||||||||||||||||
DROP TABLESPACE tools INCLUDING CONTENTS; |
|||||||||||||||||||||||||||
Drop Tablespace Including Contents & Datafiles | DROP TABLESPACE <tablespace_name> INCLUDING CONTENTS AND DATAFILES; |
||||||||||||||||||||||||||
DROP TABLESPACE tools INCLUDING CONTENTS AND DATAFILES; |
|||||||||||||||||||||||||||
Drop Tablespace Including Contents & Datafiles When There Are Referential Constraints | DROP TABLESPACE <tablespace_name> INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS; |
||||||||||||||||||||||||||
DROP TABLESPACE tools INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS; |
|||||||||||||||||||||||||||
Drop Tablespace When Datafile Has been 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; |
'Dev > Oracle' 카테고리의 다른 글
[펌] oracle import 관련 구문 (0) | 2007.11.22 |
---|---|
[펌] oracle table 관련 구문 (0) | 2007.10.26 |
[펌] oracle update 구문 (1) | 2007.10.01 |
[펌] oracle export 관련 구문 (0) | 2007.09.02 |
[펌] Logging Localized Message [메시지 로깅] (0) | 2007.08.22 |
댓글
최근에 올라온 글
최근에 달린 댓글
- Total
- Today
- Yesterday
TAG
- 짜증
- 최대 메모리
- 윈도우
- 바이크
- 출근
- Git
- connect by
- ubuntu
- 액땜
- GB-P100
- Eclipse
- 무료 프로그램
- 법무부
- 헌혈
- 파워콤해지
- 프로젝트
- 오라클
- 김용
- 정부과천청사
- 충동구매
- 금연
- VMware
- Java
- 안드로이드 앱
- oracle
- 일하는 곳
- iBATIS
- 출근길
- EditPlus
- 낭패
일 | 월 | 화 | 수 | 목 | 금 | 토 |
---|---|---|---|---|---|---|
1 | 2 | 3 | 4 | |||
5 | 6 | 7 | 8 | 9 | 10 | 11 |
12 | 13 | 14 | 15 | 16 | 17 | 18 |
19 | 20 | 21 | 22 | 23 | 24 | 25 |
26 | 27 | 28 | 29 | 30 | 31 |
글 보관함