Script for checking the status of a user and its designated tablespace, before drop

The following script will generate a "drop user" and a "drop tablespace" command if the tablespace is used exclusively by the user you want to drop.

-- ============================================================
-- drop_user_safety_check.sql
-- Run as SYSDBA
-- ============================================================

set pagesize 200
set linesize 200
set verify off
set feedback off
set trimspool on

prompt
prompt Enter username to analyse:
accept v_username char prompt 'Username: '

column username format a30
column account_status format a20
column tablespace_name format a30
column status format a12
column other_owners format a12
column generated_statement format a120

prompt
prompt ============================================================
prompt 1. Account status
prompt ============================================================

select
    username,
    account_status,
    lock_date,
    expiry_date
from
    dba_users
where
    username = upper('&v_username');

prompt
prompt ============================================================
prompt 2. Tablespaces the user actually writes to (has segments in)
prompt ============================================================

select distinct
    s.tablespace_name
from
    dba_segments s
where
    s.owner = upper('&v_username')
order by
    s.tablespace_name;

prompt
prompt ============================================================
prompt 3. Tablespace status
prompt ============================================================

select distinct
    t.tablespace_name,
    t.status,
    t.contents
from
    dba_tablespaces t
    join dba_segments s
      on s.tablespace_name = t.tablespace_name
where
    s.owner = upper('&v_username')
order by
    t.tablespace_name;

prompt
prompt ============================================================
prompt 4. Exclusivity check (must be owned ONLY by this user)
prompt ============================================================
col tablespace_name format a30
col owner_count format 999999
col owners format a20
with ts_usage as (
    select
        tablespace_name,
        count(distinct owner) as owner_count,
        listagg(distinct owner, ', ') within group (order by owner) as owners
    from
        dba_segments
    where
        tablespace_name in (
            select distinct tablespace_name
            from dba_segments
            where owner = upper('&v_username')
        )
    group by
        tablespace_name
)
select
    tablespace_name,
    owner_count,
    owners,
    case
        when owner_count = 1 then 'YES'
        else 'NO'
    end as exclusive_to_user
from
    ts_usage
order by
    tablespace_name;

prompt
prompt ============================================================
prompt 5. Generated DROP statements (SAFE ONLY)
prompt ============================================================
set heading off
set pagesize 0
spool exec.sql

-- Drop user (always generated, copy-ready) or execute the file exec.sql
select
    'DROP USER ' || upper('&v_username') || ' CASCADE;' as generated_statement
from
    dual;

-- Drop tablespaces ONLY if exclusive
with ts_usage as (
    select
        tablespace_name,
        count(distinct owner) as owner_count
    from
        dba_segments
    where
        tablespace_name in (
            select distinct tablespace_name
            from dba_segments
            where owner = upper('&v_username')
        )
    group by
        tablespace_name
)
select
    'DROP TABLESPACE ' || tablespace_name ||
    ' INCLUDING CONTENTS AND DATAFILES;' as generated_statement
from
    ts_usage
where
    owner_count = 1
order by
    tablespace_name;
spool off;

prompt
prompt ============================================================
prompt End of report
prompt ============================================================

set feedback on
exit

Generation of "alter database rename file" scripts

I have written several blog posts where I generate "alter database rename file" statements using the familiar syntax

