Find overall database size

How do I find the overall database size?

The biggest portion of a database's size comes from the datafiles. To find out how many megabytes are allocated to ALL datafiles: select sum(bytes)/1024/1024 "Meg" from dba_data_files; To get the size of all TEMP files: select nvl(sum(bytes),0)/1024/1024 "Meg" from dba_temp_files; To get the size of the on-line redo-logs: select sum(bytes)/1024/1024 "Meg" from sys.v_$log; Putting it all together into a single query: select a.data_size+b.temp_size+c.redo_size "total_size" from ( select sum(bytes) data_size          from dba_data_files ) a,      ( select nvl(sum(bytes),0) temp_size          from dba_temp_files ) b,      ( select sum(bytes) redo_size          from sys.v_$log ) c /

Who is using which UNDO or TEMP segment?

Execute the following query to determine who is using a UNDO or Rollback Segment: SQL> SELECT TO_CHAR(s.sid)||','||TO_CHAR(s.serial#) sid_serial,   2         NVL(s.username, 'None') orauser,   3         s.program,   4         r.name undoseg,   5         t.used_ublk * TO_NUMBER(x.value)/1024||'K' "Undo"   6    FROM sys.v_$rollname    r,   7         sys.v_$session     s,   8         sys.v_$transaction t,   9         sys.v_$parameter   x 10   WHERE s.taddr = t.addr 11     AND r.usn   = t.xidusn(+) 12     AND x.name  = 'db_block_size'   SID_SERIAL ORAUSER    PROGRAM                        UNDOSEG         Undo ---------- ---------- ------------------------------ --------------- ------- 260,7      SCOTT      sqlplus@localhost.localdomain  _SYSSMU4$       8K                       (TNS V1-V3) Execute the following query to determine who is using a TEMP Segment: SQL> SELECT b.tablespace,   2         ROUND(((b.blocks*p.value)/1024/1024),2)||'M  9; "SIZE",   3         a.sid||','||a.serial# SID_SERIAL,   4         a.username,   5         a.program   6    FROM sys.v_$session a,   7         sys.v_$sort_usage b,   8         sys.v_$parameter p   9   WHERE p.name  = 'db_block_size' 10     AND a.saddr = b.session_addr 11  ORDER BY b.tablespace, b.blocks;   TABLESPACE SIZE    SID_SERIAL USERNAME PROGRAM ---------- ------- ---------- -------- ------------------------------ TEMP       24M     260,7      SCOTT    sqlplus@localhost.localdomain                                        (TNS V1-V3)

How does one copy data from one database to another in SQL*Plus?

The SQL*Plus COPY command is one of the fastest ways of copying data between databases and schemas. This is also one of the few methods that will handle LONG columns correctly. Look at this example:               COPY FROM SCOTT/TIGER@LOCAL_DB TO SCOTT/TIGER@REMOTE_DB  -               CREATE IMAGE_TABLE USING                                 -                      SELECT IMAGE_NO, IMAGE                            -                      FROM   IMAGES;