Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

Oracle creates tablespaces, users, assignments (paperback)

2025-04-02 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

Shulou(Shulou.com)06/01 Report--

one,

1.Oracle creates tablespaces, users, assignments (paperback)

C:\ Documents and Settings\ Administrator > sqlplus / nolog

SQL > conn / as sysdba

two。 Delete user

Drop user username cascade

3. Create a self-increment tablespace

SQL > create tablespace tablespace datafile'e:\ oracle\ product\ 10.2.0\ oradata\ tablespace .dbf 'size 50m autoextend on

4. Create a user

Create user username identified by password default tablespace tablespace

5. Assignment

Grant dba to username

Exit

6. Import database

Import the database locally

C:\ > imp username / user password @ instance name file=G:\ oracle\ oracle.bak full=y

7. Import database

Export non-local database

C:\ > exp rows=y owner= user name file= "E:\ 289\ library name _ 201107.dmp" log= "e:\ 289\ library name _ 201107.log"

Extra operation

Increase tablespace

SQL > alter tablespace sjpt resize 500m

ORA-32773: operations on small file tablespace SJPT are not supported

If you report the above error, replace it with:

SQL > alter tablespace sjpt add datafile'd:\ oradata\ sjpt1.dbf' size 500m reuse autoextend on next 100m

two,

Http://jun0325.javaeye.com/blog/603783

2.1. View all tablespace sizes:

Select tablespace_name,sum (bytes) / 1024 Universe 1024 from dba_data_files group by tablespace_name

2.2. The size of the tablespace used:

Select tablespace_name,sum (bytes) / 1024 Universe 1024 from dba_free_space group by tablespace_name

2.3. So using space can be calculated as follows:

Select a. Tablespaceful name memorialtotalpenderfreemedtotallivefree used from

(select tablespace_name,sum (bytes) / 1024 Universe 1024 total from dba_data_files

Group by tablespace_name) a

(select tablespace_name,sum (bytes) / 1024 Universe 1024 free from dba_free_space

Group by tablespace_name) b

Where a.tablespace_name=b.tablespace_name

Recently, I have been importing a lot of backups of Oracle to the new Oracle on the new server, but after importing the Oracle of the new server, I found that I was still using the USERS tablespace that I used by default, instead of using the tablespace I created by myself, so I finally got a lot of ways to modify the tablespace from the Internet.

The following sql are mainly used:

(1) query the table occupying the table space: select segment_name,bytes/1024/1024 from dba_segments where tablespace_name='ts_name' and segment_type='TABLE'

(2) modify the table space used by the table: alter table buffalo_wf_processinstance move tablespace "ts_name"

(3) modify the table space with large fields: alter table tb_name (table name) move tablespace tbs_name (table space name) lob (col_lob1 (field name), col_lob2) store as (tablesapce tbs_name)

(4) re-index the indexed table: alter index PK_T_CMS_CATALOG (index name) rebuild; does not know how the experts modify its tablespace in such a situation?

three,

3.1

Check to see if the tablespace is self-increasing?

-if there are multiple data files, a single display is displayed separately,

Select TABLESPACE_NAME, AUTOEXTENSIBLE from dba_data_files

-the tablespace does not have self-increasing attributes, but the data files in the tablespace can be self-incremented, so the tablespace can be self-incremented indirectly.

Select distinct TABLESPACE_NAME, AUTOEXTENSIBLE from dba_data_files

3.2

To view data files, sometimes there are multiple and are not in the same directory, so you need to use

Select * from dba_data_files

2011-01-06

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

Views: 0

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report