?
Solved

Need to remove OS files based on SQL

Posted on 2011-05-10
4
Medium Priority
?
554 Views
Last Modified: 2013-12-18
This is needed when restoring a new database and I need to remove select files obtained from output of a SQL script (file path/name) pertaining to the database that will be removed.

For example, need to create a batch file that will remove the filenames obtained by the following SQL (existing DB is up at this point).

select substr(name,1,50) as filepathname, bytes/1000000 as MB from v$datafile order by bytes desc;
select t.name, t.bytes/1000000 as MBs from v$tempfile t;
select f.member as thename, l.bytes/1000000 as thesize from v$logfile f join v$log l on f.group# = l.group#;  
select name, (blocks * block_size)/1000000 as MSIZE from v$archived_log where deleted = 'NO';
select NAME, sum((blocks * block_size)/1000000) total_MB from v$datafile_copy group by NAME;  
show parameter dump;
show parameter spfile;
show parameter control;

Thanks.
0
Comment
Question by:globalwm2
  • 2
4 Comments
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1000 total points
ID: 35731701
The easy wat is to use sqlplus to spool out the output that really creates a bat file:


something like:
--------------------
set lines 0
set trimspool on
set lines 1000
spool del_them.bat
select 'del ' || file_name from dba_data_files;
--the rest of your selects
spool off

shutdown abort
host del_them
0
 
LVL 40

Assisted Solution

by:mrjoltcola
mrjoltcola earned 1000 total points
ID: 35731739
Make sure the db is down before removing it. Also make sure you double check that you have the right instance. :)
select 'rm -f '||filename from (
select substr(name,1,50) as filename, bytes/1000000 as MB from v$datafile
union
select t.name as filename, t.bytes/1000000 as MBs from v$tempfile t
union
select f.member as filename, l.bytes/1000000 as thesize from v$logfile f join v$log l on f.group# = l.group#  
union
select name as filename, (blocks * block_size)/1000000 as MSIZE from v$archived_log where deleted = 'NO'
union
select NAME as filename, sum((blocks * block_size)/1000000) total_MB from v$datafile_copy group by NAME
)

Open in new window


You might also consider using "DROP DATABASE" or the DBCA to drop the database, though you'd still need to cleanup logfiles etc.
0
 

Author Closing Comment

by:globalwm2
ID: 35732251
Seems simple enough... Thanks!
0
 

Author Comment

by:globalwm2
ID: 35733318
Here is the finished script - a combination of each:  


SET PAGESIZE 1000
SET LINESIZE 168
SPOOL G:\SQL\Clone1.lis

SELECT TO_CHAR (SYSDATE, 'mm/dd/yyyy hh24:mi') AS AS_OF FROM DUAL;

--
--
CLEAR COLUMNS
COL filepathname FORMAT a70
--

  SELECT SUBSTR (name, 1, 60) AS filepathname, bytes / 1000000 AS MB
    FROM v$datafile
ORDER BY bytes DESC;

--

SELECT t.name AS filepathname, t.bytes / 1000000 AS MBs
  FROM v$tempfile t;

--

SELECT f.MEMBER AS filepathname, l.bytes / 1000000 AS thesize
  FROM v$logfile f JOIN v$log l ON f.group# = l.group#;

--

SELECT name AS filepathname, (blocks * block_size) / 1000000 AS MSIZE
  FROM v$archived_log
 WHERE deleted = 'NO';

--

  SELECT NAME AS filepathname, SUM ( (blocks * block_size) / 1000000) total_MB
    FROM v$datafile_copy
GROUP BY NAME;

--
CLEAR COLUMNS
COL NAME_COL_PLUS_SHOW_PARAM FORMAT a30
COL VALUE_COL_PLUS_SHOW_PARAM FORMAT a80
--
SHOW PARAMETER DUMP;
--
SHOW PARAMETER spfile;
--
SHOW PARAMETER control;
--
--
CLEAR COLUMNS
COL filename FORMAT a70
--

SELECT 'rm -f ' || filename AS FILES_TO_DELETE
  FROM (SELECT SUBSTR (name, 1, 70) AS filename FROM v$datafile
        UNION
        SELECT t.name AS filename
          FROM v$tempfile t
        UNION
        SELECT f.MEMBER AS filename
          FROM v$logfile f JOIN v$log l ON f.group# = l.group#
        UNION
        SELECT name AS filename
          FROM v$archived_log
         WHERE deleted = 'NO'
        UNION
          SELECT NAME AS filename
            FROM v$datafile_copy
        GROUP BY NAME
        UNION
        SELECT VALUE AS filename
          FROM v$parameter
         WHERE SUBSTR (VALUE, 1, 1) = '/' AND UPPER (name) LIKE '%DUMP%'
        UNION
        SELECT VALUE AS filename
          FROM v$parameter
         WHERE SUBSTR (VALUE, 1, 1) = '/' AND UPPER (name) LIKE '%SPFILE%'
        UNION
        SELECT name AS filename FROM v$controlfile);

SPOOL OFF

Open in new window

0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
The purpose of this article is to demonstrate how we can upgrade Python from version 2.7.6 to Python 2.7.10 on the Linux Mint operating system. I am using an Oracle Virtual Box where I have installed Linux Mint operating system version 17.2. Once yo…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question