One can create and modify Oracle databases using the Oracle "dbca" (Database Configuration Assistant) utility. The dbca utility is located in the $ORACLE_HOME/bin directory. The Oracle Universal Installer (oui) normally starts it after installing the database server software.

One can also create databases manually using scripts. This option, however, is falling out of fashion as it is quite involved and error prone. Look at this example for creating and Oracle 9i database:

	CONNECT SYS AS SYSDBA
	ALTER SYSTEM SET DB_CREATE_FILE_DEST='/u01/oradata/';
	ALTER SYSTEM SET DB_CREATE_ONLINE_LOG_DEST_1='/u02/oradata/';
	ALTER SYSTEM SET DB_CREATE_ONLINE_LOG_DEST_2='/u03/oradata/';
	CREATE DATABASE;

What database block size should I use?

Oracle recommends that your database block size match, or be multiples of your operating system block size. One can use smaller block sizes, but the performance cost is significant. Your choice should depend on the type of application you are running. If you have many small transactions as with OLTP, use a smaller block size. With fewer but larger transactions, as with a DSS application, use a larger block size.

If you are using a volume manager, consider your "operating system block size" to be 8K. This is because volume manager products use 8K blocks (and this is not configurable).

How does one coalesce free space?

SMON coalesces free space (extents) into larger, contiguous extents every 2 hours and even then, only for a short period of time.

SMON will not coalesce free space if a tablespace's default storage parameter "pctincrease" is set to 0. With Oracle 7.3 one can manually coalesce a tablespace using the ALTER TABLESPACE ... COALESCE; command, until then use:

SQL> alter session set events 'immediate trace name coalesce level n';

where 'n' is the tablespace number you get from SELECT TS#, NAME FROM SYS.TS$;

You can get status information about this process by selecting from the SYS.DBA_FREE_SPACE_COALESCED dictionary view.

How does one prevent tablespace fragmentation?

Always set PCTINCREASE to 0 or 100.

Bizarre values for PCTINCREASE will contribute to fragmentation. For example if you set PCTINCREASE to 1 you will see that your extents are going to have weird and wacky sizes: 100K, 100K, 101K, 102K, etc. Such extents of bizarre size are rarely re-used in their entirety. PCTINCREASE of 0 or 100 gives you nice round extent sizes that can easily be reused. Eg. 100K, 100K, 200K, 400K, etc.

Where can one find the high water mark for a table?

There is no single system table which contains the high water mark (HWM) for a table. A table's HWM can be calculated using the results from the following SQL statements:

        SELECT BLOCKS
        FROM   DBA_SEGMENTS
        WHERE  OWNER=UPPER(owner) AND SEGMENT_NAME = UPPER(table);

        ANALYZE TABLE owner.table ESTIMATE STATISTICS;

        SELECT EMPTY_BLOCKS
        FROM   DBA_TABLES
        WHERE  OWNER=UPPER(owner) AND SEGMENT_NAME = UPPER(table);

Thus, the tables' HWM = (query result 1) - (query result 2) - 1

NOTE: You can also use the DBMS_SPACE package and calculate the HWM = TOTAL_BLOCKS - UNUSED_BLOCKS - 1.

How are extents allocated to a segment?

Oracle8 and above rounds off extents to a multiple of 5 blocks when more than 5 blocks are requested. If one requests 16K or 2 blocks (assuming a 8K block size), Oracle doesn't round it up to 5 blocks, but it allocates 2 blocks or 16K as requested. If one asks for 8 blocks, Oracle will round it up to 10 blocks.

Space allocation also depends upon the size of contiguous free space available. If one asks for 8 blocks and Oracle finds a contiguous free space that is exactly 8 blocks, it would give it you. If it is 9 blocks, Oracle would also give it to you. Clearly Oracle doesn't always round extents to a multiple of 5 blocks.

The exception to this rule is locally managed tablespaces. If a tablespace is created with local extent management and the extent size is 64K, then Oracle allocates 64K or 8 blocks assuming 8K block size. Oracle doesn't round it up to the multiple of 5 when a tablespace is locally managed.