select 'alter database move datafile ''' || file_name || ''' TO ''' || replace(file_name,'old_sid','new_sid') || ''';'

Recently, an experienced co-worker showed me another version of the script which is, in my opinion, much simpler:

select 'alter database move datafile ' ||chr(39) || file_name || chr(39) || ' TO ' || chr(39) || replace(file_name,'old_sid','new_sid') || chr(39)|| ';'
from dba_data_files;

By referring to chr(39) instead of masking the char ' with the same character, your script becomes simpler to read and less error-prone. This will be particulary important as your scripts get more complex.

The same method can of course be applied when generating scripts for moving table partitions, indexes etc.

How to extract all mountpoints for oracle data files in a database

To extract all unique mount points used for datafiles in a database, used the query below:

SELECT distinct SUBSTR(FILE_NAME, 1,
               INSTR(FILE_NAME, '/', -1, 1) --> Search for position of the first occurrence of the char '/', start at end of string
              -1) "PATH" --> starting from the rightmost side of the string, search from position found in INSTR above, and work your way to position 1
FROM DBA_DATA_FILES
;

Example output:

PATH
---------------------------------
/sales_db_02/oradata/SALES
/salesdb/oradata/SALES
/sales_db_01/oradata/SALES

If the database is in mount-mode (for example, a physical standby database), exchange dba_data_files with v$datafile:

 SELECT distinct SUBSTR(NAME, 1,
                   INSTR(NAME, '/', -1, 1)  
                  -1) "PATH" 
FROM v$datafile;

For logfiles, exchange dba_data_files with v$logfile and file_name with member:

  SELECT distinct SUBSTR(member, 1,
               INSTR(member, '/', -1, 1) 
              -1) "PATH" 
FROM v$logfile
;

How to specifiy default compression for a tablespace

CREATE BIGFILE TABLESPACE star_data DATAFILE
'/oradata/proddb01/star_data.dbf' SIZE 1024M AUTOEXTEND ON NEXT 128M MAXSIZE 12T
LOGGING
DEFAULT
TABLE COMPRESS FOR OLTP
INDEX COMPRESS ADVANCED LOW
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

Note that the syntax used for tablespaces

TABLE COMPRESS FOR OLTP

is equivalent to the ROW STORE COMPRESS ADVANCED clause of CREATE TABLE.

Read more about tablespaces with default compression attributes in the 12.2 documentation

How to work around ORA-03206 when running the RCU (Repository Creation Utility) used by ODI

During installation you may encounter the error

ORA-03206: maximum file size of (13107200) blocks in AUTOEXTEND clause is out of range

In my case, the RCU attempted to create a temporary tablespace with autoextend of 100G. This is too much for a smallfile tablespace, and the error is thrown.
I checked the database creation scripts, and it is indeed created with smallfile as the default tablespace type.

To solve the problem, alter your database default setting:

ALTER DATABASE SET DEFAULT BIGFILE TABLESPACE;

and retry the operation.

Documentation for Oracle 12.2 can be found here

How to move data using Transportable Tablespaces

Mission:

* Move schema FXMART with all of its data from source database proddb01 to destination database testdb01
* In the same operation, rename schema FXMART with FXMARTST in the destination database testdb01.

While there are certainly a number of ways to accomplish such a task, I will show you how to use transportable tablespaces.

1. In your source database, check which tablespaces your schema is writing to:

SELECT UNIQUE TABLESPACE_NAME
FROM DBA_SEGMENTS
WHERE OWNER = 'FXMART';

TABLESPACE_NAME
FXDATA_2016
FX_MART_DATA
FXDATA_2014
FXDATA_2015

2. In your source database, check that these are self-contained:

exec dbms_tts.transport_set_check('FXDATA_2016,FX_MART_DATA,FXDATA_2014,FXDATA_2015', true);

Check errors using the view TRANSPORT_SET_VIOLATIONS:

SELECT * FROM TRANSPORT_SET_VIOLATIONS;

If you see no rows here, proceed to step 3.

3. In your source database, set the tablespaces to be transported to read only:

alter tablespace FXDATA_2016 read only;
alter tablespace FX_MART_DATA read only;
alter tablespace FXDATA_2014 read only;
alter tablespace FXDATA_2015 read only;

4. Make sure you actually have a directory object called "DP". If not, create it:

CREATE OR REPLACE DIRECTORY DP AS '/u02/exports';

5. In your source database, export the metadata for the tablespaces to be moved using data pump export.

First, create a parameter file as follows:

userid='/ as sysdba'
DIRECTORY=DP
DUMPFILE=FXMOVE_TSS.dmp
LOGFILE=FXMOVE_TSS.log
JOB_NAME=FXMOVE
TRANSPORT_TABLESPACES=FXDATA_2016,FX_MART_DATA,FXDATA_2014,FXDATA_2015
EXCLUDE=STATISTICS
METRICS=YES

Then, execute the export:

expdp parfile=expdp_tts.par

6. Identify the data files which belong to the tablespaces to be transported.

To identify the files, use this simple query:

SELECT FILE_NAME
FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME IN (
    SELECT UNIQUE TABLESPACE_NAME
    FROM DBA_SEGMENTS
    WHERE OWNER = 'FXMART'
  );

FILE_NAME
/u02/oradata/proddb01/fx_mart_data.dbf
/u02/oradata/proddb01/fxdata_2014.dbf
/u02/oradata/proddb01/fxdata_2015.dbf
/u02/oradata/proddb01/fxdata_2016.dbf

7. Transport the resulting dmpfile along with the datafiles of the tablespaces to be transported, over the network to the destination server, using scp

scp -i /home/oracle/.ssh/destinationserver_rsa /u02/exports/FXMOVE_TSS.dmp oracle@destinationserver:/u02/exports/.
scp -i /home/oracle/.ssh/destinationserver_rsa /u02/exports/fxdata_2016.dbf oracle@destinationserver:/u02/exports/.
scp -i /home/oracle/.ssh/destinationserver_rsa /u02/exports/fxdata_2015.dbf oracle@destinationserver:/u02/exports/
scp -i /home/oracle/.ssh/destinationserver_rsa /u02/exports/fxdata_2014.dbf oracle@destinationserver:/u02/exports/
scp -i /home/oracle/.ssh/destinationserver_rsa /u02/exports/fx_mart_data.dbf oracle@destinationserver:/u02/exports/

When the scp session is over, set the tablespaces back to read/write to allow normal usage in the source database:

alter tablespace FXDATA_2016 read write;
alter tablespace FX_MART_DATA read write;
alter tablespace FXDATA_2014 read write;
alter tablespace FXDATA_2015 read write;

8. In the destination database, create a directory object that points to the location where the datafiles and the dump file now resides:

CREATE OR REPLACE DIRECTORY DP AS '/u02/exports';

9. In the destination database, drop the tablespaces to be replaced:

drop tablespace FXDATA_2016 including contents and datafiles;
drop tablespace FXDATA_2014 including contents and datafiles;
drop tablespace FX_MART_DATA including contents and datafiles;
drop tablespace FXDATA_2015 including contents and datafiles;

10. In the destination database, drop and recreate the schema to be repopulated with fresh data. Note that the user's default tablepace need to be temporarily set to USERS, as the tablespaces we intend to use as default tablespace is not yet imported:

drop user FXMARTTST cascade;

CREATE USER FXMARTTST
  IDENTIFIED BY mysecretpassword
  DEFAULT TABLESPACE USERS 
  TEMPORARY TABLESPACE TEMP
  PROFILE APP_SCHEMAS;

-- Grant of system privileges and/or roles here

11. On the destination server, create an import parameter file as follows:

userid='/ as sysdba'
DIRECTORY=DP
DUMPFILE=FXMOVE_TSS.dmp
LOGFILE=imp_FXMOVE_TSS.log
JOB_NAME=FXMOVE
TRANSPORT_DATAFILES=/u02/oradata/testdb01/fx_mart_data.dbf,/u02/oradata/testdb01/fxdata_2014.dbf,/u02/oradata/testdb01/fxdata_2015.dbf,/u02/oradata/testdb01/fxdata_2016.dbf
REMAP_SCHEMA=FXMART:FXMARTTST
METRICS=YES

12. In the destination database, make the freshly imported tablepaces read write:

alter tablespace FXDATA_2016 read write;
alter tablespace FX_MART_DATA read write;
alter tablespace FXDATA_2014 read write;
alter tablespace FXDATA_2015 read write;

All done. You should now be able to logon to the database using FXMARTST and use the refreshed data.

Create a tablespace using input variables

Here is a script to help set up tablespaces based on the path used for the system tablespace:

SET LINES 200
-- NEW_VALUE in sqlplus specifies a variable to hold a column value
COL tabspace_path FORMAT A50 NEW_VALUE path

SELECT SUBSTR(FILE_NAME, 1, INSTR(FILE_NAME, '/', -1) -1) tabspace_path
FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME = 'SYSTEM';

SELECT '&path' variable_value
FROM DUAL;

CREATE BIGFILE TABLESPACE test_tbspc DATAFILE '&path/&tablespace_name..dbf' SIZE 32M AUTOEXTEND ON NEXT 8M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;

exit

Run the script:

oracle@myserver:[testdb01]# sqlplus / as sysdba @test.sql

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options


TABSPACE_PATH
--------------------------------------------------
/u02/oradata/testdb01
old   1: SELECT '&path' variable_value
new   1: SELECT '/u02/oradata/testdb01' variable_value

VARIABLE_VALUE
---------------------
/u02/oradata/testdb01

Enter value for tablespace_name: mytablespace
old   1: CREATE BIGFILE TABLESPACE mytablespace DATAFILE '&path/&tablespace_name..dbf' SIZE 32M AUTOEXTEND ON NEXT 8M MAXSIZE UNLIMITED
new   1: CREATE BIGFILE TABLESPACE mytablespace DATAFILE '/u02/oradata/testdb01/mytablespace.dbf' SIZE 32M AUTOEXTEND ON NEXT 8M MAXSIZE UNLIMITED

Tablespace created.

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

How to generate CREATE TABLESPACE statements for both encrypted and non-encrypted tablespaces

I used the following statement when I was supposed to pre-create encrypted tablespaces as part of a migration project. We were supposed to move a database using TDE from one server to another, from AIX to Linux. The database wasn't big so import could be used.

The statement will pre-create all tablespaces as they were in the old database with the exception of SYSTEM, SYSAUX etc.

SELECT 'create bigfile tablespace ' ||T.NAME || ' datafile ''/u02/oradata/proddb01' || LOWER(T.NAME) || '.dbf'' size 256M autoextend on next 128M maxsize unlimited extent management local segment space management auto encryption using ''' || E.ENCRYPTIONALG || ''' default storage(encrypt);' 
FROM  V$TABLESPACE T, V$ENCRYPTED_TABLESPACES E
WHERE T.TS# = E.TS#
UNION
SELECT 'create bigfile tablespace ' ||T.NAME || ' datafile ''/u02/oradata/proddb01' || LOWER(F.NAME) || ''' size 256M autoextend on next 128M maxsize unlimited extent management local segment space management auto;' 
FROM  V$TABLESPACE T, V$DATAFILE F
WHERE T.TS# = F.TS#
AND T.NAME NOT IN ('SYSTEM','SYSAUX','UNDOTBS1','USERS')
AND F.TS# NOT IN (SELECT TS# FROM V$ENCRYPTED_TABLESPACES);

