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