Showing posts with label Data Files. Show all posts
Showing posts with label Data Files. 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, August 31, 2023

How to extract all datafile names without path using substr and instr functions

›
select trim( substr(file_name, (instr(file_name,'/', -1, 1) +1) ) ) ...
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, ...
Friday, February 5, 2021

Find duplicate file names

›
You can find duplicate file names, on different file systems, by using this query: set lines 200 col "file_name" format a30 col...
Wednesday, November 27, 2019

How to move a datafile online in Oracle 12.1

›
Starting with Oracle 12.1, you can move datafiles online . Generate move-script: select 'alter database move datafile ''...
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...
Wednesday, May 28, 2014

How to recreate the control file and rename the database and the datafiles

›
To change a database name or file names, it can be practical to recreate the control file, and in some cases your only option. In this exa...
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 '...