SQL WorkShop About Oracle Architecture

– DataBase Version : Oracle XE 10g
– Startup and shutdown database
– Create database
– Control files
– Redo log files
– Table spaces and data files
– Rollback segments
– Temporary segments

SQL > conn sys/pswd as sysdba;

– I prefer to shutdown immediate =)

SQL >SHUTDOWN ABORT
SQL >SHUTDOWN IMMEDIATE
SQL >SHUTDOWN NORMAL
SQL >SHUTDOWN TRANSACTIONAL

SQL >STARTUP PFILE='filepath/initXE.ora'

SQL >CREATE DATABASE BD1
 MAXLOGFILES 5
 MAXLOGMEMBERS 5
   MAXDATAFILES 100
LOGFILE
    GROUP 1 '[path]/log1a.rdo' SIZE 100 M,
    GROUP 2 '[path]/log2a.rdo' SIZE 100 M
DATAFILE
    '[path]/system01.dbf' size 200 M
SQL >exec catalog.sql -- create dictionary views
SQL >exec catproc.sql -- create pl/sql scripts for server 

– accessible for nomount and above

SQL >select * from v$parameter
SQL >select * from v$sga
SQL >select * from v$process
SQL >select * from v$session
SQL >select * from v$version
SQL >select * from v$instance

– accessible for mount and above

SQL >select * from v$thread
SQL >select * from v$controlfile
SQL >select * from v$database
SQL >select * from v$datafile
SQL >select * from v$logfile

– how to terminate a user session

SQL >select sid, serial# from v$session where username = 'HR';

SID SERIAL#
———- ———-
37 100

SQL> alter system kill session '37,100';
System altered.

– redo log files.

SQL> select group#, sequence#, bytes,members, status from v$log

SQL> select * from v$logfile

– adding new log file rdolog3.rdo

SQL> ALTER DATABASE ADD LOGFILE
('C:\ORACLEXE\APP\ORACLE\FLASH_RECOVERY_AREA\XE\ONLINELOG\RDOLOG3.rdo') size 10M;

– add redo log file to group 1

SQL>ALTER DATABASE ADD LOGFILE MEMBER
'C:\ORACLEXE\APP\ORACLE\FLASH_RECOVERY_AREA\XE\ONLINELOG\RDOLOG3.rdo' TO GROUP 1
SQL> ALTER DATABASE DROP LOGFILE GROUP 3

SQL> ALTER DATABASE DROP LOGFILE MEMBER 'C:\ORACLEXE\APP\ORACLE\FLASH_RECOVERY_AREA\XE\ONLINELOG\RDOLOG3.rdo'

SQL>CREATE TABLESPACE app_data
DATAFILE '[path]/app01.dbf' SIZE 100M,
         '[path]/app02.dbf' SIZE 100M
MINIMUM EXTENT 500K
DEFAULT STORAGE (INITIAL 500K
                   NEXT 500K
                   MINEXTENTS 3
                   MAXEXTENTS 500
                   PCTINCREASE 50 );

– INITIAL size of first extent. size of blocks
– NEXT size of second extent. size of blocks
– MINEXTENTS num of extents for created segments
– PCTINCREASE extent size grows
– MAXEXTENTS max num of extents for segments

– adding new tablespace

SQL > ALTER TABLESPACE app_data ADD DATAFILE ('[file_path]/app03.dbf' SIZE 200M);
SQL > ALTER TABLESPACE app_data ADD DATAFILE '[file_path]/app04.dbf' SIZE 200M
AUTOEXTEND ON NEXT 10M MAXSIZE 500M;

– enable autoextension for a datafile

SQL > ALTER DATABASE DATAFIEL '[file_path]/app03.dbf'
AUTOEXTEND ON NEXT 10M MAXSIZE 500M;

– changing the size of Data Files Manually

SQL > ALTER DATABASE DATAFILE '[file_path]/app02.dbf' RESIZE 200M;

– make tablespace read only
– must not contain rollback segments
– must not involved in a online backup

SQL > ALTER TABLESPACE app_data READ ONLY;
SQL > select tablespace_name, contents, status from dba_tablespaces;
SQL > select tablespace_name, initial_extent,next_extent, max_extents, pct_increase, min_extlen
from dba_tablespaces;
SQL > select file_name, tablespace_name, bytes, autoextensible, maxbytes, increment_by from dba_data_files;

– DataBlock Parameters
– INITRANS
– MAXTRANS
– PCTFREE
– PCTUSED

– about storage

SQL > select segment_name, tablespace_name, extents, blocks from dba_segments where owner = 'SYS'
SQL > select extent_id, file_id, block_id, blocks
from dba_extents
where owner='SYS' and segment_name = 'ERROR$'
SQL > select tablespace_name, COUNT(*),max(blocks), sum(blocks) from dba_free_space group by tablespace_name;

– if changes made to rallback segment are also
– protected by redo log files we can rollback all changes
– after unconditional instance fails

– request a rollback segment for this transaction

SQL > select segment_name,SEGMENT_TYPE from dba_segments where owner = 'SYS' and segment_type = 'ROLLBACK'

– SET TRANSACTION USE ROLLBACK SEGMENT SYSTEM
– create new rollback segment

SQL > CREATE ROLLBACK SEGMENT rlbs01
TABLESPACE SYSTEM
STORAGE (INITIAL 100K
         NEXT 100 K
         OPTIMAL 4M
         MINEXTENTS 20
         MAXEXTENTS 100 );

– make it online

SQL > ALTER ROLLBACK SEGMENT rbls01 online
SQL > ALTER ROLLBACK SEGMENT rbls01 offline
SQL > DROP ROLLBACK SEGMENT rbls01;

– list rollback segments

SQL > select segment_name, tablespace_name, owner, status from dba_rollback_segs;

– show status of rollback segments

select usn,extents,rssize,optsize,hwmsize,xacts,status from v$rollstat

– if SORT_AREA_SIZE is full, swap data to temporary segments.

SQL > select * from dba_segments
SQL > select * from v$sort_segment
SQL > create view large_table_view as
select * from dba_source
   UNION ALL select * from dba_source
   UNION ALL select * from dba_source
   UNION ALL select * from dba_source;

– new session for sort operation

SQL > select DISTINCT * from large_table_view

– show currently active sorts (run using different session )

SQL > select * from v$sort_usage

– deallocate of unused (after extend defragmentation ) extents
– to be able to use by other segments

SQL > ALTER TABLE hr.t1 DEALLOCATE UNUSED;

– ANALYZE TABLE
– validate integrity of data blocks in the table.

SQL > ANALYZE TABLE hr.t1 VALIDATE STRUCTURE;

– compute statistics

SQL > ANALYZE TABLE hr.t1 COMPUTE STATISTICS;

~ by enginzorlu on December 4, 2008.