Oracle Common Scripts

/*Query the number of connections for Oracle SELECT COUNT (*) from v$session;//Current user connections Select COUNT (*) from v$session t where t.username= ' WIMS ';//Database Allowed  Maximum number of connections select value from v$parameter where name = ' processes '//modify Maximum connections: Alter system set processes = Scope = SPFile; 2. Query the number of concurrent connections for Oracle SELECT COUNT (*) from v$session where status= ' ACTIVE '; 3, view connections for different users select Username,count (username) from V$session where username is not a null group by username;4, view all users: SELECT * from all_users;5, view user or role system permissions (System permissions directly assigned to a user or role): SE Lect * from Dba_sys_privs;select * from user_sys_privs;6, view roles (can only view roles owned by logged-in users) select * from Role_sys_privs;7, View User Object permissions: SELECT * FROM Dba_tab_privs;select * from All_tab_privs;select * from user_tab_privs;8, view all roles: SELECT * from Dba_r Oles;9, view the roles owned by the user or role: SELECT * from Dba_role_privs;select * from USER_ROLE_PRIVS;10, see which users have SYSDBA or Sysoper system permissions (  Require appropriate permissions when querying) select * from V$pwfile_users; Restart database: Shutdown immediate;startup;--See which users are currently using data Select Osuser, a.username,cpu_time/executions/1000000| | ' S ', Sql_fulltext,machinefrom V$session A, V$sqlarea bwhere a.sql_address =b.address ORDER by Cpu_time/executions desc;select Count (*) from V$session #连接 Number of SELECT COUNT (*) from v$session where status= ' ACTIVE ' #并发连接数show parameter processes #最大连接alter System set processes = Val UE scope = spfile;--1, view table in that tablespace select Tablespace_name,table_name from User_talbes where table_name= ' employ ';--2,  Gets the default tablespace for the user select Username, default_tablespace from dba_users where username= ' Scott ';--3, view all files in tablespace select *                                        From Dba_data_files where tablespace_name= ' USERS ';--4, viewing tablespace usage: SELECT TBS table space name, SUM (totalm) total size M, sum (USEDM) used space m, sum                                (REMAINEDM) remaining space m, sum (USEDM)/sum (Totalm) *100 percent used,                                                 SUM (REMAINEDM)/sum (Totalm) *100 remaining percent from (          SELECT b.file_id ID,                               B.tablespace_name TBS, b.file_name name, b.bytes/1024/1024 Totalm, (B.bytes-sum (NVL (a.bytes,0                                 ))/1024/1024 usedm, sum (NVL (a.bytes,0)/1024/1024) remainedm,                                 Sum (NVL (a.bytes,0)/(b.bytes) *100), (+-(SUM (NVL)/(a.bytes,0) b.bytes)) From Dba_free_space a,dba_data_files b WHERE a.file_id = b.file_i D GROUP by B.tablespace_name,b.file_name,b.file_id,b.bytes ORD                                                                                        ER by B.tablespace_name) GROUP by TBS --5, extended table space ALTER DATABASE datafile ' D:\Oracle\PRODUCT\ORADATa\test\users01.  DBF ' resize 50m; --Auto Grow ALTER DATABASE datafile ' D:\oracle\PRODUCT\ORADATA\TEST\USERS01.      DBF ' autoextend on next 50m maxsize 500m;             --Add data file alter tablespace yourtablespacename add datafile ' d:\newtablespacefile.dbf ' size 5m;*/

Oracle Common Scripts