View Oracle tablespace and create 16 K tablespace

B. b210241024 tablespace size M, (B. b2-a.a2) 10241024 used M, substr (B. b2-a.a2) B. b2 *, 1, 5) utilization from (selecttablespace

B. b2/1024/1024 tablespace size M, (B. b2-a.a2)/1024/1024 using M, substr (B. b2-a.a2)/B. b2 *, 1, 5) utilization from (select tablespace

Select f. TABLESPACE_NAME,
(T. TOTAL_SPACE-F. FREE_SPACE) "USED (MB )",
F. FREE_SPACE "FREE (MB )",
T. TOTAL_SPACE "TOTAL (MB )",
(ROUND (F. FREE_SPACE/T. TOTAL_SPACE) * 100) | '%' PER_FREE
FROM (SELECT TABLESPACE_NAME,
ROUND (SUM (BLOCKS *
(Select value/1, 1024
From v $ PARAMETER
Where name = 'db _ block_size ')/1024) FREE_SPACE
FROM DBA_FREE_SPACE
Group by TABLESPACE_NAME) F,
(SELECT TABLESPACE_NAME, ROUND (SUM (BYTES/1048576) TOTAL_SPACE
FROM DBA_DATA_FILES
Group by TABLESPACE_NAME) T
Where f. TABLESPACE_NAME = T. TABLESPACE_NAME

Select
A. a1 tablespace name,
C. c2 type,
C. c3 zone management,
B. b2/1024/1024 tablespace size M,
(B. b2-a.a2)/1024/1024 M used,
Substr (B. b2-a.a2)/B. b2 *, 1, 5) Utilization
From
(Select tablespace_name a1, sum (nvl (bytes, 0) a2 from dba_free_space group by tablespace_name),
(Select tablespace_name b1, sum (bytes) b2 from dba_data_files group by tablespace_name) B,
(Select tablespace_name c1, contents c2, extent_management c3 from dba_tablespaces) c
Where a. a1 = B. b1 and c. c1 = B. b1;

Select
B. file_name physical file name,
B. tablespace_name tablespace,
B. bytes/1024/1024 size M,
(B. bytes-sum (nvl (a. bytes, 0)/1024/1024 M used,
Substr (B. bytes-sum (nvl (a. bytes, 0)/(B. bytes) *, 1, 5) Utilization
From dba_free_space a, dba_data_files B
Where a. file_id = B. file_id
Group by B. tablespace_name, B. file_name, B. bytes
Order by B. tablespace_name

Select tablespace_name, file_id, file_name,
Round (bytes/(1024*1024), 0) total_space from dba_data_files order by tablespace_name;

Alter tablespace users add datafile '/data/house/users06.dbf' autoextend on next 100 m maxsize unlimited

Alter tablespace users add datafile '/data/house/users06.dbf' size 32761 m

1. Create a tablespace
Create tablespace tab_house datafile 'd: \ sosdb. dbf' size 500 m autoextend on next 10 m maxsize unlimited;
2. Create a user
Drop user houseproperty cascade;

Create user houseproperty identified by houseproperty default tablespace TAB_HOUSE temporary tablespace temp;

GRANT connect, resource, dba to houseproperty;

3. Restore data

Imp userid = system/Oracle @ house fromuser = houseproperty touser = houseproperty file = E: \ data_backup \ 2011-04-20 \ houseproperty. dmp tablespaces = tab_house rows = y

4. Create a 16-block tablespace
Alter system set db_16k_cache_size = 16 k;
Shutdown immediate;
Startup;
Create tablespace tb_16k datafile 'd: \ ORACLE \ PRODUCT \ 10.2.0 \ ORADATA \ HOUSE \ tb_16k.DBF 'size 2G autoextend on next 500 m maxsize unlimited blocksize 16 k;

5. Move a table to a new tablespace:
Alter table htimage move tablespace tb_16k;

,