Oracle can show us the reads and writes by datafile name. This is done by stats kept ini v$filestat
set lines 200 set pages 200 select v$datafile.name "File Name", v$filestat.phyrds "Reads", v$filestat.phywrts "Writes" from v$filestat,v$datafile where v$filestat.file# = v$datafile.file# ;
The Oracle redo log is part the Oracle database. When an Oracle transaction is committed, the transaction's is recorded in the redo log buffer than written to a redo log file.
set lines 200 set pages 200 select * from v$log;
Create Script to count rows in each table.
The Script Looks as follows:
set lines 200 set pages 200 select 'Select count(*) from ' ||owner|| '.' ||table_name|| ';' from dba_all_tables order by owner, table_name;
This will produce a script that looks like:
Select count(*) from XDBPM.REVISED_CHOICE_MODEL; Select count(*) from XDBPM.REVISED_COLL_TYPES; Select count(*) from XDBPM.REVISED_TYPES; Select count(*) from XDBPM.REVISED_TYPE_ATTRS; Select count(*) from XDBPM.REVISED_TYPE_SUMMARY; Select count(*) from XDBPM.SQLLDR_STAGING_TARGET; Select count(*) from XDBPM.STORAGE_MODEL_CACHE; Select count(*) from XDBPM.TYPE_SUMMARY; Select count(*) from XDBPM.XDBPM_INDEX_DDL_CACHE;
Show all the active Oracle users in the system. Query the v$session table to see the username and machine user is on.
set lines 200 set pages 200 select sid, serial#,user#, Username, machine, program, server, status, command, type from v$session order by username;
Display All Oracle Tables in the database. Shows all tables(less the system tables. Change for your databases. With where statement like "where owner in 'YOUROWNERNAME'"
set lines 200
set pages 200
select owner, table_name, table_type, tablespace_name
from dba_all_tables
where owner not in ('SYSTEM','DBSNMP','ORDSYS','OUTLN','SYS')
order by owner, table_name, tablespace_name
Display All Oracle Indexes in the database. Shows all indexes. Customize for your database.
set lines 200
set pages 200
Select owner, index_name, table_type, tablespace_name
From dba_indexes
where owner not in ('SYSTEM','DBSNMP','ORDSYS','OUTLN','SYS')
order by owner, index_name, tablespace_name
;
How to Convert/Upgrade an Oracle Database from Oracle Standard to Oracle Enterprise Edition
The following steps should be done to convert your Oracle Standard Edition database to Oracle Enterprise Edition:
That is pretty much it Oracle Enterprise Edition and ready to pay thoose yearly mainteance fees!
One can can check to see if Oracle is in archive log mode or has logging turned on. This means that the database is able to recover forward or backward in time. Sometimes referred to as point in time recovery
Oracle Archive Log Mode Status or Status of Archive Log Subsystem
ARCHIVE LOG LIST;
Check the Oracle log mode of the databases
SELECT name, log_mode FROM v$database;
Check the Oracle Archive mode in an instance
SELECT archiver FROM v$instance;
Select All Oracle Users Active In The System
select sid, serial#,user#, Username, machine, program, server, status, command, type from v$session order by username;
What version of Oracle is running?
select * from V$VERSION;
Another Option is to use the product table
set lines 200 column product format a50 column version format a15 column status format a20 SELECT * FROM product_component_version ;
These are examples of running the SELECT * FROM V$VERSION; from different versions Oracle would be
The resultant banner will show the current Oracle Database Edition if the Enterprise Edition is installed.
SQL> select * from v$version; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod PL/SQL Release 10.2.0.1.0 - Production CORE 10.2.0.1.0 Production TNS for 32-bit Windows: Version 10.2.0.1.0 - Production NLSRTL Version 10.2.0.1.0 - Production
The resultant banner will show the current Oracle Database Edition if the Standard Edition is installed.
SQL> select * from v$version; BANNER ---------------------------------------------------------------- Oracle Database 10g Release 10.2.0.1.0 - Production PL/SQL Release 10.2.0.1.0 - Production CORE 10.2.0.1.0 Production TNS for 32-bit Windows: Version 10.2.0.1.0 - Production NLSRTL Version 10.2.0.1.0 - Production
The resultant banner will show the current Oracle Database Edition if the Personal Edition is installed.
SQL> SELECT * FROM V$VERSION; BANNER ---------------------------------------------------------------- Personal Oracle Database 10g Release 10.2.0.1.0 - Production PL/SQL Release 10.2.0.1.0 - Production CORE 10.2.0.1.0 Production TNS for 32-bit Windows: Version 10.2.0.1.0 - Production NLSRTL Version 10.2.0.1.0 - Production
Recent Comments