– 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;
