web analytics

Oracle: get the tablespace structure

The easiest method to get the tablespace structure is to use the dbms_metadata package:

SET linesize 200 LONG 50000 pagesize 5000 SELECT dbms_metadata.get_ddl(’TABLESPACE’,tablespace_name ) FROM dba_tablespaces WHERE tablespace_name IN ( ‘&your_tablespace’ );

Additionally, exp/imp (or expdp/impdp) utilities could be used. They could generate the DDL statements for all objects in the database, which could be […]

Oracle: usage of the tablespaces

SET pagesize 10000 SET COLSEP ‘|’ SET VERIFY off SET serveroutput ON SIZE 1000000 BREAK ON report COLUMN tablespace_name format a30 heading ‘TABLESPACE’ COLUMN sizegb   format 9999999999D9 heading ‘SIZE-Gb’ COLUMN usedproc format 999D99 heading ‘USED-%’ COLUMN status format a10 heading ‘STATUS’ COMPUTE SUM LABEL ‘Total size:’ OF sizegb ON report SELECT b.tablespace_name ,   […]

List the tablespaces in backup mode

Small query to list Oracle tablespaces, that are in backup mode:

SELECT d.tablespace_name, b.TIME FROM dba_data_files d, v$backup b WHERE d.file_id = b.FILE# AND b.STATUS = ‘ACTIVE’ ;