How to rename or move a datafile in oracle.

The basic steps in relocating a datafile is as follows


1. Take the tablespace offline:

alter tablespace data offline normal;

2. Move the datafile physically, using an os command like cp:

cp data/oracle/proddb01/tmp/datafiles/data01.dbf /data/oracle/proddb01/datafile/data01.dbf

3. Inform the controlfile about your change:

alter database rename file '/data/oracle/proddb01/tmp/datafiles/data01.dbf' to '/data/oracle/proddb01/datafile/data01.dbf';

4. Make the tablespace available for usage again:

alter tablespace data online;

How to find the default tablespace type (smallfile vs bigfile)

SQL> select property_value 
from database_properties 
where property_name = 'DEFAULT_TBS_TYPE';

PROPERTY_VALUE
---------------
SMALLFILE

Change like this:

alter database set default bigfile tablespace;

The change will of course only take effect for future tablespaces, not existing ones.


Link to Oracle documentation

How to use SUBSTR and INSTR to find the path used for the files in a tablespace

SELECT SUBSTR(FILE_NAME, 1, 
               INSTR(FILE_NAME, '/', -1, 1)  -- Search for position of the first occurrence of the char '/', start at end of string
              -1) "PATH" -- starting from the rightmost side of the string, search from position found in INSTR above, and work your way to position 1
FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME = 'USERS'

If you want to limit the output the first file found for a tablespace, add

AND ROWNUM < 2;

See also this post for how to use this piece of code in a script using input variables.

Source: "SQL Certified Expert Exam Guide" by Steve O'Hearn, page 220

How to solve ORA-02180 when specifying COMPRESSION type

You get

ORA-02180: invalid option for CREATE TABLESPACE

when executing a create tablespace statement like this one:

CREATE TABLESPACE test DATAFILE 
  '/u02/oradata/mydb/test.ora' SIZE 32M AUTOEXTEND ON NEXT 32M MAXSIZE UNLIMITED
COMPRESS FOR OLTP 
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO;

Solution:
Add the DEFAULT keyword to specify the default parameters for the database:

CREATE TABLESPACE test DATAFILE 
  '/u02/oradata/mydb/test.ora' SIZE 32M AUTOEXTEND ON NEXT 32M MAXSIZE UNLIMITED
DEFAULT
COMPRESS FOR OLTP 
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO;

How to find and change the default temporary tablespace and default tablespace for a database instance

set lines 200
col description format a50
col property_value format a30
col property_name format a30
SELECT * 
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE '%TABLESPACE%';

To change the default temporary tablespace, use

alter database default temporary tablespace tmp;

To change the default tablespace, use

alter database default tablespace user_data;

How to shrink a datafile in a temporary tablespace

alter tablespace TEMP shrink tempfile '/data/oracle/PRODDB01/datafiles/temp_20.dbf' KEEP 8G;

This feature was introduced in Oracle 11g.

What is the difference between automatic segment space management and manual segment space management?

In a locally managed tablespace, there are two methods that Oracle Database can use to manage segment space: automatic and manual.

Manual segment space management uses linked lists called "freelists" to manage free space in the segment, while automatic segment space management uses bitmaps.

Automatic segment space management is the more efficient method, and is the default for all new permanent, locally managed tablespaces.

How to find size, free space, used space, allocated space for the tablespaces in your database

SELECT 
        F.FILE_ID,
        F.FILE_NAME,
        ROUND(F.BYTES/1024/1024) "Allocated(MB)",
        NVL(TO_CHAR(ROUND(F.BYTES/1024/1024 - SUM(E.BYTES/1024/1024),1)),'File full') "Used (MB)",
        NVL(TO_CHAR(ROUND(SUM(E.BYTES/1024/1024),1)), '0') "Free (MB)",
        LPAD(DECODE(F.AUTOEXTENSIBLE,'YES', 'JA','NO','NEIN!'), 11,' ') "Autoextend?",
        LPAD(DECODE(ROUND(F.MAXBYTES/1024/1024),0,'N/A',ROUND(F.MAXBYTES/1024/1024)),15,' ') "Max size (MB)"
FROM    DBA_DATA_FILES F LEFT OUTER JOIN DBA_FREE_SPACE E
ON      E.FILE_ID = F.FILE_ID
GROUP BY F.FILE_ID,F.FILE_NAME,F.BYTES,AUTOEXTENSIBLE,MAXBYTES
ORDER BY 1;

For a specific tablespace:

alter session set nls_language='american';
SET LINES 400
COL "File ID" format 999
COL "Tablespace name" format a20
COL "File name" format a50
COL "Available space (MB)" format 9999999999
COL "Free space (MB)" format a20
COL "Percentage used" format a20
COL "Max size (MB)" format a20
COL "Can file grow?" format a20
 
WITH   free_spc AS (
                  SELECT FILE_ID, SUM(BYTES/1024/1024) AS MBFree
                  FROM DBA_FREE_SPACE fre
                  WHERE TABLESPACE_NAME = UPPER('&&tablespace')
                  GROUP BY FILE_ID
          )
SELECT  F.FILE_ID "File ID",
             F.TABLESPACE_NAME "Tablespace name",
             F.FILE_NAME "File name",
             ROUND(F.USER_BYTES/1024/1024) "Available space (MB)",
             NVL( TO_CHAR(ROUND(FS.MBFree)), 'NADA') "Free space (MB)",
             LPAD(NVL(ROUND(( ROUND(F.BYTES/1024/1024 - FS.MBFree)/ROUND(F.BYTES/1024/1024) )  * 100),100),15,' ') || ' %' "Percentage used",
             LPAD(DECODE(F.AUTOEXTENSIBLE,'YES', 'JA','NO','NEIN!'), 11,' ') "Can file grow?",
             LPAD(DECODE(ROUND(F.MAXBYTES/1024/1024),0,'N/A',ROUND(F.MAXBYTES/1024/1024)),15,' ') "Max size (MB)"
FROM DBA_DATA_FILES F LEFT JOIN free_spc FS
ON F.FILE_ID = FS.FILE_ID
WHERE F.TABLESPACE_NAME = UPPER('&&tablespace')
ORDER BY F.FILE_ID ASC;

How to exclude certain file systems when search for files belonging to tablespaces

Statement can be useful for example when reorganizing databases etc.


SELECT file_name FROM dba_data_files
WHERE file_name NOT LIKE '/data/oracle/u01%'
INTERSECT
SELECT file_name FROM dba_data_files
WHERE file_name NOT LIKE '/data/oracle/u02%';

How to move a table containing LOB columns to a new tablespace

Find out which tables have LOB objects:

SELECT  TABLE_NAME,COLUMN_NAME,SEGMENT_NAME,TABLESPACE_NAME,INDEX_NAME
FROM    DBA_LOBS 
WHERE   OWNER = 'USR1';

Genrate a "move table" script:

select 'alter table ' || owner || '.' || table_name || ' move lob(' || column_name ||') store as (tablespace LOB_DATA);'
from   dba_tab_columns c
where  c.owner = 'USR1'
and    c.data_type like '%LOB%';

Query returns the following statements:

alter table USR1.LG_LOG move lob(MESSAGE_CONTEXT) store as (tablespace LOB_DATA);
alter table USR1.TFW_TEST_RESULT move lob(SQL_NUM_MATCHED_ERROR_ROWS) store as (tablespace LOB_DATA);

Note:
The LOB index is an internal structure that is strongly associated with LOB storage.
This implies that a user may not drop the LOB index or rebuild it.