WORKING WITH XMANAGER
Xmanager is used to work with with GUI applications of Linux server from client node. Steps to work with Xmanager:
1. In the client, on desktop [or from the menu] we can find Xmanager 2.0 Xmanager Passive. Double click on it.
2. In the Telnet or Putty window login to linux and export or set the DISPLAY variable. Ex: export DISPLAY=192.168.0.11:0.0 [ 192.168.0.11 is IP address of client machine]
3. Test Xwindow is opening or not using ‘xclock’ command in telnet/putty window INSTALLING ORACLE 10G
Pre-requstics for installilng oracle • We must have o/s user
• O/S user must belong to dba group
• We must have staging area [Where Oracle S/W dump exist] • We need adequate free space [ 3 to 4 GB]
Steps of install oracle
• Start x-manager passive • Login into OS Account
• Go to stage area (i.e. open the location of software) • Export or set DISPLAY
• To perform installation,we need to invoke OUI by executing setup file[ OUI – Oracle Universal Installer]
• Run the executable file ‘./runInstaller’
For installing oracle , we have to specify oracle home dir which can be created using mkdir command.
While performing installation we may face same error like
(OUI-10094 problem in saving invertory installation ,connot contain
/home/oracle/product/10.2.0/dbs/inventory/contents XML/oraclehome/props (perimisson denied))
Because while installing the oracle 10g first time some information will be stored in /etc/orainventory/contents/inventory.xml. In this xml file the link for some files are stored. To access this link files while installing the next time we have to give the perimision for that files by using chmod command.
Chmod 777 /etc/orainventory/contents/inventory.xml
It is better to give file perimission to mountpoints because these are so many files in the xml file,while installing it succeeds or not the path of oracle home is stored in that file. We need to access these files because while installing it reaches for the file link root.sh in xml file
While installing it ask us to execute root.sh this is mandatory because if we execute this file it copies files like oraenv files
This file (root.sh) must be executed as root user only [root@linux ~] cd /oraDB/kittu/ohome/root.sh Then it asks questions like
( the following env variable are set as
ORACLE_HOME=kittu, ORACLE_HOME=/oraDB/kittu/home Enter full path of local bin dir [/user/local/bin]
The file “dbname,oraenv,coraenv” already exe in /user/local/bin overwrite it(y/n) All file are extracte from source file called praductions.jar(jar java archeive) script this command will capture all the acivites that done by terminal or user Syn:- script filename
Script abc
All the activities done by the user after running script will be copied into file called abc To exit from script ,type exit
Or stop capturing
To see the activities use more command All these activites are stored in file on server The default name for script file is typescript Specific prerequestic checks
Summary oracle db10g 10.2.0.1.0
In real time envi ronment, we need to seen all the activities performed by us to client.what we need to send to client must be given by the client in documentation. This is also called as ticket. These files are called log files
The naming conversion for log file is Log_ticket_timestamp.txt
For sending this report, we need to make a clear view of activities in a file on local pc Steps to place logfile in local pc
• Right click on session and select changesettings
• Select session logging and choose log all session output and browse a location to store information and then click apply. From then whatever we fire on that session will be stored into that file including output generated by command .after finishing executing all commands and completion of work follow the next step • Again click on session and select change setting
2nd Method Of Installation using VNC [Vertual Network Computing]
Vnc server is present on server and vnc viwer is present on client
We need to start vnc server on server through our session and must open vnc viwer on client
To start vnc server Syn:- vncserver
Then vnc server is started on server. When we start vnc server it asks for password for frist time. Enter password whatever you like. After entering the password it create hidden directory called .vnc. This file is created under home directory of user. This directory consists of files like password,files,startup files,log and pid(process id) files . They are passwd startup linux6:1.pid linux6:1.log Linux6(hosthome):1(port).pid
In server,every vnc connection is created with portnumber its starts from ‘1’ next connection is ‘2’
We identified this port number from a line where vnc is started .this file is new’linux6:1(kittu)’ desktop is linux6:1
To search whether vnc started or not Ps –of |grep vnc
To kill
process:-For this we must have the process id of vnc server. This pid is stored in linux6:1.pid file more linux6:1.pid
ps –ef |grep vnc kill -9 6470
Then vnc process is stopped or killed
How to start vnc server from client and how to acces vnc server from client? open o/s user
type vnc server and press enter
enter passwd if we open vnc server for first type. identified the portnumber of vnc server
Linux6:1
open vnc viewer and type ip address of server along with portnumber 192.168.0.102:1
Enter the passwd and press enter
What is difference between vnc server and x-manager
In x-manager, we have to set display. In this if we close the session in the middle of installation or the system is shut down .then installation stops in middle. But in vnc server the installation is being done on server. So if we close the session in the middle of installation or system (pc) is shut down ,the process of installation does not stop because this is done at server level. we can get back the old session by opening vnc viewer in shared mode by entering ip address with portnumber and passwd .we can open this session on another pc also.
Screening:
Screening is the concept of maintaining the session ‘s data available when close the session
For exemple ,when we are working with file in ‘vi’ editor we modified 100 lines and close the session with out saving the file. Actually the file will not be modified at this situation. In this case ,if we use screening ,we can retrive the modification as usual what we had done.
This is possible by following the below steps type screen command
do the modification to the file whatever we want close the session
its given with name screen 0
each and every screen is identified by socket number we list the screen by using command
syn: screen -ls
9501.pts -4 .linux6 socket number
=> the local screen is attached to the session by using the following command Syn: screen –x socketnumber
this screen retains the session and we can do the activites what we want to do in previous session
actually vnc is used for gui mode and screen is used for cui mode oracle 9i software occupies 1.6gb
CREATING ORACLE DATABASE The installation of database is done in 2 ways
Manual process
DBCA (Database Configuration Assistant) The requirements for installing database:
We must have oracle software installed We must have user account
We must have adequate freespace
Installation of database using manual
process:-1. We must set the environments for database ie., we must set values to environment variables like ORACLE_SID, ORACLE_HOME, PATH .
Fallow the below steps to set environment $export ORACLE_SID= dkittu
$export ORACLE_HOME=/u001/kitty/mysore (software installed location)
$export PATH=$PATH:$ORACLE_HOME/bin
ORALCE_SID will be database name. So SID must be same as of the database name we want to create.
2. create the oracle initialization file init+sid.ora in $ORACLE_HOME/dbs directory. In this file we defined some parameters required to create and manage a database. The file name should be in the format init+$ORACLE_SID.ora . Ex: initdkittu.ora The file should be created with following parameters
db_name = dkittu db_cache_size=500m or 50000000 shared_pool_size = 50m log_buffer =10000 Undo_tablespace = undotso1 undo_management = /oraDB/kittu/kittudb/c1.ctl compatible= 10.2.0.1.0
3. After completion of above process
connect to sqlplus as sysdba and issue command startup nomount. $sqlplus
SQL>enter username: sys as sysdba Password:
SQL> startup nomount
4. Now create the database using the following command Sql> create database dkittu
Datafile '/oraDB/kittu/kittu_db/system01.dbf' size 500m, Logfile group 1 '/oraDB/kittu/kittu_db/redo01.rdo' size 5m,
group 2 '/oraDB/kittu/kittu_db/redo02.rdo' size 5m, Undo tablespace undots01
Datafile '/oraDB/kittu/kittu_db/undo01.dbf' size 50m;
After executing above statement, database is created is displayed. Then execute the below post scripts
5. Post steps: (scripts)
SQL> @$ORACLE_HOME/rdbms/admin/catalog.sql Instead of writing $ORACLE_HOME we may use ‘?’ This script creates all the dictionary views
After the completion of above script, run below script SQL> @$ORACLE_HOME/rdbms/admin/catproc.sql This script creates all scripts for all procedural scripts now connect to system/manager. Type below one
sql>connect system/manager Connected
SQL> @$ORACLE_HOME/sqlplus/admin/pupbld.sql This script creates security files
sql> conn sys as sysdba sql> shut immediate Database closed
sql> exit
Now we exited from SQL PLUS
---Same process is required to create log some modifications required in initialization file and create database.
1) $export ORACLE_SID= dbcherry
$export ORACLE_HOME=/u001/kitty/myhoome (software installed local)
$export PATH=$PATH:$ORACLE_HOME/bin Then create initialization file
2) initdbcherry.ora db_name = dbcherry db_cache_size=500m or 50000000 shared_pool_size = 50m log_buffer =10000 undo_tablespace = undotso1 undo_retention=99 control_files= /oraDB/dittu/database/cs.ctl undo_management = auto compatible= 10.2.0.1.0
Then excute the below statements after executing 3 point above executed create database dbcherry
sysaux Datafile ‘/oraDB/kittu/databases/sysaux.dbf’ size 50m,
datafile ‘/oraDB/dittu/database/system.dbf’ size 350m,
Logfile group 1 ‘/oraDB/kittu/database/redo01.rdo’ size 5m, group2 ‘/oraDB/kittu/database/redo02.rdo’ size 5m, Undo tablespace undots01
Datafile ‘/oraDB/kittu/database/undo01.dbf size 50m; Then execute the post scripts
Creating Database Using DBCA( Database Configuration Assistant) 1) For this we had to run X-manager passive and export display 2) Set environment variables like ORACLE_HOME, PATH
Don’t set ORACLE_SID, because we give this SID in creation itself 3) Don’t create initialisation file. It also created during database creation
4) After exporting ENV variables, type dbca and press enter. Then the below steps takes place
Welcome screen 1) Operation
Select operation you want to perform Create database
Configure database options is database Delete database
Manage template 2) Database templates
Select template from following list to creater database General purpose
Transaction processing New database
3) Global databasename: ramu
It is consider as ramu.appworld.com sid: ramu
4) database connection option
Select the mode in which you want your database to operate by default dedicated server mode (one user)
shared server mode (more user) 5) Initiazation parameters
By default it takes some values if not we can modify those values memory
o typical charactersets
o custom o use default dbsize
shared pool-5000000 o use unicode sort area size 524288 buffer cache=3k o choose form list
javapool=25000 large –o
pga ---1500000 file locations
1)create serverparameter file Trace file defination
Userprocess-adim /udump
Background process admin/bdump Core dumps admin/cdump
Database storage Logfiles System files Controlfiles 7) create options create database save as templates OK
Then tempfile is displayed which displays complete information of database Note: for 10g we have some modifications in creation
To indentify whether database is down or up: Connected to idle instance means database is down
This envinorments varibles values are only with that session.to make that values permanent,save this value in bash_profile.
How to we maintain multiple database in one o/s user in a server
This is possible by using fuction in bash_profile each function caontain information envinorment value for one database
Syn: function() { Export oracle_sid=dbname Export oracle_home=myhome Export PATH=$ORACLE_HOME=/bin }
Define the functions .bash_profile file
When the user login into account,this functions are loaded into memory. To execute particular function type below commad in shell prompt Syn: $functionname
Ex:- $database
Then the envinorment variables defined in database are set To switch into another function ,just type that name and press
To check whether database process is started or not use below command. $ps –ef | grep smon
When the database is started the process smon is started.
Homedirectory: - is the location where the files related to that particular user were navigated.
Generally in oracle there are 2 main users 1) sys as sysdba
2) system.
Sys as sysdba:- This is root login account through this user we perform najor activites like.
Starting a database Shutdown the database Removal of database Monitoring the database Taking backups
This is recommended to do minor activites like creating tables Creating users
Altering tables,data
There is no password for sys as sysdba. It has the highest privileges of any database. System :- Through this user we perform the low level activities as we mentioned above. The password file system is manager.
To connect to system:
Sql> connect system or sql>conn system To run the script in sql use the below command :-Sql>@scriptname
Generally when we are creating a database we have to perform 2 phases ;-1) Configuring Instance
2) Configuring Database
1) Configuring Instance : - instance is nothing but memory this activity is done creating
Init + oracle_sid.ora
When instance is started in background two things are started a) SGA
When we issue startup nomount,it reads initdb.ora and allocates memory to sga and instance is started
Sga is space reserved for memory(ram) to database 2)configuring database
When we write(create database statements) Database is created
After this 3 files are created.they are Datafiles(dbf0
Controlfiles(ctl) Redolog files(redo)
After this we have to perform post steps Sizes for software
Oracle 9i---1.6gb Oracle 10---1.26gb
Q) how to change the accounts of bash shell from k-shell This can be done in /etc/password file
Change bash from ksh fro the uer which we want to change save the file and exit before doing any modification in passwd file it is better to maintance a copy of that file
in bash the autoexe file is bash_profile in ksh the autoexe.file is .profile
When we are changing the shell the data and files in the former shell is available to later shell
Oracle memory
Oraccle memory is of 2types
.sga (system global area) or (share global area) .pga (process global area)
Sga is space reserver for oracle database
It is shared memory where the user can share the resource of sga sga basically contains three parts in fundamental method Db_buffer_cache
sharepool later one
db_buffer_cache shared pool
javapool and large pool sga contain five components like db_buffer_cache
sharepool logbuffer largepool javapool
db buffer cache we will stored recently used data in buffer cache.if the user request is present in in buffer cache it sends it to ueser .
sharepool it contain parssed sql information
it translates the sql statement to sql understandable language logbuffer it contains trasactional data
javapool it is used for jvm operations
sga should be more than 100mb sga should be minimum 100-200m
the size of sga con’t be more than the 1/3rd size of ram how to change sga size
sga size can be changed by chaging the parameter values in init.ora the total size of sga is determined by a parameter sga_max_size show parameter sga_max_size
we can also know sga size when we startup some data is displayed showing some sizes total shared global area 165007897
fixed size ---variable size database buffer ---redo buffer ---bouncing the database
INSTANCE:
Instance is combination of SGA and Oracle Background process.
• when we do startup oracle will allocate SGA and background process are started which are mandatory to run oracle. This is called Instance.
• Instance opens database files. Each and every thing is performed by Instance. • All the logical manips like creating, reading, writing, etc., are done by instance. • All the files are managed by instance.
• User cannot have access to files without instance • User just cannot to instance not to files.
• User is able to view and perform data(manips) through instance only. • Making things available to user is done by instance.
• Instance is nothing but ORACLE_SID. • We can create database with unique.
Three Phases When We Are Starting Databases:-1. Instance : At this phase Instance will allocate SGA 2. Mount: At this phase Instance opens way to database files 3. Open: Finally database is opened
When we shutdown the databse, Instance is closed and all the memory (SGA) is deallocated, now we can open the database files (datafiles,logfiles,control files). There are many ways to start the database:
1. We use this when we are creating or altering databse. This stage is used for maintainance of database ie., If we want to increase the size of datafiles, locations of files and if any issues occurred in database.
Sys: startup nomount; Alter database mount; Alter database open
2. Startup Open; Alter databse open;
3. Startup: It is used to open a database PGA(Process Global Area)
It is memory reserved for each user process connecting to oracle db. PGA is sessions private information. It is external to PGA. Every connection has one PGA. This is private to that connections. Memory is allocated when process is created and deallocated when process is terminated.
ORACLE PROCESSES
There are 3 types of processes. They are Server Processes
Client Processes Background Processes
Server Process : When this session is established, server process is created. Connects to oracle instance and is started when user establishes a session. To handle the requests of client process, user process, connected to instance server process is created of behalf of each users app can perform the one or more of following:
• Parse and execute the sql statements issued through the application (client process) • Returns result is such a way that the application can process the information Client Process: started at a time a databse user requests connection to oracle server.
• Client process is a process which is created when client software is started. • When we execute sql plus from $ prompt ,sqlplus becomes client process.
• The client proces is a process that sends message to a server, requesting the server to perform a task (service). Client program usually manage the user-interface portion of the application, validate the data entered by the user, dispatch requests to server programs and sometimes executes business logic, the client bases process is front end app that the user sees and interact with
Hand shake: When we start client process (ie., when we five sqlplus on shell prompt) before this process interact with instance, the server process interact with client process. This is called handshake.
Parent process: For every process there is a parent process. When we execute sqlplus, from shell then $prompt ID becomes parent process ID to sqlplus. ie., shell spanned a process……. Lsnrctl is a software which is executable in ORACLE_HOME/bin
LOCAL CLIENT PROCESS AND NON LOCAL CLIENT PROCESS Local Process: When the connection established in server through shell prompt, Then it is said to be local process.
Local Process: If the connection established out of the server, then it said to be Non-Local process
DBA provide user/passwd to APP developer to connect to database we have configure the database which we wish to connect in local pc. In this case client process is running on pc.
For each client process there is a server process we identify client process on server, when we start sqlplus as a local client, it is not identified when we start as a non-local client.
We indentify server process by oracle(It is a Key word)+{ORACLE_SID} Ex: ps –ef | grep oracledbsidnu
Output will be
Kittu 17148 17147 0 15:20 ? 00:00:00 oracledbsidnu(DESCRIPTION= (LOCAL=YES) (ADDRESS= (PROTOCOL=beq)))
Here 17148 is Process ID (PID)
And 17147 is Parent process ID (PPID)
Parent process ID: for non- local client process is 1 init. Init is helpful to establish a connection. These are client session (or) remote sessions. Oracle is a keyword. It is established in bin. It is in oracle engine.
* we run exe files in .profile by setting path in profile as PATH=$PATH:. ORACLE BACKGROUND PROCESS
Starts when oracle instance is started, these are used to run oracle database There are 2 tyes:
Mandatory SMON , PMON , CKPT , LGWR , DBWR Optional ARCH , Pnnn , Jnnn , LCK
The mandatory process are mandatory to run oracle. These are started automatically under SGA when we start oracle databse. These must be running in background as long as database is up
Naming convention for these process:- ora_process_ORACLE_SID EX: ora_smon_dbsidnu
ora_pmon_dbsidnu ora_ckpt_dbsidnu ora_dbwr_dbsidnu ora_lgwr_dbsidnu
RESPONSIBILITIES OF MANDATORY BACKGROUND PROCESS
These process started automatically when oracle Instance is started. Its life time is till the instance is opened. ORACLESID is the instance name.
*** There is a way to configure database name and Instance name differently SMON [ System Monitor]
It will be used in crash recovery and temp segment cleaning. It recovers after instance failure and monitors temporary segments and extensions. It wakes about every 5 min to perform house keeping activities smon must be always running for an instance. PMON [Process Monitor]
PMON recovers the resources of a failed process. If, MTS(shared server architecture) is being utilised, PMON monitors and restarts any failed dispaches or server process.
CKPT [Check Point]
It updates the headers of datafiles, control files with latest SCN(system change number) number.writes the checkpoint informantion to control files and datafiles headers. LGWR: [Log Writer]
Flushes the data from log buffer to redolog files, It writes logbuffer out to redolog files.
DBWR [DB Writer]
Flushes the dba from db buffer cache to data files, we can make multiple databuffer. It is for writing dirty buffers form the databse block cache to the databse data files. It only writes blocks back to datafiles on commit or when cache is full add space has to made for more blockes. We can create multiple dbwiters by defining a parameter in init.ora.
Parameter: db_writer_processes=2
Data reading from datafiles will be done by server process. Datawriting will be done by background process.
ARCHITECTURE OF BACKGROUND PROCESSES
When we issue a select statements, shared pool Converts the statements to sql understandable language, Then, it sends it ot db_cache. Then sp server process work is stopped. Now background processes starts working. If the sql statement related information is available in db_cache, it sends it to user. If not, it searches for the info in
datafiles and then sends to user.
when we issue a transactional statements like inserting,deletion,updation etc., then the copy of the data is maintained in 2 locations:
1. DB WRITER 2. LGWR
It stores in these 2 locations for 2 reasons
dbbuffer : for feature maintanence(faster access) logbuffer: It is for safety (recovery)
When we say commit then data will be sent to redolog files from log buffer by lgwr. The data in db_cache also sent to datafiles by dbwr but copy will be maintained in db_cache for feature maintainence
OPTIONAL PROCESS Arch: For Archeiving
Archeive process writes filled redo logs to the archive log locations. In RAC, the various arch processes can be utilised to ensure that copies of archieved redologs for each Instance are available to tha other instance in the RAC setup. They need for recovery We start optional process by defining the parameters in init.ora.
Log_archive_start=true - it start process
Log_archive_max_process=1 [Now it takes only one process. By default it takes 2 processes]
Pnnn: These are parallel store process to perform parallel DML activities. It can be used for parallel exe of sql statements or recovery. The maximum number of parallel processes that can be invoked is specified by initialising parameter
Parallel_min_servers=1 Parallel_max_servers=10
To stop these slaves: Parallel_min_servers=0 Parallel_max_servers=0
We provide some hints to oracle to use parallel mechanism. Jnnn: Job queue processes
To run scheduled jobs in the database Job_queue_process=1
Maximum processes=100 Lck: lock
This is available only in RAC instances. Meant for parallel server setups the instance lock that are used to shared resources between instances are hold by lock process.
SERVER PROCESS
In Oracle there are 2 technologies
shared server process or MTA(multi threaded architecture) dedicated server process
Dedicated server process:
One client process connected to one unique server process is said to be dedicated server process. Server process is complicated to One client. The server process is there, till there is client process. It may be idle(or) working. If 100 members are connected to database 3mb memory is needed. The Architecture we are using is dedicated server Process.
Shared server process:
Multiple clients connected to unique shared servers is said to be shared server process. It is olderone. The client process will connect to server process through bridge process called Dispatcher process. Dispatcher is mediator between client process and shared server process. We can Reduce the burden and saving some resources on the server. This process is defined in init.ora The advantage of shared server process is saving the resources of the system.
RAC: (REAL APPLICATION CLUSTERS)
A single database can be access to multiple instances. We have multiple servers for instances, but only one database to all instances. This is called RAC.
STANDBY DATABASE
Maintianing a copy of instance and database is standby database. The difference is in time log only.
DATABASE ARCHITECTURE
There are 2 types of databases Architectures. Physical
Logical
Physical Architecture is nothing but O/S level architecture. Files that are at o/s level are said to by physical architecture.
Physical:
1. datafiles,min(1) 2. redofiles, min(2) 3. controlfiles, min(1)
Select name from v$datafiles Select member from v$logfile Select name from v$controlfile Datafile: It stores actual data
Logfile: It stores transactions. The purpose of redolog files is recovery in case of failure. Controlfile: stores information and status of datafiles and redolog files, the size of control file is automatically take by system.
When we inserting data, if redo is filled then oracle starts reading to redo, after fitting this is again goes back to redo. This cycle goes on repeatedly.
If we enable arching log mode then before over writing the data to previous redlog files, it takes backup to different location.
How to identify whether a database is available or not? This is through oratab file in /etc folder. Ex: vi /etc/oratab
It stores database names and oracle homes only databases created through dbca are loaded manual creation of databases are to be updated manually to /oratab. It location is changed from one location to another in different O/S.
In solaris: /var/opt/oracle
LOGICAL ARCHITECTURE: Schema Object
Non – Schema Object
Schema is nothing but a user Seeded Databases:- Default databases
Ex: sys, system
The objects which reside in the schema are said to be schema objects. Ex: Table, view, index, synonym, procedure, package, function, database name, sequence, etc., The objects which are not associated with schema are said to be non-schema objects.
Ex: Tablespace, Roles
How to refer an object of schema?
1. Either we login to schema and access the object
2. From different login user. First the user has the permission to access that object, fallow the below syntax
Syn: schema object Ex: scott
To start database:
DATA DICTIONARY
1. Oracle will maintain entire system data into data dictionary of catalog.sql 2. System data is data which is required for functionality of database. 3. Data dictionary or catalog is set of tables, views and synonyms.
4. When we create database, some files and objects are created both physically & logically.
Physically: control files, redolog files, data files. Logically: Basetables tab$ fet$ obj$
Vet$ ts$ luster$ File$ idx$ v$
There tables are extracted when we run created data. we connot access these tables directly. It is very difficult to understand the data in these tables. There are some views to access these tables. These are created when we run catalog.sql
VIEWS
Tables dba_tables all_tables user_tables
Idx dba_indexes all_indexes user_indexes
Synonyms dba_synonym - -
-Views dba_views - -
-Sequences dba_sequences - - - -
Clusters dba_clusters - - - -
Database rows dba_db_links - - - -
Datafiles dba_datafiles - - - -
Oracle will update the activities in to base tables whatever the ddl activities done by us. Database engine will take this responsibility. Whole oracle is working based on there base tables.
we should use only select statement only system and sys has dba privileges
dba_ :- It will display everything in the database (all users info) every thing user_ :- It will display only logged in users information only own
all_ :- It will display logged in users info and the objects which have access to that user own+access
Oracle engine will gives access to 9 tables to users by default. All these base tables are objects to know about the objects and the dba objects.
DICTIONARY VIEWS:
dba_objects To know the objects in a database dba_tables T know about tables info
dba_indexes To know about index info
dba_ind_columns To know about the info of index applied in column dba_synonym To know about synonym info
dba_ views To know about views info dba_sequences To know about sequences info
dba_clusters To know about source code fro function procedure,packages etc.,
dba_constraints To know info about constraints
dba_cons_column To know about constraint columns info dba_tab_column To know about table column info. session_privs To see session privileges
desc session_privs only one column
when we give grant rde only one privilege is accessed create session
Resource: create table, unlimited table space , cluster,sequence, procedure, trigger, types, operator, index type.
user_tab_privs To see user tables privileges user_sys_privs To see users privileges
Dba_free_space To see tablespace size and freespace Dba_tablespace To see tablespace info
Dba_data_files To see datafile info V$TABLES:
The views started with v$ are said to be dynamic performers V$database Database info
V$datafile Datafile info V$controlfile Controlfile info V$logfile Log file info V$version Version V$session Session info
SQL: Structured Query Language
SQL*PLUS: This will work only in oracle.They are used to format output i :- To insert a statement in buffer ie., adds new line to the sql statement a :- Appends new words to sql statement
syn:- c/<search string>/<replace string> cl buff: It will clear sql buffer (Clear Buffer)
save: by using this, we can save sql statement. We refer these statements as sql statements. To run the sql scripts from any location, we had to mention the location of sql scripts ie., ORACLE_PATH in bash_profile
Export ORACLE_PATH = /tmp:/oraAPP:/oraAPP/kittu Usuallly these sql scripts are saved in the location from where we fire sqlplus. How can we capture some output in sql?
In unix, it is possible by script command In sql, it is achived by using spool command Syn:- sql> spool <filename>
sql> statements - - - sql> spool off
Ex:- sql> spool a.out sql> select * fromtab;
o/p along with statements are stored in a.out. sql> desc abc
sql> spool off How to format column data in sql
To know line size :- show linesize; To set line size: set linesize 200
To set column size in numeric format :- column empno format 9999 ie., it displays column empno with 4 digits
To set column size in alphabet format:- column ename format a15 Development :- Designing side is said to be development. When we work on new project we are said to be working on development side
Production :- After everything is designed and tested then it is deployed into production
Query to create
user:-Syn:- create user <username> identified by <password> Ex:- create user xyz identified by xyz;
Granting privilages to
user:-Syn:- grant connect, resource to xyz; Droping
To view the source code if
views:-Syn:- select text from dba_views where view_name=’xyz’; Granting dba to
user:-Syn:- grant dba to <username>; Revoke privilage from
user:-Syn:- revoke <privilage> from <username>; QUERIES
Views:- To see view information
Select view_name,owner,text from dba_views; Sequences:- To see sequence number
Select sequence_number,sequence_name from dba_sequences; Synonym:- To see synonym information
Select owner,synonym_name,table_owner,table_name from dba_synonyms; Indexes:- To see index information
Select owner,index_name,table_name from dba_indexes To see index column
Select index_name,table_name,column_name from dba_ind_column; To see row
Select rowid from <tablename>;
Select owner,constraint_name,constraint_type,table_name from dba_constraints; To see constraint
Select owner,constraint_name,table_name,column_name from dba_cons_column;
Select owner,table_name,tablespace_name,status from dba_tables;
Select username,default_tablespace from dba_users;
Select tablespace_name,status from dba_tablespaces;
Select name,dbid,created,open_mode from v$database;
V$version has only one column banner Select * from v$version
Select file_name,tablespace_name,bytes/1024/1024,online_status,autoextensible from dba_data_files;
To see datafile in mount stage based on ts index :-
Select name,ts#,status,bytes/1024/1024 from v$datafile where ts# = 0;
Select owner,procedure_name from dba_procedures; To see source
Select text from dba_source where name=’name’;
Select owner,object_name,object_type from dba_objects;
Select owner,object_name,object_type from dba_objects where object_type = ’FUNCTION’;
Source
Select text from dba_source where name = ‘FUNCTIONS‘;
Select owner,object_name,object_type from dba_objects where object_type = ’PACKAGE’;
Source
Select text from dba_source where name = ‘---‘;
Select owner,trigger_name,trigger_type,table_name,column_name from dba_triggers; Source code
Select text from dba_source where name= ‘---‘; Control
Select name,status from v$controlfile; Log
Select member,group#,status from v$logfile;
To seee table
Select grantee,owner,table_name,grantor,privilage from user_tab_privs; To see user
TABLESPACE MANAGEMENT
Tablespace is a logical structure which binds the objects. Tablespace is a container for data files i.e., tablespace is a collection of one or more data files. One database has minimum one tablespace. Tablespace always associated with one or more data files. Database is a collection of tablespaces. It is one to many relationships.
- Size of database is size of tablespace. - Size of tablespace is size of data files.
- As we increase tablespaces, database size increases. - We can’t create tablespace without data file.
- Each tablespace has its own data file.
- Redo logs and control files will never grow in size. So we never consider these files in the size of tablespaces. These are key structures.
- A data file cannot be shared across tablespace Syntax to create
tablespace:-Create tablespace <tablespacename> data file <location> size <size>;
Ex:- create tablespace ts01 data file ‘/oraAPP/kittu/ts01.dbf’ size 10m; How do we make tablespace offline?
Syn: - alter tablespace ts01 offline;
This means we can’t access the data in the tablespace even we can’t perform select statement.
How do we make tablespace online?
Syn: - alter tablespace ts01 online; To see tablespace name, filename, size of data file:
Select tablespace_name, file_name, bytes/1024/1024 from dba_data_files.
To see size of database:
Select sum (bytes/1024/1024) from dba_data_files; Dropping a tablespace:
Before dropping tablespace, it is better to make tablespace offline. Syn:- drop tablespace ts01;
In this case only tablespace is deleted but the data files are maintained in o/s level. To delete the files in o/s level i.e. contents and data files (contents means objects i.e. tables, views, etc) in TS.
Increasing the size of
tablespace:-We can increase the size of tablespace in two ways. 1) we can increase the size of data file
Syn:- alter database datafile ‘/oraAPP/kittu/ts01.dbf’ resize 50m;
2) we can add a datafile
Syn:- alter tablespace ts01 add datafile ‘----‘ size 10m; How can we assign tablespace to new user?
Syn:- create user <username> identified by <password> default tablespace <ts name>
Ex:- create user msb identified by msb default tablespace ts01; How can we assign tablespace to existing user?
Syn:- alter user <username> default tablespace <ts name> Ex:- alter user msb default tablespace chinni;
Not important
points:-- If datafile is very big, oracle encounters some issues. So we use datafiles max. of 5GB size.
- If we doesn’t mention datafile location, it saves that file in ORACLE_HOME/DBS directory
- When we enter data to a datafile more than its size, it shows below error. Error:- unable to extend table msb.emp by 128 in tablespace chinni; To see free space of
Syn:- Select sum(bytes/1024/1024) from dba_free_space where tablespace_name=’CHINNI’;
Nfs mount point:- ( Network file system)
All the mount points that are available to us are said to be local mount points. Ex:- /oraDB , /oraAPP , /stage , /u001 etc.
A mount point which is placed in another server is said to be NFS mount point. Syn:- mount –t nfs 192.168.0.16:/stage
This nfs creation is done byunix admin. Maintenance of
datafiles:-- Physical architectureis maintained by and managed by ORACLE ENGINE. There are two ways in work nature:
1) Proactive:- the solution before problem exists. 2) Reactive :- the solution after problem exists. - we can increase the datafile size automatically. - This is possible by making autoextend on.
To see datafile and its size
dynamically:-Syn:- select filename, bytes/1024/1024 from dba_data_files where tablespace_name =’ts’
how can we specify datafile size autoextend upto some
Syn:- alter database datafile ‘---‘ autoextend on maxsize 200m;
To see maximum size of datafile:- (autoextend)
Select file_name,autoextensible,maxbytes/1024/1024 from dba_data_files;
Increase the size of TS by certain
Alter database datafile ‘---‘ autoextend on next 10m maxsize 100m;
Renaming a It is possible only in 10G
Syn:- alter tablespace chinni rename to babu;
In 9i, it is not possible to rename a tablespace. To perform this we had to follow the below steps
- create new tablespace
- move all tables from old TS to new TS
Syn:- alter table <tname> move tablespace <new TS>; Ex:- alter table emp move tablespace venki;
To see table and tablespace
Select table_name,tablespace_name from dba_tables; To see how many datafiles in
Select count(*) from dba_tables where tablespace_name = <TS name>’;
-- > When we move table from one TS to another TS, the table will be maintained inuser.
BIGFILE TABLESPACE:
It is new in 10g. by this we can create very big tablespace of terra bytes size. Maximum size is 4 terrs bytes.
Syn:- create bigfile tablespace ts01 datafile ‘---‘ size 10g; Renaming a
datafile:-To rename datafile we had to follow the below steps. - make tablespace offline
- rename datafile in o/s level
syn:- mv < old filename> <new filename> - rename file in sqllevel
syn:- alter tablespace venki rename datafile ‘<oldname>’ to ‘<newname>’;
- make tablespace online
we rename file in sqllevel to update it in data dictionary. To see user and
select username,default_tablespace from dba_users; STARTUP AND SHUTDOWN
STARTUP:
There are different phases in startup:-1) Instance
allocation:-Memory for SGA is allocated and background process starts.memory is allocated by reading parameters from init.ora. this is instance.
Instance started 2) Mount
stage:-Instance opens control file Database mounted
3) Database
openstage:-Database is opened. i.e. instance opens datafile and redo logs through control files.because control file contains info of datafiles and redolog files.
Three methods to start database:-I
method:-sql> startup
In this method all the three phases are executed at a time. II method:-
sql> startup nomount
In this stage, only instance is allocated. Sql> alter database mount;
In this stage the database is mounted. i.e second phase. Sql> alter database open;
In this stage the database is opened. i.e third phase. We issue startup nomount to perform 2
things:-1) creation of database 2) creation of control file III method:-
sql> startup mount
In this stage, phase1 and 2 are executed. Sql> alter database open;
Database is opened.
Generally we open the database in Mount stage to perform maintenance activities like renaming datafiles, default tablespaces, redologs etc. In this stage we can’t access dba_ views. We access only v$ views.
We can’t make system tablespace, undo default tablespace offline. How can we rename datafile of system TS:
We can’t make system TS offline in DB open mode, because if we make it offline we can’t access dictionary views i.e. we can’t access users, tables etc. so we rename the datafile of system TS in mount stage only.
Steps:- open database in mount stage startup mount
move or rename system TS datafile in o/s level move or rename system TS datafile in sql level
Syn:- alter database rename file ‘<old>’ to ‘<new>’;
we can also rename undo TS in mount stage only, because we can’t make it offline. To know tablespace datafile names in mount stage:
Select ts#,name from v$tablespace where name=’SYSTEM’; Select name fom v$datafile where ts#=0;
We can rename datafile in 2 levels:-1) Database level
mount stage 2) Tablespace level
open stage
i.e. making it offline and rename it.
Undo tablespace
:-It is for undo operations. :-It will maintain old data till we issue commit. SHUT DOWN:
There are 4
methods:-1) shutdown (or) shutdown normal 2) shutdown immediate
3) shutdown abort
4) shutdown transactional
Shutdown Immediate: It is reverse of startup Phase1Database closed
All the connections(sessions) connected to instance are killed. All the pending transactions are rolled back.
A check point will happen and dirty buffers will be flushed to datafiles. Datafiles and redolog files are closed.
Phase2Database dismount stage (Database dismounted) Control file will be closed.
Phase3Instance deallocation (Instance closed)
Background processes are killed and memory for SGA is deallocated. It does not wait for the users to disconnect from the DB.
Difference B/W Shutdown and Shutdown Immediate:-- Shutdown Immediate will kill all the existed users
- Shutdown will wait for the users to get disconnected themselves. Alert Log
File:-All the startup and shutdown activities will be captured into a file called alert log file. The extension is <alert_dbname.log>.
By default it is stored in /ORACLE_HOME/RDBMS/LOG/..
Use tail –f <filename> to display alert file content and update in incremental order.
Shutdown normal:- (Shutdown)
When we use this option, all the steps which occurs in Shut Immediate happens, except the first 2 steps.
It waits for the logged in users of database to quit.
It is default option which waits for users to disconnect from the database. Further connections are prohibited.
The database is closed and dismounted.
The Instance is shutdown and no Instance recovery is needed for the next DB startup.
Shutdown abort:- (Shut abort)
The fastest possible shutdown of the DB without waiting for calls to complete or users to disconnect
Uncommitted transactions are not rolled back
Sql statements currently being processed are terminated
All users currently connected to DB are implicitly disconnected and next DB starting will require Instance recovery.
We use this option if a background process terminates abnormally and when high voltage of power occurs
It just deallocates the Instance
Shutdown transactional
This option is used to allow active transactions to complete first i.e. it will let the current transactions to be finished
It doesn’t allow client to start new transactions
Attempting to start new transaction results in disconnection
After completion of all transactions, any client still connected to Instance is disconnected
Now the Instance shuts down
The next startup of database will not require any Instance recovery. It will kill users who are idle
Startup
Restrict:-We use this option to allow only oracle users with the Restricted session system privilege to connect to database. i.e. only the DBA can have access to DB. We can use alter command to disable this restrict session feature.
Syn:- alter system disable restricted session
Actually we use this when we are in maintenance. So we can’t give access of the database to other users.We can enable restrict session feature after logging to database as sys user.
Syn:- alter system enable restricted session Startup Force:- (Shutdown abort + startup)
Shutdown the current oracle Instance with shutdown abort mode before restarting. Force is useful while debugging and under abnormal circumstance. It should not normally be used.
Read only
tablespace:-Read only tablespace allows users to do only reads from the tables with in it. No data manipulation is allowed. This read only option causes the database not to work to these files once their tablespace is altered to read only. This allows users to take advantage of media that allows for readonly operations. The major purpose of making a tablespace read only is to eliminate the need to perform backup and recovery of large, static portion of a database. we can drop items, such as tables, indexes from read only tablespace but we can’t create (or) alter objects in a read only tablespace.
SYSAUX TABLESPACE
It is new in Oracle 10g. it is used to store database components that were stored in system tablespace in prior releases of database. It was installed as an auxiliary TS to SYSTEM TS. When we create the database, some database components that formerly created and used separate tablespaces row occupy the SYSAUX TS.
If the SYSAUX TS becomed unavailable, core database functionality will remain operational. The database features that use the SYSAUX TS could fail or function with limited capacity.
CONTROL FILE MANAGEMENT
Every oracle database has a control file A control file is a small binary file that records the physical structure of database. It includes..
Database name
Names and location of associated datafiles and online rdo’s (SCN), tablespace info, archive log info
Timestamp of database creation Current log sequence number Check point information
The control file must be available for writing by the oracle database server whenever the database is open
Without control file, the database cannot be mounted and recovery is difficult It is created at the time of creation of database
We can create maximum of 8 control files
We cannot have any control over control file and its size will be determined at the timeof database creation
It is very small in size and it is static and it can’t be altered
Actually we need more than one control file when the failure of first control file. If we have ‘n’ number of control files, all the files are same in size and contain
same info
How can we add one more control file to database? Shut down the database using shut immediate mode
Make a copy of existing control file in same location or in another location Syn:- cp c1.ctl c2.ctl
Or
cp c1.ctl /oraDB/kittu/c2.ctl
Add the newly created control file location using control_files parameter in init.ora file.
Ex:- Control_files = ‘/oraDB/kittu/c2.ctl’ Start the databse
Note:- If we add control file without copying it, it searches for that file when we start the database. So, oracle doesn’t read that file.
How can we remove control file?
Remove the control file location in init.ora file (parameter file) After that remove the file in o/s level.
The dictionary view to get the control file information Select block_size,file_size_blks from v$controlfile Block_size=16384 and file_size_blks=370
((blocksize * filesizeblocks)/1024)/1024 -- > we get control file size OR
By firing ls –l command in unix , we get the bytes Minimum control files 2 to 4
V$parameter:- Lists status and location of all parameter.
V$controlfile_record_section:- Provides information about the control file record status Show parameter control files:- Lists names,status,location of control files
REDO LOG FILE MANAGEMENT
Each oracle has redo log files. These redo log files contains all changes made in datafiles
The purpose of RDO is if something happens to one of the datafiles, a copy of datafile is maintained in RDO’s which brings the datafile to the state it had before it became unavalible. i.e. it is used for recovery of data.
The size of RDO is static. We determine its size in the creation of database. We can’t change its size unless in the maintenance.
The idea is first to store the transactional data in log buffer to reduce i/o retention. When a transaction commits (or) check point occurs, the data in log buffer must be flushed into disk for the recovery. It is done by LGWR
The redolog of database contains one or more redolog files. The database requires a minimum of two files to guarantee that one is always for writing while the other is being archived ( if the database is in archivelog mode)
LGWR writes to redolog files in a circular fashion. When the current redolog file fills, LGWR begins writing to the next available redolog file. When the last available redolog file is filled, LGWR return to the first redolog file and writes to it starting the cycle again. In this case if the first RDO is overwritten the data is lost. This happens when the database is in NOARCHIVELOG mode.
This reading of data into another redolog file after filling the former one is said to be log switch process.
It is a point at which the database stops writing to one redolog file and begins writing to another file. Oracle DB assigns each redolog file a new log sequence number everytime whenever a logswitch occurs and LGWR begins writing to it. When the database archives redolog files, the archived log retains its LSN. A redolog file that is cycled back for use is given the next available LSN.
Archive Log Mode:
If the database is in logarchive mode, the database makes sure that online redologs are not overwritten. The filled redologs are archived (or) saved into another location(where we specify the location init.ora file)
We set this location in init.ora by using log_archive_dest parameter Noarchive Log Mode:
If the database is in noarchivelog mode, online redologs can be overwritten without making sure that taey saved or not. This implies that a database cannot be recovered even if backups were made.
How can we know the database is in archive log (or) noarchive log mode: - select log_mode from v$database;
(OR) - archive log list
Arch background process:
Arch is the archiver. Its task is to automatically archive online redologs so as to prevent them from being overwritten.
The archiver background process starts if the database is in archivelog mode and automatic archiving is enabled. i.e. taking the data in RDO to some other location is said to be archiving.
How can we convert database to archivelog mode: To start the archivelog mode
• Shutdown the database
• Define the parameters in init.ora parameter initialized file • The parameters are –
• log_archive_start=true -- > Necessary in 9i only. Optional in 10g. • log_archive_dest = ‘—‘ -- > locator where we need to archive log files • log_archive_format= %s arc
• The files which are archived will be saved in the format as we define in parameter. Syn:- alter system set log_archive_format =
---• Start the database in mount stage
• Now fire the command to switch to archivelog mode. • Ex:- alter database archivelog
Archived to Noarchivelog mode:-• shutdown immediate • startup mount
• alter database noarchivelog • alter database open
Note:-• We can do archivelog and noarchivelog when the database is in mont stage only. Because we must regular the activities to control file and we done this activity in database level
• We usually refer redolog file group other than redolog file.
Why we need more than one redolog file in a
group:-This is because to safeguard against damage to any single file. When we create multiple redolog files, LGWR concurrently writes the same redolog information to that files, thereby eliminating a single point of redolog failure. Other files in a group are said to be ___________. All are same in size and contains same data.
V$log_history Contains log history
V$log It contains group information
V$logfile It contains logfile or members information Select members ,group# from v$log;
It gives groupname and no.of files in a group select member, group# from v$logfile; It returns groupname,logfilenames
How can we create a Redo log Alter database add
Logfile group ’/oraAPP/redo1.rdo’ size 5m; (or)
Alter database add logfile group 1 ( ‘/oraAPP/redo1.rdo’, ’/oraAPP/redo2.rdo’) size 10m;
How can we drop redo log
Alter database drop logfile group1; How can we add a member to existing
Alter database add
Logfile member ‘/oraAPP/redo3.rdo’ to group; How can we drop a
Alter database drop
Logfile member ‘/oraAPP/redo3.rdo’; Why we need to drop log groups and members:
To reduce the no. of groups in an instance redolog. In the case of disk failures and a file must be located in improper location.
Before drop a redolog group (or) member we had to perform the below We can make the status of group as inactive (or) active. Because we can’t drop the current running group.
We get the status of group by
select group#, archived,status from v$log; So we forced oracle to switch the curren status to another group . This is possible by using a command like.
alter system switch logfile; Now current status of group changes.
*we can do this activity when the database is completely opened.
How can we change binary data to text formate: Strings –a c1.ctl>a
***When we are in mount stage and archive log is enabled. But it shows automatic archival as disabled .when we fire archive log list .
Clearing a redo log
alter database clear logfile group3; It will reinitialized the damaged group Clearing unarchived log
file:- Alter database clear unarchived logfile group3; This can be done without shutting down the database also
How can we rename (or)relocates redo log file members:-1) shut down the database
2) copy redo log files to new location (or) rename it mv old new
3) startup mount
4) alter database rename file ‘---‘ to’---‘; 5) alter database open;
Active and inactive redo log files (or) groups:
Redo log files that are currently capturing data are current redo logs.
Redo log files that is not current redo log group. It is needed for crash recovery . Redo log group that has never been written to is said to be unused rdo.
Redo log files after active are said to be inactive rdo’s. Adjusting the no.of archiver process:
alter system set log_archive_max_processes=3; Q) why we need more than 2 log groups ?
Assume that we have 2 log groups. If one log group is in current state and the other group is archiving. After first log group is also filled. But the 2 group is still archiving. Then lgwr doesn’t know to which group it had to write the data generally archiving is slow. So, inorder to prevent this , we need one more group.
To stop archive log process :-
Log_archive_start=false
P FILE AND SP FILE
P-File: (parameter file):
It is init+oracle_sid.ora file. It is present in ORACLE_HOME/dbs. This file is text file and is editable.
From 9i , oracle introduced a new file, it is SPFILE. SP-file(server parameter file):
It is binary file and is not editable. If the database contains both pfile and spfile. Oracle starts using spfile only. Because it provides dynamic allocation.
What is the solution if init file name is not in proper form ?
If the init file is x.ora. then start the database using following command. Syn:- startup pfile= /ORACLE_HOME/dbs/x.ora
How to create sp file ?
select * from dba_temp_files;
We create spfile from pfile using the below command Syn:- create spfile from pfile;
Parameter to show spfile ?
Show parameter pfile (or) show parameter spfile
If the value column of this parameter shows some value, it indicates that database starts using spfile.
How to create pfile from spfile ?
Syn:- create pfile from spfile; How to edit spfile ?
Oracle will not support to edit spfile, to edit it follow the below steps 1) Drop the existing spfile
2) Edit in init.or
3) Create new spfile from pfile 4) Start the database
How can we delete a database ?
In 9i to drop a database, delete the datafiles, rdo’s controlfiles etc.,
But in 10g , new feature is introduced to drop a database follow the below steps. 1)open database in restrict mode
SQL> startup restrict 2)drop the database
SQL> drop database;
Then automatically all the database related files are deleted except init.ora ALTER
ALTER SYSTEM set parameter value scope=[spfile/memory/both] Spfile the new parameter value will be updated only in spfile
Memory the new parameter value will be updated only in database
Both the new parameter value will be updated in both spfile and database. Audit
The files contains information, if we start sqlplus as ‘sys as sysdba’. It also updates when we connected from another user to ‘sys as sysdba’.
Who logged in started the database is stored. It contains o/s user name, database name, system name, oracle_home, database user, privilege, time etc.,
Whenever “we connect to sys user it creates audit files”. Ex:- ora_3702. and
TABLESPACES
1) Permanent tablespaces 2) Undo tablespaces 3) Temporary tablespaces
in 10g ,we cannot have more then 65536 tbs. Permanent tablespaces:
The tablespaces which are used to store the data permanently are said to be permanent tablespaces.
Ex:- system, Sysaux, Etc.,
Undo tablespaces :
Every oracle database must have a method of maintaining information that is used to rollback (or) undo,changes to the database. Such information consists of record of actions of transactions, primarly before they are committed. Such records are collectively referred as undo.
Undo tablespace is used to store undo records of database. i.e., uncommitted transactions(pending data). We create undo tablespace at the time of database creation. If there is no undo tablespace available, the instance starts but uses the SYSTEM tablespace as default undo tablespace. It is not recommended option. So create undo tablespace at the time of database creation (or) after that by setting parameter value[undo_tablespace]
Creating undo tablespace:
Create undo tablespace undots01
Datafile ‘/oraAPP/kittu/db1/undo1.dbf’ size 50m;
We can create multiple undo tablespaces. But there is no use, because we use only on undo tablespace.
How the data stored in undo tablespaces?
If the table contains the salary 1000 for some employees. If we update the salary 1000 to 5000. Then the records which contains salary 1000 will be stored into undo tablespace and salary 5000 will be updated into table. If we do commit they remain. Otherwise 1000 will come back to the table
We can view the tablespace type from dba_tablespaces
Select tablespace_name,contents from dba_tablespaces; To know which undo tablespace is assigned to database.
Show parameter undo (or)
From dictionary view database_properties How to set undo tablespace fro sql prompt?
Alter system set undo_tablespace=’UNDOTS01’;
If we set this it is available only for that session. If there is sp file it will be permanent to database because sp file allows dynamic alloction.
If there is no spfile we need to specify it in. Dropping undo tablespace:
Drop tablespace undots01; Resizing undo tablespace:
We can resize undo tablespace in 3 ways. adding a new datafile.
extend the size of existing datafile
Some process as we done for permanent tablespaces. Renaming undotablespaces:
Similar to permanent tablespaces. Temporary Tablespaces
Temporary tablespaces are used to manage space for database sort operations and for sorting global temporary tables.
1000 UNDO TS 1000 updated to 5000 User TS Old data Rollback
If we join 2 large tables, and oracle cannot do the sort in memory(see SORT_AREA-SIZE) initialization parameters, space will be allocated in a temporary tablespace for doing the sort operation. Other sql operations that might require disk sorting are
create index, Analyze, Select distinct, Order by, Group by
The DBA should assign a temporary tablespace to each user in the database to prevent them from allocating sort space in the SYSTEM tablespace.
TEMP FILES:
Unlike normal datafiles,tempfiles are not fully initialized when you create a temp file, oracle only writes to the header and last block of the file.
This is why it is much quicker to create a temp file than to create a normal database file. Temp files are not recorded in database’s control files. The implies that are one can just recreate them whenever we restore the database (or) after deleting them by accident.
One cannot remove datafiles from a tablespace until we drop entire tablespace. However, one can remove a tempfile
View:- dba_temp_files
Syn:- alter database tempfile
‘/oraAPP/temp1.dbf’ drop including datafiles;
If we remove all temp files from a temporary tablespace, you may encounter. Error ORA-25153 temporary tablespace is empty
Use the below syntax to added temp file to temporary tablespace Syn:- alter database temp
Add tempfile ‘/oraAPP/temp02.dbf’ size100m; How can we create a temporary tablespace ?
Create temporary tablespace temp Tempfile ‘/oraAPP/t1.dbf’ size 50m; How can we assigns a temporary tablespace to database ?
Alter database default temporary tablespace temp; How can we know the temporary tablespace information which is assigned to database?
create user x identified by y Temporary tablespace temp; (or)
alter user x temporary tablespace temp
How can we know to which user, which tablespace is assigned ? From dba_users
select username,temporary_tablespace from dba_users;
USER- MANAGEMENT
We connect to database as user only. Creating user:
Syn: Create user username identified by password; Ex:-create user kittu identified by kittu;
Changing password:
Ex:-alter user kittu identified by ramu; Lock the user:
Syn:-alter user username account lock; Unlock the user:
Syn:- alter user username account unlock; Password expire:
Syn:- alter user username password expire; Assigning default tablespace to
user:-Syn:- alter user kittu Default tablespace chinni; Assigning temporary tablespace to user:
select * from dba_temp_files;
Syn:- alter user kittu Temporary tablespace chinni; Dropping user:
Syn:- drop user kittu cascade;
PRIVILAGES AND ROLES Privilege:
Privilege is a right to execute a particular type of sql statement (or) to access another user’s object.
(or)
Privilege is right to perform a specific activity. A privilege can be assigned to user (or) a role
Privileges are of two 1)System privileges 2)Object privileges
