Oracle DBA Day to Day Scripts

Friday, January 22, 2021

Tablespace details

-- list all tablespaces with their associated files, the 
-- tablespace's allocated space, free space, and the 
-- next free extent:
 
clear breaks
SET linesize 130
SET pagesize 60
break ON tablespace_name skip 1
col tablespace_name format a15
col file_name format a50
col tablespace_kb heading 'TABLESPACE|TOTAL KB'
col kbytes_free heading 'TOTAL FREE|KBYTES'
 
SELECT dd.tablespace_name tablespace_name, dd.file_name file_name, dd.bytes/1024 TABLESPACE_KB, SUM(fs.bytes)/1024 KBYTES_FREE, MAX(fs.bytes)/1024 NEXT_FREE
FROM sys.dba_free_space fs, sys.dba_data_files dd
WHERE dd.tablespace_name = fs.tablespace_name
AND dd.file_id = fs.file_id
GROUP BY dd.tablespace_name, dd.file_name, dd.bytes/1024
ORDER BY dd.tablespace_name, dd.file_name;
 
 
 
-- list datafiles, tablespace names, and size in MB:
 
col file_name format a50
col tablespace_name format a10
 
SELECT file_name, tablespace_name, ROUND(bytes/1024000) MB
FROM dba_data_files
ORDER BY 1;
 
 
 
-- list tablespaces, size, free space, and percent free
-- query originally developed by Michael Lehmann 
 
SELECT df.tablespace_name TABLESPACE, df.total_space TOTAL_SPACE,
fs.free_space FREE_SPACE, df.total_space_mb TOTAL_SPACE_MB,
(df.total_space_mb - fs.free_space_mb) USED_SPACE_MB,
fs.free_space_mb FREE_SPACE_MB,
ROUND(100 * (fs.free_space / df.total_space),2) PCT_FREE
FROM (SELECT tablespace_name, SUM(bytes) TOTAL_SPACE,
      ROUND(SUM(bytes) / 1048576) TOTAL_SPACE_MB
      FROM dba_data_files
      GROUP BY tablespace_name) df,
     (SELECT tablespace_name, SUM(bytes) FREE_SPACE,
       ROUND(SUM(bytes) / 1048576) FREE_SPACE_MB
       FROM dba_free_space
       GROUP BY tablespace_name) fs
WHERE df.tablespace_name = fs.tablespace_name(+)
ORDER BY fs.tablespace_name;

Helpers

Database Scripts Library Index [ID 131704.1]

		Modified 03-NOV-2011 Type REFERENCE Status ARCHIVED

Database Scripts

Last updated on October 23, 2009

This document has been archived and will no longer be updated.

AdvancedQueueing	ContentMgmt.Spatial	ContentMgmt.Text	ContentMgmt.UltraSearch
ContentMgmt.XDB	ContentMgmt.interMedia	DBA.Admin	DBA.Architecture
DBA.DBCreationAndConfiguration	DBA.Monitoring	DBA.SQL	DBA.Storage
DBWarehouse.MaterialView	DBWarehouse.ParallelExecution	DBWarehouse.Partitioning	Distributed.General
Distributed.Replication	Distributed.Streams	Globalization	Globalization.DST
HighAvailability.BR	HighAvailability.Corruption	HighAvailability.DataGuard	HighAvailability.RMAN
Install.Installer	Install.UnixGeneric	JVM	Manageability.MemoryMgmt
Manageability.Utilities.ExportImport	Manageability.Utilities.SqlLoader	OLAP.ExpressServer	Performance.Database
Performance.Locking	Performance.SqlTuning	Scalability.OPS	Scalability.RAC
Security.DBSecurity	Storage.Exadata

AdvancedQueueing - Advanced Queuing

