Script to Get Oracle Database Size

Save the following code as SQL file and run it via SQL developer or SQLPlus as SYSDBA to get the database size (dont forget the / in the end).

SET SERVEROUTPUT ON

Declare

  ddf Number:= 0;

  dtf Number:= 0;

  log_bytes Number:= 0;

  total Number:= 0;

BEGIN

  select sum(bytes)/power(1024,3) into ddf from dba_data_files;

  select sum(bytes)/power(1024,3) into dtf from dba_temp_files;

  select sum(bytes)/power(1024,3) into log_bytes from v$log;

  total:= round(ddf+dtf+log_bytes, 3);

  dbms_output.put_line('TOTAL DB Size is: '||total||'GB ');

END;
/

Fixing /bin/bash^M: bad interpreter: No such file or directory

If you write a bash script in Windows and you get this error after FTP'ing and running it in Linux, try the following to fix it

perl -i -pe's/\r$//;' script_name.sh

Reverse Engineering Oracle Database Schema to ER Diagram

In this post, I'll show how to use Oracle SQL Developer to reverse engineer a schema into a relational diagram. You will need SQL Developer v3.x for this.


1) Go to File >> Import >> Data Dictionary

2) This will open up the Import Wizard. Click on Add to connect to the Database which contains the schema you wish to reverse engineer.

3) Once you have successfully tested connection click OK.

4) Select the newly created connection and click on Next

5) You will be prompted for password again, enter and click OK. 

6) Now we select the schema. In my case I selected a schema called Tutorial that comes bundled with Oracle APEX.

7) Next we select the tables. You may select all tables or a handful to see the relationships between them. Here I wanted to see the relationship between the tables prefixed by OEHR (again comes with APEX).

8) Next you will get a view summary. Verify and click Finish

9) You will be asked for your logins for 3rd time in this process. Enter and click OK.

10) Review the log and close to view your ER diagram.

11) To print your ER diagram to image/PDF. Go to Data Modeler >> Print Diagram >> To Image File.