sql> startup ora-32004: obsolete and/or deprecated parameter(s) specified oracle instance started. total system global area 135338868 fixed size 453492 variable size 109051904 database buffers 25165824 redo buffers 667648 database mounted. database opened. sql> select * from v$tablespace; ts# ---------3 4 5 6 7 0 8 1 9 10 2
bytes bytes bytes bytes bytes
name -----------------------------cwmlite drsys example indx odm system tools undotbs1 users xdb temp
inc --yes yes yes yes yes yes yes yes yes yes yes
11 rows selected. sql> crate tablespace sue datafile 'd:\oracle\oradata\sue\suepertab.ora' size 1m; sp2-0734: unknown command beginning "crate tabl..." - rest of line ignored. sql> create tablespace sue datafile 'd:\oracle\oradata\sue\suepertab.ora' size 1m; tablespace created. sql> create tablespace sue datafile 'd:\oracle\oradata\sue\suepertab1.ora' size 1m; create tablespace sue datafile 'd:\oracle\oradata\sue\suepertab1.ora' size 1m * error at line 1: ora-01543: tablespace 'sue' already exists sql>
select * from v$tablespace;
ts# ---------3 4 5 6 7 0 8 1 9 10 2
name -----------------------------cwmlite drsys example indx odm system tools undotbs1 users xdb temp
inc --yes yes yes yes yes yes yes yes yes yes yes
ts# name inc ---------- ------------------------------ --12 sue yes 12 rows selected. sql> ----------------------------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 ----------- ------------ ---------- --------- --------- --------- --- ---------allocatio plu segmen --------- --- -----sue 8192 65536 1 2147483645 65536 online permanent logging no local system no manual sql> ----------------------------------------------------------------------------sql> ----nologging cant recovery sql> ----any tablespace create logging is set by logging sql> ------create nologing tablespace sql> create tablespace sue1 datafile 'd:\oracle\oradata\sue\suepertab1.ora' size 1m nologging; tablespace created. sql> ---------------------------------------------------------------------------------------sql> ---------------------------------------------------------------------------------------sql> alter the tablespace 2 . sql> ---------------------------------------------------------------------------------------sql> alter database datafile 'd:\oracle\oradata\sue\suepertab1.ora' resize 2m; database altered. sql> ----auto extend sql> ---------------------------------------------------------------------------------------sql> alter database datafile 'd:\oracle\oradata\sue\suepertab1.ora' autoextend on next 1m maxsize 10m; database altered. 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; sql>
----------------------------------------------------------------------------------------------sql> -----offline tablespace sql> alter tablespace sue1 offline; tablespace altered. sql> -------user cant work in dml,select,alter,drop,truncate,create sql> -----------------online sql> alter tablespace sue1 online; tablespace altered. sql> ------user can work in dml,select,alter,drop,truncate,create sql> ----------------------------------------------------------------------------------------------sql> --------------------read only sql> alter tablespace sue1 readonly 2 ; alter tablespace sue1 readonly * error at line 1: ora-02142: missing or invalid alter tablespace option sql> alter tablespace sue1 read only; tablespace altered. sql> ---------user cant work in dml,alterdrop,truncat,create sql> ---------user can work in select,drop,alter(add,modify) 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 ----------- ------------ ---------- --------- --------- --------- --- ---------allocatio plu segmen --------- --- -----sue 8192 65536 1 2147483645 65536 online permanent logging no local system no manual 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 ----------- ------------ ---------- --------- --------- --------- --- ---------allocatio plu segmen --------- --- -----sue1 8192 65536 1 2147483645 65536 read only permanent nologging no local system no manual
sql> --------chang read & write mode 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 ----------- ------------ ---------- --------- --------- --------- --- ---------allocatio plu segmen --------- --- -----sue1 8192 65536 1 2147483645 65536 read only permanent nologging no local system no manual sql> alter tablespace sue1 read write; tablespace altered. sql> elect * from dba_tablespaces where tablespace_name='sue1'; sp2-0734: unknown command beginning "elect * fr..." - rest of line ignored. sql> s elect * from dba_tablespaces where tablespace_name='sue1'; sp2-0734: unknown command beginning "s elect * ..." - rest of line ignored. 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 ----------- ------------ ---------- --------- --------- --------- --- ---------allocatio plu segmen --------- --- -----sue1 8192 65536 1 2147483645 65536 online permanent nologging no local system no manual sql> sql> sql> drop
----------------------drop tablespace --------current use of tablespace then drop tablespace sue1 inculding contents and datafiles; tablespace sue1 inculding contents and datafiles * error at line 1: ora-02173: invalid option for drop tablespace sql> drop tablespace sue1; tablespace dropped. sql> ----------------becasue sue1 is not current using tablespace sql>----------------------add the datafile in particular tablespace sql> select file_name from dba_data_file where tablespace_name='iimca'; select file_name from dba_data_file where tablespace_name='iimca' *
error at line 1: ora-00942: table or view does not exist sql> select file_name from dba_data_files where tablespace_name='iimca'; file_name ----------------------------------------------------------------------------------------------------d:\oracle\admin\sams\mca.ora sql> alter tablespace iimca add datafile 'd:\oracle\oradata\sams\iimca1.ora'; alter tablespace iimca add datafile 'd:\oracle\oradata\sams\iimca1.ora' * error at line 1: ora-01119: error in creating database file 'd:\oracle\oradata\sams\iimca1.ora' ora-17610: file 'd:\oracle\oradata\sams\iimca1.ora' does not exist and no size specified ora-27041: unable to open file osd-04002: unable to open file o/s-error: (os 2) the system cannot find the file specified. sql> alter tablespace iimca add datafile 'd:\oracle\oradata\sams\iimca1.ora' size 100m; tablespace altered. sql> select file_name from dba_data_files where tablespace_name='iimca'; file_name ----------------------------------------------------------------------------------------------------d:\oracle\admin\sams\mca.ora d:\oracle\oradata\sams\iimca1.ora 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; sql> spool off