AQ PL/SQL Notification: PL/SQL Callback and Email Notification (Note:225749.1)
AQBasicCleanup.sql (Note:116841.1)
AQBasicDequeue.sql (Note:116840.1)
AQBasicEnqueue.sql (Note:116838.1)
AQBasicSetupQueue.sql (Note:116837.1)
AQBasicSetupUser.sql (Note:116834.1)
AQMultiConsumerCleanup.sql (Note:117062.1)
AQMultiConsumerDequeue.sql (Note:117061.1)
AQMultiConsumerEnqueue.sql (Note:117056.1)
AQMultiConsumerRegisterSubscribers.sql (Note:117053.1)
AQMultiConsumerSetupQueue.sql (Note:117051.1)
AQMultiConsumerSetupUser.sql (Note:117050.1)
Advanced Queueing Propagation using PL/SQL (Note:102771.1)
Dequeuing Messages from an Exception Queue (Note:233103.1)
How To List All Subscribers For a Multi-Consumer Queue (Note:209069.1)
PATCHAQ.SQL : Script 2 of 3 to workaround Bug:2757441 (Note:233993.1)
PATCHRULES.SQL: Script 1 of 3 to workaround Bug:2757441 (Note:233991.1)
Patch817Rules.sql : Script to remove duplicate rules prior to an upgrade to 9.2 (Note:233985.1)
Procedure to Dequeue Messages from any Queue not using Message Grouping (Note:243665.1)
UPGRADE-PATCH-92.SQL: Script 3 of 3 to workaround Bug:2757441 (Note:236899.1)

ContentMgmt.Spatial - Oracle Intermedia Spatial and Spatial Data Option

Cancer Ontology Brief Example (Note:453628.1)
Re-validation of Spatial Schema Owner MDSYS Objects (Note:132104.1)
Script To Collect Semantic Metadata (Note:453620.1)
Verification of an Oracle 8i Release 3 (8.1.7) Spatial Installation (Note:132102.1)
Verification of an Oracle 9i (Release 1 - 9.0.1) Spatial Installation (Note:221180.1)
Verification of an Oracle 9i (Release 2 - 9.2.0) Spatial Installation (Note:221174.1)

ContentMgmt.Text - Oracle Text (formerly interMedia Text)

Contents Check for an Index (Note:99881.1)
HOW TO INDEX DOCUMENTS WITH CERTAIN TYPE OF DATA IN BLOB COLUMNS (Note:170999.1)
TXTSUP_UTIL - Oracle Text diagnostics utility (Note:150546.1)

ContentMgmt.UltraSearch - Oracle Ultra Search

Ultra Search Health Script (Note:763925.1)
Verifying Oracle JVM has no problem with Runtime.exec spawning child process (Note:402661.1)

ContentMgmt.XDB - XML Database

Example Of Storing/Accesing Uri-refs In/From The Database (Note:137044.1)

ContentMgmt.interMedia - Oracle interMedia (Image, Video, Sound etc)

Script To Remove The VIR (Or VIR Compatible) Feature Objects (Note:291970.1)
Script To Verify interMedia NCOMP Libraries Are Loaded And Active (Note:308579.1)

DBA.Admin - General DBA Activities (Copying DB etc...)

GENERATE VIEW CREATION SCRIPT (Note:1016346.102) Script: To create a Database Schema Summary (Note:1019462.6) TFTS: VIEWING ORACLE PROCESS FROM WITHIN THE DATABASE (Note:1039299.6)
Toolkit for dynamic marking of Library Cache objects as Kept (PIND) (Note:301171.1)

DBA.Architecture - Oracle Server Architecture (processes and SGA)

SCRIPT: Calculating Buffer Cache Hit Ratio without Inputing Parameters (Note:1039290.6) SCRIPT: Script to Report SGA Buffer Summary (Note:1019635.6)
Script to Extract SQL Statements for all V$ Views (Note:132793.1)
Script: To list Running Jobs (Note:1020283.6) *

DBA.DBCreationAndConfiguration - Database Create and Configuration

SCRIPT: Generate a CREATE DATABASE command from an existing database. (Note:211905.1)

DBA.Monitoring - Database Monitoring

