Ant
This document was uploaded by user and they confirmed that they have the permission to share it. If you are author or own the copyright of this book, please report to us by using this DMCA report form. Report DMCA
Overview
Download & View Ant as PDF for free.
More details
- Words: 719
- Pages: 5
sql> startup oracle instance started. total system global area fixed size variable size database buffers redo buffers database mounted. database opened.
135338868 453492 109051904 25165824 667648
bytes bytes bytes bytes bytes
view all tablespace =================== sql> select * from v$tablespace; ts# name inc ---------- -----------------------------------3 cwmlite 4 drsys 5 example 6 indx
yes yes yes yes
7 odm 0 8 1 9 10
system tools undotbs1 users xdb
2 temp
yes yes yes yes
yes yes yes
11 rows selected. create tablespace ================= sql> create tablespace sue datafile 'd:\oracle\oradata\sue\suepertab.ora' size 1m; tablespace created. sql>
select * from v$tablespace;
ts# name inc ---------- -----------------------------------3 cwmlite 4 drsys 5 example 6 indx 7 odm
yes yes yes
yes yes
0 8 1 9 10
system tools undotbs1 users xdb
2 temp 12 sue
yes yes
yes
yes yes yes yes
12 rows selected. data dictionary of tablespace ============================= ***data dictionary is dba_tablespaces sql> select * from dba_tablespaces where tablespace_name='sue'; tablespace_name block_size initial_extent next_extent min_extents max_extents pct_increase min_extlen status contents logging for extent_man allocation plu segmen --------------------------- ----------------- --------------------------------------- ------------------- ---------------------------------------- ----------------- ------------------------------------- ------- ------------------- ----------------- ------ -----------sue 8192 65536 1 2147483645 65536 online permanent logging no local system no manual nologging tablespace ===================== **nologging cant recovery **any tablespace create logging is set by logging **create nologing tablespace sql> create tablespace sue1 datafile 'd:\oracle\oradata\sue\suepertab1.ora' size 1m nologging; tablespace created. sql> select * from dba_tablespaces where tablespace_name='sue1'; tablespace_name block_size initial_extent next_extent min_extents max_extents pct_increase min_extlen status contents logging for extent_man allocation plu segmen --------------------------- ----------------- --------------------------------------- ------------------- ---------------------------------------- ----------------- ------------------------------------------ ------- ------------------- ----------------- ----------------sue1 8192 65536 1 2147483645 65536 online permanent nologging no local system no manual keyword for tablespace==> online, offline, resize, autoextend or end/drop
resize ====== sql> alter database
datafile 'd:\oracle\oradata\sue\suepertab1.ora' resize 2m;
database altered. autoextend =========== sql> alter database datafile 'd:\oracle\oradata\sue\suepertab1.ora' autoextend on next 1m maxsize 10m; database altered. offline ======= sql> alter tablespace sue1 offline; tablespace altered. sql> select * from dba_tablespaces where tablespace_name='sue1'; tablespace_name block_size initial_extent next_extent min_extents max_extents pct_increase min_extlen status contents logging for extent_man allocation plu segmen --------------------------- ----------------- --------------------------------------- ------------------- ---------------------------------------- ----------------- ------------------------------------------- ------- ------------------- ----------------- ----------------sue1 8192 65536 1 2147483645 65536 offline permanent nologging no local system no manual **user cant work in dml,select,alter,drop,truncate,create online ====== sql> alter tablespace sue1 online; tablespace altered. sql> select * from dba_tablespaces where tablespace_name='sue1'; tablespace_name block_size initial_extent next_extent min_extents max_extents pct_increase min_extlen status contents logging for extent_man allocation plu segmen --------------------------- ----------------- --------------------------------------- ------------------- ---------------------------------------- ----------------- ------------------------------------------ ------- ------------------- ----------------- ----------------sue1 8192 65536 1 2147483645 65536 online permanent nologging no local system no manual **user can work in dml,select,alter,drop,truncate,create
read only ========== sql> alter tablespace sue1 read only; tablespace altered. **user cant work in dml,alterdrop,truncat,create **user can work in select,drop,alter(add,modify) sql> select * from dba_tablespaces where tablespace_name='sue1'; tablespace_name block_size initial_extent next_extent min_extents max_extents pct_increase min_extlen status contents logging for extent_man allocation plu segmen --------------------------- ----------------- --------------------------------------- ------------------- ---------------------------------------- ----------------- ----------------------------------------------- ------- ------------------- ----------------- ----------------sue1 8192 65536 1 2147483645 65536 read only permanent nologging no local system no manual read write mode ================ sql> alter tablespace sue1 read write; tablespace altered. sql> select * from dba_tablespaces where tablespace_name='sue1'; tablespace_name block_size initial_extent next_extent min_extents max_extents pct_increase min_extlen status contents logging for extent_man allocation plu segmen --------------------------- ----------------- --------------------------------------- ------------------- ---------------------------------------- ----------------- ------------------------------------------ ------- ------------------- ----------------- ----------------sue1 8192 65536 1 2147483645 65536 online permanent nologging no local system no manual ***read write mode equal to online mode
add datafiles to tablespace =========================== **view datafile for particular tablespace **the datadictionary is dba_data_files sql> select file_name from dba_data_files where tablespace_name='sue1'; file_name ----------------------------------------------------------------
d:\oracle\oradata\sue\suepertab1.ora sql> alter tablespace sue1 add datafile 'd:\oracle\oradata\sue\suetab1.ora' size 100m; tablespace altered. sql> select file_name from dba_data_files where tablespace_name='sue1'; file_name ---------------------------------------------------------------d:\oracle\oradata\sue\suepertab1.ora d:\oracle\oradata\sue\suetab1.ora set the tablespace for particular user ====================================== sql>alter user sam default tablespace sue1; alter user ***we can view the user default tablespace using datadictionary ***the datadictionary is dab_users sql> select
username,default_tablespace from dba_users where username='sam';
username default_tablespace ------------------------------ -----------------------------sam sue1 drop ===== **tablespace have any content the following statement use sql>drop tablespace sue1 inculding contents and datafiles; tablespace dropped. **tablespace have no content then we use following statement is enough **std:=>drop tablespace sue1; backup key words for tablespace ================================= **begin===>start the backup **end=====>end the backup sql> -----permentant tablespace(create tablespace using management local) sql> create tablespace sue11 datafile'path' size 100m autoextend on extent management local on next 5m maxsize 200m;
135338868 453492 109051904 25165824 667648
bytes bytes bytes bytes bytes
view all tablespace =================== sql> select * from v$tablespace; ts# name inc ---------- -----------------------------------3 cwmlite 4 drsys 5 example 6 indx
yes yes yes yes
7 odm 0 8 1 9 10
system tools undotbs1 users xdb
2 temp
yes yes yes yes
yes yes yes
11 rows selected. create tablespace ================= sql> create tablespace sue datafile 'd:\oracle\oradata\sue\suepertab.ora' size 1m; tablespace created. sql>
select * from v$tablespace;
ts# name inc ---------- -----------------------------------3 cwmlite 4 drsys 5 example 6 indx 7 odm
yes yes yes
yes yes
0 8 1 9 10
system tools undotbs1 users xdb
2 temp 12 sue
yes yes
yes
yes yes yes yes
12 rows selected. data dictionary of tablespace ============================= ***data dictionary is dba_tablespaces sql> select * from dba_tablespaces where tablespace_name='sue'; tablespace_name block_size initial_extent next_extent min_extents max_extents pct_increase min_extlen status contents logging for extent_man allocation plu segmen --------------------------- ----------------- --------------------------------------- ------------------- ---------------------------------------- ----------------- ------------------------------------- ------- ------------------- ----------------- ------ -----------sue 8192 65536 1 2147483645 65536 online permanent logging no local system no manual nologging tablespace ===================== **nologging cant recovery **any tablespace create logging is set by logging **create nologing tablespace sql> create tablespace sue1 datafile 'd:\oracle\oradata\sue\suepertab1.ora' size 1m nologging; tablespace created. sql> select * from dba_tablespaces where tablespace_name='sue1'; tablespace_name block_size initial_extent next_extent min_extents max_extents pct_increase min_extlen status contents logging for extent_man allocation plu segmen --------------------------- ----------------- --------------------------------------- ------------------- ---------------------------------------- ----------------- ------------------------------------------ ------- ------------------- ----------------- ----------------sue1 8192 65536 1 2147483645 65536 online permanent nologging no local system no manual keyword for tablespace==> online, offline, resize, autoextend or end/drop
resize ====== sql> alter database
datafile 'd:\oracle\oradata\sue\suepertab1.ora' resize 2m;
database altered. autoextend =========== sql> alter database datafile 'd:\oracle\oradata\sue\suepertab1.ora' autoextend on next 1m maxsize 10m; database altered. offline ======= sql> alter tablespace sue1 offline; tablespace altered. sql> select * from dba_tablespaces where tablespace_name='sue1'; tablespace_name block_size initial_extent next_extent min_extents max_extents pct_increase min_extlen status contents logging for extent_man allocation plu segmen --------------------------- ----------------- --------------------------------------- ------------------- ---------------------------------------- ----------------- ------------------------------------------- ------- ------------------- ----------------- ----------------sue1 8192 65536 1 2147483645 65536 offline permanent nologging no local system no manual **user cant work in dml,select,alter,drop,truncate,create online ====== sql> alter tablespace sue1 online; tablespace altered. sql> select * from dba_tablespaces where tablespace_name='sue1'; tablespace_name block_size initial_extent next_extent min_extents max_extents pct_increase min_extlen status contents logging for extent_man allocation plu segmen --------------------------- ----------------- --------------------------------------- ------------------- ---------------------------------------- ----------------- ------------------------------------------ ------- ------------------- ----------------- ----------------sue1 8192 65536 1 2147483645 65536 online permanent nologging no local system no manual **user can work in dml,select,alter,drop,truncate,create
read only ========== sql> alter tablespace sue1 read only; tablespace altered. **user cant work in dml,alterdrop,truncat,create **user can work in select,drop,alter(add,modify) sql> select * from dba_tablespaces where tablespace_name='sue1'; tablespace_name block_size initial_extent next_extent min_extents max_extents pct_increase min_extlen status contents logging for extent_man allocation plu segmen --------------------------- ----------------- --------------------------------------- ------------------- ---------------------------------------- ----------------- ----------------------------------------------- ------- ------------------- ----------------- ----------------sue1 8192 65536 1 2147483645 65536 read only permanent nologging no local system no manual read write mode ================ sql> alter tablespace sue1 read write; tablespace altered. sql> select * from dba_tablespaces where tablespace_name='sue1'; tablespace_name block_size initial_extent next_extent min_extents max_extents pct_increase min_extlen status contents logging for extent_man allocation plu segmen --------------------------- ----------------- --------------------------------------- ------------------- ---------------------------------------- ----------------- ------------------------------------------ ------- ------------------- ----------------- ----------------sue1 8192 65536 1 2147483645 65536 online permanent nologging no local system no manual ***read write mode equal to online mode
add datafiles to tablespace =========================== **view datafile for particular tablespace **the datadictionary is dba_data_files sql> select file_name from dba_data_files where tablespace_name='sue1'; file_name ----------------------------------------------------------------
d:\oracle\oradata\sue\suepertab1.ora sql> alter tablespace sue1 add datafile 'd:\oracle\oradata\sue\suetab1.ora' size 100m; tablespace altered. sql> select file_name from dba_data_files where tablespace_name='sue1'; file_name ---------------------------------------------------------------d:\oracle\oradata\sue\suepertab1.ora d:\oracle\oradata\sue\suetab1.ora set the tablespace for particular user ====================================== sql>alter user sam default tablespace sue1; alter user ***we can view the user default tablespace using datadictionary ***the datadictionary is dab_users sql> select
username,default_tablespace from dba_users where username='sam';
username default_tablespace ------------------------------ -----------------------------sam sue1 drop ===== **tablespace have any content the following statement use sql>drop tablespace sue1 inculding contents and datafiles; tablespace dropped. **tablespace have no content then we use following statement is enough **std:=>drop tablespace sue1; backup key words for tablespace ================================= **begin===>start the backup **end=====>end the backup sql> -----permentant tablespace(create tablespace using management local) sql> create tablespace sue11 datafile'path' size 100m autoextend on extent management local on next 5m maxsize 200m;
