-- 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;
Friday, January 22, 2021
Tablespace details
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 - 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)
[Top]
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)
[Top]
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)
[Top]
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)
[Top]
ContentMgmt.XDB - XML Database
-
Example Of Storing/Accesing Uri-refs In/From The Database
(Note:137044.1)
[Top]
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)
[Top]
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)
[Top]
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) *
[Top]
DBA.DBCreationAndConfiguration - Database Create and Configuration
[Top]
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)
[Top]
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)
[Top]
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)
[Top]
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)
[Top]
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)
[Top]
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)
[Top]
Distributed.General - Distributed Database Issues
- Script to show Active Distributed Transactions (Note:104420.1) *
[Top]
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)
[Top]
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)
[Top]
Globalization.DST - Daylight Savings Time (DST) Issues
[Top]
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)
[Top]
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)
[Top]
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)
[Top]
Install.Installer - Oracle Server Installer issues
- WIN: Invoking OUI in JAVA's Verbose Mode (Note:107551.1)
[Top]
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)
[Top]
JVM - Oracle JServer
[Top]
Manageability.MemoryMgmt - ORA-4030/ORA-4031 errors
- Script: Listing Memory Used By All Sessions (Note:1070975.6)
[Top]
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)
[Top]
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)
[Top]
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)
[Top]
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)
[Top]
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) *
[Top]
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)
[Top]
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)
[Top]
Scalability.RAC - Real Application Clusters
-
Script to Collect RAC Diagnostic Information (racdiag.sql)
(Note:135714.1)
[Top]
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)
[Top]
Storage.Exadata - Exadata issues
- Oracle Exadata Diagnostic Information required for Disk Failures (Note:761868.1)
[Top]
* - 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
ORA-4031; ORA-28112; ORA-1031; ORA-4030 
Back to top
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;
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
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 ServerORACLE SERVER X5-2 |
Exadata Storage Server X5-2L ServersORACLE SERVER X5-2L |
| Oracle Exadata Database Machine X5-8 | Oracle Server X5-8 ServerORACLE SERVER X5-8 |
Exadata Storage Server X5-2L ServersORACLE SERVER X5-2L |
| Oracle Exadata Database Machine X4-2 | Sun Server X4-2 Oracle Database ServerSUN SERVER X4-2 |
Exadata Storage Server X4-2L ServersSUN SERVER X4-2L |
| Oracle Exadata Database Machine X3-2 | Sun Server X3-2 Oracle Database ServerSUN FIRE X4170 M3 |
Exadata Storage Server X3-2 ServersSUN FIRE X4270 M3 |
| Oracle Exadata Database Machine X2-2 | Sun Fire X4170 M2 Oracle Database ServerSUN FIRE X4170 M2 SERVER |
Exadata Storage Server with Sun Fire X4270 M2 ServersSUN FIRE X4270 M2 SERVER |
| Oracle Exadata Database Machine X4-8 Full Rack | Sun Server X4-8 Oracle Database ServerSUN SERVER X4-8 |
Exadata Storage Server X4-2L ServersSUN SERVER X4-2L |
| Oracle Exadata Database Machine X3-8 Full Rack | Sun Server X2-8 Oracle Database ServerSun Fire X4800 M2 |
Exadata Storage Server X3-2 ServersSUN FIRE X4270 M3 |
| Oracle Exadata Database Machine X2-8 Full Rack | Sun Fire X4800 Oracle Database Server or Sun Server X2-8 Oracle Database ServerSun Fire X4800 or Sun Fire X4800 M2 |
Exadata Storage Server with Sun Fire X4270 M2 ServersSUN FIRE X4270 M2 SERVER |
| Oracle Exadata Database Machine | Sun Fire X4170 Oracle Database ServerSUN FIRE X4170 SERVER |
Exadata Storage Server with Sun Fire X4275 ServersSUN 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
Tuesday, May 3, 2016
Oracle database size sql query
How to find an oracle database size allocated and how much is occupied by the actual data..
my own query ..:) not copied from any where... :)
set serveroutput on
set linesize 99
col DATABASE_NAME format a20
select * from
(SELECT TO_CHAR (SYSDATE, 'MM-DD-YYYY HH24:MI:SS') "NOW" FROM DUAL)m ,
(select name "DATABASE_NAME" from v$database) f,
(select a.data_size+b.temp_size+c.redo_size "ALLOCATED_SIZE_IN_GB"
from ( select sum(bytes) / 1024 / 1024 / 1024 data_size
from dba_data_files ) a,
( select nvl(sum(bytes) / 1024 / 1024 / 1024,0) temp_size
from dba_temp_files ) b,
( select sum(bytes) / 1024 / 1024 / 1024 redo_size
from sys.v$log ) c ) t,
(select sum(bytes) /1024 / 1024 /1024 "ACTUAL_DATA_OCCUPIED_IN_GB" from dba_segments ) z;
Output would be similar as below ...
NOW DATABASE_NAME ALLOCATED_SIZE_IN_GB ACTUAL_DATA_OCCUPIED_IN_GB
------------------- -------------------- -------------------- --------------------------
05-03-2016 15:18:39 RDTEST 60.9462891 2.46868896
my own query ..:) not copied from any where... :)
set serveroutput on
set linesize 99
col DATABASE_NAME format a20
select * from
(SELECT TO_CHAR (SYSDATE, 'MM-DD-YYYY HH24:MI:SS') "NOW" FROM DUAL)m ,
(select name "DATABASE_NAME" from v$database) f,
(select a.data_size+b.temp_size+c.redo_size "ALLOCATED_SIZE_IN_GB"
from ( select sum(bytes) / 1024 / 1024 / 1024 data_size
from dba_data_files ) a,
( select nvl(sum(bytes) / 1024 / 1024 / 1024,0) temp_size
from dba_temp_files ) b,
( select sum(bytes) / 1024 / 1024 / 1024 redo_size
from sys.v$log ) c ) t,
(select sum(bytes) /1024 / 1024 /1024 "ACTUAL_DATA_OCCUPIED_IN_GB" from dba_segments ) z;
Output would be similar as below ...
NOW DATABASE_NAME ALLOCATED_SIZE_IN_GB ACTUAL_DATA_OCCUPIED_IN_GB
------------------- -------------------- -------------------- --------------------------
05-03-2016 15:18:39 RDTEST 60.9462891 2.46868896
Thanks
Rajesh R D
Subscribe to:
Posts (Atom)