SCRIPT: REGENERATING PACKAGE and PROCEDURE CODE from DBA_SOURCE (Note:1012473.7) Script To Convert Hexadecimal Input Into a Decimal Value (Note:1019580.6)
Script To Monitor Memory Usage By Database Sessions (Note:239846.1)
Script to Monitor Current User Activity in the Database (Note:135749.1) Script: To Report Number of Tables/Indexes for Each User Per Tablespace (Note:1019998.6)
Script: V8.0 Report the Largest Segments in the Database (Note:222464.1)
Script: V8i/9i Report the Largest Segments in the Database (Note:222525.1)
Script: Batch Interface to the ORADIM utility on Windows NT (Note:74000.1) Script: Computing Table Size (Note:70183.1)
Script: Detemine Segment Order of Columns (Note:1020345.6)
Script: Display SQL text from Locked Transactions (Note:1020010.6) Script: Finding Objects in both SYSTEM and SYS schemas (Note:68080.1)
Script: For Data & Index Block Distribution and Selectivity (Note:1019718.6)
Script: List Inactive Users (Note:1020004.6) Script: Report Cluster Information (Note:90396.1)
Script: Report Open Cursors by User (Note:111415.1)
Script: Report Statistics for a Table, its Columns and Indexes with DBMS_STATS (Note:130688.1) Script: Report sessions waiting for Locks (Note:1020088.6) Script: To Calculate Business Days between two Dates (Note:1019589.6)
Script: To Capture Index Stat Information (Note:1020281.6) Script: To Capture Table Constraints (Note:1019469.6) Script: To Create a CREATE ROLLBACK SEGMENT Script (Note:18075.1) Script: To Derive the Name of a Session Generated Trace File on Windows NT or Unix (Note:73419.1)
Script: To Describe all Indexes in a User's Schema (Note:1019464.6) Script: To Determine the Number of Commits made by a Specific Session (Note:108273.1)
Script: To Display Active Transactions in Rollback Segments (Note:1019467.6)
Script: To Eliminate Non-Unique Rows from a Table (Note:1019920.6)
Script: To Extract Schema Information (Note:90449.1)
Script: To Find Invalid Objects in the Database (Note:1014072.102)
Script: To Format the Select of the Explain Plan Table (Note:1020282.6) Script: To List Tablespace, Datafiles and Free Space (Note:1019999.6) Script: To Monitor Session Idle Time (Note:97515.1) Script: To Obtain Rollback Segment Information (Note:1019485.6)
Script: To Obtain Session Information (Note:1019526.6) Script: To Report All Events Set in a Session (Note:1020308.6)
Script: To Report Dispatcher & Process Mapping Using MTS (Note:1019594.6)
Script: To Report Information on Indexes (Note:1019722.6) Script: To Report Instance Statistics (Note:1019624.6)
Script: To Report Map of all Database File (Note:1019714.6) Script: To Report Tables with many Extents (Note:1020085.6) Script: To Report Users and Number of Objects in Tablespaces (Note:1019994.6)
Script: To Report Users who Own Objects in a Database (Note:1019919.6) Script: To Show Data Selectivity for a Column (Note:1019622.6)
Script: To Show SGA Parameters and Statistics (Note:1020076.6) Script: To create a view to display all Open Pipes (Note:1020087.6)
Script: To determine an Object's Dependencies (Note:1020289.6)
Script: To display Wrap Time for Rollback Segments (Note:1019993.6)
Script: To export User Definitions (Note:1019554.6)
Script: To fully Describe a Table (Note:1020077.6) Script: To generate a Synonym Creations Script (Note:1020179.6)
Script: To generate a list of Table Dependencies (Note:1020089.6) Script: To provide a listing of all Database Files (Note:1020082.6) Script: To report Statistics for a Table (Note:1020003.6)
Script: To report Table Constraints (Note:1019930.6)
Script: To verify Stored Procedures (Note:1019928.6)
Script: Unload Data to Fixed-Width Text (Note:1019522.6)
Script: V7.3 To Report the Largest Segments in the Database (Note:1020083.6)
Script:Locating the Primary & Foreign Key Relationships (Note:16414.1)
Script:SQL DIAG- Determining Dependency Information (Note:62435.1)
TFTS: CONVERT MM/DD/YYYY HH:MI:SS TO REDO DUMP TIME (Note:1020342.6)
TFTS: CONVERT REDO DUMP TIME TO MM/DD/YYYY HH:MI:SS (Note:1020343.6)
TFTS: COUNT HOW MANY OF EACH OBJECT USERS HAVE (Note:1020185.6)
TFTS: FINDING THE NTH MAXIMUM VALUE OF FIELD (Note:1039306.6)

DBA.SQL - SQL Scripts, Examples and Reference Information

