Reverse engineer the DDL

Here is how to reverse engineer the DDL commands. I've found this pretty useful sometimes. For example, in case you want to recreate the db and you want all the tablespace creation scrips. Instead of doin' it from scratch you can use the package, GET_METADA.GET_DDL to get the generate the script for you and then you can use these scripts to create the tablespaces once the db is recreated.

For example, to reverse engineer the tablespace creation script,

sql > set long 900000

sql > select DBMS_METADATA.GET_DDL('TABLESPACE','tablespace_name') from dual;

For example, if i want to get the tablespace creation script for the tablespace users, i use

sql > select DBMS_METADATA.GET_DDL('TABLESPACE','USERS') from dual;

and i set long as 900000 before doin' this, otherwise my output is truncated and i can't see the whole script.

To reverse engineer the table creation script,

select DBMS_METADATA.GET_DDL('TABLE','table_name') from dual;

You can do this to get the DDL of all the objects in the database.

Hope this helps.

-- Vish.

ALL ABOUT TABLESPACES

ALL ABOUT TABLESPACES

Here is all that you might need to know about when working with tablespaces. I've just given the syntax taking only some requirements into account. Change them according to your requirement.

DISCLAIMER: All the views experessed here are my own. I'm not responsible for any problem that you might get into by following the below syntax. Use them at your own risk. But, this is pretty much i use and they worked fine for me until now.

NOTE:

1. Never, Ever, Ever delete a datafile. You can only resize the datafile to five oracle blocks. You can drop the whole tablespace though !! If you delete the datafile, then you’ve to recover it. The control file remembers that the datafile is still there eventhough you drop it at the database level. Also, remember that there is no RECYCLE BIN in unix.

2. You can’t drop the default temp and undo tablespaces. You need to create a new temp/undotbs, make it default temp/undotbs and then drop the old temp/undo tbs.

3. Make sure you fill up the temp datafile once you add them to the temp tablespaces, ‘cos it does not really occupy the whole OS space until it is used completely. If you are on solaris, use mkfile command to do that.


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

1. To get the script of an already existing tablespace, use the following. (Reverse engineering the tablespace creation script)

select DBMS_METADATA.GET_DDL('TABLESPACE','tablespace_name') from dual;

For Example, to get the script of the SYSTEM tablespace,

select DBMS_METADATA.GET_DDL('TABLESPACE','SYSTEM') from dual;


2. To create a tablespace: (with extent management local, uniform size 128K extent sizes)

CREATE TABLESPACE "tablespace_name" DATAFILE
'/db/dbxxx/oracle/xxxxxxxxx/datafile_name.dbf' SIZE 1024M,
'/db/dbxxx/oracle/xxxxxxxxx/datafile_name.dbf' SIZE 1024M
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K SEGMENT SPACE MANAGEMENT AUTO;

3. To alter/add a datafile :

alter database datafile '/db/xxxx/xxx/datafilename.dbf' resize 2048M;

To add a datafile to an existing tablespace:

alter tablespace tablespace_name add datafile ‘datafile_name’ size xxxxM;

For example, to add a datafile of size 512M to the SYSTEM tablespace,

Alter tablespace system add datafile ‘/db/dbxxx/oracle/xxxxxxx/datafile_name.dbf’ size 512M;

4. To drop a tablespace:

drop tablespace tablespace_name including contents and datafiles;

5. To get the tablespace info of a database:

Note that the values are rounded to the nearest integer.

select a.tablespace_name, trunc(b.allocated)Allocated, trunc(a.free) Free, trunc(b.allocated-a.free) USED,
(100-trunc(((b.allocated-a.free)*100)/b.allocated)) Percentfree from
(select tablespace_name, sum(bytes)/1024/1024 free from sys.dba_free_space group by tablespace_name) a,
(select sum(bytes)/1024/1024 allocated, tablespace_name from dba_data_files group by tablespace_name ) b
where a.tablespace_name = b.tablespace_name
union
select 'Total', trunc( sum(b.allocated)), trunc(sum(a.free)), trunc(sum((b.allocated-a.free))),
00000 from
(select tablespace_name, sum(bytes)/1024/1024 free from sys.dba_free_space group by tablespace_name) a,
(select sum(bytes)/1024/1024 allocated, tablespace_name from dba_data_files group by tablespace_name ) b
where a.tablespace_name = b.tablespace_name
order by 2;

