ORACLE ADMIN I PART II

Table Space & DATA File

Tablespace – Logical

Data file – Physical files

Note : Tablespace can be created to use one of the following method to manage free space & used space

i)                Locally managed tablspace

ii)            Dictionary-managed tablespace

It is recommended to use locally managed tablspace, If your using Dictionary managed tablespace to migrate from it use below given procedure DBMS_SPACE_ADMIN.TABLSPACE_MIGRATE_TO_LOCAL

Viewing Tableapce, Datafiles, & Temp file information

Tablespace – DBA_TABLESPACES,V$TABLESPACE

Data file – DBA_DATA_FILES,V$DATAFILES

Temp file information – DBA_TEMP_FILES,V$TEMPFILE

Types of DATA Files

1.    Permanent

2.    Temporary

3.    Undo data files

To See the all the table space

SQL > select * from v$tablespace

To see the Data files

SQL > select * from v$datafiles

Creating new table space

SQL > create tablespace <new name> datafile ‘d:\oracle\..\file.dbf’ size 5m

To see the current user tablespace

SQL > select default_tablespace from dba_users where username=’USER1’

SQL > select tablespace_name from user_tables

To see the datafile which belog to table space

SQL > select name,byte from V$Datafile where TS#=7

Allocating tablespace to user

SQL > create user <user> identified by <pwd> default table space <tb name> quota 5m on <tb name>

                  (or)

SQL > alter user <user> default tablespace <table space name>

To modify table space size

SQL > alter database datafile ‘d:\file.dbf’ resize 20m

SQL > select name,byte from v$datafile where TS#=7

To set the table space to auto increment

SQL > desc dba_data_file

SQL > select * from dba_data_files where file_id=6

SQL > alter database datafile ‘d:\file.dbf’ autoextend 1m maxsize unlimited

SQL > select * from dba_data_files where file_id=6

Tablespace Properties

Note : sysaux and system are the mandatory tablspace

v      Segments – Exists within tablespace

Note: The segment space management within a locally managed tablespace can be specified as (i)Automatic or (ii) Manual

v      Extends – Segments are made up of collection of extends

Note: The extend within the a locally managed tablespace can be allocated in two types (i) Automatic (ii)Uniform

v      Datablock – Extends are made up of collection of datablocks

v      Disk block – datavlock are mapped to disk block

v      Default datablock size is 8kb

To bring the other than system table space online or offline

SQL > alter tablespace <tbs name> offline/online

Note : When table space is offline

Possible queries –      Alter table <name> add

                        Alter table <name> modify

                        Drop table <name>

Impossible query -      select, DML, create, truncate, alter table <name> drop col

To see the status of table space

SQL > select name,status,enabled from v$datafile where ts#=<tbs number>

To bring the other than system table space read only or read write mode

SQL > alter tablespace <tbs name> readonly / read write

Note : When table space is read only mode

Possible queries –      Alter table <name> add

                        Alter table <name> modify

                        Drop table <name>

                        Select

Impossible query -      DML, create, truncate,

To see the status of table space

SQL > select name,status,enabled from v$datafile where ts#=<tbs number>

Droping table space

Note: To drop table space if any content inside have to drop those also

SQL > drop tablespace <tbs name> including contents and datafiles

Handling Default table space

Note : To drop default table space, have to change as non default and remove it

SQL > select * from database_properties where property_name =’Default_permanent_tablespace’ - To See the current default tablespace

SQL > alter database default tablespace <new tbs name>

SQL > drop tablespace <old default tbs> including content and datafile

Handling with system tablespace

Note: We cannot bring system table space offline only read only is possible

That to by executing sequence of following command not like previous drop tablespace command.

When we make system table space read only all other table space also automatically become read only.

When system table space in read only

Possible – select query

Impossible – all other queries are not possible including

SQL > alter table add/modify column

Steps to bring system table space to read only

SQL > shutdown immediate

SQL > startup mount

SQL > alter database open read only

SQL > select name,status,enabled from v$datafile where ts#=0

To bring back the system tablespace to read write mode

SQL > shutdown immediate

SQL > startup

Relocating & renaming DATA file

SQL > alter tablespace <name> offline

• Then manually cut & past the data file to new location

SQL > alter tablespace <name> rename datafile <old path\file.dbf>

<New path\file.dbf>

SQL > alter tablespace <name> online

OMF (Oracle Managed File)

SQL > show parameter db_create_file_dest

SQL > alter system set db_create_file_dest=<path> - To change default table space location

SQL > create tablespace <name> - Will stored in above declared path

