1 Database Administration //* Lession 1 Tablespace Management *// select * from dba_tablespaces; desc selectdba_tablespaces;tablespace_name,file_name from dba_data_files; create tablespace student datafile 'C:\oraclexe\app\oracle\oradata\xe\student.dbf' size 100 m; select tablespace_name,file_name from dba_data_files; alter tablespace student add datafile 'C:\oraclexe\app\oracle\oradata\xe\student_1.dbf' 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 tablespace)// alter 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 alteralterclear'C:\oraclexe\app\oracle\oradata\xe\Student\student_01.dbf';screen;tablespacestudentonline;databasedatafile'C:\oraclexe\app\oracle\oradata\xe\Student\student_01.dbf' resize 200 m; alter tablespace student drop datafile 'C:\oraclexe\app\oracle\oradata\xe\Student\student_01.dbf'; select tablespace_name,file_name from dba_data_files; alter database datafile 'C:\oraclexe\app\oracle\oradata\xe\student.dbf' autoextend on next 10 m maxsize 20 m; select * from dba_data_files; alter database datafile 'C:\oraclexe\app\oracle\oradata\xe\student.dbf' autoextend off; alter database datafile 'C:\oraclexe\app\oracle\oradata\xe\student.dbf' autoextend on; 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 extent(datafile 'C:\oraclexe\app\oracle\oradata\xe\student.dbf'); grant unlimited tablespace to satish; select owner,table_name,tablespace_name from dba_tables where table_name='DOCTOR'; select t.table_name,t.tablespace_name,df.file_name from dba_tables t,dba_data_files df where table_name='DOCTOR' and t.tablespace_name=df.tablespace_name; drop table doctor; drop tablespace student including contents;
2 Database Administration select * from dba_tablespaces; select * from dba_data_files; select * from dba_free_space; select * from v$tablespace; select * from v$datafile; select file_name,maxbytes/(1024*1024) from dba_data_files; //* Lession 2 Managing Storage Parameter *// create tablespace physics datafile 'C:\oraclexe\app\oracle\oradata\xe\physics.dbf' size 100 m; select tablespace_name,status,autoextensible,bytes/(1024*1024) from dba_data_files; alter database datafile 'C:\oraclexe\app\oracle\oradata\xe\student.dbf' autoextend on; create table physics (sno number(2),name varchar2(10),department varchar2(50),location varchar2(50)) tablespace physics ; alter tablespace student add datafile 'C:\oraclexe\app\oracle\oradata\xe\engineer.dbf' size 5 m; alter table engineer allocate extent(datafile 'C:\oraclexe\app\oracle\oradata\xe\engineer.dbf'); select t.table_name,t.tablespace_name,df.file_name from dba_tables t,dba_data_files df where table_name='ENGINEER' and t.tablespace_name=df.tablespace_name; create table test (A number(2)) storage (initial 10k); select * from user_segments where segment_name='ENGINEER'; alterdescselectselectalteralterfromselectwhereselectalteralterselectcommit;tablespace_name='PHYSICS';selectselectalterdescinsertdescselectselectselectselectselectax_extents/(1024*1024)segment_name,tablespace_name,bytes/(1024*1024),initial_extent/(1024),next_extent/(1024*1024),mselectfromuser_segmentswheresegment_name='ENGINEER';*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)MBfromdba_data_files;ENGINEER;intophysicsvalues(10,'AMITKUMAR','astrology','Australia');physics;tablephysicsmodify(subjectchar(1024),locationchar(1024));*fromdba_free_spacewherefile_id=7;tablespace_name,bytes/(1024*1024)MBfromdba_free_spacewhere*fromengineer;tablephysicsadd(subjectvarchar2(1024),locationvarchar2(1024));databasedatafile'C:\oraclexe\app\oracle\oradata\xe\engineer.dbf'resize2m;file_name,tablespace_name,autoextensible,maxbytes/(1024*1024)MBfromdba_data_filestablespace_namelike'PHYSICS';tablespace_name,file_id,count(*),max(blocks)Maximum,min(blocks),avg(blocks),sum(blocks)dba_free_spacewheretablespace_name='STUDENT'groupbytablespace_name,file_id;databasedatafile'C:\oraclexe\app\oracle\oradata\xe\student.dbf'resize2m;databasedatafile'C:\oraclexe\app\oracle\oradata\xe\student.dbf'autoextendoff;tablespace_name,file_name,bytesfromdba_data_files;*fromengineer;physicsdatabasedatafile'C:\oraclexe\app\oracle\oradata\xe\physics.dbf'autoextendon;
3 Database Administration select segment_name,tablespace_name,initial_extent,next_extent,min_extents,max_extents from dba_segments where segment_name='PHYSICS'; //* Lession 3 Managinig Rollback segment *// select segment_name,segment_type from dba_segments where segment_type='DIFFERED ROLLBACK'; create rollback segment rbs tablespace student storage (initial 10k next 10k optimal 20k minextents 2 maxextents 5); desc selectdba_segments;segment_name,segment_type,tablespace_name,max_extents from dba_segments where altertablespace_name='STUDENT';rollbacksegmentrbsstorage (maxextents 10); select segment_name,segment_type,tablespace_name,max_extents from dba_segments where selectsegment_name='RBS';*fromdba_segments where owner='SATISH'; alter rollback segment rbs online; 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 grantsystem;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 profile='DEFAULT'; create profile logitech 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 profile='LOGITECH'; alter system set resource_limit=True; alter system set resource_limit=False; 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 v$statname;
4 Database Administration //* Lession 5 Mnaging Resource with Profile *// 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 profile='SATISH_ADMIN'; alter profile satish_admin limit logical_reads_per_session 2000 cpu_per_call default logical_reads_per_call 100; select profile,resource_name,resource_type,limit from dba_profiles where profile='SATISH_ADMIN'; Drop profile satish_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 dba_users; select * 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 v$statname; //*Lession 6 Managing 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;
5
Database Administration revoke alter any table from dbclint; 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 grantee='SATISH'; grant update on dbclint.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 username='DBCLINT'; select username,user_id,account_status,expiry_date from dba_users where username='DBCLINT'; alter user dbclint account 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 grantee='SATISH'; drop user dbclint 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 datafile 'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\UNDOTBS2.DBF' size alter100mb;tablespace undotbs3 add datafile 'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\UNDOTBS3.DBF' auto extend on; alter system set undo_tblespace='UNDOTBS'; drop tablespace undotbs3 including contents; select * from v$undostat; fromsegment_name,tablespace_name,extent_id,file_id,block_id,block,commit_jtime,commit_wtime,statusselectsys.dba_undo_extnets; //* lession 8 Mnagaging Redolog Files *// select * from v$logfile;
6 Database Administration alter database add logfile group ('C:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\ONLINELOG\O2_MF_2a.LOG')03 size 10m; alter database add logfile group ('C:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\ONLINELOG\O3_MF_2b.LOG')04 size 10m; alter database add logfile 'C:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\ONLINELOG\O4_MF_2b.LOG'member to group 04; alter database rename 'C:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\ONLINELOG\O3_MF_2b.LOG'file 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';todatabasedroplogfilegroup03;databasedroplogfilemember*fromv$log;*fromv$logfile;*fromv$log_history;*fromv$loglist;*fromv$recovery_log;*fromv$archived_log; //* Lession 9 managing Control Files *// select * from v$controlfile; alter database backup controlfile to trace; host cp d:\ORACLEXE\APP\ORACLE\ORADATA\XE\CONTROL.DBFC:\ORACLEXE\APP\ORACLE\ORADATA\XE\CONTROL.DBF //* Lession 10 Managing Archive Log file *// //* First Close database and shut down then open database in mount stage the swtich archivelog altermode*//database archivelog; select name,open_mode,log_mode from v$database; alter database noarchivelog; //* Lesson 11 Managing Export and Import *// select * from panchkurwa; drop table panchkurwa; create table image_data (image Blob); create table image_data1 (image Blob); 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 setsetsetsetsetSql>@script//*getfile.sqlechooffpauseofffeedoffheadofflinesize200
7 Database Administration 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 shutdownoff immediate; host cls host c:\batchfile\coldzipbackup.bat host zip cold.zip d:\oracle\backup\*.* host move c:\cold.zip f:\Zipbackup *//startup; //* Lession 13 Hot Backup *// //*Hot Backup:Hot backup perform at the 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 corrupt datafile using RMAN *// 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
8 Database Administration SQL> alter database create datafile 'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\MUZF03.DBF' as 'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\MUZF03.DBF' size 100m; SQL> recover datafile file_id; SQL> alter database open;