Make your own free website on Tripod.com
TechDirectory  Back

SQL TIPS for Systems Administrators

See this slide show on sqltools


1- Create Table Space

CREATE TABLESPACE rb_segs
      DATAFILE 'datafilers_1' SIZE 50M
      DEFAULT STORAGE (
        INITIAL 50K
        NEXT 50K
        MINEXTENTS 2
        MAXEXTENTS 50
        PCTINCREASE 0)
      OFFLINE;


2- Add a datafile to a TableSpace

ALTER TABLESPACE rb_segs
   ADD DATAFILE 'filename1' SIZE 1M;


3- A datafile Added with Automatic extension of datafile

ALTER TABLESPACE users
    ADD DATAFILE 'filename2' SIZE 10M
      AUTOEXTEND ON
      NEXT 512K
      MAXSIZE 250M


4- Rename the datafiles in a table space1

ALTER TABLESPACE users
    RENAME DATAFILE 'filename1', 'filename2'
        TO 'filename3', 'filename4';



5- Disable Autoextension

ALTER DATABASE DATAFILE 'filename2'
    AUTOEXTEND OFF



6- Manualy Resizing Datafile

ALTER DATABASE DATAFILE 'filename2'
   RESIZE 100M



7- Identify datafile names of a Tablespace


SELECT file_name, bytes FROM sys.dba_data_files
       WHERE tablespace_name = 'USERS';


8- View tablespaces with all storage parameters of a database

SELECT tablespace_name "TABLESPACE",
   initial_extent "INITIAL_EXT",
   next_extent "NEXT_EXT",
   min_extents "MIN_EXT",
   max_extents "MAX_EXT",
   pct_increase
from sys.dba_tablespaces;



9- U can use table sizes to estimate the maximum space needed.
Table sizes can be found in the USER_SEGMENTS view of the Oracle
data dictionary. The following query displays disk usage for all tables: 

select sum(bytes) from user_segments where segment_type='TABLE';
10- Check the Total, free and used space in all Tablespaces.

select dfs.tablespace_name, sum(ddf.bytes) total, sum(dfs.bytes) free, sum(ddf.bytes) - sum(dfs.bytes) used from dba_data_files ddf, dba_free_space dfs where ddf.file_id = dfs.file_id group by dfs.tablespace_name; exit;
11- Create user CREATE USER myuser IDENTIFIED by mypasswd DEFAULT TABLESPACE ourtablespace; grant dba to ourtablespace; grant all on ourtablespace to public; 12- Creating Some Tables and indexes create table Times ( Source VARCHAR2(2) not null, Ris VARCHAR2(32) not null, RisType CHAR(1) not null, Fip INT not null, TimeID INT not null ); create table TCurrent ( TimeID INT not null, Version INT not null, NumEntries INT not null, StartTime DATE not null, EndTime DATE not null, MaxDays INT, BaseInterType CHAR(1), BaseInterLength INT, SummType CHAR(1), Data LONG RAW ); create index TCurrIndex on TCurr (TimeID); create table THist ( TimeID INT not null, RowNum INT not null, Version INT not null, Format INT not null, NumEntries INT not null, StartTime DATE not null, EndTime DATE not null, Data BLOB ); create index ThistIndex on THistory (TimeID, RowNum);
13- Steps for oracle hotbackup