6. To check the default temporary tablespace of the database:

select property_name, property_value from database_properties;

select property_name, property_value from database_properties where property_name like ‘%TEMP%’;

7. To change the default temporary tablespace of the whole database:

alter database default temporary tablespace temp_tablespace_name;

8. To change the default temporary tablespace of a particular user :

alter user username default temporary tablespace temp_tablespace_name;


9. To create a temp tablespace:

On solaris:

host /usr/sbin/mkfile 1024m /db/db004/oracle/TOR_M704/dsmtemp.df01.dbf

create temporary tablespace DSMTEMP
tempfile '/db/dbXXX/XXXX/XXXXX/temp.df01.dbf' reuse
extent management local uniform size 128K;

If the OS is not solaris, then

create temporary tablespace DSMTEMP
tempfile '/db/dbXXX/XXXX/XXXXX/temp.df01.dbf' size 1024M
extent management local uniform size 128K;

NOTE : When you create the tempfile without using mkfile, the file is created but the entire space is not occupied at the OS level. Lets say that there is 1GB in that mount point where you have created the datafile of size 512M. It still shows that there is 1GB of space in that mountpoint eventhough this tempfile is created. This space is occupied only when that space is used. You can use the following script to recursively sort until the space is filled.

Select * from dba_source a, dba_source b, dba_source c order by 1;

This script basically keeps on sorting and is never ending. So, it fills up the temp space continiously until the whole tempfile is full. Once the whole tempfile is full, you can see that the space in that mountpoint (where there was 1GB of space ) is now only 512MB, ‘cos 512MB is used for this tempfile.


10. To add a tempfile to the existing temp tablespace:

alter tablespace temp add tempfile ‘/db/dbxxx/xxx/temp02.dbf’ size 1024M;

11. To resize the tempfile

alter database tempfile ‘/db/dbxxx/xxx/temp02.dbf’ resize 512M;

12. To drop and recreate a temp tablespace (lets say temp1):

a) first create another temp tablespace ..lets say temp2
b) then make this the default tablespace for the database, also for the users who had default temp tablespace as temp1
c) then drop the tablespace temp1
Note: The database won’t let you drop the default temporary tablespace of the database anyway.

13. To drop and recreate the undotablespace:

Follow the same steps as in step 12.

14. To drop a tempfile:

In 10g:

alter database tempfile '/db/dbxxx/xxxx/xxxx/temp01.dbf' drop including datafiles;

In 9i:

Alter database tempfile ‘/db/dbxxx/xxx/xxxx/temp01.dbf’ offline drop;

NOTE : Be careful in deleting the temp files. Refer to the documentation before doin’ that and remember that there is no RECYCLE BIN for unix. So, be extra careful when you delete something in unix.

15. To get the space usage of the datafiles:

COLUMN free_space_mb format 999999.90
COLUMN allocated_mb format 999999.90
COLUMN used_mb format 999999.90

SELECT SUBSTR (df.NAME, 1, 40) file_name, df.bytes / 1024 / 1024 allocated_mb,
((df.bytes / 1024 / 1024) - NVL (SUM (dfs.bytes) / 1024 / 1024, 0))
used_mb,
NVL (SUM (dfs.bytes) / 1024 / 1024, 0) free_space_mb
FROM v$datafile df, dba_free_space dfs
WHERE df.file# = dfs.file_id(+)
GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes
ORDER BY file_name;

Author : Kam Muhamad Sirajdin (Acknowledgement)


-- Will keep on adding stuff as i remember.....so keep checking.....