Note : Without setting ‘db_create_file_dest’ parameter creating tablespace without location will throw an error

Relocate & rename system tablespace

SQL > shudown immediate

• Physically relocate the file

SQL > alter database rename file <old path\fil.dbf> to <new path\fil.dbf>

Note : In open state command is different i.e ‘alter system’ if it is shutdown state command is ‘alter database’

Note : When currently used tablespace is removed user not able to create any table. Without users commiting their transaction it is not possible to remove the table space

Temporary tablespace

SQL > select temporary_tablespace from dba_users where username=’SCOTT’

SQL > select temporary_tablespace from dba_users where username=’USER1’

Note : For all users temporary table space common

To see the default temp table space

SQL > select * from database_properties where property_name=’DEFAULT_TEMP_TABLESPACE’

Create new temporary tablespace

Create temporary tablespace <name> tempfile <path\file.dbf> size 10m

To assign new temp tablespace

SQL > alter user <user> temporary tablespace <tablespace name>

Droping tablespace

SQL > drop tablespace <name> including content

Note : It is not possible to delete default temporary tablespace

UNDO Tablespace

After deletion before commit if we want to undo the deletion data is fetched from undo table space

Note : There will be only one undo tablespace for a database.When undo tablespace is full system get hanged. Only option to fix the issue is rollback (or) kill user sessions

To avoid this situation keep commiting often

To check undo tablespace size

SQL > select name,bytes from V$datafile

SQL > desc dba_users

TO see the undo tablespace

SQL > show parameter undo_tablespace

SQL > show parameter undo

Reating undo tablspace

SQL > create undo tablespace <name> datafile <path\file.dbf> size 10m

To set the newly created undo tablespace as default

SQL > alter system set undo_tablspace=<nem table space>

Droping undo tablespace

SQL > drop tablespace <name> inclding content

Internal Preocess when we delete a record

1.    Compilation & user authentication checked in chared pool

2.    Data deleted frompermanent data file

3.    a. Data which is selected for deletion stored in database buffer

b. cache. That block become used block

   When deletion done it become dirty block

1.     The data transferred to redo log buffer

2.     Then with hello of LGWR it is written to redo log file

Use of UNDO Segment

Storage & relationship structure

Configuring Oracle SID for (TNS Protocol error)

Windows - > Run - > cmd

In cmd -> set oracle_SID=<orcl>

In cmd -> d:/>sqlplusw /nolog

Creating User & Profile

Inside profile there are two concept
1.     Resource management (Values will be in term of minutes)
2.     Password management (Values will be in term of days)

Password management

To see the assigned profile privilege

SQL > select resource_name,limit from dba_profile where profile=’DEFAULT’

To see the assigned profile

SQL > select profile from dba_user wherer user_name=’USER1’

To change the pofile parameters

SQL > alter profile <name> limit FILED_LOGIN_ATTEMPTS 2,PASSWORD_LOCK_TIME 1; - In

the above query only 2 login attempts are allowed & if wrong password is given password get locked for 1 day next day it will be released

To see the user status whether locker or not

