View Oracle tablespace and create 16 K tablespace

Source: Internet
Author: User
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;

,

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.