New table name in DDL using DBMS_METADATA

If you want to get a DDL with the desired name for a table this is function will be used.

Step 1:

Replace YOUR SCHEMA name in line # 13 in below block i.e., from 'A' to 'SCHEMA name'

  1  create or replace function remap_name(orig_name varchar2, new_name varchar2 )
  2  return clob is
  3    -- Define local variables.
  4    h   number; --handle returned by OPEN
  5    th  number; -- handle returned by ADD_TRANSFORM
  6    doc clob;
  7  begin
  8    -- Specify the object type.
  9    h := dbms_metadata.open('TABLE');
 10    -- Use filters to specify the particular object desired.
 11    dbms_metadata.set_filter(h
 12                            ,'SCHEMA'
 13                            ,'A');
 14    dbms_metadata.set_filter(h
 15                            ,'NAME'
 16                            ,orig_name);
 17    -- Request that the schema name be modified.
 18    th := dbms_metadata.add_transform(h
 19                                     ,'MODIFY');
 20    dbms_metadata.set_remap_param(th
 21                                 ,'REMAP_NAME'
 22                                 ,orig_name
 23                                 ,new_name);
 24    -- Request that the metadata be transformed into creation DDL.
 25    th := dbms_metadata.add_transform(h
 26                                     ,'DDL');
 27    -- Specify that segment attributes are not to be returned.
 28    dbms_metadata.set_transform_param(th
 29                                     ,'SEGMENT_ATTRIBUTES'
 30                                     ,false);
 31    dbms_metadata.set_transform_param(th,'SQLTERMINATOR',TRUE);
 32    -- Fetch the object.
 33    doc := dbms_metadata.fetch_clob(h);
 34    -- Release resources.
 35    dbms_metadata.close(h);
 36    return doc;
 37  end remap_name;
 38  /

PLEASE REMOVE LINE NUMBERS BEFORE EXECUTION

Step 2. Call the remape_name and spool it

set echo off head off feed off

spool renameDDL.sql

SELECT 'SELECT remap_name('''||  TABLE_NAME || ''','''||  TABLE_NAME || '_new'') FROM DUAL;' FROM DBA_TABLES where owner='A';

spool off

Step 3. Review the above script and execute accordingly

Example:

sys@TESTDB> SELECT remap_name('TEST_OBJ','TEST_OBJ_new') FROM DUAL;

REMAP_NAME('TEST_OBJ','TEST_OBJ_NEW')

-------------------------------------------------

  CREATE TABLE "A"."TEST_OBJ_new"

   (    "OWNER" VARCHAR2(30),

        "OBJECT_NAME" VARCHAR2(128),

Note: 

REMAP_NAME is not available in 10g , available from 11g

NO REMAP_NAME ins 10g

http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_metada.htm#BGBBIEGA

REMAP_NAME is available in SET_REMAP_PARAM 11g only

http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_metada.htm#BGBBIEGA 

You will get the below error in 10g but function will be created 

ORA-31600: invalid input value REMAP_NAME for parameter NAME in function SET_REMAP_PARAM