Showing posts with label Tablespaces. Show all posts
Showing posts with label Tablespaces. Show all posts
Friday, October 27, 2023

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 ...
Thursday, June 10, 2021

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, ...
Monday, March 16, 2020

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 ...
Monday, September 16, 2019

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 I...
Monday, June 25, 2018

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 operati...
Tuesday, July 11, 2017

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 specifi...
Friday, April 7, 2017

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 m...
Tuesday, August 2, 2016

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 th...
Tuesday, November 10, 2015

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 --------------- S...
Sunday, May 31, 2015

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 cha...
Monday, February 23, 2015

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...
Monday, December 1, 2014

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 WHER...
Friday, January 24, 2014

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...
Wednesday, November 27, 2013

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 ...

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.BYTE...
Monday, October 28, 2013

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 '...
Monday, October 21, 2013

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 ...