SQL > select account_status from dba_users where user_name=<’USER1`’>

User lock types

There are two types

1.     timed (lock) – will be unlocked automatically based on lock period

2.     locked – have to be manually un locked

SQL > alter user <name> account lock/unlock

To lock based on minutes

Calculation are

(1/24)/60 – is a minute i.e .0006

To release the lock automatically after 1 minute

SQL > alter profile <name> limit FILED_LOGIN_ATTEMPTS 2,PASSWORD_LOCK_TIME .0006

Password life time & grace time

SQL > alter profile default limit password_life_time 10, password_grace_time 5

Password reuse history restriction

SQL > alter profile <name> limit password_reuse_max 1,

Password_reuse_time .0006

Note : After one minute same old password accepted

Password verify function

There is inbuikt PL/SQl function to apply for password restriction like

Moer that 8 char must, no dictionary word, etc.

To apply the script restriction

SQL > @ d:\oracle\product\10.2.0\db_1\rdbms\admin\utlpwdmg.sql

To cancel password verify function

SQL > alter profile <name> limit password_verify_function NULL;

To create New profie             

SQL > create profile <name> limit

Failed_login_attempts 1

Password_limit 1

Password resuse_time 1

To assign profile to user

SQL > alter user <name> profile <profile name>

To see the assigned profile

SQL > selecy profile from dba_users where user_name=’USER1’;

To drop profile

SQL > drop profile <name> casecade – Even assigne profile will be deleted that user profile changed to DEFAULT

Note – It is not possible to delete default profile

Resource management

Before changing any resource parameter we have to set resource_limit = true,

SQL > alter system set resource_limit = True

SQL > show parameter resource_limit

SQL > alter profile set session_per_user 1 – only one session for a login

SQL > alter profile set connect_time 20,idle_time 1;

Unable to see the demo of following resource management

CPU_PER_SESSION,CPU_PER_CALL – Like setting download limit, call limit

Privileges & Roles

Two types of privileges

1.     System privileges – Sys is the owner, With Admin option. Enable user to perform particular action with database

2.     Object privileges – With grant option. Enable user to access and manipulate a specific object.

System Privilege

SQL > create session,Table,Index,Procedure,user,etc

SQL > Alter session,table,Index,Procedure,user,etc

SQL > Drop session,table,Index,Procedure,user,etc

SQL > Create/alter/drop any schema.table/index – any means it allows you to create table or index in other users schema

SQL > grant create any table to user with admin – With admin allows users to pass the permission means received user can give the same privilege to other users

Note : If system privilege is given with admin option if received user give  permission to other user,if grant parent delete the permission from parent means child permission will not be deleted automatically has to be done manually.

For object privilege chil permission also get deleted

To check who are all have the admin privilege

SQL > select grantee from dba_sys_privs where privilege=’CREATE ANY TABLE’

To Revoke given permission

SQL > revoke create any table from user1,user2

Roles

Role is grouping of privileges,predefined roles are connect,resource,DBA,etc

To see the existing roles

SQL > select * from dba_roles

Create roles

SQL > create role <name>

SQL > grant create user,alter user to <role name>

SQL > grant <Role> to <user>

As a end user to see applied role

SQL > select * from session_roles

As a end user to see applied privileges

SQL > select * from role_sys_privs

To see role assigned to which user

SQL > select * from dba_role_privs where granted_role=<role name>

To drop role

SQL > drop role <name>

As a SYS to check user log in

SQL > select * from dba_sys_privs where grantee=<User name>

Installing and uninstalling using Wizard

1.     Use OUI to remove oracle from server

2.     regedit - HKLM – SW – Oracle – search for oracle and sid remove

3.     regedit – HKLM – SYS – Service – oracle – deleteCreating oracle database manually

1.     Create the following folders manually

            SID folder

            Insdide SID folder three folder pfile,bdump,udump) (cdump is optional)

2.     Copy and paste the pfile inside the parameter file change all old sid to new sid

3.     Create password file using following command in command prompt

C:\> prapwd file=d:\oracle\product\10.2.0\db1\database\<passfile+sidname>.ora password=oracle

4.     Cerate service manually uing following command in command prompt

C:\> oradim –new –SID <SID name> -pfile d:\oracle\product\10.2.0\admin\india\pfile\init.ora

5.     C:\> set oracle-sid=<sid name>

6.     C:\> sqlplusw /nolog

In SQLPLUSW

SQL >  conn sys/oracle as sysdba

SQL > startup nomount pfile=’d:\oracle\product\10.2.0\admin\india\pfile\init.ora

Note:

Minimum system file required is system,sysaux,undo,temp

Run SQl command to create database

SQL > create database <name>

maxlogfiles 10

maxlogmembers 20

maxdatafiles 4

maxloghistroy 100

datafile ‘d:\oracle\product\...\oradata\india\system01.dbf size 300m’

sysaux datafile ‘d:\oracle\product\...\oradata\india\sysaux01.dbf size 300m’

undo tablespace undotbs1 default

       ‘d:\oracle\product\...\oradata\india\undotbs01.dbf size 200m’

Default temporary tablespace temp

      Tempfile ‘d:\oracle\product\...\oradata\india\temptbs01.dbf size 20m’

Logfile group1 (‘d:\oracle\....\oradata \india\redo01.log’) size 8m

  Group2 (‘d:\oracle\....\oradata \india\redo02.log’) size 8m

      Noarchivelog

/

Creating dictionary

Note: During manual database installation no dictionary exist has to be created manually

SQL > @ ‘D:\app\Administrator\product\11.2.0\dbhome_1\RDBMS\ADMIN\catalog.sql’

Creating packages

Note: During manual database installation no packages exist has to be created manually

SQL > @ ‘D:\app\Administrator\product\11.2.0\dbhome_1\RDBMS\ADMIN\cataproc.sql’

Droping Database manually

SQL > shutdown immediate

SQL > startup mount restrict

pfile=’=’d:\oracle\product\10.2.0\admin\india\pfile\init.ora

SQL > drop database

Note: drop database command only available from 10g