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
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;
|
No comments:
Post a Comment