Oracle DBA common statements page 1/2

View the table space name and size Copy codeThe Code is 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;

View the name and size of the tablespace physical file:Copy codeThe Code is 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;

View the rollback segment name and size:Copy codeThe Code is 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 or what SQL statement is being executed in a rollback segment:Copy codeThe Code is 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: Which one do you want to check? Just a few)
View the control file:
SQL> select * from v $ controlfile;
View log files:
SQL> col member format a50
SQL> select * from v $ logfile;
How to view the sid and serial of 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 to determine the SQL optimization method currently used:
Use explain plan to generate an explain plan and check the value of POSITION column ID = 0 in PLAN_TABLE.
SQL> select decode (nvl (position,-1),-1, 'rbo', 1, 'cbo') from plan_table where id = 0;
How to view the latest SCN Number of the system:
SQL> select max (ktuxescnw * power (2, 32) + ktuxescnb) from x $ ktuxe;
Find the script for the TRACE file in ORACLE:
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 $ process. 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 the IP address of the client login:
SQL> select sys_context ('userenv', 'IP _ address') from dual;
How to Create a trigger to track Client IP addresses in the production database:Copy codeThe Code is as follows: SQL> create or replace trigger on_logon_trigger
After logon database
Begin
Dbms_application_info.set_client_info (sys_context ('userenv', 'IP _ address '));
End;
Trigger for recording login information in REM
Create or replace trigger LOGON_HISTORY
After logon on 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 the current date:
SQL> select to_char (sysdate, 'yyyy-mm-dd, hh24: mi: ss') from dual;
View the data file names of all tablespaces:
SQL> select distinct file_name, tablespace_name, AUTOEXTENSIBLE from dba_data_files;
View table space usage:Copy codeThe Code is 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 (. bytes, 0) FreeSpace, (sum (nvl (. bytes, 0)/(B. bytes) * 100 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 (minimum size for resize) and file header size of the data file:Copy codeThe Code is 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 (+ );