19 Jul 2013

DBA Commands



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;

Posted January 24th, 2013 by admin & filed under Oracle Administration, Oracle Database Structures.
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;

Posted January 22nd, 2013 by admin & filed under Oracle Administration, Oracle Database Structures.
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: 
  1. Backup the database
  2. UnInstall or De-install the Oracle Standard Edition software 
  3. Install the Oracle Enterprise Edition software  
  4. If you have an existing database, point your ORACLE_SID to this pre-existing database
  5. Startup the database
  6. 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;
/

No comments:

Post a Comment