Finding The Top 'N' Values (Using SQL) (Note:30953.1)
How to Ignore Case and Accent in a Select Without Using interMedia (Note:156831.1)
PL/SQL script for cascade delete (Note:274219.1)
SCRIPT: Listing Trigger Errors and Line Numbers (Note:1050821.6)
Script For Recreating Referential Integrity Constraints for a Table (Note:1050254.6) Script to Analyze all Tables and Columns in Database (Note:124834.1)
Script to Create Numeric to Word Conversion Function (Note:1019544.6)
Script to Create SQL*Plus demo tables (Note:102635.1)
Script to Generate a Delete Cascade Report (Note:1019471.6)
Script to Generate a report of the users defined in a database (Note:111527.1) Script: Delete Commit Procedure (Note:1020306.6)
Script: Delete Statistics for All Tables in a Schema in Oracle 7.3 to 8.0 (Note:1020190.6) Script: How to Change a Sequence Starting Number Without Dropping the Sequence (Note:117387.1)
Script: How to Spool SQL*Plus Output to File (Note:120878.1)
Script: PL/SQL Block to Find VARCHAR2 Values that Cannot be Converted to Number (Note:149942.1)
Script: Report SQL Area Memory Usage by Users/Statement (Note:111322.1)
Script: SQL Area Summary Memory Usage Statistics by User (Note:111341.1)
Script: Select Distinct Row Data (Note:100658.1)
Script: To check Tables for Statistics (Note:1020188.6)
Script: To select the Top N values of Column 2 for each Distinct value of Column 1 (Note:114347.1)
Script: Tree Walking Examples (Note:29704.1)
Using DBMS_METADATA To Get The DDL For Objects (Note:188838.1)

DBA.Storage - Space Management and Object Storage

Database Report - Space Usage (Note:121632.1)
Script to Calculate Blocks Needed by a Table (Note:1019585.6)
Script to Detect Tablespace Fragmentation (Note:1020182.6)
Script to Determine Objects Per Tablespace (Note:1047952.6)
Script to Generate CREATE TABLESPACE (Note:1020180.6)
Script to List Percentage Utilization of Index Tablespace (Note:1039284.6)
Script to List the Details of Database Growth per Month (Note:135294.1)
Script to Print Block Map of Entire Database (Note:1019710.6)
Script to Report Extents and Contiguous Free Space (Note:162994.1)
Script to Report Segment Storage Parameters (Note:1019918.6)
Script to Report Segments in a Given Datafile (Note:1019720.6)
Script to Report Size of Stored Objects (8.x) (Note:111156.1)
Script to Report Space Distribution and utilization (Note:135677.1)
Script to Report Space Used in a Tablespace (Note:1019524.6)
Script to Report Table Extents & Storage Parameters (Note:1019505.6)
Script to Report Table Fragmentation (Note:1019716.6)
Script to Report Tables Approaching MAXEXTENTS in Oracle 9 and earlier (Note:1019721.6)
Script to Report Tablespace Free and Fragmentation (Note:1019709.6)
Script to Report Tablespace Use by Segment Type and User (Note:1019711.6)
Script to Report on Segment Extents (Note:1019915.6)
Script to Report on Space in Tablespaces (Note:1020090.6)
Script to Report on Tablespace Storage Parameters (Note:1019506.6)
Script to Show Which Tables Cannot Extend On Dictionary Manged Tablespaces (Note:1019553.6)
Script to Sort the Physical Rows of a Table (Note:1019924.6)
Script to capture INDEX_STAT Information (Note:35492.1)
Script to view Graphically the Occupied Space in a Tablespace (Note:1039298.6)

DBWarehouse.MaterialView - Materialized Views - Distributed & Local Summary

Script to create Snapshot Replication groups and objects (Note:123560.1)
Scripts to Report Information about Materialized View Logs at the Master Site (Note:236292.1)
Scripts to create Trusted / Untrusted Updateable Snapshot Replication Sites (Note:120094.1)

DBWarehouse.ParallelExecution - Parallel Execution

Display Execution plans from Statement's in V$SQL_PLAN (Note:260942.1)
Procedure PqStat to monitor Current PX Queries (Note:240762.1)
Report for the Degree of Parallelism on Tables and Indexes (Note:270837.1)
Script to map Parallel Execution Server to User Session (Note:344196.1)
Script to map Senderid in PX Wait Event to an Oracle Process (Note:304317.1)
Script to map parallel query coordinators to slaves (Note:202219.1)
Script to monitor PX limits from Resource Manager for active sessions (Note:240877.1)
Script to monitor parallel queries (Note:457857.1)

DBWarehouse.Partitioning - Partitioning

