how to alter a tablespace  through pl /sql procedure

Posted on 2009-02-12
Medium Priority
Last Modified: 2012-05-06
I want to alter a tablespace through pl/sql procedure ..how???
Question by:ashis1278
LVL 74

Expert Comment

ID: 23630438
create or replace procedure alter_my_tablespace(p_tablespace in varchar2)
        execute immediate 'alter tablespace ' || p_tablespace || ' coalesce';

change "coalesce" to whatever command you want
LVL 35

Accepted Solution

Mark Geerlings earned 2000 total points
ID: 23632123
Yes, the "execute immediate" command will allow you to execute DDL commands in a PL\SQL procedure.  But are you sure this is the best solution to the problem?  Using PL\SQL to do DDL commands (something that PL\SQL is not designed or optimized to do) is not usually the best option.

What is the business problem that you are trying to solve?

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses
Course of the Month15 days, 17 hours left to enroll

850 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