Oracle Table Space

Create table spaces and users

Create temporary tablespace nhgl_temp
Tempfile ' E:\oracledata\nhgl\nhgl_temp.dbf '
Size 50m
Autoextend on
Next 50m
Extent management Local;

Create Tablespace Nhgl_data
Logging
DataFile ' E:\oracledata\nhgl\nhgl_data.dbf '
Size 100m
Autoextend on
Next 50m
Extent management Local;

Create user chain identified by cz920501
Default Tablespace nhgl_data
Temporary tablespace nhgl_temp;

Grant all privileges to chain;

Delete User and table spaces

Step One: Delete user
Drop Userxxcascade
Note: Delete the user, just delete the schema objects under the user, will not delete the corresponding tablespace.
Step Two: Delete tablespace
DROP tablespace tablespace_name including CONTENTS and datafiles;

--Query the usage of table space
Select UPPER (f.tablespace_name) "Table space name",
D.TOT_GROOTTE_MB "Table space size (M)",
D.tot_grootte_mb-f.total_bytes "used Space (M)",
To_char (ROUND ((d.tot_grootte_mb-f.total_bytes)/d.tot_grootte_mb * 100,2), ' 990.99 ') | | '% ' "use ratio",
F.total_bytes "free Space (M)",
F.max_bytes "Max Block (M)"
From (SELECT Tablespace_name,
ROUND (SUM (BYTES)/(1024x768 * 1024x768), 2) Total_bytes,
ROUND (MAX (BYTES)/(1024x768), 2) max_bytes
From SYS. Dba_free_space
GROUP by Tablespace_name) F,
(SELECT DD.) Tablespace_name,
ROUND (SUM (DD). BYTES)/(1024x768 * 1024x768), 2) TOT_GROOTTE_MB
From SYS. Dba_data_files DD
GROUP by DD. Tablespace_name) D
WHERE D.tablespace_name = F.tablespace_name
ORDER by 1;
--Query The free space of the tablespace
Select Tablespace_name,
COUNT (*) as extends,
Round (sum (bytes)/1024/1024, 2) as MB,
SUM (blocks) as blocks
From Dba_free_space
Group BY Tablespace_name;
　　 --The total capacity of the query table space
Select Tablespace_name, sum (bytes)/1024/1024 as MB
From Dba_data_files
Group BY Tablespace_name;
　　 --Query table space utilization
Select Total.tablespace_name,
Round (total. MB, 2) as TOTAL_MB, exam big Forum
Round (total. Mb-free. MB, 2) as USED_MB,
Round (1-free. Mb/total. MB) * 100, 2) | | '% ' as used_pct
From (select Tablespace_name, sum (bytes)/1024/1024 as MB
From Dba_free_space
Group by Tablespace_name) free,
(select Tablespace_name, sum (bytes)/1024/1024 as MB
From Dba_data_files
Group BY Tablespace_name) total
where free.tablespace_name = Total.tablespace_name;

1. Find the SQL for the current table-level lock as follows:
Select Sess.sid,
sess.serial#,
Lo.oracle_username,
Lo.os_user_name,
Ao.object_name,
Lo.locked_mode
From V$locked_object Lo,
Dba_objects AO,
V$session Sess
where ao.object_id = lo.object_id and lo.session_id = Sess.sid;

2. Kill the Lock table process :
Alter system kill session ' 436,35123 ';

Lock Lookup in 3.RAC environment:
SELECT Inst_id,decode (request,0, ' Holder: ', ' Waiter: ') | | Sid Sess,
ID1, ID2, Lmode, request, Type,block,ctime
From Gv$lock
WHERE (ID1, ID2, type) in
(SELECT id1, Id2, type from Gv$lock WHERE request>0)
ORDER by ID1, request;

4. Monitor the current database who is running what SQL statement
Select Osuser, username, Sql_text
From V$session A, V$sqltext b
where a.sql_address =b.address order by address, piece;

5. Find a user session  with more CPU;
Select A.sid,spid,status,substr (a.program,1,40) prog, a.terminal , osuser,value/60/100 value 
from  v$session a,v$process b,v$sesstat c 
where  c.statistic# =12 and  
       c.sid=a.sid and  
        a.paddr=b.addr  
       ORDER BY value Desc;

6. Viewing deadlock information
Select (SELECT username
           from V$session
         WHERE SID = a.sid) blocker, A.sid, ' is Blocking ',
       (SELECT username
           from V$session
         WHERE SID = b.sid) Blockee, B. SID
  from V$lock A, V$lock b
 where a.block = 1 and b.request > 0 and a.id1 = b.id1 and A.id2 = B.id2;

7. The object with the highest waiting
SELECT O.owner,o.object_name, O.object_type, A.event,
SUM (A.wait_time + a.time_waited) total_wait_time
From V$active_session_history A, dba_objects o
WHERE a.sample_time between sysdate-30/2880 and Sysdate
and a.current_obj# = o.object_id
GROUP by O.owner,o.object_name, O.object_type, a.event
ORDER by Total_wait_time DESC;

SELECT a.session_id, S.osuser, S.machine, S.program, O.owner, O.object_name,
O.object_type, A.event,
SUM (A.wait_time + a.time_waited) total_wait_time
From V$active_session_history A, dba_objects O, v$session s
WHERE a.sample_time between sysdate-30/2880 and Sysdate
and a.current_obj# = o.object_id
and a.session_id = S.sid
GROUP by O.owner,
O.object_name,
O.object_type,
A.event,
A.SESSION_ID,
S.program,
S.machine,
S.osuser
ORDER by Total_wait_time DESC;

8. Query the current number of connected sessions
Select S.value,s.sid,a.username
From
V$sesstat s,v$statname n,v$session A
where
N.statistic#=s.statistic# and
Name= ' Session PGA Memory '
and S.sid=a.sid
Order BY S.value;

9. Waiting for the most users
SELECT S.sid, S.username, SUM (A.wait_time + a.time_waited) total_wait_time
From V$active_session_history A, v$session s
WHERE a.sample_time between sysdate-30/2880 and Sysdate
GROUP by S.sid, S.username
ORDER by Total_wait_time DESC;

10. Waiting for the most SQL
SELECT A.program, a.session_id, a.user_id, D.username, S.sql_text,
SUM (A.wait_time + a.time_waited) total_wait_time
From V$active_session_history A, V$sqlarea s, dba_users D
WHERE a.sample_time between sysdate-30/2880 and Sysdate
and a.sql_id = s.sql_id
and a.user_id = d.user_id
GROUP by A.program, a.session_id, a.user_id, S.sql_text, D.username;

11. View the SQL that consumes the most resources
SELECT hash_value, executions, buffer_gets, Disk_reads, Parse_calls
From V$sqlarea
WHERE buffer_gets > 10000000 OR disk_reads > 1000000
ORDER by Buffer_gets + * Disk_reads DESC;

12. View resource consumption for an SQL statement
SELECT Hash_value, Buffer_gets, disk_reads, executions, parse_calls
From V$sqlarea
WHERE Hash_value = 228801498 and address = Hextoraw (' cbd8e4b0 ');

13. Query the actual SQL that the session executes
SELECT A.sid, A.username, S.sql_text
From V$session A, v$sqltext s
WHERE a.sql_address = s.address
and A.sql_hash_value = S.hash_value
and a.status = ' ACTIVE '
ORDER by A.username, A.sid, s.piece;

14. Show all sessions that are waiting for a lock
SELECT * from Dba_waiters;

Oracle Table Space