Example of Script to Create Local Prefixed Partitioned Index (Note:165938.1)
Example of Script to Create a Composite RANGE-HASH Partition Table (Note:165924.1)
Example of Script to Create a Global Prefixed Partitioned Index (Note:165656.1)
Example of Script to Create a Hash Partition Table (Note:164873.1)
Example of Script to Create a Range Partition Table (Note:164874.1)
Example of Script to Maintain Partitioned Indexes (Note:166755.1)
Example of Script to Maintain Range Partitioned Table (Note:166652.1)
How to Merge Partitions Script Example (Note:62847.1)
Oracle8i Partitioning - Updatable Partition key example (Note:219815.1)
Oracle8i-9i Partitioning - Updatable Partition key example (Note:62848.1)
SCRIPT: To Automate the Composite-Partitioned Table Exchange (Note:100701.1)
Script to Create Local Non-Prefixed Partitioned Index (Note:166112.1)

Distributed.General - Distributed Database Issues

Script to show Active Distributed Transactions (Note:104420.1) *

Distributed.Replication - Master Replication

Multi Master Replication Set-up Scripts for Oracle8.0.x on Unix (Note:74165.1) [Top]

Distributed.Streams - Streams

How To Rebuild 9.2 Streams Queue Tables via Export/Import (Note:373994.1)
Script to Prevent Excessive Spill of Message From the Streams Buffer Queue To Disk (Note:259609.1)

Globalization - Globalization Technology (NLS) on data storage, data access and server utilities

DATECHECK script to locate date columns requiring careful date handling (Note:76626.1)
Finding out your NLS Setup (Note:226692.1)
SCRIPT: Changing columns to CHAR length semantics ( NLS_LENGTH_SEMANTICS ) (Note:313175.1)
search.sql: a script for searching strings or characters in the database (Note:243096.1)

Globalization.DST - Daylight Savings Time (DST) Issues

Script to check if you are storing New Zealand Timezones in the database (Note:436909.1)

HighAvailability.BR - eneric RDBMS Backup and Recovery Issues

HotBackup Script (Note:221630.1)
Sample Hot Backup Script for Unix (Note:30454.1)

Script To Identify Files Needed For Hot Backups (Note:220509.1) [Top]

HighAvailability.Corruption - Diagnosing and Addressing Corruption Problems

"hcheck.sql" script to check for known problems in Oracle8i, Oracle9i, Oracle10g and Oracle 11g (Note:136697.1)
Baddata Script To Check Database For Corrupt column data (Note:428526.1)
Baddate Script To Check Database For Corrupt dates (Note:95402.1)
SALVAGE8i.PC - Oracle8i,Oracle9i Pro*C Code to Extract Data from a Corrupt Table. (Note:97357.1)
SCRIPT: For Bug:970640 to check if Target Database has been corrupted (Note:76746.1)
SCRIPT: VALIDATE.SQL to ANALYZE .. VALIDATE STRUCTURE objects in a Tablespace (Note:100419.1)

HighAvailability.DataGuard - Data Guard

Script to Collect Data Guard Logical Standby Diagnostic Information (Note:241512.1)
Script to Collect Data Guard Logical Standby Table Information (Note:269954.1)
Script to Collect Data Guard Physical Standby Diagnostic Information (Note:241438.1)
Script to Collect Data Guard Primary Site Diagnostic Information (Note:241374.1)

HighAvailability.RMAN - Recovery Manager

RMAN Backup Shell Script Example (Note:137181.1)
RMAN: How to Delete Obsolete Backups in 8.0.X Releases (Note:131973.1)
RMAN: Set Maxcorrupt For a Whole Database Backup (Note:130605.1)

Install.Installer - Oracle Server Installer issues

WIN: Invoking OUI in JAVA's Verbose Mode (Note:107551.1)

Install.UnixGeneric - Oracle Server Installation and Configuration Unix Generic issues

UNIX: Script to Verify Installation Requirements for Oracle 8.0.5 to 9.2 versions of RDBMS (Note:189256.1)

JVM - Oracle JServer

How To Determine The JDK Version Used by the Oracle JVM in the Database (Note:131872.1)

Manageability.MemoryMgmt - ORA-4030/ORA-4031 errors

Script: Listing Memory Used By All Sessions (Note:1070975.6)

Manageability.Utilities.ExportImport - Oracle Utilities (Export/Import) Technical Notes

