=============================================<\/p>\n
NO 1: Display the Current Archivelog Status<\/p>\n
=============================================<\/p>\n
<\/p>\n
The command below, when entered via SQLDBA, will display the current archivelog status of the database. The script<\/p>\n
will display the active log_archive_dest location, as well as the current and oldest online redo log sequence numbers.<\/p>\n
<\/p>\n
ARCHIVE LOG LIST;<\/p>\n
<\/p>\n
===========================================<\/p>\n
NO 2: Creating a Control File Trace File<\/p>\n
===========================================<\/p>\n
<\/p>\n
The command below, when entered via SQLDBA, will create a trace file in your trace file destination directory. The<\/p>\n
trace file will contain the complete CREATE CONTROLFILE command for the current database, saving you vast<\/p>\n
quantities of time if you ever need to generate that command.<\/p>\n
<\/p>\n
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;<\/p>\n
<\/p>\n
=============================================<\/p>\n
NO 3: Tablespace Free Extents and Free Space<\/p>\n
=============================================<\/p>\n
<\/p>\n
The query below will ONLY work in Oracle7.2 and higher. In previous versions, it was difficult to query<\/p>\n
DBA_DATA_FILES and DBA_FREE_SPACE in one query, since both could have multiple rows for each tablespace.<\/p>\n
Using 7.2’s FROM clause subquery feature, you can group the two queries separately, then join them on the<\/p>\n
Tablespace_Name value. The result shows the largest free extent in the tablespace, the number of free extents in the<\/p>\n
tablespace, the total free space in the tablespace, and the percentage of the tablespace’s available space that is free.<\/p>\n
<\/p>\n
column Tablespace_Name format A20<\/p>\n
column Pct_Free format 999.99<\/p>\n
<\/p>\n
select Tablespace_Name,<\/p>\n
Max_Blocks,<\/p>\n
Count_Blocks,<\/p>\n
Sum_Free_Blocks,<\/p>\n
100*Sum_Free_Blocks\/Sum_Alloc_Blocks AS Pct_Free<\/p>\n
from<\/p>\n
(select Tablespace_Name, SUM(Blocks) Sum_Alloc_Blocks<\/p>\n
from DBA_DATA_FILES<\/p>\n
group by Tablespace_Name),<\/p>\n
(select Tablespace_Name FS_TS_NAME,<\/p>\n
MAX(Blocks) AS Max_Blocks,<\/p>\n
COUNT(Blocks) \u00a0 AS Count_Blocks,<\/p>\n
SUM(Blocks) AS Sum_Free_Blocks<\/p>\n
from DBA_FREE_SPACE<\/p>\n
group by Tablespace_Name)<\/p>\n
where Tablespace_Name = FS_TS_NAME;<\/p>\n
<\/p>\n
================================================<\/p>\n
No. 4: Display Allocated Space & Quota by User<\/p>\n
================================================<\/p>\n
<\/p>\n
DBA_TS_QUOTAS displays the allocated space, by user, in each tablespace, along with the quota for each of those<\/p>\n
users. If a user has unlimited quota in a tablespace, the quota will be displayed as 0 or negative. Running this script is a<\/p>\n
great quick way to see which users own objects in which tablespaces, and how close they are to their quotas. You’ll<\/p>\n
need to SET LINESIZE 132 before running this script.<\/p>\n
<\/p>\n
select * from DBA_TS_QUOTAS<\/p>\n
order by Tablespace_Name, Username;<\/p>\n
<\/p>\n
================================================<\/p>\n
No. 5: Show Allocated Storage for All Objects<\/p>\n
================================================<\/p>\n
<\/p>\n
DBA_SEGMENTS shows the allocated storage for all objects in the database. Valid values for the &segment_type<\/p>\n
variable include:<\/p>\n
<\/p>\n
TEMPORARY–for currently used temporary segments. Watch them grow!<\/p>\n
ROLLBACK–for rollback segments<\/p>\n
INDEX–for indexes<\/p>\n
TABLE–for tables<\/p>\n
<\/p>\n
There is one record in DBA_SEGMENTS for each segment. There is a one-to-many relationship between<\/p>\n
DBA_SEGMENTS and DBA_EXTENTS: DBA_SEGMENTS shows you the total space allocated to a segment;<\/p>\n
DBA_EXTENTS shows the space allocated to each extent within the segment.<\/p>\n
<\/p>\n
column Segment_Name format A40<\/p>\n
<\/p>\n
select Segment_Name, Extents, Blocks<\/p>\n
from DBA_SEGMENTS<\/p>\n
where Segment_Type = ‘&segment_type’<\/p>\n
order by Segment_Name;<\/p>\n
<\/p>\n
==============================================<\/p>\n
No. 6: Map a Tablespace’s Used and Free Space<\/p>\n
==============================================<\/p>\n
<\/p>\n
Since DBA_EXTENTS shows the starting block of each extent (the combination of File_ID and Block_ID), plus the<\/p>\n
length of each extent (the Blocks column), you can map out the allocated space within a given tablespace as a series of<\/p>\n
consecutive extents. DBA_FREE_SPACE shows the free extents, so the combination of DBA_EXTENTS and<\/p>\n
DBA_FREE_SPACE produces a map of the used and unused free space within a tablespace. You can use this map of<\/p>\n
the tablespace’s space to determine whether an object has been properly sized or if the tablespace needs to be<\/p>\n
defragmented.<\/p>\n
<\/p>\n
select Segment_Name, File_Id, Block_Id, Blocks<\/p>\n
from DBA_EXTENTS<\/p>\n
where Tablespace_Name = ‘&&tablespacename’<\/p>\n
UNION<\/p>\n
select ‘Free Space’, File_Id, Block_Id, Blocks<\/p>\n
from DBA_FREE_SPACE<\/p>\n
where Tablespace_Name = ‘&&tablespacename’<\/p>\n
order by 2,3;<\/p>\n
<\/p>\n
=======================================<\/p>\n
No. 7: Blocks Used by Data in a Table<\/p>\n
=======================================<\/p>\n
<\/p>\n
When you replace “sometable” with the name of a table in your database, the query below will return the number of<\/p>\n
blocks used by the data in that table. The query examines the Block_ID (columns 1-8) and the File_ID (columns 15-18)<\/p>\n
of the ROWID. The query then returns the number of distinct blocks used by the records within the table. The<\/p>\n
ANALYZE command can also provide this information, but this query is faster.<\/p>\n
<\/p>\n
select COUNT(DISTINCT(SUBSTR(ROWID,1,8)||SUBSTR(ROWID,15,4)))<\/p>\n
Blocks_Used<\/p>\n
from sometable;<\/p>\n
<\/p>\n
================================<\/p>\n
No. 8: Reset a User’s Password<\/p>\n
================================<\/p>\n
<\/p>\n
The script below generates an ALTER USER command that will reset a user’s password (the user whose username is<\/p>\n
passed to the script as &&1). If you spool the output of this command, you will be able to reset the user’s password<\/p>\n
without ever having to know what the password was. You can change the user’s password to anything you like, use the<\/p>\n
account for testing purposes, and then reset the password when your testing is complete. For a detailed discussion, see<\/p>\n
Chapter 9 of the Oracle DBA Handbook.<\/p>\n
<\/p>\n
select ‘ALTER USER &&1 IDENTIFIED BY VALUES ‘<\/p>\n
||””||Password||””||’;’<\/p>\n
from DBA_USERS where Username = UPPER(‘&&1’);<\/p>\n
<\/p>\n
=========================<\/p>\n
No. 9: Query V$PARAMETER<\/p>\n
=========================<\/p>\n
<\/p>\n
Don’t try to guess what parameters are in effect. You can never tell for sure which INIT.ORA file was used to start an<\/p>\n
instance, or if the INIT.ORA file has been modified since the database was started. The only sure way to tell the setting<\/p>\n
of an initialization parameter is to query V$PARAMETER.<\/p>\n
<\/p>\n
column Name format A50<\/p>\n
column Value format A28<\/p>\n
<\/p>\n
select Name, Value from V$PARAMETER;<\/p>\n
<\/p>\n
======================================<\/p>\n
No. 10: Show Statement Execution Path<\/p>\n
======================================<\/p>\n
<\/p>\n
This query selects the execution path from the PLAN_TABLE, ordered according to the hierarchy of the steps<\/p>\n
involved. The query shown in the listing specifically queries for the execution path of a statement using the<\/p>\n
Statement_ID of ‘TEST’. Prior to running this query, you should use the EXPLAIN PLAN command to populate<\/p>\n
PLAN_TABLE.<\/p>\n
<\/p>\n
select<\/p>\n
LPAD(‘ ‘,2*LEVEL)||Operation||’ ‘||Options<\/p>\n
||’ ‘||Object_Name \u00a0 Q_PLAN<\/p>\n
from PLAN_TABLE<\/p>\n
where Statement_ID = ‘TEST’<\/p>\n
connect by prior ID = Parent_ID and Statement_ID = ‘TEST’<\/p>\n
start with ID=1;<\/p>\n","protected":false},"excerpt":{"rendered":"
============================================= NO 1: Display the Current Archivelog Status ============================================= The command below, when entered via SQLDBA, will display the current archivelog status of the database. The script will display the active log_archive_dest location, as well as the current and oldest online redo log sequence numbers. ARCHIVE LOG LIST; =========================================== NO 2: Creating […]<\/p>\n","protected":false},"author":1,"featured_media":2410,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"spay_email":""},"categories":[3],"tags":[902,903],"jetpack_featured_media_url":"https:\/\/i0.wp.com\/haisins.synology.me\/wordpress\/wp-content\/uploads\/2018\/01\/databaetail.jpg?fit=905%2C480","amp_enabled":true,"_links":{"self":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/2715"}],"collection":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2715"}],"version-history":[{"count":1,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/2715\/revisions"}],"predecessor-version":[{"id":2716,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/2715\/revisions\/2716"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/media\/2410"}],"wp:attachment":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2715"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2715"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2715"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}