Funda 1 > Table Space - 25th May
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 Funda 1 > Table Space - 25th May as PDF for free.
More details
- Words: 5,820
- Pages: 53
sql> sql> select * from v$tablespace; ts# ---------0 1 3 4 5 6 7 8 9 10 2
name -----------------------------system undotbs1 cwmlite drsys example indx odm tools users xdb temp
inc --yes yes yes yes yes yes yes yes yes yes yes
11 rows selected. sql> set lines200; sql> set pages 200; sql> sql> select * from user_tablespaces; tablespace_name block_size initial_extent next_extent min_extents max_extents pct_increase min_extlen status contents logging for extent_man allocatio segmen ------------------------------ ---------- -------------- ----------- --------------------- ------------ ---------- --------- --------- --------- --- ------------------ -----system 8192 65536 1 2147483645 65536 online permanent logging no local system manual undotbs1 8192 65536 1 2147483645 65536 online undo logging no local system manual temp 8192 1048576 1048576 1 0 1048576 online temporary nologging no local uniform manual cwmlite 2147483645 system auto drsys 2147483645 system auto example 2147483645 system auto indx 2147483645 system auto odm 2147483645 system auto tools 2147483645 system auto users
8192 65536 online
65536 permanent logging
no
local
8192 65536 online
65536 permanent logging
no
local
8192 65536 online
65536 permanent logging
no
local
8192 65536 online
65536 permanent logging
no
local
8192 65536 online
65536 permanent logging
no
local
8192 65536 online
65536 permanent logging
no
local
8192
65536
1 1 1 1 1 1 1
2147483645 system manual xdb 2147483645 system auto
65536 online
permanent logging
no
local
8192 65536 online
65536 permanent logging
no
local
1
11 rows selected. sql> select * from dba_tablespaces; 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 ------------------------------ ---------- -------------- ----------- --------------------- ------------ ---------- --------- --------- --------- --- ------------------ --- -----system 8192 65536 1 2147483645 65536 online permanent logging no local system no manual undotbs1 8192 65536 1 2147483645 65536 online undo logging no local system no manual temp 8192 1048576 1048576 1 0 1048576 online temporary nologging no local uniform no manual cwmlite 2147483645 system no drsys 2147483645 system no example 2147483645 system no indx 2147483645 system no odm 2147483645 system no tools 2147483645 system no users 2147483645 system no xdb 2147483645 system no
auto auto auto auto auto auto manual auto
8192 65536 online
65536 permanent logging
no
local
8192 65536 online
65536 permanent logging
no
local
8192 65536 online
65536 permanent logging
no
local
8192 65536 online
65536 permanent logging
no
local
8192 65536 online
65536 permanent logging
no
local
8192 65536 online
65536 permanent logging
no
local
8192 65536 online
65536 permanent logging
no
local
8192 65536 online
65536 permanent logging
no
local
11 rows selected. sql> sql> select * from ts$tablespaces; select * from ts$tablespaces * error at line 1: ora-00942: table or view does not exist
1 1 1 1 1 1 1 1
sql> select * from ts$; ts# name owner# online$ contents$ undofile# undoblock# blocksize inc# scnwrp scnbas dflminext dflmaxext dflinit dflincr dflminlen ---------- ------------------------------ ---------- ---------- ------------------- ---------- ---------- ---------- ---------- ---------- ------------------- ---------- ---------- ---------dflextpct dflogging affstrength bitmapped plugged directallowed flags pitrscnwrp pitrscnbas ownerinstance backupowner groupname ---------- ---------- ----------- ---------- ---------- ------------- ------------------- ---------- ----------------------------------------------------------- -----------------------------spare1 spare2 ---------- ---------spare3 -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------spare4 --------0 8 0
0 system 0 128 0 3 0
0 8 0
8192 8 1
2 temp
0
0
0
8
0
1
0
0 1 2147483645 1
1
0
1 undotbs1 0 8192 128 8 0 1 187618 0
1
2 0
0
0
8
0
1
0
0 1 2147483645 1
17
0
0
1
1
0 128 0
0 128
0 5348 0
0 8 0
0
4 drsys 0 128 0 6293 0
0 8 0
0 8
6 indx 0 128
0
0
0
128
1 2147483645
0
1
2
1 0
0
0
8
0
1
0
0 1 2147483645 1
33
0
8192 8 1
1 0
0
0
8
0
1
0
0 1 2147483645 1
33
0
5 example 0 8192 128 8 0 1 6312 0
1
0
3 cwmlite 0 8192 128 8 0 1 6275 0
0 8
8192 128 0
1 0
0
0
8
0
1
0
0 1 2147483645 1
0
8192 8
1
0
0
0
1
0 1 2147483645
33
0
0 6334
1
0
0
8
0
1
33
0
8 rows selected. sql> ------- for tablespace qutoas sql> sql> sql> sql> select * from dba_ts_quotas; tablespace_name username max_bytes blocks max_blocks
bytes
------------------------------ ------------------------------ ------------------- ---------- ---------odm -1
664
-1
odm -1
520
-1
1192
-1
tools -1
768
-1
example -1
200
-1
example -1
848
-1
example -1
1808
-1
example -1 15352
-1
example -1
0
-1
example -1
192
-1
example -1
144
-1
cwmlite -1
odm
5439488
odm_mtr
4259840
olapsys
9764864
rman
6291456
hr
1638400
oe
6946816
pm
14811136
sh
125763584
qs_adm
0
qs
1572864
qs_ws
1179648
example -1
144
-1
example -1
144
-1
example -1
104
-1
example -1
0
-1
example -1
80
-1
qs_es
1179648
qs_os
1179648
qs_cbadm
851968
qs_cb
0
qs_cs
655360
16 rows selected. sql> select * from user_ts_quotas; no rows selected sql> select * from tsq$; ts#
user#
grantor#
blocks
maxblocks
priv1
priv2
priv3 ---------- ---------- ---------- ---------- ---------- ---------- ------------------0
11
0
48
0
0
0
0
5
0
2632
0
0
0
0
21
0
472
0
0
0
0
30
0
56
0
0
0
0
32
0
496
0
0
0
4
33
0
608
0
0
0
10
35
0
4848
0
0
0
4
39
0
624
0
0
0
7
42
0
664
-1
0
0
7
43
0
520
-1
0
0
0 0 0 0 0 0 0 0 0
0 3
44
0
1192
-1
0
0
5
46
0
200
-1
0
0
5
47
0
848
-1
0
0
5
48
0
1808
-1
0
0
5
49
0
15352
-1
0
0
5
51
0
0
-1
0
0
5
52
0
192
-1
0
0
5
53
0
144
-1
0
0
5
54
0
144
-1
0
0
5
55
0
144
-1
0
0
5
56
0
104
-1
0
0
5
57
0
0
-1
0
0
5
58
0
80
-1
0
0
0
59
0
48
0
0
0
8
60
0
768
-1
0
0
9
59
0
0
0
0
0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
26 rows selected. sql> sql> create tablespace velu datafile 'd:\oracle\oradata\iimdba\velu.dbf' size 100m autoextend on next 5m maxsize 200m; tablespace created.
sql> select * from dba_tablespace; select * from dba_tablespace * error at line 1: ora-00942: table or view does not exist sql> select * from dba_tablespaces; 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 ------------------------------ ---------- -------------- ----------- --------------------- ------------ ---------- --------- --------- --------- --- ------------------ --- -----system 8192 65536 1 2147483645 65536 online permanent logging no local system no manual undotbs1 8192 65536 1 2147483645 65536 online undo logging no local system no manual temp 8192 1048576 1048576 1 0 1048576 online temporary nologging no local uniform no manual cwmlite 2147483645 system no drsys 2147483645 system no example 2147483645 system no indx 2147483645 system no odm 2147483645 system no tools 2147483645 system no users 2147483645 system no xdb 2147483645 system no velu 2147483645 system no
auto auto auto auto auto auto manual auto
8192 65536 online
65536 permanent logging
no
local
8192 65536 online
65536 permanent logging
no
local
8192 65536 online
65536 permanent logging
no
local
8192 65536 online
65536 permanent logging
no
local
8192 65536 online
65536 permanent logging
no
local
8192 65536 online
65536 permanent logging
no
local
8192 65536 online
65536 permanent logging
no
local
8192 65536 online
65536 permanent logging
no
local
8192 65536 online
65536 permanent logging
no
local
1 1 1 1 1 1 1 1 1
manual
12 rows selected. sql> alter tablespace velu datafile 'd:\oracle\oradata\iimdba\velu.dbf' autoextend off; alter tablespace velu datafile 'd:\oracle\oradata\iimdba\velu.dbf' autoextend off
* error at line 1: ora-02142: missing or invalid alter tablespace option sql> alter tablespace datafile 'd:\oracle\oradata\iimdba\velu.dbf' autoextend off; alter tablespace datafile 'd:\oracle\oradata\iimdba\velu.dbf' autoextend off * error at line 1: ora-02142: missing or invalid alter tablespace option sql> alter database
datafile 'd:\oracle\oradata\iimdba\velu.dbf' autoextend off;
database altered. sql> sql> sql> select * from dba_data_files; file_name -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------file_id tablespace_name bytes blocks status relative_fno aut maxbytes maxblocks increment_by user_bytes user_blocks ---------- ------------------------------ ---------- ---------- -------------------- --- ---------- ---------- ------------ ---------- ----------d:\oracle\oradata\iimdba\system01.dbf 1 system 1 yes 3.4360e+10
4194302
419430400 419364864
51200 available 51192
209715200 209649664
25600 available 25592
80
20971520 20905984
2560 available 2552
80
20971520 20905984
2560 available 2552
1280
d:\oracle\oradata\iimdba\undotbs01.dbf 2 undotbs1 2 yes 3.4360e+10 4194302
640
d:\oracle\oradata\iimdba\cwmlite01.dbf 3 cwmlite 3 yes 3.4360e+10
4194302
d:\oracle\oradata\iimdba\drsys01.dbf 4 drsys 4 yes 3.4360e+10
4194302
d:\oracle\oradata\iimdba\example01.dbf 5 example 5 yes 3.4360e+10
80
155975680 155910144
19040 available 19032
160
26214400 26148864
3200 available 3192
80
20971520 20905984
2560 available 2552
4194302
40
10485760 10420224
1280 available 1272
0
0
104857600 104792064
12800 available 12792
80
39976960 39911424
4880 available 4872
0
104857600 104792064
12800 available 12792
4194302
d:\oracle\oradata\iimdba\indx01.dbf 6 indx 6 yes 3.4360e+10
4194302
d:\oracle\oradata\iimdba\odm01.dbf 7 odm 7 yes 3.4360e+10
4194302
d:\oracle\oradata\iimdba\tools01.dbf 8 tools 8 yes 3.4360e+10
d:\bkp\users01.dbf 9 no
9 users 0
d:\oracle\oradata\iimdba\xdb01.dbf 10 xdb 10 yes 3.4360e+10
4194302
d:\oracle\oradata\iimdba\velu.dbf 11 velu 11 no
0
0
11 rows selected. sql> sql> archive log list database log mode automatic archival
archive mode enabled
archive destination oldest online log sequence next log sequence to archive current log sequence sql> sql> sql> sql> select * from v$datafile;
d:\oracle\ora92\rdbms 1 3 3
file# creation_change# creation_ ts# rfile# status enabled checkpoint_change# checkpoin unrecoverable_change# unrecover last_change# last_time offline_change# online_change# ---------- ---------------- --------- ---------- ---------- ------- --------------------------- --------- --------------------- --------- -------------------- --------------- -------------online_ti bytes blocks create_bytes block_size --------- ---------- ---------- ------------ ---------name -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------plugged_in block1_offset ---------- ------------aux_name -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------1 11 12-may-02 0 1 system read write 1092812 24-may-08 0 1009561 1009562 23-may-08 419430400 51200 0 8192 d:\oracle\oradata\iimdba\system01.dbf 0
8192
none
2 187697 12-may-02 1092812 24-may-08 0 1009561 1009562 23-may-08 209715200 25600 d:\oracle\oradata\iimdba\undotbs01.dbf 0 none
8192
1 0
2 online 8192
read write
3 6283 12-may-02 1092812 24-may-08 0 1009561 1009562 23-may-08 20971520 2560
3 0
3 online
read write
4 online
read write
5 online
read write
6 online
read write
7 online
read write
8192
d:\oracle\oradata\iimdba\cwmlite01.dbf 0
8192
none
4 6302 12-may-02 1092812 24-may-08 0 1009561 1009562 23-may-08 20971520 2560
4 0
8192
d:\oracle\oradata\iimdba\drsys01.dbf 0
8192
none
5 6324 12-may-02 1092812 24-may-08 0 1009561 1009562 23-may-08 155975680 19040
5 0
8192
d:\oracle\oradata\iimdba\example01.dbf 0
8192
none
6 6343 12-may-02 1092812 24-may-08 0 1009561 1009562 23-may-08 26214400 3200
6 0
8192
d:\oracle\oradata\iimdba\indx01.dbf 0
8192
none
7 6363 12-may-02 1092812 24-may-08 0 1009561 1009562 23-may-08 20971520 2560
7 0
8192
d:\oracle\oradata\iimdba\odm01.dbf 0
8192
none
8 6382 12-may-02 1092812 24-may-08 0 1009561 1009562 23-may-08 10485760 1280
8 0
8 online
read write
9 online
read write
10 online
read write
11 online
read write
8192
d:\oracle\oradata\iimdba\tools01.dbf 0
8192
none
9 328647 14-may-08 1092812 24-may-08 0 1009561 1009562 23-may-08 104857600 12800 104857600
9 8192
d:\bkp\users01.dbf 0
8192
none
10 6420 12-may-02 1092812 24-may-08 0 1009561 1009562 23-may-08 39976960 4880
10 0
8192
d:\oracle\oradata\iimdba\xdb01.dbf 0
8192
none
11 1096445 24-may-08 1096446 24-may-08 0 0 0 104857600 12800 104857600 d:\oracle\oradata\iimdba\velu.dbf 0 none
8192
13 8192
11 rows selected. sql> sql> sql> alter database datafile 'd:\oracle\oradata\iimdba\velu.dbf' extend management local uniform size 100m; alter database datafile 'd:\oracle\oradata\iimdba\velu.dbf' extend management local uniform size 100m * error at line 1: ora-01916: keyword online, offline, resize, autoextend or end/drop expected sql> ed wrote file afiedt.buf 1* alter database datafile 'd:\oracle\oradata\iimdba\velu.dbf' size 100m extend management local uniform size 100k sql> / alter database datafile 'd:\oracle\oradata\iimdba\velu.dbf' size 100m extend management local uniform size 100k * error at line 1: ora-01916: keyword online, offline, resize, autoextend or end/drop expected sql> alter database datafile 'd:\oracle\oradata\iimdba\velu.dbf' 2 resize 100m extend management local uniform size 120k; resize 100m extend management local uniform size 120k * error at line 2: ora-00933: sql command not properly ended sql> ed wrote file afiedt.buf 1 alter database datafile 'd:\oracle\oradata\iimdba\velu.dbf' 2* resize 100m extend management local uniform size 120k 3 / resize 100m extend management local uniform size 120k * error at line 2: ora-00933: sql command not properly ended sql> select * from v$datafile; file# creation_change# creation_ ts# rfile# status enabled checkpoint_change# checkpoin unrecoverable_change# unrecover last_change# last_time offline_change# online_change# ---------- ---------------- --------- ---------- ---------- ------- --------------------------- --------- --------------------- --------- -------------------- --------------- -------------online_ti bytes blocks create_bytes block_size
--------- ---------- ---------- ------------ ---------name -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------plugged_in block1_offset ---------- ------------aux_name -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------1 11 12-may-02 0 1 system read write 1092812 24-may-08 0 1009561 1009562 23-may-08 419430400 51200 0 8192 d:\oracle\oradata\iimdba\system01.dbf 0
8192
none
2 187697 12-may-02 1092812 24-may-08 0 1009561 1009562 23-may-08 209715200 25600
1 0
2 online
read write
3 online
read write
8192
d:\oracle\oradata\iimdba\undotbs01.dbf 0
8192
none
3 6283 12-may-02 1092812 24-may-08 0 1009561 1009562 23-may-08 20971520 2560
3 0
8192
sql> select ts#,name,enables,status from v$datafile; select ts#,name,enables,status from v$datafile * error at line 1: ora-00904: "enables": invalid identifier sql> ed
wrote file afiedt.buf 1* select ts#,name,enabled,status from v$datafile sql> / ts# ---------name -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------enabled status ---------- ------0 d:\oracle\oradata\iimdba\system01.dbf read write system
1 d:\oracle\oradata\iimdba\undotbs01.dbf read write online
3 d:\oracle\oradata\iimdba\cwmlite01.dbf read write online
4 d:\oracle\oradata\iimdba\drsys01.dbf read write online
5 d:\oracle\oradata\iimdba\example01.dbf read write online
6 d:\oracle\oradata\iimdba\indx01.dbf read write online
7 d:\oracle\oradata\iimdba\odm01.dbf read write online
8 d:\oracle\oradata\iimdba\tools01.dbf read write online
9 d:\bkp\users01.dbf read write online
10 d:\oracle\oradata\iimdba\xdb01.dbf read write online
13 d:\oracle\oradata\iimdba\velu.dbf read write online
11 rows selected. sql> set lines 200; sql> set pages 200; sql> / ts# ----------
name -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------enabled status ---------- ------0 d:\oracle\oradata\iimdba\system01.dbf read write system
1 d:\oracle\oradata\iimdba\undotbs01.dbf read write online
3 d:\oracle\oradata\iimdba\cwmlite01.dbf read write online
4 d:\oracle\oradata\iimdba\drsys01.dbf read write online
5 d:\oracle\oradata\iimdba\example01.dbf read write online
6 d:\oracle\oradata\iimdba\indx01.dbf read write online
7 d:\oracle\oradata\iimdba\odm01.dbf read write online
8 d:\oracle\oradata\iimdba\tools01.dbf read write online
9 d:\bkp\users01.dbf read write online
10 d:\oracle\oradata\iimdba\xdb01.dbf read write online
13 d:\oracle\oradata\iimdba\velu.dbf read write online
11 rows selected. sql> sql> select tablespace_name from user_tables; tablespace_name -----------------------------system system system system
system system system system system system system system system system system system system system system system system system
system system system system system system system system system system
system system system system system system system system system system system system system system system system system system system system system system system system system system system system system
system system system system system system system system system system system system system system system system system system system system system system system system system system system system system
system system system system system system system system system system system system system system system system system system system system system system 114 rows selected. sql> select * from dba_tablespace where tablespace_name='users'; select * from dba_tablespace where tablespace_name='users' * error at line 1: ora-00942: table or view does not exist sql> sql> alter database 'd:\oracle\oradata\iimdba\velu.dbf' resize 100m; alter database 'd:\oracle\oradata\iimdba\velu.dbf' resize 100m
* error at line 1: ora-02231: missing or invalid option to alter database sql> ed wrote file afiedt.buf 1* alter database datafile 'd:\oracle\oradata\iimdba\velu.dbf' resize 100m sql> / database altered. sql> alter database datafile 'd:\oracle\oradata\iimdba\velu.dbf' autoextent on next 1m maxsize 200m; alter database datafile 'd:\oracle\oradata\iimdba\velu.dbf' autoextent on next 1m maxsize 200m * error at line 1: ora-01916: keyword online, offline, resize, autoextend or end/drop expected sql> alter database datafile 'd:\oracle\oradata\iimdba\velu.dbf' autoextend next 1m maxsize 200m;
on
database altered. sql> alter database datafile 'd:\oracle\oradata\iimdba\velu.dbf' extent management local uniform size 1m; alter database datafile 'd:\oracle\oradata\iimdba\velu.dbf' extent management local uniform size 1m * error at line 1: ora-01916: keyword online, offline, resize, autoextend or end/drop expected sql> alter database datafile 'd:\oracle\oradata\iimdba\velu.dbf' extend management local uniform size 1m; alter database datafile 'd:\oracle\oradata\iimdba\velu.dbf' extend management local uniform size 1m * error at line 1: ora-01916: keyword online, offline, resize, autoextend or end/drop expected sql> select dbms_metadata.get_ddl('tablespace', 'uwdata') 2 from dual; error: ora-25153: temporary tablespace is empty ora-06512: at "sys.dbms_lob", line 424 ora-06512: at "sys.dbms_metadata", line 557 ora-06512: at "sys.dbms_metadata", line 1221 ora-06512: at line 1
no rows selected
sql> select dbms_metadata.get_ddl('tablespace', 'users') from dual; error: ora-25153: temporary tablespace is empty ora-06512: at "sys.dbms_lob", line 424 ora-06512: at "sys.dbms_metadata", line 557 ora-06512: at "sys.dbms_metadata", line 1221 ora-06512: at line 1
no rows selected sql> db_create_file_dest sp2-0734: unknown command beginning "db_create_..." - rest of line ignored. sql> sql> sql> sql> -----------to set the default location sql> sql> sql> alter systems set db_create_file_dest='d:\bkp 2 '; alter systems set db_create_file_dest='d:\bkp * error at line 1: ora-00940: invalid alter command sql> alter systems set db_create_file_dest= d:\bkp'; alter systems set db_create_file_dest= d:\bkp' * error at line 1: ora-00940: invalid alter command sql> alter systems set db_create_file_dest= $d:\bkp'; alter systems set db_create_file_dest= $d:\bkp' * error at line 1: ora-00940: invalid alter command sql> sql> sql> dbms_space_admin.tablespace_migrate_to_local('velu'); sp2-0734: unknown command beginning "dbms_space..." - rest of line ignored. sql> dbms_space_admin.tablespace_migrate_to_local('velu'); sp2-0734: unknown command beginning "dbms_space..." - rest of line ignored. sql> sql> sql> create temporary tablespace temp_v tempfile 'd:\oracle\oradata\iimdba\temps_v.dbf' size 100m anutoextend o; create temporary tablespace temp_v tempfile 'd:\oracle\oradata\iimdba\temps_v.dbf' size 100m anutoextend o * error at line 1: ora-02180: invalid option for create tablespace
sql> ed wrote file afiedt.buf 1* create temporary tablespace temp_v tempfile 'd:\oracle\oradata\iimdba\temps_v.dbf' size 100k autoextend on next 1m maxsize 100m sql> / create temporary tablespace temp_v tempfile 'd:\oracle\oradata\iimdba\temps_v.dbf' size 100k autoextend on next 1m maxsize 100m * error at line 1: ora-03214: file size specified is smaller than minimum required sql> ed wrote file afiedt.buf 1* create temporary tablespace temp_v tempfile 'd:\oracle\oradata\iimdba\temps_v.dbf' size 100k autoextend on next 1m maxsize 1000m sql> / create temporary tablespace temp_v tempfile 'd:\oracle\oradata\iimdba\temps_v.dbf' size 100k autoextend on next 1m maxsize 1000m * error at line 1: ora-03214: file size specified is smaller than minimum required sql> ed wrote file afiedt.buf 1* create temporary tablespace temp_v tempfile 'd:\oracle\oradata\iimdba\temps_v.dbf' size 1000k autoextend on next 1m maxsize 100m sql> sql> / create temporary tablespace temp_v tempfile 'd:\oracle\oradata\iimdba\temps_v.dbf' size 1000k autoextend on next 1m maxsize 100m * error at line 1: ora-03214: file size specified is smaller than minimum required sql> sql> ed wrote file afiedt.buf 1* create temporary tablespace temp_v tempfile 'd:\oracle\oradata\iimdba\temps_v.dbf' size 1000k sql> / create temporary tablespace temp_v tempfile 'd:\oracle\oradata\iimdba\temps_v.dbf' size 1000k * error at line 1: ora-03214: file size specified is smaller than minimum required
sql> ed wrote file afiedt.buf 1* create temporary tablespace temp_v tempfile 'd:\oracle\oradata\iimdba\temps_v.dbf' size 3m sql> / tablespace created. sql> alter database tempfile 'd:\oracle\oradata\iimdba\temps_v.dbf' autoextend on next 1m maxsize 100m; database altered. sql> select * from v$datafile; file# creation_change# creation_ ts# rfile# status enabled checkpoint_change# checkpoin unrecoverable_change# unrecover last_change# last_time offline_change# online_change# ---------- ---------------- --------- ---------- ---------- ------- --------------------------- --------- --------------------- --------- -------------------- --------------- -------------online_ti bytes blocks create_bytes block_size --------- ---------- ---------- ------------ ---------name -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------plugged_in block1_offset ---------- ------------aux_name -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------1 11 12-may-02 0 1 system read write 1092812 24-may-08 0 1009561 1009562 23-may-08 419430400 51200 0 8192 d:\oracle\oradata\iimdba\system01.dbf 0
8192
none
2 187697 12-may-02 1092812 24-may-08 0 1009561 1009562 23-may-08 209715200 25600
1 0
2 online 8192
read write
d:\oracle\oradata\iimdba\undotbs01.dbf 0
8192
none
3 6283 12-may-02 1092812 24-may-08 0 1009561 1009562 23-may-08 20971520 2560
3 0
3 online
read write
4 online
read write
5 online
read write
6 online
read write
8192
d:\oracle\oradata\iimdba\cwmlite01.dbf 0
8192
none
4 6302 12-may-02 1092812 24-may-08 0 1009561 1009562 23-may-08 20971520 2560
4 0
8192
d:\oracle\oradata\iimdba\drsys01.dbf 0
8192
none
5 6324 12-may-02 1092812 24-may-08 0 1009561 1009562 23-may-08 155975680 19040
5 0
8192
d:\oracle\oradata\iimdba\example01.dbf 0
8192
none
6 6343 12-may-02 1092812 24-may-08 0 1009561 1009562 23-may-08 26214400 3200 d:\oracle\oradata\iimdba\indx01.dbf 0 none
8192
6 0
8192
7 6363 12-may-02 1092812 24-may-08 0 1009561 1009562 23-may-08 20971520 2560
7 0
7 online
read write
8 online
read write
9 online
read write
10 online
read write
11 online
read write
8192
d:\oracle\oradata\iimdba\odm01.dbf 0
8192
none
8 6382 12-may-02 1092812 24-may-08 0 1009561 1009562 23-may-08 10485760 1280
8 0
8192
d:\oracle\oradata\iimdba\tools01.dbf 0
8192
none
9 328647 14-may-08 1092812 24-may-08 0 1009561 1009562 23-may-08 104857600 12800 104857600
9 8192
d:\bkp\users01.dbf 0
8192
none
10 6420 12-may-02 1092812 24-may-08 0 1009561 1009562 23-may-08 39976960 4880
10 0
8192
d:\oracle\oradata\iimdba\xdb01.dbf 0
8192
none
11 1096446 24-may-08 0 0
1096445 24-may-08
0
13
104857600
12800
104857600
8192
d:\oracle\oradata\iimdba\velu.dbf 0
8192
none
11 rows selected. sql> select * from v$tablespace; ts# name
inc
---------- ------------------------------ --0 system
yes
1 undotbs1
yes
3 cwmlite
yes
4 drsys
yes
5 example
yes
6 indx
yes
7 odm
yes
8 tools
yes
9 users
yes
10 xdb
yes
2 temp
yes
13 velu
yes
14 temp_v
yes
13 rows selected. sql> select * from database_properties; property_name -----------------------------property_value -------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------description -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------dict.base 2 dictionary base tables version #
default_temp_tablespace temp name of default temporary tablespace
dbtimezone -07:00 db time zone
nls_language american language
nls_territory america territory
nls_currency $ local currency
nls_iso_currency america
iso currency
nls_numeric_characters ., numeric characters
nls_characterset we8mswin1252 character set
nls_calendar gregorian calendar system
nls_date_format dd-mon-rr date format
nls_date_language american date language
nls_sort binary linguistic definition
nls_time_format hh.mi.ssxff am time format
nls_timestamp_format dd-mon-rr hh.mi.ssxff am time stamp format
nls_time_tz_format hh.mi.ssxff am tzr time with timezone format
nls_timestamp_tz_format dd-mon-rr hh.mi.ssxff am tzr timestamp with timezone format
nls_dual_currency $ dual currency symbol
nls_comp binary nls comparison
nls_length_semantics byte nls length semantics
nls_nchar_conv_excp false nls conversion exception
nls_nchar_characterset al16utf16 nchar character set
nls_rdbms_version 9.2.0.1.0 rdbms version for nls parameters
global_db_name iimdba.us.oracle.com global database name
export_views_version 8 export views revision #
25 rows selected. sql> sql> alter database tempfile 'd:\oracle\oradata\iimdba\temps_v.dbf' resize 150m; database altered. sql> alter database tempfile 'd:\oracle\oradata\iimdba\temps_v.dbf' drop; database altered. sql> select * from v$tablespace; ts# name
inc
---------- ------------------------------ --0 system
yes
1 undotbs1
yes
3 cwmlite
yes
4 drsys
yes
5 example
yes
6 indx
yes
7 odm
yes
8 tools
yes
9 users
yes
10 xdb
yes
2 temp
yes
13 velu
yes
14 temp_v
yes
13 rows selected. sql> drop tablespace temp_v; tablespace dropped. sql> drop tablespace velu; tablespace dropped. sql> select * from v$datafile; file# creation_change# creation_ ts# rfile# status enabled checkpoint_change# checkpoin unrecoverable_change# unrecover last_change# last_time offline_change# online_change# ---------- ---------------- --------- ---------- ---------- ------- --------------------------- --------- --------------------- --------- -------------------- --------------- -------------online_ti bytes blocks create_bytes block_size --------- ---------- ---------- ------------ ---------name -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------plugged_in block1_offset ---------- ------------aux_name -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------1 11 12-may-02 0 1 system read write 1092812 24-may-08 0 1009561 1009562
23-may-08
419430400
51200
0
8192
d:\oracle\oradata\iimdba\system01.dbf 0
8192
none
2 187697 12-may-02 1092812 24-may-08 0 1009561 1009562 23-may-08 209715200 25600
1 0
2 online
read write
3 online
read write
4 online
read write
5 online
read write
8192
d:\oracle\oradata\iimdba\undotbs01.dbf 0
8192
none
3 6283 12-may-02 1092812 24-may-08 0 1009561 1009562 23-may-08 20971520 2560
3 0
8192
d:\oracle\oradata\iimdba\cwmlite01.dbf 0
8192
none
4 6302 12-may-02 1092812 24-may-08 0 1009561 1009562 23-may-08 20971520 2560
4 0
8192
d:\oracle\oradata\iimdba\drsys01.dbf 0
8192
none
5 6324 12-may-02 1092812 24-may-08 0 1009561 1009562 23-may-08 155975680 19040 d:\oracle\oradata\iimdba\example01.dbf 0
8192
5 0
8192
none
6 6343 12-may-02 1092812 24-may-08 0 1009561 1009562 23-may-08 26214400 3200
6 0
6 online
read write
7 online
read write
8 online
read write
9 online
read write
10 online
read write
8192
d:\oracle\oradata\iimdba\indx01.dbf 0
8192
none
7 6363 12-may-02 1092812 24-may-08 0 1009561 1009562 23-may-08 20971520 2560
7 0
8192
d:\oracle\oradata\iimdba\odm01.dbf 0
8192
none
8 6382 12-may-02 1092812 24-may-08 0 1009561 1009562 23-may-08 10485760 1280
8 0
8192
d:\oracle\oradata\iimdba\tools01.dbf 0
8192
none
9 328647 14-may-08 1092812 24-may-08 0 1009561 1009562 23-may-08 104857600 12800 104857600
9 8192
d:\bkp\users01.dbf 0
8192
none
10 1092812 24-may-08
6420 12-may-02
10 0
1009561 23-may-08
1009562 39976960
4880
0
8192
d:\oracle\oradata\iimdba\xdb01.dbf 0
8192
none
10 rows selected. sql> clear screen sql> create tablespace temps tempfile 'd:\oracle\oradata\iimdata\temps_v.dbf' minimun extend 2m; create tablespace temps tempfile 'd:\oracle\oradata\iimdata\temps_v.dbf' minimun extend 2m * error at line 1: ora-02180: invalid option for create tablespace sql> ed wrote file afiedt.buf 1* create tablespace temps tempfile 'd:\oracle\oradata\iimdata\temps_v.dbf' size 1m sql> / create tablespace temps tempfile 'd:\oracle\oradata\iimdata\temps_v.dbf' size 1m * error at line 1: ora-02180: invalid option for create tablespace sql> ed wrote file afiedt.buf 1* create temporary tablespace temps tempfile 'd:\oracle\oradata\iimdata\temps_v.dbf' minimum extend 1m sql> / create temporary tablespace temps tempfile 'd:\oracle\oradata\iimdata\temps_v.dbf' minimum extend 1m * error at line 1: ora-25139: invalid option for create temporary tablespace sql> ed wrote file afiedt.buf 1* create temporary tablespace temps tempfile 'd:\oracle\oradata\iimdata\temps_v.dbf' size 1m sql> / create temporary tablespace temps tempfile 'd:\oracle\oradata\iimdata\temps_v.dbf' size 1m
* error at line 1: ora-03214: file size specified is smaller than minimum required sql> ed wrote file afiedt.buf 1* create temporary tablespace temps tempfile 'd:\oracle\oradata\iimdata\temps_v.dbf' size 8m sql> / create temporary tablespace temps tempfile 'd:\oracle\oradata\iimdata\temps_v.dbf' size 8m * error at line 1: ora-01119: error in creating database file 'd:\oracle\oradata\iimdata\temps_v.dbf' ora-27040: skgfrcre: create error, unable to create file osd-04002: unable to open file o/s-error: (os 3) the system cannot find the path specified. sql> eded sp2-0042: unknown command "eded" - rest of line ignored. sql> ed wrote file afiedt.buf 1* create temporary tablespace temps tempfile 'd:\oracle\oradata\iimdata\temps_v.dbf' size 100m sql> / create temporary tablespace temps tempfile 'd:\oracle\oradata\iimdata\temps_v.dbf' size 100m * error at line 1: ora-01119: error in creating database file 'd:\oracle\oradata\iimdata\temps_v.dbf' ora-27040: skgfrcre: create error, unable to create file osd-04002: unable to open file o/s-error: (os 3) the system cannot find the path specified. sql> ed wrote file afiedt.buf 1* create temporary tablespace temps tempfile 'd:\oracle\oradata\iimdba\temps_v.dbf' size 100m sql> / create temporary tablespace temps tempfile 'd:\oracle\oradata\iimdba\temps_v.dbf' size 100m * error at line 1: ora-01119: error in creating database file 'd:\oracle\oradata\iimdba\temps_v.dbf' ora-27038: skgfrcre: file exists osd-04010: option specified, file already exists sql> ed wrote file afiedt.buf
1* create temporary tablespace temps tempfile 'd:\oracle\oradata\iimdba\temps_v1.dbf' size 100m sql> / tablespace created. sql> alter tablespace temps minimum extend 2m; alter tablespace temps minimum extend 2m * error at line 1: ora-02142: missing or invalid alter tablespace option sql> alter tablespace users add datafile 'd:\oracle\oradata\iimdba\users02.dbf' size 200m; tablespace altered. sql> sleect * from v$datafile; sp2-0734: unknown command beginning "sleect * f..." - rest of line ignored. sql> select * from v4datafile; select * from v4datafile * error at line 1: ora-00942: table or view does not exist sql> select * from v$datafile; file# creation_change# creation_ ts# rfile# status enabled checkpoint_change# checkpoin unrecoverable_change# unrecover last_change# last_time offline_change# online_change# ---------- ---------------- --------- ---------- ---------- ------- --------------------------- --------- --------------------- --------- -------------------- --------------- -------------online_ti bytes blocks create_bytes block_size --------- ---------- ---------- ------------ ---------name -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------plugged_in block1_offset ---------- ------------aux_name -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------1 11 12-may-02 0 1 system read write 1092812 24-may-08 0 1009561 1009562 23-may-08 419430400 51200 0 8192
d:\oracle\oradata\iimdba\system01.dbf 0
8192
none
2 187697 12-may-02 1092812 24-may-08 0 1009561 1009562 23-may-08 209715200 25600
1 0
2 online
read write
3 online
read write
4 online
read write
5 online
read write
8192
d:\oracle\oradata\iimdba\undotbs01.dbf 0
8192
none
3 6283 12-may-02 1092812 24-may-08 0 1009561 1009562 23-may-08 20971520 2560
3 0
8192
d:\oracle\oradata\iimdba\cwmlite01.dbf 0
8192
none
4 6302 12-may-02 1092812 24-may-08 0 1009561 1009562 23-may-08 20971520 2560
4 0
8192
d:\oracle\oradata\iimdba\drsys01.dbf 0
8192
none
5 6324 12-may-02 1092812 24-may-08 0 1009561 1009562 23-may-08 155975680 19040 d:\oracle\oradata\iimdba\example01.dbf 0 none
8192
5 0
8192
6 6343 12-may-02 1092812 24-may-08 0 1009561 1009562 23-may-08 26214400 3200
6 0
6 online
read write
7 online
read write
8 online
read write
9 online
read write
10 online
read write
8192
d:\oracle\oradata\iimdba\indx01.dbf 0
8192
none
7 6363 12-may-02 1092812 24-may-08 0 1009561 1009562 23-may-08 20971520 2560
7 0
8192
d:\oracle\oradata\iimdba\odm01.dbf 0
8192
none
8 6382 12-may-02 1092812 24-may-08 0 1009561 1009562 23-may-08 10485760 1280
8 0
8192
d:\oracle\oradata\iimdba\tools01.dbf 0
8192
none
9 328647 14-may-08 1092812 24-may-08 0 1009561 1009562 23-may-08 104857600 12800 104857600
9 8192
d:\bkp\users01.dbf 0
8192
none
10 6420 12-may-02 1092812 24-may-08 0 1009561 1009562
10
23-may-08
39976960
4880
0
8192
d:\oracle\oradata\iimdba\xdb01.dbf 0
8192
none
11 1115843 24-may-08 1115844 24-may-08 0 0 0 209715200 25600 209715200 d:\oracle\oradata\iimdba\users02.dbf 0
8192
none
11 rows selected. sql> select * from v$tablespace; ts# name
inc
---------- ------------------------------ --0 system
yes
1 undotbs1
yes
3 cwmlite
yes
4 drsys
yes
5 example
yes
6 indx
yes
7 odm
yes
8 tools
yes
9 users
yes
10 xdb
yes
2 temp
yes
15 temps
yes
12 rows selected.
9
11 online 8192
read write
sql> alter tablespace temps offline; alter tablespace temps offline * error at line 1: ora-03217: invalid option for alter of temporary tablespace sql> alter tablespace system offline; alter tablespace system offline * error at line 1: ora-01541: system tablespace cannot be brought offline; shut down if necessary sql> alter system example offline; alter system example offline * error at line 1: ora-02065: illegal option for alter system sql>
alter tablespace users offline;
tablespace altered. sql>
alter tablespace users online;
tablespace altered. sql> alter tablespace users read only; tablespace altered. sql> select tablespacename,status from dba_tablespaces; select tablespacename,status from dba_tablespaces * error at line 1: ora-00904: "tablespacename": invalid identifier sql>
select tablespace_name ,status from dba_tablespaces;
tablespace_name
status
------------------------------ --------system
online
undotbs1
online
temp
online
cwmlite
online
drsys
online
example
online
indx
online
odm
online
tools
online
users
read only
xdb
online
temps
online
12 rows selected. sql> alter tablespace users read write; tablespace altered. sql>
select tablespace_name ,status from dba_tablespaces;
tablespace_name
status
------------------------------ --------system
online
undotbs1
online
temp
online
cwmlite
online
drsys
online
example
online
indx
online
odm
online
tools
online
users
online
xdb
online
temps
online
12 rows selected. sql> ------ to find which tablespace for which tables sql> sql> sql> select table_name from dba_tables where tablespace_name='users';
no rows selected sql>
select table_name from dba_tables where tablespace_name='system';
table_name -----------------------------seg$ clu$ obj$ file$ col$ con$ proxy_data$ user$ ind$ fet$ proxy_role_data$ undo$ uet$ icol$ cdef$ bootstrap$ tab$ ts$ ccol$ ugroup$ tsq$ syn$ view$ typed_view$ superobj$
seq$ procedure$ procedureinfo$ argument$ source$ idl_ub1$ idl_char$ idl_ub2$ idl_sb4$ dir$ error$ settings$ resource_plan$ resource_consumer_group$ resource_plan_directive$ trigger$ triggercol$ reg$ loc$ triggerjavaf$ triggerjavas$ triggerjavac$ triggerjavam$ objauth$ sysauth$ objpriv$ defrole$ profile$ profname$
dependency$ access$ aud$ link$ trusted_list$ props$ com$ resource_cost$ incexp incvid incfil _default_auditing_options_ audit$ pending_trans$ pending_sessions$ pending_sub_sessions$ lob$ sumpartlog$ 73 rows selected. sql> ------- to rename the tablespace sql> sql> sql> alter tablespace users rename to users_data; alter tablespace users rename to users_data * error at line 1: ora-01904: datafile keyword expected sql> create tablespace velu datafile 'd:\oracle\oradata\iimdba\velu_sa.dbf' size 1m; tablespace created. sql> -------- rname a datafile in alter tablespace sql> sql> alter tablespace velu datafile 'd:\oracle\oradata\iimdba\velu_sa.dbf' to
'd:\; alter tablespace velu datafile 'd:\oracle\oradata\iimdba\velu_sa.dbf' to 'd:\ * error at line 1: ora-02142: missing or invalid alter tablespace option sql> alter tablespace velu rename datafile 'd:\oracle\oradata\iimdba\velu_sa.dbf' to 'd:\bkp\velu_sa.dbf'; alter tablespace velu rename datafile 'd:\oracle\oradata\iimdba\velu_sa.dbf' to 'd:\bkp\velu_sa.dbf' * error at line 1: ora-01525: error in renaming data files ora-01121: cannot rename database file 12 - file is in use or recovery ora-01110: data file 12: 'd:\oracle\oradata\iimdba\velu_sa.dbf' sql> alter tablespace velu offine; alter tablespace velu offine * error at line 1: ora-02142: missing or invalid alter tablespace option sql> alter tablespace velu offline; tablespace altered. sql> alter tablespace velu rename datafile 'd:\oracle\oradata\iimdba\velu_sa.dbf' to 'd:\bkp\velu_sa.dbf'; alter tablespace velu rename datafile 'd:\oracle\oradata\iimdba\velu_sa.dbf' to 'd:\bkp\velu_sa.dbf' * error at line 1: ora-01525: error in renaming data files ora-01141: error renaming data file 12 - new file 'd:\bkp\velu_sa.dbf' not found ora-01110: data file 12: 'd:\oracle\oradata\iimdba\velu_sa.dbf' 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 velu rename datafile 'd:\oracle\oradata\iimdba\velu_sa.dbf' to 'd:\oracle\oradata\velu_sa.dbf'; alter tablespace velu rename datafile 'd:\oracle\oradata\iimdba\velu_sa.dbf' to 'd:\oracle\oradata\velu_sa.dbf' * error at line 1: ora-01525: error in renaming data files ora-01141: error renaming data file 12 - new file 'd:\oracle\oradata\velu_sa.dbf' not found ora-01110: data file 12: 'd:\oracle\oradata\iimdba\velu_sa.dbf' 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 velu rename datafile 'd:\oracle\oradata\iimdba\velu_sa.dbf' to 'd:\velu_sa.dbf'; alter tablespace velu rename datafile 'd:\oracle\oradata\iimdba\velu_sa.dbf' to 'd:\velu_sa.dbf' * error at line 1: ora-01525: error in renaming data files ora-01141: error renaming data file 12 - new file 'd:\velu_sa.dbf' not found ora-01110: data file 12: 'd:\oracle\oradata\iimdba\velu_sa.dbf' 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 velu rename datafile 'd:\oracle\oradata\iimdba\velu_sa.dbf' to 'd:\oracle\oradata\iimdba\velu_sa1.dbf'; alter tablespace velu rename datafile 'd:\oracle\oradata\iimdba\velu_sa.dbf' to 'd:\oracle\oradata\iimdba\velu_sa1.dbf' * error at line 1: ora-01525: error in renaming data files ora-01141: error renaming data file 12 - new file 'd:\oracle\oradata\iimdba\velu_sa1.dbf' not found ora-01110: data file 12: 'd:\oracle\oradata\iimdba\velu_sa.dbf' 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 velu rename datafile 'd:\oracle\oradata\iimdba\velu_sa.dbf' to 'd:\bkp\velu_sa.dbf'; alter tablespace velu rename datafile 'd:\oracle\oradata\iimdba\velu_sa.dbf' to 'd:\bkp\velu_sa.dbf' * error at line 1: ora-01525: error in renaming data files ora-01141: error renaming data file 12 - new file 'd:\bkp\velu_sa.dbf' not found ora-01110: data file 12: 'd:\oracle\oradata\iimdba\velu_sa.dbf' 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 velu rename datafile 'd:\oracle\oradata\iimdba\velu.dbf' to 'd:\bkp\velu.dbf'; alter tablespace velu rename datafile 'd:\oracle\oradata\iimdba\velu.dbf' to 'd:\bkp\velu.dbf' * error at line 1: ora-01525: error in renaming data files ora-01522: file 'd:\oracle\oradata\iimdba\velu.dbf' to be renamed does not exist sql> alter database velu rename datafile 'd:\oracle\oradata\iimdba\velu_sa.dbf'to 'd:\bkp\velu_sa.dbf'; alter database velu rename datafile 'd:\oracle\oradata\iimdba\velu_sa.dbf'to 'd:\bkp\velu_sa.dbf' *
error at line 1: ora-02231: missing or invalid option to alter database sql> alter database velu rename file 'd:\oracle\oradata\iimdba\velu_sa.dbf'to 'd:\bkp\velu_sa.dbf'; alter database velu rename file 'd:\oracle\oradata\iimdba\velu_sa.dbf'to 'd:\bkp\velu_sa.dbf' * error at line 1: ora-01509: specified name 'velu' does not match actual 'iimdba' sql> alter database rename file 'd:\oracle\oradata\iimdba\velu_sa.dbf'to 'd:\bkp\velu_sa.dbf'; alter database rename file 'd:\oracle\oradata\iimdba\velu_sa.dbf'to 'd:\bkp\velu_sa.dbf' * error at line 1: ora-01511: error in renaming log/data files ora-01141: error renaming data file 12 - new file 'd:\bkp\velu_sa.dbf' not found ora-01110: data file 12: 'd:\oracle\oradata\iimdba\velu_sa.dbf' 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> sql> sql> sql> sql> sql> sql> sql> sql>
-------- so we want to shut down the database --------- to move phyically move the files ------ then mount the database ---------- then use use the alter database rename file ----- and then open the database alter tablespace velu online;
tablespace altered. sql> create table velus(n number default tablespace velu); create table velus(n number default tablespace velu) * error at line 1: ora-00984: column not allowed here sql> ed wrote file afiedt.buf 1* create table velus(n number) default tablespace velu sql> / create table velus(n number) default tablespace velu * error at line 1: ora-00922: missing or invalid option sql> alter user scott default tablespace velu;
user altered. sql> drop tablespace velu; tablespace dropped. sql> ----------- for undo tablespace sql> sql> sql> desc user_free_space name null? type ---------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------tablespace_name varchar2(30) file_id number block_id number bytes number blocks number relative_fno number sql> sql> sql> sql> sql> sql> 2
set lines 200 set pages 200
select * from user_free_space ;
tablespace_name relative_fno
file_id
block_id
bytes
blocks
------------------------------ ---------- ---------- ---------- --------------------system 1
1
50537
262144
32
system 1
1
50697
4128768
504
undotbs1 2
2
297
131072
16
undotbs1 2
2
329
65536
8
undotbs1 2
2
361
262144
32
undotbs1 2
2
521
3145728
384
undotbs1 2
2
1193
199950336
24408
cwmlite 3
3
737
262144
32
cwmlite 3
3
1233
10878976
1328
drsys 4
4
1241
10813440
1320
example 5
5
19025
131072
16
indx 6
6
9
26148864
3192
odm 7
7
1193
11206656
1368
tools 8
8
777
4128768
504
users 9
9
9
104792064
12792
users 11
11
9
209649664
25592
xdb 10
10
4857
196608
24
17 rows selected. sql> spool off
name -----------------------------system undotbs1 cwmlite drsys example indx odm tools users xdb temp
inc --yes yes yes yes yes yes yes yes yes yes yes
11 rows selected. sql> set lines200; sql> set pages 200; sql> sql> select * from user_tablespaces; tablespace_name block_size initial_extent next_extent min_extents max_extents pct_increase min_extlen status contents logging for extent_man allocatio segmen ------------------------------ ---------- -------------- ----------- --------------------- ------------ ---------- --------- --------- --------- --- ------------------ -----system 8192 65536 1 2147483645 65536 online permanent logging no local system manual undotbs1 8192 65536 1 2147483645 65536 online undo logging no local system manual temp 8192 1048576 1048576 1 0 1048576 online temporary nologging no local uniform manual cwmlite 2147483645 system auto drsys 2147483645 system auto example 2147483645 system auto indx 2147483645 system auto odm 2147483645 system auto tools 2147483645 system auto users
8192 65536 online
65536 permanent logging
no
local
8192 65536 online
65536 permanent logging
no
local
8192 65536 online
65536 permanent logging
no
local
8192 65536 online
65536 permanent logging
no
local
8192 65536 online
65536 permanent logging
no
local
8192 65536 online
65536 permanent logging
no
local
8192
65536
1 1 1 1 1 1 1
2147483645 system manual xdb 2147483645 system auto
65536 online
permanent logging
no
local
8192 65536 online
65536 permanent logging
no
local
1
11 rows selected. sql> select * from dba_tablespaces; 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 ------------------------------ ---------- -------------- ----------- --------------------- ------------ ---------- --------- --------- --------- --- ------------------ --- -----system 8192 65536 1 2147483645 65536 online permanent logging no local system no manual undotbs1 8192 65536 1 2147483645 65536 online undo logging no local system no manual temp 8192 1048576 1048576 1 0 1048576 online temporary nologging no local uniform no manual cwmlite 2147483645 system no drsys 2147483645 system no example 2147483645 system no indx 2147483645 system no odm 2147483645 system no tools 2147483645 system no users 2147483645 system no xdb 2147483645 system no
auto auto auto auto auto auto manual auto
8192 65536 online
65536 permanent logging
no
local
8192 65536 online
65536 permanent logging
no
local
8192 65536 online
65536 permanent logging
no
local
8192 65536 online
65536 permanent logging
no
local
8192 65536 online
65536 permanent logging
no
local
8192 65536 online
65536 permanent logging
no
local
8192 65536 online
65536 permanent logging
no
local
8192 65536 online
65536 permanent logging
no
local
11 rows selected. sql> sql> select * from ts$tablespaces; select * from ts$tablespaces * error at line 1: ora-00942: table or view does not exist
1 1 1 1 1 1 1 1
sql> select * from ts$; ts# name owner# online$ contents$ undofile# undoblock# blocksize inc# scnwrp scnbas dflminext dflmaxext dflinit dflincr dflminlen ---------- ------------------------------ ---------- ---------- ------------------- ---------- ---------- ---------- ---------- ---------- ------------------- ---------- ---------- ---------dflextpct dflogging affstrength bitmapped plugged directallowed flags pitrscnwrp pitrscnbas ownerinstance backupowner groupname ---------- ---------- ----------- ---------- ---------- ------------- ------------------- ---------- ----------------------------------------------------------- -----------------------------spare1 spare2 ---------- ---------spare3 -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------spare4 --------0 8 0
0 system 0 128 0 3 0
0 8 0
8192 8 1
2 temp
0
0
0
8
0
1
0
0 1 2147483645 1
1
0
1 undotbs1 0 8192 128 8 0 1 187618 0
1
2 0
0
0
8
0
1
0
0 1 2147483645 1
17
0
0
1
1
0 128 0
0 128
0 5348 0
0 8 0
0
4 drsys 0 128 0 6293 0
0 8 0
0 8
6 indx 0 128
0
0
0
128
1 2147483645
0
1
2
1 0
0
0
8
0
1
0
0 1 2147483645 1
33
0
8192 8 1
1 0
0
0
8
0
1
0
0 1 2147483645 1
33
0
5 example 0 8192 128 8 0 1 6312 0
1
0
3 cwmlite 0 8192 128 8 0 1 6275 0
0 8
8192 128 0
1 0
0
0
8
0
1
0
0 1 2147483645 1
0
8192 8
1
0
0
0
1
0 1 2147483645
33
0
0 6334
1
0
0
8
0
1
33
0
8 rows selected. sql> ------- for tablespace qutoas sql> sql> sql> sql> select * from dba_ts_quotas; tablespace_name username max_bytes blocks max_blocks
bytes
------------------------------ ------------------------------ ------------------- ---------- ---------odm -1
664
-1
odm -1
520
-1
1192
-1
tools -1
768
-1
example -1
200
-1
example -1
848
-1
example -1
1808
-1
example -1 15352
-1
example -1
0
-1
example -1
192
-1
example -1
144
-1
cwmlite -1
odm
5439488
odm_mtr
4259840
olapsys
9764864
rman
6291456
hr
1638400
oe
6946816
pm
14811136
sh
125763584
qs_adm
0
qs
1572864
qs_ws
1179648
example -1
144
-1
example -1
144
-1
example -1
104
-1
example -1
0
-1
example -1
80
-1
qs_es
1179648
qs_os
1179648
qs_cbadm
851968
qs_cb
0
qs_cs
655360
16 rows selected. sql> select * from user_ts_quotas; no rows selected sql> select * from tsq$; ts#
user#
grantor#
blocks
maxblocks
priv1
priv2
priv3 ---------- ---------- ---------- ---------- ---------- ---------- ------------------0
11
0
48
0
0
0
0
5
0
2632
0
0
0
0
21
0
472
0
0
0
0
30
0
56
0
0
0
0
32
0
496
0
0
0
4
33
0
608
0
0
0
10
35
0
4848
0
0
0
4
39
0
624
0
0
0
7
42
0
664
-1
0
0
7
43
0
520
-1
0
0
0 0 0 0 0 0 0 0 0
0 3
44
0
1192
-1
0
0
5
46
0
200
-1
0
0
5
47
0
848
-1
0
0
5
48
0
1808
-1
0
0
5
49
0
15352
-1
0
0
5
51
0
0
-1
0
0
5
52
0
192
-1
0
0
5
53
0
144
-1
0
0
5
54
0
144
-1
0
0
5
55
0
144
-1
0
0
5
56
0
104
-1
0
0
5
57
0
0
-1
0
0
5
58
0
80
-1
0
0
0
59
0
48
0
0
0
8
60
0
768
-1
0
0
9
59
0
0
0
0
0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
26 rows selected. sql> sql> create tablespace velu datafile 'd:\oracle\oradata\iimdba\velu.dbf' size 100m autoextend on next 5m maxsize 200m; tablespace created.
sql> select * from dba_tablespace; select * from dba_tablespace * error at line 1: ora-00942: table or view does not exist sql> select * from dba_tablespaces; 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 ------------------------------ ---------- -------------- ----------- --------------------- ------------ ---------- --------- --------- --------- --- ------------------ --- -----system 8192 65536 1 2147483645 65536 online permanent logging no local system no manual undotbs1 8192 65536 1 2147483645 65536 online undo logging no local system no manual temp 8192 1048576 1048576 1 0 1048576 online temporary nologging no local uniform no manual cwmlite 2147483645 system no drsys 2147483645 system no example 2147483645 system no indx 2147483645 system no odm 2147483645 system no tools 2147483645 system no users 2147483645 system no xdb 2147483645 system no velu 2147483645 system no
auto auto auto auto auto auto manual auto
8192 65536 online
65536 permanent logging
no
local
8192 65536 online
65536 permanent logging
no
local
8192 65536 online
65536 permanent logging
no
local
8192 65536 online
65536 permanent logging
no
local
8192 65536 online
65536 permanent logging
no
local
8192 65536 online
65536 permanent logging
no
local
8192 65536 online
65536 permanent logging
no
local
8192 65536 online
65536 permanent logging
no
local
8192 65536 online
65536 permanent logging
no
local
1 1 1 1 1 1 1 1 1
manual
12 rows selected. sql> alter tablespace velu datafile 'd:\oracle\oradata\iimdba\velu.dbf' autoextend off; alter tablespace velu datafile 'd:\oracle\oradata\iimdba\velu.dbf' autoextend off
* error at line 1: ora-02142: missing or invalid alter tablespace option sql> alter tablespace datafile 'd:\oracle\oradata\iimdba\velu.dbf' autoextend off; alter tablespace datafile 'd:\oracle\oradata\iimdba\velu.dbf' autoextend off * error at line 1: ora-02142: missing or invalid alter tablespace option sql> alter database
datafile 'd:\oracle\oradata\iimdba\velu.dbf' autoextend off;
database altered. sql> sql> sql> select * from dba_data_files; file_name -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------file_id tablespace_name bytes blocks status relative_fno aut maxbytes maxblocks increment_by user_bytes user_blocks ---------- ------------------------------ ---------- ---------- -------------------- --- ---------- ---------- ------------ ---------- ----------d:\oracle\oradata\iimdba\system01.dbf 1 system 1 yes 3.4360e+10
4194302
419430400 419364864
51200 available 51192
209715200 209649664
25600 available 25592
80
20971520 20905984
2560 available 2552
80
20971520 20905984
2560 available 2552
1280
d:\oracle\oradata\iimdba\undotbs01.dbf 2 undotbs1 2 yes 3.4360e+10 4194302
640
d:\oracle\oradata\iimdba\cwmlite01.dbf 3 cwmlite 3 yes 3.4360e+10
4194302
d:\oracle\oradata\iimdba\drsys01.dbf 4 drsys 4 yes 3.4360e+10
4194302
d:\oracle\oradata\iimdba\example01.dbf 5 example 5 yes 3.4360e+10
80
155975680 155910144
19040 available 19032
160
26214400 26148864
3200 available 3192
80
20971520 20905984
2560 available 2552
4194302
40
10485760 10420224
1280 available 1272
0
0
104857600 104792064
12800 available 12792
80
39976960 39911424
4880 available 4872
0
104857600 104792064
12800 available 12792
4194302
d:\oracle\oradata\iimdba\indx01.dbf 6 indx 6 yes 3.4360e+10
4194302
d:\oracle\oradata\iimdba\odm01.dbf 7 odm 7 yes 3.4360e+10
4194302
d:\oracle\oradata\iimdba\tools01.dbf 8 tools 8 yes 3.4360e+10
d:\bkp\users01.dbf 9 no
9 users 0
d:\oracle\oradata\iimdba\xdb01.dbf 10 xdb 10 yes 3.4360e+10
4194302
d:\oracle\oradata\iimdba\velu.dbf 11 velu 11 no
0
0
11 rows selected. sql> sql> archive log list database log mode automatic archival
archive mode enabled
archive destination oldest online log sequence next log sequence to archive current log sequence sql> sql> sql> sql> select * from v$datafile;
d:\oracle\ora92\rdbms 1 3 3
file# creation_change# creation_ ts# rfile# status enabled checkpoint_change# checkpoin unrecoverable_change# unrecover last_change# last_time offline_change# online_change# ---------- ---------------- --------- ---------- ---------- ------- --------------------------- --------- --------------------- --------- -------------------- --------------- -------------online_ti bytes blocks create_bytes block_size --------- ---------- ---------- ------------ ---------name -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------plugged_in block1_offset ---------- ------------aux_name -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------1 11 12-may-02 0 1 system read write 1092812 24-may-08 0 1009561 1009562 23-may-08 419430400 51200 0 8192 d:\oracle\oradata\iimdba\system01.dbf 0
8192
none
2 187697 12-may-02 1092812 24-may-08 0 1009561 1009562 23-may-08 209715200 25600 d:\oracle\oradata\iimdba\undotbs01.dbf 0 none
8192
1 0
2 online 8192
read write
3 6283 12-may-02 1092812 24-may-08 0 1009561 1009562 23-may-08 20971520 2560
3 0
3 online
read write
4 online
read write
5 online
read write
6 online
read write
7 online
read write
8192
d:\oracle\oradata\iimdba\cwmlite01.dbf 0
8192
none
4 6302 12-may-02 1092812 24-may-08 0 1009561 1009562 23-may-08 20971520 2560
4 0
8192
d:\oracle\oradata\iimdba\drsys01.dbf 0
8192
none
5 6324 12-may-02 1092812 24-may-08 0 1009561 1009562 23-may-08 155975680 19040
5 0
8192
d:\oracle\oradata\iimdba\example01.dbf 0
8192
none
6 6343 12-may-02 1092812 24-may-08 0 1009561 1009562 23-may-08 26214400 3200
6 0
8192
d:\oracle\oradata\iimdba\indx01.dbf 0
8192
none
7 6363 12-may-02 1092812 24-may-08 0 1009561 1009562 23-may-08 20971520 2560
7 0
8192
d:\oracle\oradata\iimdba\odm01.dbf 0
8192
none
8 6382 12-may-02 1092812 24-may-08 0 1009561 1009562 23-may-08 10485760 1280
8 0
8 online
read write
9 online
read write
10 online
read write
11 online
read write
8192
d:\oracle\oradata\iimdba\tools01.dbf 0
8192
none
9 328647 14-may-08 1092812 24-may-08 0 1009561 1009562 23-may-08 104857600 12800 104857600
9 8192
d:\bkp\users01.dbf 0
8192
none
10 6420 12-may-02 1092812 24-may-08 0 1009561 1009562 23-may-08 39976960 4880
10 0
8192
d:\oracle\oradata\iimdba\xdb01.dbf 0
8192
none
11 1096445 24-may-08 1096446 24-may-08 0 0 0 104857600 12800 104857600 d:\oracle\oradata\iimdba\velu.dbf 0 none
8192
13 8192
11 rows selected. sql> sql> sql> alter database datafile 'd:\oracle\oradata\iimdba\velu.dbf' extend management local uniform size 100m; alter database datafile 'd:\oracle\oradata\iimdba\velu.dbf' extend management local uniform size 100m * error at line 1: ora-01916: keyword online, offline, resize, autoextend or end/drop expected sql> ed wrote file afiedt.buf 1* alter database datafile 'd:\oracle\oradata\iimdba\velu.dbf' size 100m extend management local uniform size 100k sql> / alter database datafile 'd:\oracle\oradata\iimdba\velu.dbf' size 100m extend management local uniform size 100k * error at line 1: ora-01916: keyword online, offline, resize, autoextend or end/drop expected sql> alter database datafile 'd:\oracle\oradata\iimdba\velu.dbf' 2 resize 100m extend management local uniform size 120k; resize 100m extend management local uniform size 120k * error at line 2: ora-00933: sql command not properly ended sql> ed wrote file afiedt.buf 1 alter database datafile 'd:\oracle\oradata\iimdba\velu.dbf' 2* resize 100m extend management local uniform size 120k 3 / resize 100m extend management local uniform size 120k * error at line 2: ora-00933: sql command not properly ended sql> select * from v$datafile; file# creation_change# creation_ ts# rfile# status enabled checkpoint_change# checkpoin unrecoverable_change# unrecover last_change# last_time offline_change# online_change# ---------- ---------------- --------- ---------- ---------- ------- --------------------------- --------- --------------------- --------- -------------------- --------------- -------------online_ti bytes blocks create_bytes block_size
--------- ---------- ---------- ------------ ---------name -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------plugged_in block1_offset ---------- ------------aux_name -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------1 11 12-may-02 0 1 system read write 1092812 24-may-08 0 1009561 1009562 23-may-08 419430400 51200 0 8192 d:\oracle\oradata\iimdba\system01.dbf 0
8192
none
2 187697 12-may-02 1092812 24-may-08 0 1009561 1009562 23-may-08 209715200 25600
1 0
2 online
read write
3 online
read write
8192
d:\oracle\oradata\iimdba\undotbs01.dbf 0
8192
none
3 6283 12-may-02 1092812 24-may-08 0 1009561 1009562 23-may-08 20971520 2560
3 0
8192
sql> select ts#,name,enables,status from v$datafile; select ts#,name,enables,status from v$datafile * error at line 1: ora-00904: "enables": invalid identifier sql> ed
wrote file afiedt.buf 1* select ts#,name,enabled,status from v$datafile sql> / ts# ---------name -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------enabled status ---------- ------0 d:\oracle\oradata\iimdba\system01.dbf read write system
1 d:\oracle\oradata\iimdba\undotbs01.dbf read write online
3 d:\oracle\oradata\iimdba\cwmlite01.dbf read write online
4 d:\oracle\oradata\iimdba\drsys01.dbf read write online
5 d:\oracle\oradata\iimdba\example01.dbf read write online
6 d:\oracle\oradata\iimdba\indx01.dbf read write online
7 d:\oracle\oradata\iimdba\odm01.dbf read write online
8 d:\oracle\oradata\iimdba\tools01.dbf read write online
9 d:\bkp\users01.dbf read write online
10 d:\oracle\oradata\iimdba\xdb01.dbf read write online
13 d:\oracle\oradata\iimdba\velu.dbf read write online
11 rows selected. sql> set lines 200; sql> set pages 200; sql> / ts# ----------
name -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------enabled status ---------- ------0 d:\oracle\oradata\iimdba\system01.dbf read write system
1 d:\oracle\oradata\iimdba\undotbs01.dbf read write online
3 d:\oracle\oradata\iimdba\cwmlite01.dbf read write online
4 d:\oracle\oradata\iimdba\drsys01.dbf read write online
5 d:\oracle\oradata\iimdba\example01.dbf read write online
6 d:\oracle\oradata\iimdba\indx01.dbf read write online
7 d:\oracle\oradata\iimdba\odm01.dbf read write online
8 d:\oracle\oradata\iimdba\tools01.dbf read write online
9 d:\bkp\users01.dbf read write online
10 d:\oracle\oradata\iimdba\xdb01.dbf read write online
13 d:\oracle\oradata\iimdba\velu.dbf read write online
11 rows selected. sql> sql> select tablespace_name from user_tables; tablespace_name -----------------------------system system system system
system system system system system system system system system system system system system system system system system system
system system system system system system system system system system
system system system system system system system system system system system system system system system system system system system system system system system system system system system system system
system system system system system system system system system system system system system system system system system system system system system system system system system system system system system
system system system system system system system system system system system system system system system system system system system system system system 114 rows selected. sql> select * from dba_tablespace where tablespace_name='users'; select * from dba_tablespace where tablespace_name='users' * error at line 1: ora-00942: table or view does not exist sql> sql> alter database 'd:\oracle\oradata\iimdba\velu.dbf' resize 100m; alter database 'd:\oracle\oradata\iimdba\velu.dbf' resize 100m
* error at line 1: ora-02231: missing or invalid option to alter database sql> ed wrote file afiedt.buf 1* alter database datafile 'd:\oracle\oradata\iimdba\velu.dbf' resize 100m sql> / database altered. sql> alter database datafile 'd:\oracle\oradata\iimdba\velu.dbf' autoextent on next 1m maxsize 200m; alter database datafile 'd:\oracle\oradata\iimdba\velu.dbf' autoextent on next 1m maxsize 200m * error at line 1: ora-01916: keyword online, offline, resize, autoextend or end/drop expected sql> alter database datafile 'd:\oracle\oradata\iimdba\velu.dbf' autoextend next 1m maxsize 200m;
on
database altered. sql> alter database datafile 'd:\oracle\oradata\iimdba\velu.dbf' extent management local uniform size 1m; alter database datafile 'd:\oracle\oradata\iimdba\velu.dbf' extent management local uniform size 1m * error at line 1: ora-01916: keyword online, offline, resize, autoextend or end/drop expected sql> alter database datafile 'd:\oracle\oradata\iimdba\velu.dbf' extend management local uniform size 1m; alter database datafile 'd:\oracle\oradata\iimdba\velu.dbf' extend management local uniform size 1m * error at line 1: ora-01916: keyword online, offline, resize, autoextend or end/drop expected sql> select dbms_metadata.get_ddl('tablespace', 'uwdata') 2 from dual; error: ora-25153: temporary tablespace is empty ora-06512: at "sys.dbms_lob", line 424 ora-06512: at "sys.dbms_metadata", line 557 ora-06512: at "sys.dbms_metadata", line 1221 ora-06512: at line 1
no rows selected
sql> select dbms_metadata.get_ddl('tablespace', 'users') from dual; error: ora-25153: temporary tablespace is empty ora-06512: at "sys.dbms_lob", line 424 ora-06512: at "sys.dbms_metadata", line 557 ora-06512: at "sys.dbms_metadata", line 1221 ora-06512: at line 1
no rows selected sql> db_create_file_dest sp2-0734: unknown command beginning "db_create_..." - rest of line ignored. sql> sql> sql> sql> -----------to set the default location sql> sql> sql> alter systems set db_create_file_dest='d:\bkp 2 '; alter systems set db_create_file_dest='d:\bkp * error at line 1: ora-00940: invalid alter command sql> alter systems set db_create_file_dest= d:\bkp'; alter systems set db_create_file_dest= d:\bkp' * error at line 1: ora-00940: invalid alter command sql> alter systems set db_create_file_dest= $d:\bkp'; alter systems set db_create_file_dest= $d:\bkp' * error at line 1: ora-00940: invalid alter command sql> sql> sql> dbms_space_admin.tablespace_migrate_to_local('velu'); sp2-0734: unknown command beginning "dbms_space..." - rest of line ignored. sql> dbms_space_admin.tablespace_migrate_to_local('velu'); sp2-0734: unknown command beginning "dbms_space..." - rest of line ignored. sql> sql> sql> create temporary tablespace temp_v tempfile 'd:\oracle\oradata\iimdba\temps_v.dbf' size 100m anutoextend o; create temporary tablespace temp_v tempfile 'd:\oracle\oradata\iimdba\temps_v.dbf' size 100m anutoextend o * error at line 1: ora-02180: invalid option for create tablespace
sql> ed wrote file afiedt.buf 1* create temporary tablespace temp_v tempfile 'd:\oracle\oradata\iimdba\temps_v.dbf' size 100k autoextend on next 1m maxsize 100m sql> / create temporary tablespace temp_v tempfile 'd:\oracle\oradata\iimdba\temps_v.dbf' size 100k autoextend on next 1m maxsize 100m * error at line 1: ora-03214: file size specified is smaller than minimum required sql> ed wrote file afiedt.buf 1* create temporary tablespace temp_v tempfile 'd:\oracle\oradata\iimdba\temps_v.dbf' size 100k autoextend on next 1m maxsize 1000m sql> / create temporary tablespace temp_v tempfile 'd:\oracle\oradata\iimdba\temps_v.dbf' size 100k autoextend on next 1m maxsize 1000m * error at line 1: ora-03214: file size specified is smaller than minimum required sql> ed wrote file afiedt.buf 1* create temporary tablespace temp_v tempfile 'd:\oracle\oradata\iimdba\temps_v.dbf' size 1000k autoextend on next 1m maxsize 100m sql> sql> / create temporary tablespace temp_v tempfile 'd:\oracle\oradata\iimdba\temps_v.dbf' size 1000k autoextend on next 1m maxsize 100m * error at line 1: ora-03214: file size specified is smaller than minimum required sql> sql> ed wrote file afiedt.buf 1* create temporary tablespace temp_v tempfile 'd:\oracle\oradata\iimdba\temps_v.dbf' size 1000k sql> / create temporary tablespace temp_v tempfile 'd:\oracle\oradata\iimdba\temps_v.dbf' size 1000k * error at line 1: ora-03214: file size specified is smaller than minimum required
sql> ed wrote file afiedt.buf 1* create temporary tablespace temp_v tempfile 'd:\oracle\oradata\iimdba\temps_v.dbf' size 3m sql> / tablespace created. sql> alter database tempfile 'd:\oracle\oradata\iimdba\temps_v.dbf' autoextend on next 1m maxsize 100m; database altered. sql> select * from v$datafile; file# creation_change# creation_ ts# rfile# status enabled checkpoint_change# checkpoin unrecoverable_change# unrecover last_change# last_time offline_change# online_change# ---------- ---------------- --------- ---------- ---------- ------- --------------------------- --------- --------------------- --------- -------------------- --------------- -------------online_ti bytes blocks create_bytes block_size --------- ---------- ---------- ------------ ---------name -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------plugged_in block1_offset ---------- ------------aux_name -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------1 11 12-may-02 0 1 system read write 1092812 24-may-08 0 1009561 1009562 23-may-08 419430400 51200 0 8192 d:\oracle\oradata\iimdba\system01.dbf 0
8192
none
2 187697 12-may-02 1092812 24-may-08 0 1009561 1009562 23-may-08 209715200 25600
1 0
2 online 8192
read write
d:\oracle\oradata\iimdba\undotbs01.dbf 0
8192
none
3 6283 12-may-02 1092812 24-may-08 0 1009561 1009562 23-may-08 20971520 2560
3 0
3 online
read write
4 online
read write
5 online
read write
6 online
read write
8192
d:\oracle\oradata\iimdba\cwmlite01.dbf 0
8192
none
4 6302 12-may-02 1092812 24-may-08 0 1009561 1009562 23-may-08 20971520 2560
4 0
8192
d:\oracle\oradata\iimdba\drsys01.dbf 0
8192
none
5 6324 12-may-02 1092812 24-may-08 0 1009561 1009562 23-may-08 155975680 19040
5 0
8192
d:\oracle\oradata\iimdba\example01.dbf 0
8192
none
6 6343 12-may-02 1092812 24-may-08 0 1009561 1009562 23-may-08 26214400 3200 d:\oracle\oradata\iimdba\indx01.dbf 0 none
8192
6 0
8192
7 6363 12-may-02 1092812 24-may-08 0 1009561 1009562 23-may-08 20971520 2560
7 0
7 online
read write
8 online
read write
9 online
read write
10 online
read write
11 online
read write
8192
d:\oracle\oradata\iimdba\odm01.dbf 0
8192
none
8 6382 12-may-02 1092812 24-may-08 0 1009561 1009562 23-may-08 10485760 1280
8 0
8192
d:\oracle\oradata\iimdba\tools01.dbf 0
8192
none
9 328647 14-may-08 1092812 24-may-08 0 1009561 1009562 23-may-08 104857600 12800 104857600
9 8192
d:\bkp\users01.dbf 0
8192
none
10 6420 12-may-02 1092812 24-may-08 0 1009561 1009562 23-may-08 39976960 4880
10 0
8192
d:\oracle\oradata\iimdba\xdb01.dbf 0
8192
none
11 1096446 24-may-08 0 0
1096445 24-may-08
0
13
104857600
12800
104857600
8192
d:\oracle\oradata\iimdba\velu.dbf 0
8192
none
11 rows selected. sql> select * from v$tablespace; ts# name
inc
---------- ------------------------------ --0 system
yes
1 undotbs1
yes
3 cwmlite
yes
4 drsys
yes
5 example
yes
6 indx
yes
7 odm
yes
8 tools
yes
9 users
yes
10 xdb
yes
2 temp
yes
13 velu
yes
14 temp_v
yes
13 rows selected. sql> select * from database_properties; property_name -----------------------------property_value -------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------description -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------dict.base 2 dictionary base tables version #
default_temp_tablespace temp name of default temporary tablespace
dbtimezone -07:00 db time zone
nls_language american language
nls_territory america territory
nls_currency $ local currency
nls_iso_currency america
iso currency
nls_numeric_characters ., numeric characters
nls_characterset we8mswin1252 character set
nls_calendar gregorian calendar system
nls_date_format dd-mon-rr date format
nls_date_language american date language
nls_sort binary linguistic definition
nls_time_format hh.mi.ssxff am time format
nls_timestamp_format dd-mon-rr hh.mi.ssxff am time stamp format
nls_time_tz_format hh.mi.ssxff am tzr time with timezone format
nls_timestamp_tz_format dd-mon-rr hh.mi.ssxff am tzr timestamp with timezone format
nls_dual_currency $ dual currency symbol
nls_comp binary nls comparison
nls_length_semantics byte nls length semantics
nls_nchar_conv_excp false nls conversion exception
nls_nchar_characterset al16utf16 nchar character set
nls_rdbms_version 9.2.0.1.0 rdbms version for nls parameters
global_db_name iimdba.us.oracle.com global database name
export_views_version 8 export views revision #
25 rows selected. sql> sql> alter database tempfile 'd:\oracle\oradata\iimdba\temps_v.dbf' resize 150m; database altered. sql> alter database tempfile 'd:\oracle\oradata\iimdba\temps_v.dbf' drop; database altered. sql> select * from v$tablespace; ts# name
inc
---------- ------------------------------ --0 system
yes
1 undotbs1
yes
3 cwmlite
yes
4 drsys
yes
5 example
yes
6 indx
yes
7 odm
yes
8 tools
yes
9 users
yes
10 xdb
yes
2 temp
yes
13 velu
yes
14 temp_v
yes
13 rows selected. sql> drop tablespace temp_v; tablespace dropped. sql> drop tablespace velu; tablespace dropped. sql> select * from v$datafile; file# creation_change# creation_ ts# rfile# status enabled checkpoint_change# checkpoin unrecoverable_change# unrecover last_change# last_time offline_change# online_change# ---------- ---------------- --------- ---------- ---------- ------- --------------------------- --------- --------------------- --------- -------------------- --------------- -------------online_ti bytes blocks create_bytes block_size --------- ---------- ---------- ------------ ---------name -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------plugged_in block1_offset ---------- ------------aux_name -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------1 11 12-may-02 0 1 system read write 1092812 24-may-08 0 1009561 1009562
23-may-08
419430400
51200
0
8192
d:\oracle\oradata\iimdba\system01.dbf 0
8192
none
2 187697 12-may-02 1092812 24-may-08 0 1009561 1009562 23-may-08 209715200 25600
1 0
2 online
read write
3 online
read write
4 online
read write
5 online
read write
8192
d:\oracle\oradata\iimdba\undotbs01.dbf 0
8192
none
3 6283 12-may-02 1092812 24-may-08 0 1009561 1009562 23-may-08 20971520 2560
3 0
8192
d:\oracle\oradata\iimdba\cwmlite01.dbf 0
8192
none
4 6302 12-may-02 1092812 24-may-08 0 1009561 1009562 23-may-08 20971520 2560
4 0
8192
d:\oracle\oradata\iimdba\drsys01.dbf 0
8192
none
5 6324 12-may-02 1092812 24-may-08 0 1009561 1009562 23-may-08 155975680 19040 d:\oracle\oradata\iimdba\example01.dbf 0
8192
5 0
8192
none
6 6343 12-may-02 1092812 24-may-08 0 1009561 1009562 23-may-08 26214400 3200
6 0
6 online
read write
7 online
read write
8 online
read write
9 online
read write
10 online
read write
8192
d:\oracle\oradata\iimdba\indx01.dbf 0
8192
none
7 6363 12-may-02 1092812 24-may-08 0 1009561 1009562 23-may-08 20971520 2560
7 0
8192
d:\oracle\oradata\iimdba\odm01.dbf 0
8192
none
8 6382 12-may-02 1092812 24-may-08 0 1009561 1009562 23-may-08 10485760 1280
8 0
8192
d:\oracle\oradata\iimdba\tools01.dbf 0
8192
none
9 328647 14-may-08 1092812 24-may-08 0 1009561 1009562 23-may-08 104857600 12800 104857600
9 8192
d:\bkp\users01.dbf 0
8192
none
10 1092812 24-may-08
6420 12-may-02
10 0
1009561 23-may-08
1009562 39976960
4880
0
8192
d:\oracle\oradata\iimdba\xdb01.dbf 0
8192
none
10 rows selected. sql> clear screen sql> create tablespace temps tempfile 'd:\oracle\oradata\iimdata\temps_v.dbf' minimun extend 2m; create tablespace temps tempfile 'd:\oracle\oradata\iimdata\temps_v.dbf' minimun extend 2m * error at line 1: ora-02180: invalid option for create tablespace sql> ed wrote file afiedt.buf 1* create tablespace temps tempfile 'd:\oracle\oradata\iimdata\temps_v.dbf' size 1m sql> / create tablespace temps tempfile 'd:\oracle\oradata\iimdata\temps_v.dbf' size 1m * error at line 1: ora-02180: invalid option for create tablespace sql> ed wrote file afiedt.buf 1* create temporary tablespace temps tempfile 'd:\oracle\oradata\iimdata\temps_v.dbf' minimum extend 1m sql> / create temporary tablespace temps tempfile 'd:\oracle\oradata\iimdata\temps_v.dbf' minimum extend 1m * error at line 1: ora-25139: invalid option for create temporary tablespace sql> ed wrote file afiedt.buf 1* create temporary tablespace temps tempfile 'd:\oracle\oradata\iimdata\temps_v.dbf' size 1m sql> / create temporary tablespace temps tempfile 'd:\oracle\oradata\iimdata\temps_v.dbf' size 1m
* error at line 1: ora-03214: file size specified is smaller than minimum required sql> ed wrote file afiedt.buf 1* create temporary tablespace temps tempfile 'd:\oracle\oradata\iimdata\temps_v.dbf' size 8m sql> / create temporary tablespace temps tempfile 'd:\oracle\oradata\iimdata\temps_v.dbf' size 8m * error at line 1: ora-01119: error in creating database file 'd:\oracle\oradata\iimdata\temps_v.dbf' ora-27040: skgfrcre: create error, unable to create file osd-04002: unable to open file o/s-error: (os 3) the system cannot find the path specified. sql> eded sp2-0042: unknown command "eded" - rest of line ignored. sql> ed wrote file afiedt.buf 1* create temporary tablespace temps tempfile 'd:\oracle\oradata\iimdata\temps_v.dbf' size 100m sql> / create temporary tablespace temps tempfile 'd:\oracle\oradata\iimdata\temps_v.dbf' size 100m * error at line 1: ora-01119: error in creating database file 'd:\oracle\oradata\iimdata\temps_v.dbf' ora-27040: skgfrcre: create error, unable to create file osd-04002: unable to open file o/s-error: (os 3) the system cannot find the path specified. sql> ed wrote file afiedt.buf 1* create temporary tablespace temps tempfile 'd:\oracle\oradata\iimdba\temps_v.dbf' size 100m sql> / create temporary tablespace temps tempfile 'd:\oracle\oradata\iimdba\temps_v.dbf' size 100m * error at line 1: ora-01119: error in creating database file 'd:\oracle\oradata\iimdba\temps_v.dbf' ora-27038: skgfrcre: file exists osd-04010:
1* create temporary tablespace temps tempfile 'd:\oracle\oradata\iimdba\temps_v1.dbf' size 100m sql> / tablespace created. sql> alter tablespace temps minimum extend 2m; alter tablespace temps minimum extend 2m * error at line 1: ora-02142: missing or invalid alter tablespace option sql> alter tablespace users add datafile 'd:\oracle\oradata\iimdba\users02.dbf' size 200m; tablespace altered. sql> sleect * from v$datafile; sp2-0734: unknown command beginning "sleect * f..." - rest of line ignored. sql> select * from v4datafile; select * from v4datafile * error at line 1: ora-00942: table or view does not exist sql> select * from v$datafile; file# creation_change# creation_ ts# rfile# status enabled checkpoint_change# checkpoin unrecoverable_change# unrecover last_change# last_time offline_change# online_change# ---------- ---------------- --------- ---------- ---------- ------- --------------------------- --------- --------------------- --------- -------------------- --------------- -------------online_ti bytes blocks create_bytes block_size --------- ---------- ---------- ------------ ---------name -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------plugged_in block1_offset ---------- ------------aux_name -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------1 11 12-may-02 0 1 system read write 1092812 24-may-08 0 1009561 1009562 23-may-08 419430400 51200 0 8192
d:\oracle\oradata\iimdba\system01.dbf 0
8192
none
2 187697 12-may-02 1092812 24-may-08 0 1009561 1009562 23-may-08 209715200 25600
1 0
2 online
read write
3 online
read write
4 online
read write
5 online
read write
8192
d:\oracle\oradata\iimdba\undotbs01.dbf 0
8192
none
3 6283 12-may-02 1092812 24-may-08 0 1009561 1009562 23-may-08 20971520 2560
3 0
8192
d:\oracle\oradata\iimdba\cwmlite01.dbf 0
8192
none
4 6302 12-may-02 1092812 24-may-08 0 1009561 1009562 23-may-08 20971520 2560
4 0
8192
d:\oracle\oradata\iimdba\drsys01.dbf 0
8192
none
5 6324 12-may-02 1092812 24-may-08 0 1009561 1009562 23-may-08 155975680 19040 d:\oracle\oradata\iimdba\example01.dbf 0 none
8192
5 0
8192
6 6343 12-may-02 1092812 24-may-08 0 1009561 1009562 23-may-08 26214400 3200
6 0
6 online
read write
7 online
read write
8 online
read write
9 online
read write
10 online
read write
8192
d:\oracle\oradata\iimdba\indx01.dbf 0
8192
none
7 6363 12-may-02 1092812 24-may-08 0 1009561 1009562 23-may-08 20971520 2560
7 0
8192
d:\oracle\oradata\iimdba\odm01.dbf 0
8192
none
8 6382 12-may-02 1092812 24-may-08 0 1009561 1009562 23-may-08 10485760 1280
8 0
8192
d:\oracle\oradata\iimdba\tools01.dbf 0
8192
none
9 328647 14-may-08 1092812 24-may-08 0 1009561 1009562 23-may-08 104857600 12800 104857600
9 8192
d:\bkp\users01.dbf 0
8192
none
10 6420 12-may-02 1092812 24-may-08 0 1009561 1009562
10
23-may-08
39976960
4880
0
8192
d:\oracle\oradata\iimdba\xdb01.dbf 0
8192
none
11 1115843 24-may-08 1115844 24-may-08 0 0 0 209715200 25600 209715200 d:\oracle\oradata\iimdba\users02.dbf 0
8192
none
11 rows selected. sql> select * from v$tablespace; ts# name
inc
---------- ------------------------------ --0 system
yes
1 undotbs1
yes
3 cwmlite
yes
4 drsys
yes
5 example
yes
6 indx
yes
7 odm
yes
8 tools
yes
9 users
yes
10 xdb
yes
2 temp
yes
15 temps
yes
12 rows selected.
9
11 online 8192
read write
sql> alter tablespace temps offline; alter tablespace temps offline * error at line 1: ora-03217: invalid option for alter of temporary tablespace sql> alter tablespace system offline; alter tablespace system offline * error at line 1: ora-01541: system tablespace cannot be brought offline; shut down if necessary sql> alter system example offline; alter system example offline * error at line 1: ora-02065: illegal option for alter system sql>
alter tablespace users offline;
tablespace altered. sql>
alter tablespace users online;
tablespace altered. sql> alter tablespace users read only; tablespace altered. sql> select tablespacename,status from dba_tablespaces; select tablespacename,status from dba_tablespaces * error at line 1: ora-00904: "tablespacename": invalid identifier sql>
select tablespace_name ,status from dba_tablespaces;
tablespace_name
status
------------------------------ --------system
online
undotbs1
online
temp
online
cwmlite
online
drsys
online
example
online
indx
online
odm
online
tools
online
users
read only
xdb
online
temps
online
12 rows selected. sql> alter tablespace users read write; tablespace altered. sql>
select tablespace_name ,status from dba_tablespaces;
tablespace_name
status
------------------------------ --------system
online
undotbs1
online
temp
online
cwmlite
online
drsys
online
example
online
indx
online
odm
online
tools
online
users
online
xdb
online
temps
online
12 rows selected. sql> ------ to find which tablespace for which tables sql> sql> sql> select table_name from dba_tables where tablespace_name='users';
no rows selected sql>
select table_name from dba_tables where tablespace_name='system';
table_name -----------------------------seg$ clu$ obj$ file$ col$ con$ proxy_data$ user$ ind$ fet$ proxy_role_data$ undo$ uet$ icol$ cdef$ bootstrap$ tab$ ts$ ccol$ ugroup$ tsq$ syn$ view$ typed_view$ superobj$
seq$ procedure$ procedureinfo$ argument$ source$ idl_ub1$ idl_char$ idl_ub2$ idl_sb4$ dir$ error$ settings$ resource_plan$ resource_consumer_group$ resource_plan_directive$ trigger$ triggercol$ reg$ loc$ triggerjavaf$ triggerjavas$ triggerjavac$ triggerjavam$ objauth$ sysauth$ objpriv$ defrole$ profile$ profname$
dependency$ access$ aud$ link$ trusted_list$ props$ com$ resource_cost$ incexp incvid incfil _default_auditing_options_ audit$ pending_trans$ pending_sessions$ pending_sub_sessions$ lob$ sumpartlog$ 73 rows selected. sql> ------- to rename the tablespace sql> sql> sql> alter tablespace users rename to users_data; alter tablespace users rename to users_data * error at line 1: ora-01904: datafile keyword expected sql> create tablespace velu datafile 'd:\oracle\oradata\iimdba\velu_sa.dbf' size 1m; tablespace created. sql> -------- rname a datafile in alter tablespace sql> sql> alter tablespace velu datafile 'd:\oracle\oradata\iimdba\velu_sa.dbf' to
'd:\; alter tablespace velu datafile 'd:\oracle\oradata\iimdba\velu_sa.dbf' to 'd:\ * error at line 1: ora-02142: missing or invalid alter tablespace option sql> alter tablespace velu rename datafile 'd:\oracle\oradata\iimdba\velu_sa.dbf' to 'd:\bkp\velu_sa.dbf'; alter tablespace velu rename datafile 'd:\oracle\oradata\iimdba\velu_sa.dbf' to 'd:\bkp\velu_sa.dbf' * error at line 1: ora-01525: error in renaming data files ora-01121: cannot rename database file 12 - file is in use or recovery ora-01110: data file 12: 'd:\oracle\oradata\iimdba\velu_sa.dbf' sql> alter tablespace velu offine; alter tablespace velu offine * error at line 1: ora-02142: missing or invalid alter tablespace option sql> alter tablespace velu offline; tablespace altered. sql> alter tablespace velu rename datafile 'd:\oracle\oradata\iimdba\velu_sa.dbf' to 'd:\bkp\velu_sa.dbf'; alter tablespace velu rename datafile 'd:\oracle\oradata\iimdba\velu_sa.dbf' to 'd:\bkp\velu_sa.dbf' * error at line 1: ora-01525: error in renaming data files ora-01141: error renaming data file 12 - new file 'd:\bkp\velu_sa.dbf' not found ora-01110: data file 12: 'd:\oracle\oradata\iimdba\velu_sa.dbf' 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 velu rename datafile 'd:\oracle\oradata\iimdba\velu_sa.dbf' to 'd:\oracle\oradata\velu_sa.dbf'; alter tablespace velu rename datafile 'd:\oracle\oradata\iimdba\velu_sa.dbf' to 'd:\oracle\oradata\velu_sa.dbf' * error at line 1: ora-01525: error in renaming data files ora-01141: error renaming data file 12 - new file 'd:\oracle\oradata\velu_sa.dbf' not found ora-01110: data file 12: 'd:\oracle\oradata\iimdba\velu_sa.dbf' 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 velu rename datafile 'd:\oracle\oradata\iimdba\velu_sa.dbf' to 'd:\velu_sa.dbf'; alter tablespace velu rename datafile 'd:\oracle\oradata\iimdba\velu_sa.dbf' to 'd:\velu_sa.dbf' * error at line 1: ora-01525: error in renaming data files ora-01141: error renaming data file 12 - new file 'd:\velu_sa.dbf' not found ora-01110: data file 12: 'd:\oracle\oradata\iimdba\velu_sa.dbf' 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 velu rename datafile 'd:\oracle\oradata\iimdba\velu_sa.dbf' to 'd:\oracle\oradata\iimdba\velu_sa1.dbf'; alter tablespace velu rename datafile 'd:\oracle\oradata\iimdba\velu_sa.dbf' to 'd:\oracle\oradata\iimdba\velu_sa1.dbf' * error at line 1: ora-01525: error in renaming data files ora-01141: error renaming data file 12 - new file 'd:\oracle\oradata\iimdba\velu_sa1.dbf' not found ora-01110: data file 12: 'd:\oracle\oradata\iimdba\velu_sa.dbf' 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 velu rename datafile 'd:\oracle\oradata\iimdba\velu_sa.dbf' to 'd:\bkp\velu_sa.dbf'; alter tablespace velu rename datafile 'd:\oracle\oradata\iimdba\velu_sa.dbf' to 'd:\bkp\velu_sa.dbf' * error at line 1: ora-01525: error in renaming data files ora-01141: error renaming data file 12 - new file 'd:\bkp\velu_sa.dbf' not found ora-01110: data file 12: 'd:\oracle\oradata\iimdba\velu_sa.dbf' 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 velu rename datafile 'd:\oracle\oradata\iimdba\velu.dbf' to 'd:\bkp\velu.dbf'; alter tablespace velu rename datafile 'd:\oracle\oradata\iimdba\velu.dbf' to 'd:\bkp\velu.dbf' * error at line 1: ora-01525: error in renaming data files ora-01522: file 'd:\oracle\oradata\iimdba\velu.dbf' to be renamed does not exist sql> alter database velu rename datafile 'd:\oracle\oradata\iimdba\velu_sa.dbf'to 'd:\bkp\velu_sa.dbf'; alter database velu rename datafile 'd:\oracle\oradata\iimdba\velu_sa.dbf'to 'd:\bkp\velu_sa.dbf' *
error at line 1: ora-02231: missing or invalid option to alter database sql> alter database velu rename file 'd:\oracle\oradata\iimdba\velu_sa.dbf'to 'd:\bkp\velu_sa.dbf'; alter database velu rename file 'd:\oracle\oradata\iimdba\velu_sa.dbf'to 'd:\bkp\velu_sa.dbf' * error at line 1: ora-01509: specified name 'velu' does not match actual 'iimdba' sql> alter database rename file 'd:\oracle\oradata\iimdba\velu_sa.dbf'to 'd:\bkp\velu_sa.dbf'; alter database rename file 'd:\oracle\oradata\iimdba\velu_sa.dbf'to 'd:\bkp\velu_sa.dbf' * error at line 1: ora-01511: error in renaming log/data files ora-01141: error renaming data file 12 - new file 'd:\bkp\velu_sa.dbf' not found ora-01110: data file 12: 'd:\oracle\oradata\iimdba\velu_sa.dbf' 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> sql> sql> sql> sql> sql> sql> sql> sql>
-------- so we want to shut down the database --------- to move phyically move the files ------ then mount the database ---------- then use use the alter database rename file ----- and then open the database alter tablespace velu online;
tablespace altered. sql> create table velus(n number default tablespace velu); create table velus(n number default tablespace velu) * error at line 1: ora-00984: column not allowed here sql> ed wrote file afiedt.buf 1* create table velus(n number) default tablespace velu sql> / create table velus(n number) default tablespace velu * error at line 1: ora-00922: missing or invalid option sql> alter user scott default tablespace velu;
user altered. sql> drop tablespace velu; tablespace dropped. sql> ----------- for undo tablespace sql> sql> sql> desc user_free_space name null? type ---------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------tablespace_name varchar2(30) file_id number block_id number bytes number blocks number relative_fno number sql> sql> sql> sql> sql> sql> 2
set lines 200 set pages 200
select * from user_free_space ;
tablespace_name relative_fno
file_id
block_id
bytes
blocks
------------------------------ ---------- ---------- ---------- --------------------system 1
1
50537
262144
32
system 1
1
50697
4128768
504
undotbs1 2
2
297
131072
16
undotbs1 2
2
329
65536
8
undotbs1 2
2
361
262144
32
undotbs1 2
2
521
3145728
384
undotbs1 2
2
1193
199950336
24408
cwmlite 3
3
737
262144
32
cwmlite 3
3
1233
10878976
1328
drsys 4
4
1241
10813440
1320
example 5
5
19025
131072
16
indx 6
6
9
26148864
3192
odm 7
7
1193
11206656
1368
tools 8
8
777
4128768
504
users 9
9
9
104792064
12792
users 11
11
9
209649664
25592
xdb 10
10
4857
196608
24
17 rows selected. sql> spool off
Related Documents
Funda 1 > Table Space - 25th May
November 2019 2
Funda 1 > Man_database - 25th May
November 2019 4
Funda 1 > Undo - 25th May
November 2019 7
Funda 1 > Regologs - 22 May
November 2019 5
Funda 1 > Redolog - 21 May
November 2019 4