How to Export Tables for a specific Tablespace (Note:1039292.6)
SCRIPT: Where to Find Specifications of Character Encoding Schemes (Note:93358.1)
Unix Script: IMPSHOW2SQL - Extracting SQL from an EXPORT file (Note:29765.1)

Manageability.Utilities.SqlLoader - SQL Loader

SCRIPT TO DUMP A TABLE TO AN ASCII FILE FOR SQL*LOADER LOAD (Note:1050919.6)
SCRIPT TO GENERATE SQL*LOADER CONTROL FILE (Note:1019523.6)
Script to Dump Non-Printable Data to a File to Be Used by SQL*Loader (Note:74947.1)

OLAP.ExpressServer - Oracle Express Server Generic

Calculating Time for Individual Object Exports in Express Databases (Note:98563.1)
How to troubleshoot OCI Connections for Express Server on UNIX (Note:99448.1)

Performance.Database - Tuning, Optimization and Other Performance Issues

AWR diagnostic collection script (Note:733655.1)
Formated V$SQL_SHARED_CURSOR Report by SQLID or Hash Value (Note:438755.1)
Reactive Performance Overview Report (Note:1020046.6)
SCRIPT - to Gauge the Impact of the SESSION_CACHED_CURSORS Parameter (Note:208918.1) SCRIPT: LATCH PERFORMANCE (Note:1019627.6)
SCRIPT: LIBRARY CACHE INFO (Note:1019934.6)
SCRIPT: REPORT FILE I/O STATISTICS (Note:1019629.6)
SCRIPT: REPORT ONLINE REDO LOG STATISTICS (Note:1020002.6)
SCRIPT: REPORT SESSION & SYSTEM WAITS (Note:1019936.6)
SCRIPT: REPORT SESSION STATS (Note:1020001.6)
SCRIPT: SGA TUNING INFORMATION (Note:1019935.6)
SCRIPT:ESTIMATE SHARED POOL UTILIZATION (Note:105004.1)
USING ORACLE SCRIPTS TO TUNE INDEX (Note:1061888.6)
VMS: UTLBSTAT/UTLESTAT Script Generator for Parallel Reporting (Note:174553.1)
ashdump* scripts and post-load processing of MMNL traces (Note:555303.1)

Performance.Locking - Database Lock and Latch Information and Diagnoses

APPLICATION TUNING AND LOCKING CONFLICTS SCRIPTS (Note:2062842.102)
SCRIPT TO CHECK FOR FOREIGN KEY LOCKING ISSUES (Note:1019527.6)
SCRIPT TO RETURN MEDIUM DETAIL LOCKING INFO (Note:1020012.6)
SCRIPT: FULLY DECODED LOCKING (Note:1020008.6)
SCRIPT: LOW COMPLEXITY LOCKING INFO (Note:1020011.6)
SCRIPT: VIEWING LOCKS ON OBJECTS HELD BY SPECIFIC USER (Note:1039273.6)
Script: To display Locks and give the SID and Serial# of the Session to Kill (Note:1020007.6) *

Performance.SqlTuning - Tuning SQL and application access - including CBO

"hcursor8.sql" script to install the "hCursor" helper package for Oracle8 (Note:101471.1)
"hdesc8.sql" script to install the "hDesc" helper package for Oracle8 (Note:101469.1)
"hinstall8.sql" script to install the "h*" helper packages for Oracle8 (Note:101467.1)
"hout.sql" script to install the "hOut" helper package (Note:101468.1)
"hplan8.sql" script to install the "hPlan" helper package for Oracle8 (Note:101470.1)
"hsession8.sql" script to install the "hSession" helper package for Oracle8 (Note:134231.1)
"hstat.sql" script to install "hStat" helper (Note:132946.1)
Introduction to the "H*" Helper Scripts (Note:101466.1)

Scalability.OPS - Oracle Parallel Server (including MPP)

Script to Collect OPS Diagnostic Information (opsdiag.sql) (Note:205809.1)
Script to help diagnose OPS hanging issues (utlopslt.sql) (Note:115595.1)
TFTS: Example Script to Create an OPS Database on UNIX (Note:90321.1)

Scalability.RAC - Real Application Clusters

Script to Collect RAC Diagnostic Information (racdiag.sql) (Note:135714.1)

Security.DBSecurity - User and Security Issues

