(1) to query the DBA way
- query reference object SELECT * FROM dba_dependencies WHERE referenced_name='& lt; Object_name & gt; '; - check references related statement SELECT * FROM dba_source WHERE text like '% & lt; Object_name & gt; % ';
(2) in the form of a common database user query
- query reference object SELECT * FROM all_dependencies WHERE referenced_name='& lt; Object_name & gt; '; - check references related statement SELECT * FROM user_source WHERE text like '% & lt; Object_name & gt; % ';
2. Check the object/compile failure
- check INVALID object SELECT * FROM dba_objects WHERE status='INVALID'; SELECT * FROM dba_invalid_objects;
- compiling failure object BEGIN dbms_utility.com pile_schema (' & lt; Username> ', false); END;
- query compiler error message the SELECT * FROM dba_errors WHERE the owner='& lt; Username> ';
3. Check the object lock and unlock
- view the locked object
SELECT b.o wner, b.o bject_name, a.s ession_id, a. ocked_mode FROM v $locked_object a, dba_objects b WHERE b.o bject_id=a.o bject_id;
- see which user which process cause a deadlock
The SELECT b.u sername, b.s id, b.s erial#, logon_time FROM v $locked_object a, b v $session WHERE a.s ession_id=b.s id order by b.l ogon_time;
- check the connection process
SELECT the sid, serial#, username, osuser FROM v $session;
- find out lock objects sid, serial#, os_user_name, machine_name, terminal, the type of lock, mode
The SELECT s.s id, s.s erial#, s.u sername, s.s chemaname, s.o suser, supachai panitchpakdi rocess, s.m achine, s.t erminal, s.l ogon_time, l.t ypeFROM v $session s, v $lock lWHERE s.s id=l.s idAND s.u sername IS NOT NULLORDER BY sid.
This statement will find all the DML statements to the database lock, can also be found that any DML statements produced two locks, actually is a table lock, is a row lock,
Sid - kill process, serial#
The ALTER system kill session 'sid, serial#' immediate; - sid and serial# for the above query out the value of the
4. The stored procedure compilation jammed
The solution is as follows:
(1) check the V $DB_OBJECT_CACHE
SELECT * FROM V $DB_OBJECT_CACHE WHERE name='XXXX_PKG' AND the LOCKS!='0'.
Note: XXXX_PKG for the stored procedure name,
Find the locks=2
(2) according to the value of the object detected sid
The SELECT/* + */SID rule from V $ACCESS WHERE object='XXXX_PKG';
Note: XXXX_PKG for the stored procedure name,
(3) check the sid, serial#
SELECT the SID, SERIAL# PADDR FROM V $SESSION WHERE SID='just to check the SID';
(4) to kill the session
The ALTER system kill session 'sid value, serial# value' immediate;
5. Oracle table space
The query table space usage:
SELECT a.tablespace_name, a.bytes/1024/1024 "sum MB", (a.bytes - b.bytes)/1024/1024 "used MB", b.bytes/1024/1024 "free MB", round(((a.bytes - b.bytes)/a.bytes) * 100, 2) "used%" FROM (SELECT tablespace_name, sum(bytes) bytes FROM dba_data_files GROUP BY tablespace_name) a, (SELECT tablespace_name, sum(bytes) bytes, max(bytes) largest FROM dba_free_space GROUP BY tablespace_name) b WHERE a.tablespace_name=b.tablespace_name ORDER BY ((a.bytes - b.bytes)/a.bytes) DESC;
Table space extension:
(1) to check the table space name and file location:
The SELECT tablespace_name file_id, file_name, round (bytes/(1024 * 1024), 0) total_space FROM sys. Dba_data_files ORDER BY tablespace_name
(2) by increasing the required table space data file size to extend the tablespace
The ALTER database datafile 'table space' resize the new size
For example:
The ALTER database datafile '\ oracle \ oradata \ xxx_data10 DBF' resize 4000 m
(3) by increasing the number of table space data file extension table space
The ALTER in tablespace table space name add datafile 'new data file address size data file size
For example:
The ALTER in tablespace ESPS_2008 add datafile '\ oracle \ oradata \ xxx_data11 DBF' size
1000 m
(4) set the table space automatically expand
The ALTER database datafile 'data file location autoextend on next extend size automatically maxsize maximum extend size
For example:
The ALTER database datafile '\ oracle \ oradata \ xxx_data11 DBF' autoextend on next 100 m maxsize 10000 m
6. Check the database connection number
SELECT count (*) FROM v $session;
7. The user lock
- to track the status of the database user
SELECT the username, account_status FROM dba_users b WHERE b.u sername=upper (' & lt; Username> ');
- unlock
The ALTER user & lt; Username> The account unlock.
- unlock EXPIRED
The ALTER user & lt; Username> Identified by sh. The ALTER user & lt; Username> Identified by '& amp; Pw ';
CodePudding user response:
Mark
CodePudding user response:
Thanks for sharing, it is recommended that the record in a blog post,
CodePudding user response: