Oracle creates table spaces, users, assignments (Lite)

One

1.Oracle CREATE table space, user, Assignment (Lite)

C:\Documents and Settings\administrator>sqlplus/nolog
Sql> Conn/as SYSDBA

2. Delete a user
Drop user username cascade;

3. Create a self-add table space
sql> Create tablespace tablespace datafile ' E:\oracle\product\10.2.0\oradata\ table space. dbf ' size 50m autoextend on;
4. Create a user

Create user username identified by password default tablespace table space;
5. Assigning values

Grant DBA to username;

Exit

6. Import the database

Import the database locally

C:\>imp User name/user password @ instance name File=g:\oracle\oracle.bak full=y

7. Import the Database

Exporting a 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 action

Increase table Space
sql> alter tablespace sjpt resize 500m;
ORA-32773: sjpt of small file table spaces is not supported

If you report the above error, change to:
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 table space sizes:
Select Tablespace_name,sum (bytes)/1024/1024 from Dba_data_files Group by Tablespace_name;

2.2. Size of table space already in use:
Select Tablespace_name,sum (bytes)/1024/1024 from Dba_free_space Group by Tablespace_name;

2.3. So the use of space can be calculated as follows:
Select A.tablespace_name,total,free,total-free used from
(select Tablespace_name,sum (bytes)/1024/1024 Total from Dba_data_files
Group by Tablespace_name) A,
(select Tablespace_name,sum (bytes)/1024/1024 free from Dba_free_space
Group BY Tablespace_name) b
where A.tablespace_name=b.tablespace_name;

Many of the recent backups of Oracle have been imported to new Oracle on new servers, but after importing Oracle on a new server, it is still found using the users table space that was used by default, not using the tablespace I created myself. So from the Internet, a lot of ways to modify the table space is finally done.
The main use of the following SQL:
(1) Query the table that occupies the tablespace: select segment_name,bytes/1024/1024 from dba_segments where tablespace_name= ' ts_name ' and segment_type= ' TABLE ';
(2) Table space used to modify the table: ALTER TABLE buffalo_wf_processinstance move Tablespace "Ts_name";
(3) Modify tablespace with large large field table: 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) Rebuilding indexes on indexed tables: Alter INDEX PK_T_CMS_CATALOG (index name) rebuild; I do not know how to change the table space when you encounter this situation?

Three

3.1

See if the tablespace is self-growing??

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

Select Tablespace_name, autoextensible from Dba_data_files;

----table space does not have an auto-increment property, and the data files in the table space are self-increasing, so the table space is indirectly up to

Select distinct Tablespace_name, autoextensible from Dba_data_files;

3.2

View data files, sometimes multiple, and not in the same directory, so you need to use

SELECT * from Dba_data_files;

2011-01-06

This article is from the "Five Corners" blog, please be sure to keep this source http://hi289.blog.51cto.com/4513812/1832572

Oracle creates table spaces, users, assignments (Lite)