Example Code Encrypting Credit Card Numbers (Note:197400.1)
How often tables are accessed (AUDIT) (Note:74725.1)
How to use DBMS_OBFUSCATION_TOOLKIT.DES3Encrypt and DES3Decrypt procedures. (Note:166884.1)
SCRIPT: Generate ROLE Creation Script for 8.XX (Note:107182.1)
SCRIPT: How to grant select on dictionary tables only (Note:138232.1)
SCRIPT: Report Roles Granted to Users (Note:1019486.6)
SCRIPT: Script to Generate object privilege GRANTS (Note:1020176.6)
SCRIPT: Script to show table privileges for users and roles (Note:1050267.6)
SCRIPT: Show Tablespace Quota Used by User (Note:1019712.6)
SCRIPT: To Report Privileges Granted To a User (Note:1020086.6)
SCRIPT: Workaround For ORA-28112 Error by Reformatting the Predicate String Returned (Note:262882.1)
Script To Capture System Privilege Grants (Note:18074.1)
Script to Capture Role Grants (Note:18079.1)
Script to Check for Default Passwords Being Used for Common Usernames (Note:227010.1)
Script to Create Roles (Note:18080.1)
Script to Create View to Show All User Privs (Note:1020286.6)
Script to Show System and Object Privs for a User (Note:1019508.6)
Script to list profile resources and limits (Note:1019933.6)
Script to move SYS.AUD$ table out of SYSTEM tablespace (Note:1019377.6)
Script to prevent a user from changing his password (Note:135878.1)
UNIX: Diagnostic C program for ORA-1031 from CONNECT INTERNAL / AS SYSDBA (Note:67984.1)

Storage.Exadata - Exadata issues

Oracle Exadata Diagnostic Information required for Disk Failures (Note:761868.1)

* - indicates Enterprise Manager provides similar functionality to this script

Email us your comments!
Email the Web Page Manager to report broken hyperlinks or to share your comments and ideas. Your comments will be routed to the Knowledge Team for evaluation. The Web Page Manager cannot process requests for information, technical support or other questions. For technical support click on the Service Request tab and log an SR.
Use the MetaLink Feedback button for all other requests including licensing issues.
ACROBAT files ending with extension .PDF can be read with an Acrobat Reader. Readers for many platforms are available without fee from the Adobe web site.
External sites are not endorsed by Oracle Corporation. All company or product names mentioned are used for identification purposes only and may be trademarks of their respective owners.

Related

Products

Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Personal Edition
Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Enterprise Edition

Errors

ORA-4031; ORA-28112; ORA-1031; ORA-4030

Rate this document

Thursday, March 28, 2019

DLL of a user in oracle

-- -----------------------------------------------------------------------------------
-- File Name    : https://oracle-base.com/dba/script_creation/user_ddl.sql
-- Author       : Tim Hall
-- Description  : Displays the DDL for a specific user.
-- Call Syntax  : @user_ddl (username)
-- Last Modified: 07/08/2018
-- -----------------------------------------------------------------------------------

set long 20000 longchunksize 20000 pagesize 0 linesize 1000 feedback off verify off trimspool on
column ddl format a1000

begin
   dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'SQLTERMINATOR', true);
   dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'PRETTY', true);
end;
/
 
variable v_username VARCHAR2(30);

exec :v_username := upper('&1');

select dbms_metadata.get_ddl('USER', u.username) AS ddl
from   dba_users u
where  u.username = :v_username
union all
select dbms_metadata.get_granted_ddl('TABLESPACE_QUOTA', tq.username) AS ddl
from   dba_ts_quotas tq
where  tq.username = :v_username
and    rownum = 1
union all
select dbms_metadata.get_granted_ddl('ROLE_GRANT', rp.grantee) AS ddl
from   dba_role_privs rp
where  rp.grantee = :v_username
and    rownum = 1
union all
select dbms_metadata.get_granted_ddl('SYSTEM_GRANT', sp.grantee) AS ddl
from   dba_sys_privs sp
where  sp.grantee = :v_username
and    rownum = 1
union all
select dbms_metadata.get_granted_ddl('OBJECT_GRANT', tp.grantee) AS ddl
from   dba_tab_privs tp
where  tp.grantee = :v_username
and    rownum = 1
union all
select dbms_metadata.get_granted_ddl('DEFAULT_ROLE', rp.grantee) AS ddl
from   dba_role_privs rp
where  rp.grantee = :v_username
and    rp.default_role = 'YES'
and    rownum = 1
union all
select to_clob('/* Start profile creation script in case they are missing') AS ddl
from   dba_users u
where  u.username = :v_username
and    u.profile <> 'DEFAULT'
and    rownum = 1
union all
select dbms_metadata.get_ddl('PROFILE', u.profile) AS ddl
from   dba_users u
where  u.username = :v_username
and    u.profile <> 'DEFAULT'
union all
select to_clob('End profile creation script */') AS ddl
from   dba_users u
where  u.username = :v_username
and    u.profile <> 'DEFAULT'
and    rownum = 1
/