Can one rename a database user (schema)?

No, this is listed as Enhancement Request 158508. Workaround:

Do a user-level export of user A
create new user B
import system/manager fromuser=A touser=B
drop user A

Can one rename a tablespace?

No, this is listed as Enhancement Request 148742. Workaround:

Export all of the objects from the tablespace
Drop the tablespace including contents
Recreate the tablespace
Import the objects

Can one resize tablespaces and data files?

One can manually increase or decrease the size of a datafile from Oracle 7.2 using the

	ALTER DATABASE DATAFILE 'filename2' RESIZE 100M;

command.

Because you can change the sizes of datafiles, you can add more space to your database without adding more datafiles. This is beneficial if you are concerned about reaching the maximum number of datafiles allowed in your database.

Manually reducing the sizes of datafiles allows you to reclaim unused space in the database. This is useful for correcting errors in estimations of space requirements.

Also, datafiles can be allowed to automatically extend if more space is required. Look at the following command:

        CREATE TABLESPACE pcs_data_ts
                DATAFILE 'c:\ora_apps\pcs\pcsdata1.dbf' SIZE 3M
                        AUTOEXTEND ON NEXT 1M  MAXSIZE UNLIMITED
                DEFAULT STORAGE (  INITIAL 10240
                                   NEXT 10240
                                   MINEXTENTS 1
                                   MAXEXTENTS UNLIMITED
                                   PCTINCREASE 0)
                ONLINE
                PERMANENT;

How does one create a standby database?

While your production database is running, take an (image copy) backup and restore it on duplicate hardware. Note that an export will not work!!!

On your standby database, issue the following commands:

	ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'file_name';

	ALTER DATABASE MOUNT STANDBY DATABASE;

	RECOVER STANDBY DATABASE;

On systems prior to Oracle 8i, write a job to copy archived redo log files from the primary database to the standby system, and apply the redo log files to the standby database (pipe it). Remember the database is recovering and will prompt you for the next log file to apply.

Oracle 8i onwards provide an "Automated Standby Database" feature which will send archived log files to the remote site via NET8, and apply then to the standby database.

When one needs to activate the standby database, stop the recovery process and activate it:

	ALTER DATABASE ACTIVATE STANDBY DATABASE;

How does one give developers access to trace files (required as input to tkprof)?

The "alter session set sql_trace=true" command generates trace files in USER_DUMP_DEST that can be used by developers as input to tkprof. On Unix the default file mask for these files are "rwx r-- ---".

There is an undocumented INIT.ORA parameter that will allow everyone to read (rwx r-- r--) these trace files:

	_trace_files_public = true

Include this in your INIT.ORA file and bounce your database for it to take effect.

Thanks to Kismat R Kathrani

How does one see the uptime for a database?

Look at the following SQL query:

	SELECT to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') "DB Startup Time"
	FROM   sys.v_$instance;

An alternative solution:

	SELECT to_char(logon_time,'Dy dd Mon HH24:MI:SS') "DB Startup Time"
	FROM   sys.v_$session
	WHERE  sid=1   /* this is pmon */
	/

Users still running on Oracle 7 can try one of the following queries:

	column STARTED format a18 head 'STARTUP TIME'
	select C.INSTANCE,
	       to_date(JUL.VALUE, 'J')
	       || to_char(floor(SEC.VALUE/3600),         '09'       )
	       || ':'
	       -- || substr (to_char(mod(SEC.VALUE/60, 60), '09'), 2, 2)
	       || substr (to_char(floor(mod(SEC.VALUE/60, 60)), '09'), 2, 2)
	       || '.'
	       || substr (to_char(mod(SEC.VALUE,    60), '09'), 2, 2)  STARTED
	  from SYS.V_$INSTANCE JUL,
	       SYS.V_$INSTANCE SEC,
	       SYS.V_$THREAD   C
	 where JUL.KEY like '%JULIAN%'
	   and SEC.KEY like '%SECOND%';

	select  to_date(JUL.VALUE, 'J')
	        || to_char(to_date(SEC.VALUE, 'SSSSS'), ' HH24:MI:SS') STARTED
	   from SYS.V_$INSTANCE JUL,
	        SYS.V_$INSTANCE SEC
	  where JUL.KEY like '%JULIAN%'
	    and SEC.KEY like '%SECOND%';

	select  to_char(to_date(JUL.VALUE, 'J') + (SEC.VALUE/86400), -- Return a DATE
                'DD-MON-YY HH24:MI:SS') STARTED
	   from V$INSTANCE JUL,
	        V$INSTANCE SEC
	  where JUL.KEY like '%JULIAN%'
	    and SEC.KEY like '%SECOND%';

