TP2

DESC DICT

COL comments  format A60 wrap;
COL tablename format A30;

SELECT *
FROM  dict
WHERE  table_name LIKE 'V$%';






COL y      NOPRINT
COL typFic  FORM A15 HEAD "Type de fichier" TRUNC
COL nom    FORM A65 HEAD "Nom"  WRAP
BREAK ON y ON typFic

SELECT 1 y, 'REDO LOG FILES' typFic, '...'||SUBSTR(l.member,25) nom
FROM  v$logfile l
UNION
SELECT 2 y, 'CONTROL FILES' typFic, '...'||SUBSTR(p.value,25) nom
FROM v$parameter p
UNION
--SELECT 3 y, 'DATABASE FILES' typFic, '...'||SUBSTR(D.name,25) nom
SELECT 3 y, 'DATABASE FILES' typFic, '...'||SUBSTR(D.status,25) nom
FROM  v$datafile D
UNION
SELECT 4 y, 'ARCHIVE REDO' typFic, '...'||SUBSTR(p.value,25) nom
FROM  v$parameter p
WHERE  p.name = 'log_archive_dest'
ORDER BY 1,3;






col fichierDonnee format A25 wrap
col nomTablespace format A15
SELECT x2.name  nomTablespace,
      '\...\' || SUBSTR(x1.name, 34) fichierDonnnee,
      -- x1.creation_time  creeLe,
      x1.status          etat,
      x1.block_size      tailleBloc,
      x1.BYTES          nbByte,
      x1.BLOCKS          nbBloc,
      (x1.block_size * x1.blocks)/1024/1024 MB
FROM  v$datafile  x1,
      v$tablespace x2
WHERE  x1.ts# = x2.ts#;






SELECT *
FROM  sys.dba_segments
WHERE  OWNER NOT IN ('PUBLIC','SYS','SYSTEM')
      AND tablespace_name = 'SYSTEM'
;






col username            format A25
col default_Tablespace  format A20
col temporary_Tablespace format A30
col password            format A20

SELECT  username,
        password,
        default_tablespace,
        temporary_tablespace
FROM    dba_users
ORDER BY username;





SELECT username, password,
      default_tablespace, temporary_tablespace
FROM  dba_users
-- WHERE  username = 'DBUFR';


SELECT * FROM user_role_privs;


SELECT * FROM role_sys_privs
WHERE role = 'CONNEXION';


SELECT * FROM sys.dba_sys_privs
WHERE grantee = 'CONNEXION';

CREATE USER dbufr IDENTIFIED BY dbufr
DEFAULT  tablespace users
TEMPORARY tablespace temp
QUOTA UNLIMITED ON users    quota 0K on SYSTEM;

CREATE ROLE connexion;          -- ( user_role_privs)

GRANT CREATE session, CREATE table, CREATE view,
      CREATE procedure, CREATE synonym,
      CREATE type, EXECUTE ANY PROCEDURE
TO connexion,                    -- ( role_sys_privs)

GRANT connexion TO dbufr;

SELECT username, password,
      default_tablespace, temporary_tablespace
FROM  dba_users
WHERE  username = 'DBUFR';

SELECT * FROM user_role_privs;

SELECT * FROM role_sys_privs
WHERE  role = 'CONNEXION';







SELECT * FROM sys.dba_sys_privs
WHERE grantee = 'CONNEXION';

/*                              PROFIL                              */
CREATE PROFILE profEtudiant LIMIT
  SESSIONS_PER_USER 1
  CPU_PER_SESSION unlimited
  CONNECT_TIME 500;

ALTER PROFILE etudiant LIMIT
  CONNECT_TIME 600;

CREATE USER etudiantMaster2 IDENTIFIED BY dbufr
PROFILE profEtudiant;

SELECT username, value ||
        'bytes' "Current UGA memory"
FROM  v_$session sess,
      v_$sesstat sstat,
      v_$statname sname
WHERE sess.sid = sstat.sid
AND  sstat.statistic# = sname.statistic#
AND  sname.name = 'session uga memory';


SELECT COUNT(*) "ACTIVE USERS"
FROM  v_$session
WHERE  username IS NOT NULL;

SELECT SUBSTR(s.USERNAME,1,15) USERNAME,
      SUBSTR(s.status,1,8)    STATUS,
      SUBSTR(s.server,1,10)  SERVER,
      SUBSTR(s.type,1,10)    TYPE,
      SUBSTR(s.event,1,20)    "WAIT EVENT",
      DECODE(s.command,
              1, 'Create table', 2, 'Inser',  3, 'Select',
              6, 'Update', 7, 'Delete', 8, 'Drop',
              9, 'Create Index', 10, 'Drop Index', 12, 'Drop Table',
              17, 'Grant', 26, 'Lock Table', 42, 'Alter Session',
              43, 'Alter User', 44, 'Commit', 45, 'Rollback',
              s.command) COMMAND
FROM  v_$session s,
      v_$session_wait w
WHERE  (s.sid = w.sid)
AND    s.username != 'SYS'
GROUP BY s.username;

SELECT u.username,
      u.default_tablespace,
      u.temporary_tablespace,
      u.profile,
      r.granted_role,
      r.admin_option,
      r.default_role
FROM  sys.dba_users u, sys.dba_role_privs r
WHERE  u.username = r.grantee (+)
AND    u.username = 'DBUFR'
GROUP BY u.username, u.default_tablespace,
        u.temporary_tablespace, u.profile,
        r.granted_role, r.admin_option,
        r.default_role;





COL SID      FORMAT 999
COL IDLE    FORMAT A20
COL PROGRAM  FORMAT A20
COL USERNAME FORMAT A20

SELECT sid, osuser, username, status,
      TO_CHAR(logon_time, 'DAY HH24:MI:SS')
      LOGON_TIME,
      FLOOR(last_call_et/3600)||':'||
        FLOOR(MOD(last_call_et,3600)/60)||':'||
        MOD(MOD(last_call_et,3600),60) IDLE,
      program
FROM  v_$session
WHERE username IS NOT NULL
ORDER BY last_call_et;







COL name  FORMAT A30
COL value FORMAT A45

SELECT name, type, value
FROM  v$parameter
WHERE  name LIKE 'db%_'
ORDER BY name;






COL sid      FORMAT 9999
COL serial#  FORMAT 9999
COL osuser  FORMAT A30
COL program  FORMAT A20
COL type    FORMAT A12
COL username FORMAT A10

SELECT s.sid,
      s.serial#,
      s.osuser,
      s.program,
      type,
      username
FROM  v$session s
ORDER BY 2;






COL sid      FORMAT 9999
COL serial#  FORMAT 9999
COL osuser  FORMAT A30
COL program  FORMAT A20
COL type    FORMAT A12
COL username FORMAT A10

SELECT s.sid, s.serial#,
      p.spid,
      s.osuser,
      s.program
FROM  v$process p,
      v$session s
WHERE  p.addr = s.paddr
ORDER BY 2;





SELECT sid,
      serial#,
      osuser,
      program,
      type,
      username
FROM  v$session
WHERE  type = 'USER'
ORDER BY 2;

COL object_name FOR A30
COL object_type FOR A20

SELECT object_name, object_type, status
FROM  dba_objects
-- WHERE  object_type != 'INDEX'
ORDER BY 2 DESC;