set linesize 80 pagesize 14 feedback on trimspool on verify on

Monday, December 12, 2016

Rman backup status

Rman backup status

set linesize 999
set pagesize 500
col STATUS format a30
col START_TIME format a30
col END_TIME format a30
col hrs format 999.99
select
SESSION_KEY,
INPUT_TYPE,
STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi') end_time,
elapsed_seconds/3600 hrs
from
V$RMAN_BACKUP_JOB_DETAILS
order
by
session_key;

Example out put

Sunday, December 4, 2016

To check which Exadata server complete details

To Check the exadata server complete details

command : dmidecode -s system-product-name

Oracle Exadata Database Machine	Database Server Model	Exadata Storage Server Model
Oracle Exadata Database Machine X5-2	Oracle Server X5-2 Database Server `ORACLE SERVER X5-2`	Exadata Storage Server X5-2L Servers `ORACLE SERVER X5-2L`
Oracle Exadata Database Machine X5-8	Oracle Server X5-8 Server `ORACLE SERVER X5-8`	Exadata Storage Server X5-2L Servers `ORACLE SERVER X5-2L`
Oracle Exadata Database Machine X4-2	Sun Server X4-2 Oracle Database Server `SUN SERVER X4-2`	Exadata Storage Server X4-2L Servers `SUN SERVER X4-2L`
Oracle Exadata Database Machine X3-2	Sun Server X3-2 Oracle Database Server `SUN FIRE X4170 M3`	Exadata Storage Server X3-2 Servers `SUN FIRE X4270 M3`
Oracle Exadata Database Machine X2-2	Sun Fire X4170 M2 Oracle Database Server `SUN FIRE X4170 M2 SERVER`	Exadata Storage Server with Sun Fire X4270 M2 Servers `SUN FIRE X4270 M2 SERVER`
Oracle Exadata Database Machine X4-8 Full Rack	Sun Server X4-8 Oracle Database Server `SUN SERVER X4-8`	Exadata Storage Server X4-2L Servers `SUN SERVER X4-2L`
Oracle Exadata Database Machine X3-8 Full Rack	Sun Server X2-8 Oracle Database Server `Sun Fire X4800 M2`	Exadata Storage Server X3-2 Servers `SUN FIRE X4270 M3`
Oracle Exadata Database Machine X2-8 Full Rack	Sun Fire X4800 Oracle Database Server or Sun Server X2-8 Oracle Database Server `Sun Fire X4800` or `Sun Fire X4800 M2`	Exadata Storage Server with Sun Fire X4270 M2 Servers `SUN FIRE X4270 M2 SERVER`
Oracle Exadata Database Machine	Sun Fire X4170 Oracle Database Server `SUN FIRE X4170 SERVER`	Exadata Storage Server with Sun Fire X4275 Servers `SUN FIRE X4275 SERVER`

Monitoring ambient temparatures of exadata cluster

dcli -g /opt/oracle.SupportTools/onecommand/all_group -l root 'ipmitool   \
sunoem cli "show /SYS/T_AMB" | grep value'

example :

[root@fldadbadm01 bin]# ./dcli -g /opt/oracle.SupportTools/onecommand/all_group -l root 'ipmitool   \
> sunoem cli "show /SYS/T_AMB" | grep value': value = 21.250 degree C
fldadbadm02: value = 21.000 degree C
fldadbadm03: value = 20.750 degree C
fldadbadm04: value = 21.000 degree C
fldaceladm01: value = 21.000 degree C
fldaceladm02: value = 20.750 degree C
fldaceladm03: value = 21.000 degree C
fldaceladm04: value = 20.000 degree C
fldaceladm05: value = 21.000 degree C
fldaceladm06: value = 21.000 degree C
fldaceladm07: value = 21.250 degree C