To view the name and size of a table space
Copy Code code as follows:
Sql>select T.tablespace_name, round (SUM (bytes/(1024*1024)), 0) ts_size
From Dba_tablespaces T, dba_data_files d where t.tablespace_name = D.tablespace_name GROUP by T.tablespace_name;
To view the name and size of a physical file in a tablespace:
Copy Code code as follows:
Sql>select Tablespace_name, file_id, File_name,round (bytes/(1024*1024), 0) Total_space from Dba_data_files ORDER by Tablespace_name;
To view the rollback segment name and size:
Copy Code code as follows:
Sql>select segment_name, Tablespace_name, R.status,
(initial_extent/1024) Initialextent, (next_extent/1024) nextextent,
Max_extents, V.curext curextent from Dba_rollback_segs R, V$rollstat V
Where r.segment_id = V.USN (+) Order by Segment_name;
How to view what is running in a rollback segment, or what SQL statement is being executed:
Copy Code code as follows:
Sql>select D.sql_text,a.name
From V$rollname a,v$transaction b,v$session c,v$sqltext D
where A.usn=b.xidusn and B.addr=c.taddr and c.sql_address=
D.address and C.sql_hash_value=d.hash_value
and A.usn=1;
(Note: If you want to see which, you can write a few usn=?)
To view Control files:
Sql>select * from V$controlfile;
To view the log file:
Sql> Col member Format A50
Sql>select * from V$logfile;
How to view the SIDs and serial# for the current Sql*plus user:
Sql>select SID, Serial#, status from V$session where Audsid=userenv (' SessionID ');
How to view the character set of the current database:
Sql>select userenv (' language ') from dual;
Sql>select userenv (' Lang ') from dual;
How do you determine what SQL optimization is currently being used:
Generate explain plan with explain plan to check the id=0 position column values in the plan_table.
Sql>select Decode (NVL (position,-1), -1, ' RBO ', 1, ' CBO ') from plan_table where id=0;
How to view the current system's latest SCN number:
Sql>select Max (KTUXESCNW * Power (2,32) + KTUXESCNB) from X$ktuxe;
To find a trace file in Oracle script:
Sql>select U_dump.value | | '/' || Instance.value | | ' _ora_ ' | |
V$process.spid | | NVL2 (V$process.traceid, ' _ ' | | | | v$process.traceid, NULL) | | '. TRC ' "Trace File" from V$parameter U_dump cross join V$parameter instance cross join V$process join V$session on V$proces S.addr = v$session.paddr where u_dump.name = ' user_dump_dest ' and
Instance.name = ' instance_name ' and V$session.audsid=sys_context (' Userenv ', ' SessionID ');
Sql>select D.value | | '/ora_ ' | | P.spid | | '. TRC ' Trace_file_name
From (select P.spid from Sys.v_$mystat m,sys.v_$session s,
Sys.v_$process p WHERE m.statistic# = 1 and
S.sid = M.sid and p.addr = s.paddr) p, (select value from sys.v_$parameter where name = ' user_dump_dest ') D;
How to view client-logged IP addresses:
Sql>select sys_context (' Userenv ', ' ip_address ') from dual;
How to create a trigger to track client IP addresses in the production database:
Copy Code code as follows:
Sql>create or replace Trigger On_logon_trigger
After logon on database
Begin
Dbms_application_info.set_client_info (Sys_context (' Userenv ', ' ip_address '));
End
Trigger for REM Log login information
CREATE OR REPLACE TRIGGER logon_history
After LOGON in DATABASE--when (user= ' Wacos ')--only for USER ' Wacos '
BEGIN
INSERT INTO Session_history
Select Username,sid,serial#,audsid,osuser,action,sysdate,null,sys_context (' USERENV ', ' ip_address '), TERMINAL, Machine,program
From v$session where audsid = Userenv (' SessionID ');
End;
Query Current Date:
Sql> Select To_char (sysdate, ' Yyyy-mm-dd,hh24:mi:ss ') from dual;
To view the data file name for all table spaces:
Sql>select distinct file_name,tablespace_name,autoextensible from Dba_data_files;
To view table space usage:
Copy Code code as follows:
Sql>select sum (bytes)/(1024*1024) as Free_space,tablespace_name
From Dba_free_space GROUP by Tablespace_name;
Sql>select a.tablespace_name,a.bytes total,b.bytes USED, c.bytes free,
(b.bytes*100)/a.bytes "% USED", (c.bytes*100)/a.bytes "% free"
From SYS. Sm$ts_avail A,sys. Sm$ts_used B,sys. Sm$ts_free C
WHERE A.tablespace_name=b.tablespace_name and A.tablespace_name=c.tablespace_name;
Column tablespace_name format A18;
Column sum_m format A12;
Column used_m format A12;
Column free_m format A12;
Column pto_m format 9.99;
Select S.tablespace_name,ceil (SUM (s.bytes/1024/1024)) | | M ' Sum_m,ceil (Sum (s.usedspace/1024/1024)) | | M ' Used_m,ceil (sum (s.freespace/1024/1024)) | | M ' free_m, sum (s.usedspace)/sum (s.bytes) ptused
From (select B.file_id,b.tablespace_name,b.bytes,
(B.bytes-sum (NVL (a.bytes,0)) Usedspace,
Sum (NVL (a.bytes,0)) FreeSpace, (SUM (NVL (a.bytes,0))/(B.bytes)) * Freepercentratio from Sys.dba_free_space A, Sys.dba_data_files b
where a.file_id (+) =b.file_id GROUP by B.file_id,b.tablespace_name,b.bytes
Order by B.tablespace_name) s GROUP by S.tablespace_name
ORDER by sum (s.freespace)/sum (s.bytes) desc;
View the HWM of the data file (the minimum space that can be resize) and the file header size:
Copy Code code as follows:
SELECT v1.file_name,v1.file_id,
NUM1 Totle_space,
Num3 Free_space,
Num1-num3 "Used_space (HWM)",
NVL (num2,0) Data_space,
NUM1-NUM3-NVL (num2,0) file_head
From
(SELECT file_name,file_id,sum (bytes) num1 from Dba_data_files GROUP by file_name,file_id) v1,
(SELECT file_id,sum (bytes) num2 from Dba_extents GROUP by file_id) v2,
(SELECT file_id,sum (BYTES) num3 from Dba_free_space GROUP by file_id) v3
WHERE v1.file_id=v2.file_id (+)
and v1.file_id=v3.file_id (+);
Current 1/2 page
12 Next read the full text
