Creating Oracle users, authorizing users

1. Querying all Table spaces

Select Dbf.tablespace_name,

Dbf.totalspace " Total (M)",

Dbf.totalblocks as total block number ,

Dfs.freespace " Total remaining (M)",

Dfs.freeblocks " number of remaining blocks ",

(dfs.freespace/dbf.totalspace) * " idle ratio "

From (select T.tablespace_name,

SUM (t.bytes)/1024/1024 Totalspace,

SUM (t.blocks) totalblocks

From Dba_data_files t

Group by T.tablespace_name) DBF,

(Select Tt.tablespace_name,

SUM (tt.bytes)/1024/1024 FreeSpace,

SUM (tt.blocks) freeblocks

From Dba_free_space TT

GROUP by Tt.tablespace_name) DFS

where trim (dbf.tablespace_name) = Trim (dfs.tablespace_name);

2 , querying the table space HBZF information (such as No table space available requires a new table space )

Select t.* from Sys.dba_data_files t where T.tablespace_name = ' hbzf ';

3 , create a new user

Create User Bthb_wjgl

Identified by "BTHB_WJGL"

Default Tablespace HBZF

Profile DEFAULT

Account UNLOCK;

4 , authorization to the user

Grant CONNECT,DBA to BTHB_WJGL;

5 , Query HBZF All users of the table space

SELECT * from Dba_users t where T.default_tablespace = ' HBZF '

Creating Oracle users, authorizing users