oracle

Oracle TablespacesVersion 11.1 GeneralData Dictionary ObjectsTablespacests$dba_tablespacesuser_tablespacesTablespace Quotastsq$dba_ts_quotasuser_ts_quotasData Filesdba_data_filesv_$backup_datafilev_$datafilev_$datafile_copyv_$datafile_headerFree Spacedba_free_spaceSegmentsdba_segmentsv_$segment_statisticsExtentsdba_extentsBlocksv_$database_block_corruptionGroupsdba_tablespace_groupsSYSAUX Tablespacev_$sysaux_occupantsTemp Tablespacedba_temp_filesUndo Tablespacedba_rollback_segsdba_undo_extentsv_$rollstat v_$undostatTransportable Tablespacestransport_set_violationsDictionary Managementfet$uet$System Privilegesalter tablespacedrop tablespaceunlimited tablespacecreate tablespacemanage 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 TablespacePermanent Tablespace On A File System Without Auto-extendCREATE [<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 | FILESYSTEM_LIKE_LOGGING>][FORCE LOGGING][ENCRYPTION USING '<encryption_algorithm>' IDENTIFIED BY <password> [NO] SALT][DEFAULT <COMPRESS [FOR <ALL | DIRECT_LOAD> OPERATIONS] | NOCOMPRESS>][<ONLINE | OFFLINE>]EXTENT MANAGEMENT LOCAL <AUTOALLOCATE | UNIFORM SIZE <extent_size>>[SEGMENT SPACE MANAGEMENT <AUTO | MANUAL>][FLASHBACK <ON | OFF>];CREATE TABLESPACE uwdata DATAFILE'c:/oracle/oradata/orabase/uwdata01.dbf' SIZE 150M,'c:/oracle/oradata/orabase/uwdata02.dbf' SIZE 100MAUTOEXTEND OFFBLOCKSIZE 8192FORCE LOGGINGEXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K FLASHBACK ON;desc dba_tablespacesset linesize 121col tablespace_name format a20SELECT tablespace_name, block_size, status, contents, retention, extent_management, allocation_type, plugged_in, segment_space_managementFROM dba_tablespaces;desc dba_data_filescol file_name format a45SELECT file_name, tablespace_name, bytes, blocks, autoextensible, increment_byFROM dba_data_files;SELECT dbms_metadata.get_ddl('TABLESPACE', 'UWDATA')FROM dual;Permanent Tablespace On A Raw DeviceCREATE TABLESPACE <tablespace_name>DATAFILE '<path_and_file_name>'SIZE <integer><K | M | G | T>BLOCKSIZE <bytes>AUTOEXTEND OFFEXTENT MANAGEMENT UNIFORM LOCAL SIZE <extent_size>SEGMENT SPACE MANAGEMENT AUTO;CREATE TABLESPACE tools LOGGINGDATAFILE '/u01/oradata/' SIZE 1024MBLOCKSIZE 4096EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1MSEGMENT SPACE MANAGEMENT AUTO;Oracle Managed Permanent Auto-extendable TablespaceCREATE TABLESPACE <tablespace_name>;ALTER SYSTEM SET DB_CREATE_FILE_DEST = $ORACLE_BASE/oradata';CREATE TABLESPACE user_data;Oracle Managed Permanent Fixed Size TablespaceCREATE TABLESPACE <tablespace_name>DATAFILE AUTOEXTEND OFF;ALTER SYSTEMSET DB_CREATE_FILE_DEST = $ORACLE_BASE/oradata';CREATE TABLESPACE user_data DATAFILE AUTOEXTEND OFF;Set default tablespace typeALTER DATABASE SET DEFAULT <tablespace_type> TABLESPACE;ALTER DATABASE DEFAULT BIGFILE TABLESPACE;set linesize 121col value$ format a20col comment$ format a60SELECT *FROM props$WHERE name LIKE '%DEF%'ORDER BY by name;Set tablespace as the defaultALTER DATABASE DEFAULT TABLESPACE <tablespace_name>;ALTER DATABASE DEFAULT TABLESPACE uwdata;set linesize 121col value$ format a20col comment$ format a60SELECT *FROM props$WHERE name LIKE '%DEF%'ORDER BY by name; SYSAUX TablespaceCreate SYSAUX TablespaceSELECT tablespace_nameFROM dba_tablespaces;CREATE TABLESPACE sysauxDATAFILE '/u01/oradata/sysaux01.dbf' SIZE 700MEXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;Move SYSAUX Contents-- Move must be done using the indicated procedurecol occupant_name format a30col schema_name format a30col move_procedure format a50SELECT occupant_name, schema_name, move_procedureFROM v_$sysaux_occupantsORDER BY 1; Undo TablespaceCreate An UNDO TablespaceCREATE UNDO TABLESPACE <tablespace_name>DATAFILE '<path_and_file_name>'SIZE <integer><K | M | G | T>AUTOEXTEND <ON | OFF>RETENTION <GUARANTEE | NOGUARANTEE>;CREATE UNDO TABLESPACE tspundoDATAFILE '/u01/oradata/undotbs01.dbfSIZE 50000M REUSE AUTOEXTEND ONRETENTION NOGUARANTEE;desc dba_undo_extentsSELECT segment_name, tablespace_name, status, SUM(bytes)FROM dba_undo_extentsGROUP BY segment_name, tablespace_name, status;Change The Current UNDO TablespaceCreate a second undo tablespace then alter the SPFILE Temporary TablespacesCreate Temporary TablespaceCREATE 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 tempTEMPFILE '/u01/oradata/temp01.dbf'SIZE 10000M AUTOEXTEND OFFEXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K;SELECT tablespace_name, block_size, status, contents, retention, extent_management, allocation_type, plugged_inFROM dba_tablespaces;desc dba_temp_filesSELECT file_name, tablespace_name, bytes, blocks, autoextensible, increment_byFROM dba_temp_files;Add TempfileALTER TABLESPACE <tablespace_name>ADD TEMPFILE '<path_and_file_name>' SIZE <n>M;ALTER TABLESPACE lmtempADD TEMPFILE '/u02/oradata/temp02.dbf' SIZE 200M;Resize TempfileALTER DATABASE TEMPFILE '<file_name>'RESIZE <mega_bytes_integer>M;ALTER DATABASE TEMPFILE '/u02/oradata/temp02.dbf' RESIZE 200M;Drop TempfileALTER DATABASE TEMPFILE '<file_name>' DROP;ALTER DATABASE TEMPFILE '/u02/oradata/temp02.dbf' DROP;Take Temporary Tablespace Off-lineALTER DATABASE TEMPFILE '<path_and_file_name>' OFFLINE;ALTER DATABASE TEMPFILE '/u02/oradata/temp02.dbf' OFFLINE;Place Temporary Tablespace On-lineALTER DATABASE TEMPFILE '<path_and_file_name>' ONLINE;ALTER DATABASE TEMPFILE '/u02/oradata/temp02.dbf' ONLINE; Transportable TablespacesDetermine Transportabilitydbms_tts.transport_set_check(tablespace_name IN VARCHAR2, TRUE);dbms_tts.transport_set_check('uwdata', TRUE);View Violations; If AnySELECT * FROM TRANSPORT_SET_VIOLATIONS;Generate A Transportable SetALTER TABLESPACE <tablespace_name> READ ONLY;ALTER TABLESPACE tools READ ONLY;Export TablespaceAlthough the Export utility is used, only data dictionary structural information (metadata) for the tablespaces is exported.EXP TRANSPORT_TABLESPACE=YTABLESPACES=(<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.dmpImport TablespaceIMP 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.dmpDATAFILES=('/db/sales_jan','/db/sales_feb')TABLESPACES=(sales_1,sales_2) TTS_OWNERS=(dcranney,jfee) FROMUSER=(dcranney,jfee) TOUSER=(smith,williams)Import Parameter FileTRANSPORT_TABLESPACE=y FILE=expdat.dmpDATAFILES=('/db/sales_jan','/db/sales_feb')TABLESPACES=(sales_1, sales_2) TTS_OWNERS=(dcranney, jfee) FROMUSER=(dcranney, jfee) TOUSER=(smith, williams) Alter Permanent TablespaceAdd DatafileALTER 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 25MAUTOEXTEND OFF;ALTER TABLESPACE tools ADD ADD DATAFILE'c:/oracle/product/oradata/tools99.xxx' SIZE 10MAUTOEXTEND ON;Take Off-lineALTER TABLESPACE <tablespace_name> OFFLINE;ALTER TABLESPACE tools OFFLINE;Place On-lineALTER TABLESPACE <tablespace_name> ONLINE;ALTER TABLESPACE tools ONLINE;Make Read OnlyALTER TABLESPACE <tablespace_name> READ ONLY;ALTER TABLESPACE tools READ ONLY;SELECT tablespace_name, statusFROM dba_tablespaces;-- READ ONLY prevents DML ... not DDL including DROP and TRUNCATE.Make A Tablespace Read WriteALTER TABLESPACE <tablespace_name> READ WRITE;ALTER TABLESPACE tools READ WRITE;SELECT tablespace_name, statusFROM dba_tablespaces;Prepare Tablespace For Backup (archive logging must be active)ALTER TABLESPACE <tablespace_name> BEGIN BACKUP;ALTER TABLESPACE tools BEGIN BACKUP;End Tablespace BackupALTER TABLESPACE <tablespace_name> END BACKUP;ALTER TABLESPACE tools END BACKUP;RenameALTER TABLESPACE <tablespace_name> RENAME TO <new_tablespace_name>;SELECT tablespace_nameFROM dba_tablespaces;SELECT table_nameFROM dba_tablesWHERE tablespace_name = 'USERS';ALTER TABLESPACE users RENAME TO user_data;SELECT tablespace_nameFROM dba_tablespaces;SELECT table_nameFROM dba_tablesWHERE tablespace_name = 'USER_DATA'; Alter Undo TablespaceRetention GuaranteeALTER TABLESPACE <tablespace_name> RETENTION <GUARANTEE | NOGUARANTEE>;SELECT tablespace_name, retentionFROM dba_tablespaces;ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;select tablespace_name, retentionfrom dba_tablespaces;ALTER TABLESPACE undotbs1 RETENTION NOGUARANTEE;select tablespace_name, retentionfrom dba_tablespaces; Drop TablespaceDrop TablespaceDROP TABLESPACE <tablespace_name>;DROP TABLESPACE tools;Drop Tablespace Including ContentsDROP TABLESPACE <tablespace_name>INCLUDING CONTENTS;DROP TABLESPACE tools INCLUDING CONTENTS;Drop Tablespace Including Contents & DatafilesDROP TABLESPACE <tablespace_name>INCLUDING CONTENTS AND DATAFILES;DROP TABLESPACE tools INCLUDING CONTENTS AND DATAFILES;Drop Tablespace Including Contents & Datafiles When There Are Referential ConstraintsDROP TABLESPACE <tablespace_name>INCLUDING CONTENTS AND DATAFILESCASCADE CONSTRAINTS;DROP TABLESPACE tools INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;Drop tablespace afgter datafile was accidentally droppedconn / as sysdbaCREATE TABLESPACE badideaDATAFILE 'c:/temp/badidea.dbf' SIZE 10MBLOCKSIZE 8192EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256KSEGMENT SPACE MANAGEMENT AUTOONLINE;SELECT tablespace_nameFROM dba_tablespaces;SELECT file_nameFROM dba_data_files;shutdown immediate;-- in an operating system window drop the file c:/temp/badidea.dbfstartup-- record the error messageshutdown immediate;startup mount;alter database datafile 'c:/temp/badidea.dbf' offline drop;alter database open;SELECT tablespace_nameFROM dba_tablespaces;SELECT file_nameFROM dba_data_files;drop tablespace badidea including contents;SELECT tablespace_nameFROM dba_tablespaces;SELECT file_nameFROM dba_data_files; Mandatory TablespacesSystem (must be named SYSTEM) - all versionsAlways 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 aboveEvery 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. DefinitionsThe Number Of Extents - Dictionary vs. Locally Managed TablespacesThe 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).LoggingSpecify 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 ManagementWhen 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:MANUALSpecifying 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 QueriesList tablespaces, their files, allocated space, free space, and next free extentclear breaksset linesize 132set pagesize 60break on tablespace_name skip 1col tablespace_name format a15col file_name format a50col 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_FREEFROM sys.dba_free_space fs, sys.dba_data_files ddWHERE dd.tablespace_name = fs.tablespace_nameAND dd.file_id = fs.file_idGROUP BY dd.tablespace_name, dd.file_name, dd.bytes/1024ORDER BY dd.tablespace_name, dd.file_name;List datafiles, tablespace names, and size in MBcol file_name format a50col tablespace_name format a10SELECT file_name, tablespace_name, ROUND(bytes/1024000) MBFROM dba_data_filesORDER BY 1;List tablespaces, size, free space, and percent freeThanks to Michael Lehmann for this querySELECT 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_FREEFROM (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) fsWHERE df.tablespace_name = fs.tablespace_name(+)ORDER BY fs.tablespace_name;View For Schema Owner To Monitoring Free SpaceCREATE OR REPLACE VIEW freespace_view ASSELECT tablespace_name, SUM(bytes/1024/1024) AVAILABLEFROM user_free_spaceGROUP BY tablespace_name;Another Statement For Tablespace Managementset linesize 121SELECT 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_FULLFROM ( 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 ForTablespace Management col tablespace_name format a15col alloc_size format 999.999col pct_used format 999.999col free_space format 999.999col maxnext format 999.999col definitsz format 999.999col defnextsz format 999.999SELECT 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 DEFNEXTSZFROM ( 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) BWHERE 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_chunkFROM ( 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) ddfWHERE dfs.tablespace_name = ddf.tablespace_nameORDER 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_SIZEFROM dba_extents e, dba_data_files fWHERE e.file_id = f.file_idGROUP BY f.file_name;Schemas In The SYSAUX Tablespacecol occupant_name format a25col schema_name format a20col move_procedure format a30col move_procedure_desc format a40set linesize 131SELECT occupant_name, schema_name, move_procedure, move_procedure_descFROM v_$sysaux_occupants; Contiguous Spacecreate 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 ASSELECT 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_MBFROM tl_contig_spaceGROUP 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 a20col sum_mb format 999.999SELECT * FROM v_contig_space; Related Topics Backup Restore & RecoveryCreate DatabaseData FilesDBMS_TTS Built-in PackageEncrypted TablespacesTablespace GroupsTransportable Tablespaces Contact Us � Legal Notices and Terms of Use � Privacy Statement