More on Oracle Scripts

(We marked visited pages blue)
  • View unified audit report - oracle database script
  • View privileges granted to an user - oracle database script
  • View dataguard message or errors - oracle database script
  • View container db information - oracle database script
  • View all scheduler schedules - oracle database script
  • View acl information - oracle database script
  • User creation details in user$ table - oracle database script
  • Unified audit policies present in db - oracle database script
  • Top index sizes of table/schema - oracle database script
  • Temp tablespace details in multitenant - oracle database script
  • Target list monitored by oem - oracle database script
  • Tablespace quota for a user - oracle database script
  • Table_exists_action option with impdp - oracle database script
  • Stop/start oms in cloud control - oracle database script
  • Stop/start mrp process in standby - oracle database script
  • Stop/start agent in oem cloud control - oracle database script
  • Stop and start pluggable db - oracle database script
  • Status of pdbs in multitenant - oracle database script
  • Statements audited in oracle
  • Sqlfile option with impdp - oracle database script
  • Space usage by lob column - oracle database script
  • Show history of pdbs - oracle database script
  • Set local_listener in db - oracle database script
  • Services associated with pdbs - oracle database script
  • Scheduler shell script in dbms_scheduler - oracle database script
  • Scheduler job detail in cdb - oracle database script
  • Rename/move a datafile - oracle database script
  • Rename tablespace in oracle db
  • Privileges audited in database - oracle database script
  • Plugins installed on oms server - oracle database script
  • Occupants usage in sysaux tablespace - oracle database script
  • Object with mix or lowercase name - oracle database script
  • Move lob segment to another tablespace - oracle database script
  • Move aud$ table to new tablespace - oracle database script
  • Monitor standby background process - oracle database script
  • Monitor scheduler jobs - oracle database script
  • Monitor recovery progress in standby db - oracle database script
  • Monitor lag in standby including rac - oracle database script
  • Modify scan listener port - oracle database script
  • Managing columns of table - oracle database script
  • Manage listener in oracle
  • Manage dbms_schedulerjobs - oracle database script
  • Manage acls in oracle
  • Make index invisible - oracle database script
  • Log information for all scheduler jobs - oracle database script
  • Last log applied/received - oracle database script
  • Is the database is a multitenant or not - oracle database script
  • Index rebuild in oracle
  • History of all scheduler job runs - oracle database script
  • Grant table/column privilege to user - oracle database script
  • Get standby redo log info - oracle database script
  • Get oms/agent url details - oracle database script
  • Get oms repository details - oracle database script
  • Get ddl of a scheduler job - oracle database script
  • Find tables with lob seg in db - oracle database script
  • Find nested tables in db - oracle database script
  • Find dependents of an object - oracle database script
  • Find column usage statistics - oracle database script
  • Find chained rows in table - oracle database script
  • Find active services in db - oracle database script
  • Expdp/impdp with parallel option - oracle database script
  • Expdp/impdp for tables - oracle database script
  • Expdp/impdp for schemas - oracle database script
  • Expdp with query clause - oracle database script
  • Expdp with compression parameter - oracle database script
  • Expdp to multiple directories - oracle database script
  • Expdp to asm diskgroup - oracle database script
  • Exclude/include option in expdp - oracle database script
  • Estimate space require for index creation - oracle database script
  • Enable/disable/drop a dbms_job - oracle database script
  • Enable/disable triggers of a schema - oracle database script
  • Enable/disable em express 12c - oracle database script
  • Enable tracing for a listener - oracle database script
  • Enable pure unified auditing 12c - oracle database script
  • Enable auditing in database - oracle database script
  • Enable auditing for datapump jobs - oracle database script
  • Enable audit for sys operations - oracle database script
  • Drop tablespace in oracle db
  • Drop a schedule - oracle database script
  • Drop a pluggable database - oracle database script
  • Definition of job in dbms_jobs - oracle database script
  • Default users in oracle 12c
  • Currently connected pdb name - oracle database script
  • Create/drop synonyms - oracle database script
  • Create/drop database link - oracle database script
  • Create/drop database link - oracle database script
  • Create user in oracle
  • Create unified audit policy - oracle database script
  • Create tablespace in oracle db
  • Create static listener for oracle db
  • Create db link w/o modifying tnsnames.ora - oracle database script
  • Create and scheduler a scheduler job - oracle database script
  • Create /alter profile in database - oracle database script
  • Copy scheduler job from one user to other - oracle database script
  • Connect to user without knowing password - oracle database script
  • Compile invalid objects - oracle database script
  • Common user/role in cdb - oracle database script
  • Cluster parameter in rac - oracle database script
  • Checkpoint time of datafiles - oracle database script
  • Check undo mode in multitenant db (oracle 12.2)
  • Check the syntax of rman commands - oracle database script
  • Check encryption wallet status - oracle database script
  • Check db role(primary/standby) - oracle database script
  • Change sysman pwd in oem cloud - oracle database script
  • Change default tablespace of user - oracle database script
  • Audit records of an user - oracle database script
  • Alter an user - oracle database script
  • All scheduler windows - oracle database script
  • Add/drop/alter datafile - oracle database script
  • Add/drop tempfile - oracle database script
  • Xplain plan of sql_id from cursor - oracle database script
  • Xplain plan of sql_id from awr - oracle database script
  • Xplain plan of a sql statement - oracle database script
  • Xplain plan of a sql baseline - oracle database script
  • View/modify stats retention - oracle database script
  • View/modify awr retention - oracle database script
  • View timezone info in db - oracle database script
  • View hidden parameter setting - oracle database script
  • Utilization of current redo log ( in % ) - oracle database script
  • Upgrade statistics in db - oracle database script
  • Truncate partitions - oracle database script
  • Tracing all session of a user - oracle database script
  • Top sql queries using literal values - oracle database script
  • Top running queries from ash - oracle database script
  • Top query with high elapsed time - oracle database script
  • Top cpu consuming sessions - oracle database script
  • Table statistics history - oracle database script
  • Table not having index on fk column - oracle database script
  • Stop/start cluster in rac standalone. - oracle database script
  • Stop/start a service - oracle database script
  • Stop and start instance using srvctl - oracle database script
  • Stop and start db using srvctl - oracle database script
  • Stop and start crs - oracle database script
  • Sqls doing full table scan - oracle database script
  • Sql tuning advisor for sql_id from cursor - oracle database script
  • Spool sql query output to html - oracle database script
  • Split partition online(12cr2 only) - oracle database script
  • Space used to store stats - oracle database script
  • Set env variables using srvctl - oracle database script
  • Sessions holding library cache lock - oracle database script
  • Sessions accessing an object - oracle database script
  • Session login history from ash - oracle database script
  • Segments with high physical read - oracle database script
  • Script to monitor temp tablespace usage - oracle database script
  • Script to monitor tablespace usage - oracle database script
  • Script to find active sessions in database - oracle database script
  • Scn to timestamp and viceversa - oracle database script
  • Run shared pool advisory - oracle database script
  • Run sga target advisory - oracle database script
  • Rman tablespace backup run block - oracle database script
  • Rman incremental db backup run block script - oracle database script
  • Rman full database backup script - oracle database script
  • Rman datafile(s) backup run block - oracle database script
  • Restore archivelog from rman tape - oracle database script
  • Rename a partition - oracle database script
  • Relocate a service - oracle database script
  • Recover dropped table with rman 12c - oracle database script
  • Recover a dropped table - oracle database script
  • Queries causing high physical read - oracle database script
  • Purge recyclebin in database - oracle database script
  • Purge old awr snapshots - oracle database script
  • Publish pending stats - oracle database script
  • Pga usage by sessions - oracle database script
  • Oracle license usage info
  • Oracle db is 32bit or 64 bit?
  • Open database link information - oracle database script
  • Objects locked by library cache - oracle database script
  • Objects causing latch contention - oracle database script
  • Objects causing flushing of shared pool - oracle database script
  • Non-partitioned to partitioned online(12cr2 only) - oracle database script
  • Mutex sleep in database - oracle database script
  • Move voting disk to new diskgroup - oracle database script
  • Move partition to new tablespace - oracle database script
  • Mount/dismount asm diskgroups - oracle database script
  • Monitor undo tablespace usage - oracle database script
  • Monitor rollback transactions - oracle database script
  • Monitor rman backup progress - oracle database script
  • Monitor parallel queries - oracle database script
  • Monitor index usage - oracle database script
  • Monitor asm diskgroup i/o - oracle database script
  • Monitor asm disk rebalance - oracle database script
  • Modify moving window baseline size - oracle database script
  • Modify asm user password - oracle database script
  • Merge partition - oracle database script
  • Manual backup of ocr and list backups - oracle database script
  • Manage mgmtdb in 12c rac - oracle database script
  • Make a partition ready only(12cr2) - oracle database script
  • Lock/unlock statistics - oracle database script
  • List flashback restore points - oracle database script
  • Latch type and sql hash value - oracle database script
  • Kill snipped session in db - oracle database script
  • Kill all sessions of a sql_id - oracle database script
  • Kill all session of a user - oracle database script
  • I/o usage of each tempfile - oracle database script
  • Installed rdbms components - oracle database script
  • How to change asm sys password - oracle database script
  • How far we can flashback - oracle database script
  • Get statistics preference setting - oracle database script
  • Get sql_text from sid - oracle database script
  • Get sql_profile of a sql_id - oracle database script
  • Get size of the database - oracle database script
  • Get sid from os pid - oracle database script
  • Get row_count of partitions of a table - oracle database script
  • Get redo log member info - oracle database script
  • Get parallel query detail - oracle database script
  • Get os pid from sid - oracle database script
  • Get olr info in rac - oracle database script
  • Get node info using olsnodes - oracle database script
  • Get interface info in rac - oracle database script
  • Get installed sqlpatches in db - oracle database script
  • Get disktimeout values - oracle database script
  • Get ddl of privileges granted to user - oracle database script
  • Get ddl of all tablespaces - oracle database script
  • Get database uptime - oracle database script
  • Get database incarnation info - oracle database script
  • Get cpu memory info of db server - oracle database script
  • Get cluster_interconnect details - oracle database script
  • Get bind values of a sql_id - oracle database script
  • Get background process details - oracle database script
  • Get asm diskgroup details - oracle database script
  • Get asm disk info - oracle database script
  • Get alert log location in db - oracle database script
  • Get active sid of a pl/sql object - oracle database script
  • Get acl details in database - oracle database script
  • Ger row_count of all the tables of a schema - oracle database script
  • Generate resize datafile script - oracle database script
  • Generate multiple awr report - oracle database script
  • Generate addm report - oracle database script
  • Gather stats for schema - oracle database script
  • Gather stats for a table - oracle database script
  • Flush a sql query from cursor - oracle database script
  • Flashback query as of timestamp - oracle database script
  • Flashback db using restore point - oracle database script
  • Flashback area usage info - oracle database script
  • Flashback a table to point in time - oracle database script
  • Flashback a procedure/package - oracle database script
  • Find who locked your account - oracle database script
  • Find waitevents in database - oracle database script
  • Find top disk_reads by an user - oracle database script
  • Find the temp usage of sessions - oracle database script
  • Find the table partition keys - oracle database script
  • Find the locked objects - oracle database script
  • Find the grid version - oracle database script
  • Find the cluster name in rac - oracle database script
  • Find sql baseline info from sql_id - oracle database script
  • Find sessions generating undo - oracle database script
  • Find sessions generating alot of redo - oracle database script
  • Find queries triggered from a procedure - oracle database script
  • Find optimal undo retention size - oracle database script
  • Find ocr and vd location - oracle database script
  • Find long running operations - oracle database script
  • Find locks present in database - oracle database script
  • Find duplicate rows in table - oracle database script
  • Find current running sqls - oracle database script
  • Find column usage statistics - oracle database script
  • Find buffer cache usage - oracle database script
  • Find blocking sessions - oracle database script
  • Find blocking sessions from ash - oracle database script
  • Find active transactions in db - oracle database script
  • Export import statistics - oracle database script
  • Execution detail of a sql_id in cursor - oracle database script
  • Execute runcluvfy.sh for rac precheck - oracle database script
  • Enable/disable db/instance using srvctl - oracle database script
  • Enable/disable autorestart of crs - oracle database script
  • Enable tracing for asmcmd - oracle database script
  • Enable tracing for a listener - oracle database script
  • Enable trace for srvctl commands - oracle database script
  • Enable trace for rman - oracle database script
  • Enable trace for a sql_id - oracle database script
  • Enable trace for a session - oracle database script
  • Enable incremental stats collection - oracle database script
  • Enable flashback for database - oracle database script
  • Enable block change tracking - oracle database script
  • Enable archivelog mode in standalone db - oracle database script
  • Dropping partition 11g/12c - oracle database script
  • Drop asm diskgroup - oracle database script
  • Drop an asm disk - oracle database script
  • Drop a sql profile - oracle database script
  • Drop a sql baseline - oracle database script
  • Disable/enable sql profile - oracle database script
  • Disable/enable all triggers of schema - oracle database script
  • Dictionary cache hit ratio - oracle database script
  • Delete statistics - oracle database script
  • Delete archive older than 1 day - oracle database script
  • Db optimizer processing rate - oracle database script
  • Database growth per month - oracle database script
  • Current sga usage - oracle database script
  • Create/drop flashback restore point - oracle database script
  • Create sql baseline from cursor cache - oracle database script
  • Create password file in asm dg - oracle database script
  • Create baselines for all sqls of a schema - oracle database script
  • Create asm disk in Linux using oracleasm
  • Copy asm file to remote asm instance - oracle database script
  • Copy archive from asm to mount point - oracle database script
  • Clock synchronization status in rac - oracle database script
  • Cleanup orphaned datapump jobs - oracle database script
  • Check stale stats - oracle database script
  • Check open cursors - oracle database script
  • Check cluster component status - oracle database script
  • Characterset info of database - oracle database script
  • Change asm rebalance power - oracle database script
  • Buffer cache hit ratio - oracle database script
  • Backup archivelogs using rman - oracle database script
  • Backup archive between 2 sequence number - oracle database script
  • Archive generation per hour - oracle database script
  • Alter/disable a sql plan baseline - oracle database script
  • Add/remove instance using srvctl - oracle database script
  • Add/remove db using srvctl - oracle database script
  • Add/remove a service - oracle database script
  • Adding partitions 11g/12c - oracle database script
  • 10053 optimizer trace - oracle database script
  • Script to alter all users passwords in Oracle
  • Script to list if tablespace is in backup mode in Oracle
  • Script to list all your tables with LOB, LONG and LONG RAW data types in ORacle
  • Script to list Oracle tablespace usage
  • Script to list hidden parameters in Oracle
  • Script to find schema size in Oracle
  • Script to Extract Role granted Privileges in Oracle
  • Script to check for default password in Oracle
  • Oracle Script to check what standby logs are being used
  • Script to find last RMAN backup executed in Oracle
  • Script to Estimate Oracle RMAN backup
  • Oracle Script to list RMAN backup work done so far.
  • Oracle Script to list space usage per data file
  • Oracle Script to create dynamic kill command
  • Oracle Script to Check for Data dictionary corruption
  • Script Drop all temp datafiles
  • List top SQL statements in Oracle
  • Find Top Disk reads for a User in Oracle
  • Find Top Disk Reads in Oracle
  • Uptime Script
  • Script to List all Datafiles and Tablespaces
  • Script to find Database size in Oracle
  • Oracle Script to unload/load a table
  • Oracle Script to Clone User
  • Oracle Full Secutiry Database Audit
  • Object Dependency Report
  • Tablespace info in multitenant - oracle database script

    Tablespace info in Multitenant - Oracle Database Script.

    SET LINES 132 PAGES 100 COL con_name FORM A15 HEAD "Container|Name" COL tablespace_name FORM A15 COL fsm FORM 999,
                                                                                                                  999,
                                                                                                                  999,
                                                                                                                  999 HEAD "Free|Space Meg." COL apm FORM 999,
                                                                                                                                                          999,
                                                                                                                                                          999,
                                                                                                                                                          999 HEAD "Alloc|Space Meg." -- COMPUTE SUM OF fsm apm ON REPORT BREAK ON REPORT ON con_id ON con_name ON tablespace_name -- WITH x AS (SELECT c1.con_id, cf1.tablespace_name, SUM(cf1.bytes)/1024/1024 fsm  FROM cdb_free_space cf1  ,v$containers c1  WHERE cf1.con_id = c1.con_id  GROUP BY c1.con_id, cf1.tablespace_name),  y AS (SELECT c2.con_id, cd.tablespace_name, SUM(cd.bytes)/1024/1024 apm  FROM cdb_data_files cd  ,v$containers c2  WHERE cd.con_id = c2.con_id  GROUP BY c2.con_id  ,cd.tablespace_name) SELECT x.con_id, v.name con_name, x.tablespace_name, x.fsm, y.apm FROM x, y, v$containers v WHERE x.con_id          = y.con_id AND   x.tablespace_name = y.tablespace_name AND   v.con_id          = y.con_id UNION SELECT vc2.con_id, vc2.name, tf.tablespace_name, null, SUM(tf.bytes)/1024/1024 FROM v$containers vc2, cdb_temp_files tf WHERE vc2.con_id = tf.con_id GROUP BY vc2.con_id, vc2.name, tf.tablespace_name ORDER BY 1, 2;


    Leave a comment or a question in the comment area bellow if you fell like


    Be a sport and

    Search

    Loading... Please wait

    Subscribe to our Newsletter

    Be a sport and

    x