Database Administrator //* Lession 1 Tablespace Management *// select * from dba_tablespaces; desc selectdba_tablespaces;tablespace_name,file_name from dba_data_files; create tablespace student 'C:\oraclexe\app\oracle\oradata\xe\student.dbf'datafile size 100 m; select tablespace_name,file_name from dba_data_files; alter tablespace student add 'C:\oraclexe\app\oracle\oradata\xe\student_1.dbf'datafile size 100 m; select tablespace_name,file_name from dba_data_files; desc dba_tablespaces; desc selectdba_data_files;tablespace_name,file_name,bytes/(1024*1024) m from dba_data_files; alter tablespace student (initial 10k next 10k minextents 2 maxextents 20 pctincrease 50); alter tablespace student offline; select tablespace_name,status from dba_tablespaces; alter tablespace student online; alter tablespace student read only; select tablespace_name,status from dba_tablespaces; alter tablespace student read write; alter tablespace student temporary;//(if tablespace made temporary permanently object (table,index,etc) cannot be created in that altertablespace)//tablespace student rename to college_student; alter tablespace college_student rename to student; alter tablespace student offline; alter tablespace student online; alter database rename file 'C:\oraclexe\app\oracle\oradata\xe\student_1.dbf' to 'C:\oraclexe\app\oracle\oradata\xe\Student\student_01.dbf'alteralterclear'C:\oraclexe\app\oracle\oradata\xe\Student\student_01.dbf';screen;tablespacestudentonline;databasedatafileresize 200 m; alter tablespace student drop autoextendalterautoextendalterselectautoextendalterselect'C:\oraclexe\app\oracle\oradata\xe\Student\student_01.dbf';datafiletablespace_name,file_namefromdba_data_files;databasedatafile'C:\oraclexe\app\oracle\oradata\xe\student.dbf'onnext10mmaxsize20m;*fromdba_data_files;databasedatafile'C:\oraclexe\app\oracle\oradata\xe\student.dbf'off;databasedatafile'C:\oraclexe\app\oracle\oradata\xe\student.dbf'on;Page1
Database Administrator create table student (sno number(2),name varchar2(20)) tablespace student; create table doctor (sno number(2),name varchar2(20))tablespace student ; alter table doctor allocate selectgrant'C:\oraclexe\app\oracle\oradata\xe\student.dbf');extent(datafileunlimitedtablespacetosatish;owner,table_name,tablespace_namefromdba_tables where selecttable_name='DOCTOR';t.table_name,t.tablespace_name,df.file_name from dba_tables t,dba_data_files df where table_name='DOCTOR' and select'C:\oraclexe\app\oracle\oradata\xe\physics.dbf'create//*selectselectselectselectselectselectdropdropt.tablespace_name=df.tablespace_name;tabledoctor;tablespacestudentincludingcontents;*fromdba_tablespaces;*fromdba_data_files;*fromdba_free_space;*fromv$tablespace;*fromv$datafile;file_name,maxbytes/(1024*1024)fromdba_data_files;Lession2ManagingStorageParameter*//tablespacephysicsdatafilesize1m;tablespace_name,status,autoextensible,bytes/(1024*1024) from alterdba_data_files;databasedatafile 'C:\oraclexe\app\oracle\oradata\xe\student.dbf' autoextend on; create table physics (sno number(2),name varchar2(10)) tablespace physics ; alter tablespace student add 'C:\oraclexe\app\oracle\oradata\xe\engineer.dbf'datafile size 5 m; alter table engineer allocate select'C:\oraclexe\app\oracle\oradata\xe\engineer.dbf');extent(datafilet.table_name,t.tablespace_name,df.file_name from dba_tables t,dba_data_files df where table_name='ENGINEER' and createt.tablespace_name=df.tablespace_name;tabletest(Anumber(2))storage (initial 10k); select * from user_segments where segment_name='ENGINEER'; selectselectselectselectselectsegment_name='ENGINEER';tent/(1024*1024),max_extents/(1024*1024)segment_name,tablespace_name,bytes/(1024*1024),initial_extent/(1024),next_exselectfromuser_segmentswhere*fromdba_segmentswheresegment_name='TEST';*fromdba_extentswheresegment_name='ENGINEER';*fromdba_tableswheretable_name='ENGINEER';*fromdba_tablespaceswheretablespace_name='STUDENT';tablespace_name,file_name,file_id,bytes/(1024*1024)MBfromPage2
Database Administrator insertdescdba_data_files;ENGINEER;intophysics values(10,'AMITKUMAR',' a s t r o l o g y','A u s t r a l i alterdesca');physics;tablephysics modify (subject char(1024),location char(1024)); select * from dba_free_space where file_id=7; select tablespace_name,bytes/(1024*1024) MB from dba_free_space where alterselectcommit;tablespace_name='PHYSICS';*fromengineer;tablephysicsadd(subject varchar2(1024),location varchar2(1024)); alter database datafile 'C:\oraclexe\app\oracle\oradata\xe\engineer.dbf' resize 2 m; select file_name,tablespace_name,autoextensible,maxbytes/(1024*1024) MB from dba_data_files where tablespace_name like 'PHYSICS'; select tablespace_name,file_id,count(*),max(blocks) Maximum ,min(blocks),avg(blocks),sum(blocks) from dba_free_space where tablespace_name='STUDENT' group by tablespace_name,file_id; alter database datafile 'C:\oraclexe\app\oracle\oradata\xe\student.dbf' resize 2 m ; alter database datafile 'C:\oraclexe\app\oracle\oradata\xe\student.dbf' autoextend off; select tablespace_name,file_name,bytes from dba_data_files; select * from engineer; desc alterphysicsdatabase datafile 'C:\oraclexe\app\oracle\oradata\xe\physics.dbf' autoextend on; alterselectdba_segmentsselectalterdba_segmentsselectdescoptimalcreatesegment_type='DIFFEREDselect//*ntssegment_name,tablespace_name,initial_extent,next_extent,min_extents,max_exteselectfromdba_segmentswheresegment_name='PHYSICS';Lession3ManaginigRollbacksegment*//segment_name,segment_typefromdba_segmentswhereROLLBACK';rollbacksegmentrbstablespacestudentstorage(initial10knext10k20kminextents2maxextents5);dba_segments;segment_name,segment_type,tablespace_name,max_extentsfromwheretablespace_name='STUDENT';rollbacksegmentrbsstorage(maxextents10);segment_name,segment_type,tablespace_name,max_extentsfromwheresegment_name='RBS';*fromdba_segmentswhereowner='SATISH';rollbacksegmentrbsonline;Page3
Database Administrator alter rollback segment rbs offline; select * from user_segments where segment_name='RBS'; select * from dba_rollback_segs; select * from v$rollstat; select * from v$rollname; //* Lession 4 Managing Users and Resources *// select username,account_status from dba_users; select tablespace_name,status from dba_tablespaces; create user dbaadmin identified by dbaadmin default tablespace users quota 10m on users quota 10m on system; grant connect,resource to dbaadmin; select username,account_status from dba_users; alter user dbaadmin identified by dbaadmin; create user architect identified by architect; drop user architect cascade; desc selectdba_profiles;profile,resource_type,resource_name from dba_profiles; select profile,resource_type,resource_name,limit from dba_profiles where createprofile='DEFAULT';profilelogitech limit failed_login_attempts 3 password_life_time 60 password_reuse_max 10 password_grace_time 7; select profile,resource_type,resource_name,limit from dba_profiles where //*descdescdescdescdescdescdescdescdescdescdescalteralterprofile='LOGITECH';systemsetresource_limit=True;systemsetresource_limit=False;dba_users;all_users;user_users;dba_ts_quotas;user_password_limits;user_resource_limits;dba_profiles;resource_cost;v$session;v$sesstat;v$statname;Lession5MnagingResourcewithProfile *// create profile satish_admin limit failed_login_attempts 3 password_life_time 60 password_reuse_max 10 password_grace_time 7 sessions_per_user 1 connect_time 560 cpu_per_call 500; select profile,resource_name,resource_type,limit from dba_profiles where alterprofile='SATISH_ADMIN';profilesatish_admin limit logical_reads_per_session 2000 cpu_per_call default logical_reads_per_call 100;Page 4
Database Administrator select profile,resource_name,resource_type,limit from dba_profiles where Dropprofile='SATISH_ADMIN';profilesatish_admin cascade; alter system set license_max_session=100; alter system set license_max_session=84 license_session_warning=64; alter system set license_max_users=30; create user demo identified by demo default tablespace users quota 10m on users quota 10m on system profile satish_admin; select username,account_status,default_tablespace,created,profile from selectdba_users;*from dba_profiles order by profile; select username,created,profile from dba_users order by profile; desc selectdba_profiles;profile,resource_name,limit from dba_profiles order by profile; desc dba_users; desc all_users; desc user_users; desc dba_ts_quotas; desc user_password_limits; desc user_resource_limits; desc dba_profiles; desc resource_cost; desc v$session; desc v$sesstat; desc //*Lessionv$statname;6Managing Users Privileges and Role *// create user dbclint identified by dbclint; conn grantdbclint;connect,resource to dbclint; revoke connect,resource from dbclint; grant create session to dbclint; grant create user to dbclint; grant alter,table to dbclint; grant alter any table to dbclint; grant alter any sequence to dbclint; grant alter any view to dbclint; grant create any index to dbclint; grant drop any table to dbclint; grant update any table to dbclint; select table_name,owner from dba_tables where owner='DBCLINT'; grant insert,update on dbclint.stable to dbclint; revoke insert on stable from dbclint; revoke update any table from dbclint; revoke alter any table from dbclint;Page 5
Database Administrator revoke create user from dbclint; revoke create any index from dbclint; revoke alter any sequence from dbclint; revoke update on dbclint.stable from dbclint; revoke insert on dbclint.stable from dbclint; select table_name,privilege,grantable from sys.dba_tab_privs where grantgrantee='SATISH';updateondbclint.stable to dbclint; grant dba to satish; create role sdba identified by sdba; drop role sdba; desc dba_role_privs; desc dba_sys_privs; desc role_sys_privs; desc selectdba_roles;granted_role,grantee from dba_role_privs where grantee='DBCLINT'; grant connect,resource to sdba; grant sdba to dbclint; select role,privilege from role_sys_privs where role='SDBA'; alter user dbclint identified by dbclint; alter user dbclint account lock; alter user dbclint account unlock; select username,user_id,account_status,lock_date from dba_users where selectusername='DBCLINT';username,user_id,account_status,expiry_date from dba_users where alterusername='DBCLINT';userdbclintaccount unlock; alter user dbclint password expire; select * from sys.dba_sys_privs; select * from sys.dba_role_privs; select table_name,privilege,grantable from sys.dba_tab_privs where dropgrantee='SATISH';userdbclint casecade; select grantee,table_name,column_name,privilege from sys.dba_col_privs; select * from sys.dba_roles; select granted_role,admin_option from role_role_privs where role='SDBA'; //*lession 7 Undo Management *// select * from dba_data_files; select * from dba_tablespaces; create undo tablespace undotbs2 'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\UNDOTBS2.DBF'datafile size 100mb; alter tablespace undotbs3 add 'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\UNDOTBS3.DBF'datafile auto extend on; alter system set undo_tblespace='UNDOTBS';Page6
Database Administrator drop tablespace undotbs3 including contents; select * from v$undostat; createcreatedropselect//*alterselectalterswtich//*//*d:\ORACLEXE\APP\ORACLE\ORADATA\XE\CONTROL.DBFhostalterselect//*selectselectselectselectselectselect'C:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\ONLINELOG\O4_MF_2b.LOG';alteralter'C:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\ONLINELOG\O5_MF_2b.LOG';'C:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\ONLINELOG\O3_MF_2b.LOG'altergroup'C:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\ONLINELOG\O4_MF_2b.LOG'alter10m;('C:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\ONLINELOG\O3_MF_2b.LOG')alter10m;('C:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\ONLINELOG\O2_MF_2a.LOG')alterselect//*ommit_wtime,statussegment_name,tablespace_name,extent_id,file_id,block_id,block,commit_jtime,cselectfromsys.dba_undo_extnets;lession8MnagagingRedologFiles*//*fromv$logfile;databaseaddlogfilegroup03sizedatabaseaddlogfilegroup04sizedatabaseaddlogfilememberto04;databaserenamefiletodatabasedroplogfilegroup03;databasedroplogfilemember*fromv$log;*fromv$logfile;*fromv$log_history;*fromv$loglist;*fromv$recovery_log;*fromv$archived_log;lession9managingControlFiles*//*fromv$controlfile;databasebackupcontrolfiletotrace;cpC:\ORACLEXE\APP\ORACLE\ORADATA\XE\CONTROL.DBFLession10ManagingArchiveLogfile*//FirstClosedatabaseandshutdownthenopendatabaseinmountstagethearchivelogmode*//databasearchivelog;name,open_mode,log_modefromv$database;databasenoarchivelog;Lesson11ManagingExportandImport*//*frompanchkurwa;tablepanchkurwa;tableimage_data(imageBlob);tableimage_data1(imageBlob);Page7
Database Administrator host exp satish/satish file=panchkurwa.dmp tables=panchkurwa; host exp sys/administrator file=satish.dmp log=satish.log full=y ignore=y host imp sys/administrator file=satish.dmp log=satish.log full=y ignore=y //* Lession 12 Cold backup*// shutdown database; copy all datafiles in backup location script//*Sql>@ getfile.sql set echo off set pause off set feed off set head off set linesize 200 spool selectc:\batchfile\coldzipbackup.bat'copy'||NAME||'d:\oracle\backup ' from v$datafile; select ' copy ' ||NAME|| ' d:\oracle\backup ' from v$tempfile; select ' copy ' ||NAME|| ' d:\oracle\backup ' from v$controlfile; select ' copy ' ||MEMBER|| ' d:\oracle\backup ' from v$logfile; spool //*Hot//**//startup;hosthosthosthostshutdownoffimmediate;clsc:\batchfile\coldzipbackup.batzipcold.zipd:\oracle\backup\*.*movec:\cold.zipf:\ZipbackupLession13HotBackup*//Backup:Hotbackupperformatthe running time (online). Sql>startup nomount; Sql>alter database archivelog; Sql>alter database open; Sql>select log_mode from v$database; Sql>archive log list; Sql>alter database begin backup; C:\copySql>host *.* d:\backup\ Sql>selectExit * from v$backup; Sql>alter database backup controlfile to ‘d:\backup\control’; Sql>alter database end backup; Sql>recoverSql>startupSql>shutdown;mount;database until cancel using backup controlfile; //**// Lession 14 Recover Database datafile corruupt Using RMAN*// Page 8
Database Administrator SQL> select * from v$recover_file; SQL> select file#,create_bytes from v$datafile; SQL> desc dba_data_files; SQL> col file_name for a60; SQL> select file_name,tablespace_name,bytes/(1024*1024) from dba_data_files; SQL> host rman RMAN> connect target sys/administrator RMAN> report schema; RMAN> backup datafile file_id; RMAN> backup as copy datafile file_id; RMAN> list backup datafile file_id; RMAN> list copy of datafile file_id; RMAN> restore datafile file_id; RMAN> recover datafile file_id; RMAN> switch copy datafile file_id; RMAN> recover datafile file_id; RMAN> exit SQL> alter database create 'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\MUZF03.DBF'datafile as 'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\MUZF03.DBF' size 100m; SQL> recover datafile file_id; SQL> alter database open; Page 9