How can one see who is using a temporary segment?

For every user using temporary space, there is an entry in SYS.V$_LOCK with type 'TS'.

All temporary segments are named 'ffff.bbbb' where 'ffff' is the file it is in and 'bbbb' is first block of the segment.

If your temporary tablespace is set to TEMPORARY, all sorts are done in one large temporary segment. For usage stats, see SYS.V_$SORT_SEGMENT

From Oracle 8.0, one can just query SYS.v$sort_usage. Look at these examples:

	select s.username, u."USER", u.tablespace, u.contents, u.extents, u.blocks
        from   sys.v_$session s, sys.v_$sort_usage u
        where  s.addr = u.session_addr
        /

        select s.osuser, s.process, s.username, s.serial#,
               sum(u.blocks)*vp.value/1024 sort_size
        from   sys.v_$session s, sys.v_$sort_usage u, sys.v_$parameter vp
        where  s.saddr = u.session_addr
          and  vp.name = 'db_block_size'
          and  s.osuser like '&1'
        group  by s.osuser, s.process, s.username, s.serial#, vp.value
        /

Force a redo log switch

/* 	
Sam  ORACLE D.B.A.

Purpose:Force a redo log switch		

*/

alter system switch logfile
/

Back to the List Of Scripts

Recreates your Redo Logs group if any logs are invalid

/* 	
Sam ORACLE D.B.A.

Purpose:Recreates your Redo Logs group if any logs are invalid.  		

*/


set linesize 120
set heading off
set veri off
set show off
set feed off
set echo off
set head off

spool temp.sql

