Oracle creates tablespaces, users, assignments (paperback)

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