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.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:
- Backup the database
- UnInstall or De-install the Oracle Standard Edition software
- Install the Oracle Enterprise Edition software
- If you have an existing database, point your ORACLE_SID to this pre-existing database
- Startup the database
- Run the "catalog.sql" and "catproc.sql" scripts (This make sures all objects for Oracle Enterprise Edition are up to date)
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 ;
SELECT * FROM V$VERSION; to
get the different versions Oracle.
Show
all Oracle Datafiles in a Tablespace dba_data_files
SQL
query to show all the Oracle Datafiles that are in a Tablespace:
set
lines 200
column
file_name format a69
select
file_name,tablespace_name,bytes / (1024 * 1024) "Size
(MB)",autoextensible, maxbytes,increment_by
from
dba_data_files
where
tablespace_name like '&tbs' order by file_name ;
Show
all the active Oracle Users on the system.
select
sid, serial#,user#, Username, machine, program, server, status, command, type
from v$session order by username;
How
does one drop an Oracle Table without putting it in the recycle bin.
drop
<table Name> purge;
or
drop and object
drop
<object_type> <object_name> purge;
Show
Contents Of The Recycle Bin
The
following command will show the contents of the Oracle recycle bin. This like the windows recycle bin where items
can be recovered from.
show
recyclebin;
Tags:
recycle bin recyclebin
Empty
Oracle Recycle Bin
Posted
December 13th, 2012 by admin & filed under Oracle Administration.
How
does one purge, empty, clean out the Oracle Recyclebin or Recycle Bin?
A
new feature in Oracle 10g Release 2 was the Reycycle Bin. The recycle bin in
the Oracle database is where all dropped objects reside. Underneath the covers
the object are taking up the same space when they were created.
"DROP TABLE mytable", it doesn't
really drop it. It instead renames it to e.g.: BIN$67815682942.
Dropped
objects are not deleted they are just rename with a prefix of BIN$$. One can
get access to the data in a dropeed table or even use a Flashback Query if you
have this feature enabled on your version of Oracle.
To
completely remove a table from the system and not keep in recycle been use
PURGE TABLE command. So if table is called mytable.
The
syntax would be PURGE TABLE mytable;
Other
ways to purge the recycle bin are:
PURGE TABLE mytable; (purges mytable from
system and recycle bin)
PURGE INDEX myindex; (purges myindex from
system and recycle bin ) PURGE recyclebin;
Purge
all object in recyclebin PURGE dba_recyclebin; (purge all objects / only sysdba
can do this command)
To
drop and purge a table in one command it would be: DROP TABLE mytable PURGE; So
thats It.
Killing
Oracle Sessions
Finding
a Oracle session to kill starts with First finding the offending Query. Than
kill it with the alter system kill command.
SET
LINESIZE 100
COLUMN
spid FORMAT A10
COLUMN
username FORMAT A10
COLUMN
program FORMAT A45
SELECT
s.inst_id, s.sid, s.serial#, p.spid, s.username, s.program
FROM
gv$session s JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE
s.type != 'BACKGROUND';
INST_ID
SID SERIAL# SPID USERNAME PROGRAM
----------
---------- ---------- ---------- ----------
---------------------------------------------
1
69 99 4879 TEST billbprog@my.telly
ALTER
SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE; or ALTER SYSTEM KILL SESSION
'69,99' IMMEDIATE;
Security
Grants
grant
select on PERSON_TABLE to public with grant option;
select
* from dba_tab_privs where TABLE_NAME = 'PERSON_TABLE'
select
* from dba_role_privs where granted_role = 'PORTMAN_TABLE'
Resizing
A Data File
alter
database datafile '/u04/oradata/wpk/temp01.dbf' resize 500m;
Show
All Product Information
select
* from product_component_version;
Show
Row Counts For All Tables That Have ANALYZE On
select
owner table_name, num_rows from dba_tables where num_rows > 0;
Select
All Users Active In The System
select
sid, serial#,user#, Username, machine, program, server, status, command, type
from v$session order by username;
Show
What A Current User Is Doing
select
sid, serial#, status, server from v$session where username = 'BROWNBH';
Create
Count For All Tables
select
'Select count(*) from ' ||owner|| '.' ||table_name|| ';' from dba_all_tables
order by owner, table_name;
Show
All Indexes
select
owner, index_name, table_type, tablespace_name from dba_indexes where owner <>'SYSTEM' and owner <>
'DBSNMP' and owner <> 'ORDSYS' and owner <> 'OUTLN' and owner
<> 'SYS' and owner <> 'SYSTEM' order by owner, index_name,
tablespace_name
Show
All Tables
select
owner, table_name, table_type, tablespace_name from dba_all_tables where owner
<>'SYSTEM' and owner <> 'DBSNMP' and owner <> 'ORDSYS' and
owner <> 'OUTLN' and owner <> 'SYS' and owner <> 'SYSTEM'
order by owner, table_name, tablespace_name
Show
Space Used
select
Tablespace_Name, /*Tablespace name*/ Owner, /*Owner of the segment*/
Segment_Name, /*Name of the segment*/ Segment_Type, /*Type of segment (ex.
TABLE, INDEX)*/ Extents, /*Number of extents in the segment*/ Blocks, /*Number
of db blocks in the segment*/ Bytes /*Number of bytes in the segment*/ from
DBA_SEGMENTS where owner <>'SYSTEM' and owner <> 'DBSNMP' and owner
<> 'ORDSYS' and owner <> 'OUTLN' and owner <> 'SYS' and owner
<> 'SYSTEM'
Sum
Space By Owner
select
owner, sum(blocks) Totalblocks, sum(bytes)TotalBytes from DBA_SEGMENTS group by
owner
Sum
Space by Tablespace
select
tablespace_name, sum(blocks) Totalblocks, sum(bytes)TotalBytes from
DBA_SEGMENTS group by tablespace_name
Show
Reads And Writes By File Name In Oracle DB
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#
Show
Versions Of Software
select
* from V$VERSION
Identify
Segments That Are Getting Close To Their Max-Extent Values
select
owner,tablespace_name,segment_name,bytes,extents,max_extents from dba_segments
where extents*2 > max_extents
Identifies
Segments That Are Getting Close To Running Out Of Contiguous Free Space
select
owner, s.tablespace_name, segment_name, s.bytes, next_extent, max(f.bytes)
largest from dba_segments s, dba_free_space f where s.tablespace_name =
f.tablespace_name(+) group by owner, s.tablespace_name, segment_name, s.bytes,
next_extent having next_extent*2 >max(f.bytes)
Displays
Archived Redo Log Information
select
* from v$database
Display
Count Historical Archived Log Information From The Control File
select
count(*) from v$archived_log
select
min(completion_time) from v$archived_log
Shows
Current Archive Destinations
select
* from v$archive_dest
Backups
Of Archived Logs
select
count(*) from v$backup_redolog
Display
All Online Redo Log Groups For The database
select
* from v$log
Show
All Datafiles For Tablespace And Oracle Stuff
select
* from dba_data_files order by tablespace_name, file_name
DELETE
THE DUPLICATE VALUES FROM THE TABLES.
DELETE
FROM emp
WHERE
ROWID NOT IN (SELECT MIN(ROWID) FROM emp GROUP BY id);
Block
developers from using TOAD and other tools on production databases
Today,
I’ve seen a nice example at www.psoug.org on how to prevent users from using
additional tools to connect to production database. This is the AFTER LOGON
trigger create ON DATABASE as follows:
CONNECT
/ AS SYSDBA;
CREATE
OR REPLACE TRIGGER block_tools_from_prod
AFTER LOGON ON DATABASE
DECLARE
v_prog sys.v_$session.program%TYPE;
BEGIN
SELECT program INTO v_prog
FROM sys.v_$session
WHERE
audsid = USERENV('SESSIONID')
AND
audsid != 0 -- Don't Check SYS
Connections
AND
ROWNUM = 1; -- Parallel processes
will have the same AUDSID's
IF UPPER(v_prog) LIKE '%TOAD%' OR
UPPER(v_prog) LIKE '%T.O.A.D%' OR -- Toad
UPPER(v_prog) LIKE '%SQLNAV%' OR -- SQL Navigator
UPPER(v_prog) LIKE '%PLSQLDEV%' OR --
PLSQL Developer
UPPER(v_prog) LIKE '%BUSOBJ%' OR
-- Business Objects
UPPER(v_prog) LIKE '%EXCEL%' -- MS-Excel plug-in
THEN
RAISE_APPLICATION_ERROR(-20000,
'Development tools are not allowed here.');
END IF;
END;
/