select
'alter database drop logfile member '||''''||member||''''||';'
from v$logfile
where status ='INVALID'
and 'INACTIVE' = (select status from v$log where group# = v$logfile.group#);

select
'alter database add logfile member '||''''||member||''''||
' reuse to group '||to_char(group#)||';'
from v$logfile
where status ='INVALID'
and 'INACTIVE' = (select status from v$log where group# = v$logfile.group#);

select
'alter system switch logfile;'
from dual;

select
'alter database drop logfile member '||''''||member||''''||';'
from v$logfile
where status ='INVALID'
and 'CURRENT' = (select status from v$log where group# = v$logfile.group#);

select
'alter database add logfile member '||''''||member||''''||
' reuse to group '||to_char(group#)||';'
from v$logfile
where status ='INVALID'
and 'CURRENT' = (select status from v$log where group# = v$logfile.group#);

select
'alter system switch logfile;'
from dual;

select
'alter system switch logfile;'
from dual;

select
'alter system switch logfile;'
from dual;

spool off

set echo on

start see_logs.sql
start temp.sql
start see_logs.sql


spool off

set head on

Back to the List Of Scripts

Start Backup

svrmgrl internal/oracle <
Back to the List Of Scripts




Resets the Sequences of a complete schema to a given start value
/* Sam   ORACLE D.B.A..

Purpose:Resets the Sequences of a complete schema to a given start value		

*/

set heading off
set linesize 132

spool temp.sql

select 'drop sequence '||sequence_name||';'
from all_sequences
Where Sequence_Owner = '&&SchemaOwner'
/

Back  to the List Of Scripts


Lists SQL text for a given view
Start Backup
/* 	
Sam  ORACLE D.B.A.

Purpose:Lists SQL text for a given view		

*/
set long 50000

Col Text Word_Wrapped

Set Heading Off
Set Pagesize 0

SPOOL see_view_text

SELECT 
TEXT 
FROM ALL_VIEWS 
WHERE VIEW_NAME 	= '&ViewName'
And Owner 		= '&SchemaOwner'
/

SPOOL OFF

SET HEADING ON
SET PAGESIZE 55

Back to  the List Of Scripts



Quick view of all users and a count of open cursors
/*Sam ORACLE D.B.A.

Purpose:Quick view of all users	and a count of open cursors

*/


set linesize 80

Col Ctr Format 9,990

spool see_users

select
dba_users.username username,
decode(v$session.username, Null, 'Not Connected', 'Connected') Status,
Nvl(OsUser, '-') OSUser,
Nvl(Terminal,'-') Terminal,
Sum(decode(v$session.username, Null, 0,1)) Sessions
From
dba_users, v$session
where dba_users.username = v$session.username (+)
Group By 
dba_users.username, 
decode(v$session.username, Null, 'Not Connected', 'Connected'),
OsUser,
Terminal
Order by 1
/

spool off

Back to the List Of Scripts



See the number of objects and total spaced used  by username and tablespace
/* 	
Sam ORACLE D.B.A.

Purpose:See the number of objects and total spaced used  by username and tablespace.		
*/


col tablespace_name format a25 trunc head 'Tablespace|Name'
col owner format a25 trunc head 'Schema|Owner'
col kbyte format 999,999,990.00 head 'KiloBytes|Used'
col ctr head 'Object|Count'

break on owner skip 2 on report

compute sum of kbyte on report
compute sum of kbyte on owner

select 
owner, 
tablespace_name, 
count(*) ctr, 
sum(bytes)/1000 kbyte
from dba_segments
group by owner, tablespace_name
having count(*) > 0
order by 1,2,3 desc
/

Back to the List Of Scripts


:Lists the storage information for  locally managed tempfiles
/* 	
Sam
Purpose:Lists the storage information for  locally managed tempfiles		

*/

Select * From V$Temp_Extent_Map
/

Back to the List Of Scripts



Lists the Table current storage space for a given schema
/* 	
Sam   ORACLE  D.B.A.

Purpose:Lists the Table current storage space for a given schema		

*/

col segment_name format a30 trunc
col tablespace_name format a30 trunc
col bytes format 99,999,999,990

break on report
compute sum of bytes on report

spool see_index_space

select 
distinct 
segment_name, 
tablespace_name, 
sum(bytes) bytes
from dba_segments
where owner 	= '&SchemaName'
and segment_type 	= 'TABLE'
group by segment_name, tablespace_name
order by 1,2
/

spool off

clear breaks
clear computes

Back to the List Of Scripts



Lists the Max SGA sizes of your table data	
set linesize 80
set pagesize 999 

/* 	
Sam ORACLE D.B.A.

Purpose:Lists the Max SGA sizes of your table data		

*/

col table_name format a32 trunc
col keep_bytes format 99,999,999,990
col recycle_bytes format 99,999,999,990
col default_bytes format 99,999,999,990

break on report

compute sum of keep_bytes on report
compute sum of recycle_bytes on report
compute sum of default_bytes on report


spool seetabspace

select 
table_name, 
decode(buffer_pool,'KEEP',initial_extent, 0) Keep_Bytes,
decode(buffer_pool,'RECYCLE',initial_extent, 0) Recycle_Bytes,
decode(buffer_pool,'DEFAULT',initial_extent, 0) Default_Bytes
from all_tables
where owner = '&SchemaName'
Order by 1
/


spool off

Back to the List Of Scripts


Unix script to view the SGA	
/* 	
Sam  D.B.A. ORACLE   

Purpose:Unix script to view the SGA		

*/

svrmgrl
connect internal;
sho sga
EOF

Back  to the List Of Scripts



A simple way to view the explain plan output	
/* 	
Sam ORACLE DBA

Purpose:A simple way to view the explain plan output		

*/


col object_name format a20 trunc
col remarks format a10 trunc
col operation format a10 trunc
col options format a10 trunc

select 
object_name,
remarks, 
operation, 
options 
from plan_table
/

Back to the List Of Scripts




List of Objects and their sizes  for a given schmea.	
/* 	
STMistry  ORACLE DBA

Purpose:List of Objects and their sizes  for a given schmea.		

*/


set linesize 80


col object_name format a28 trunc
col object_type format a18 trunc
col kbytes format 999,999,999 heading 'Space Used (KB)'

break on object_type skip 1 on report

compute sum of kbytes on object_type
compute sum of kbytes on report

spool see_obj_space

select
object_type,
object_name,
sum(bytes)/1000 kbytes
from dba_objects o, dba_segments s
where o.object_name = s.segment_name
and   o.owner = s.owner
and s.owner = '&SchemaOwner'
group by object_type, object_name
order by 1,2
/



spool off

clear breaks
clear computes

Back to the List Of Scripts




View information on the last redo log archived
/* 	
Sam ORACLE DBA

Purpose:View information on the last redo log archived

*/



Select
*
From V$Archived_Log
Where RecID =
(
 Select
 Max(RecID)
 From V$Archived_Log
 Where Archived = 'YES'
)
/

Back to the List Of Scripts




:List the username and server connections  originating from your terminal.	
/* 	
Sam  ORACLE DBA.   

Purpose:List the username and server connections  originating from your terminal.		

*/

set linesize 80

col username format a30 trunc
col osuser format a20 trunc
col machine format a20 trunc

spool see_machine

select username, osuser, machine from v$session where userenv('TERMINAL')=TERMINAL
and username is not null
/

spool off

Back to the List Of Scripts




Views the location and status of all redo logs	
/* 	
STMistry   ORACLE D.B.A.

Purpose:Views the location and status of all redo logs		

*/

spool see_logs

col member format a50 trunc

select * from v$logfile
order by group# asc, member desc
/

select group#, status from v$log
/

spool off

Back to the List Of Scripts




View currently held locks by user
/* 	
STMistry  ORACLE D.B.A.
Purpose:View currently held locks by user		

*/


set linesize 80

col object_name format a20
col uid format 99999

spool see_locks

select
username,
uid,
object_name,
decode(lmode,2,'row share',3,'row exclusive',4,
'share',5,'share_row_exclusive',6,'exclusive','') LockType
from
v$lock l, v$session s, dba_objects o 
where l.sid = s.sid
and l.id1 = o.object_id (+)
and username is not null
/

spool off

Back to the List Of Scripts



:Lists the Index current storage space for a given schema	
/* 	
Sam ORACLE D.B.A.

Purpose:Lists the Index current storage space for a given schema		

*/

col segment_name format a30 trunc
col tablespace_name format a30 trunc
col bytes format 99,999,999,990

break on report
compute sum of bytes on report

spool see_index_space

select 
distinct 
segment_name, 
tablespace_name, 
sum(bytes) bytes
from dba_segments
where owner 	= '&SchemaName'
and segment_type 	= 'INDEX'
group by segment_name, tablespace_name
order by 1,2
/

spool off

clear breaks
clear computes

Back to the List Of Scripts



Lists the free extent space for each tablespace
/* 	
Sam ORACLE D.B.A.

Purpose:Lists the free extent space for each tablespace		

*/

col bytes format 999,999,999,990

break on tablespace_name skip 1 on report skip 2

compute sum of bytes on tablespace_name
compute sum of bytes on report

spool see_free

select tablespace_name, bytes from dba_free_space
/

spool off

clear breaks
clear computes

Back to the List Of Scripts



List Objects exceeding their initial extent size  in a given schema.
/* 	
Sam
Purpose:List Objects exceeding their initial extent size  in a given schema.		

*/

set linesize 80

col segment_name format a40 trunc

spool see_extents

select segment_name, segment_type, count(*) from dba_extents
where owner = '&SchemaOwner'
group by segment_name, segment_type
having count(*) > 1
order by 3
/

spool off

Back to the List Of Scripts





E-MAIL ME







 PL TAKE SOME TIME TO SIGN MY GUEST BOOK   
 








CHAT WITH ME