Differences

This shows you the differences between two versions of the page.

--- oracle [2024/12/17 07:00] – mduersch
+++ oracle [2025/08/14 09:19] (current) – [ACLs] mduersch
@@ Line 1: / Line 1: @@
 ====== ORACLE ======
+==== ACLs ====
+ACLs abfragen:
+  SET LINESIZE 150
+  COLUMN host FORMAT A40
+  COLUMN acl FORMAT A50
+  SELECT host, lower_port, upper_port, acl
+  FROM   dba_network_acls
+  ORDER BY host;
+  SET LINESIZE 150
+  COLUMN acl FORMAT A50
+  COLUMN principal FORMAT A20
+  COLUMN privilege FORMAT A10
+  SELECT acl,
+       principal,
+       privilege,
+       is_grant,
+       TO_CHAR(start_date, 'DD-MON-YYYY') AS start_date,
+       TO_CHAR(end_date, 'DD-MON-YYYY') AS end_date
+  FROM   dba_network_acl_privileges
+  ORDER BY acl, principal, privilege;
+Source: https://oracle-base.com/articles/12c/fine-grained-access-to-network-services-enhancements-12cr1
+==== Spool ====
+Spool output as csv
+  connect <user>
+  set markup csv on;
+  spool /tmp/data.out
+  select * from <table>;
+  spool off;
+==== Sessions ====
+ SQL> set line 1000
+ SQL> col NETWORK_SERVICE_BANNER for a100
+ SQL> select SID,SERIAL#,NETWORK_SERVICE_BANNER from V$SESSION_CONNECT_INFO order by SID,SERIAL#;
+  SID SERIAL# NETWORK_SERVICE_BANNER
+  ---------- ----------  ----------------------------------------------------------------------------------------------------
+0
+0
+15475 TCP/IP NT Protocol Adapter for Linux: Version 19.0.0.0.0 - Production
+15475 Encryption service for Linux: Version 19.0.0.0.0 - Production
+15475 AES256 Encryption service adapter for Linux: Version 19.0.0.0.0 - Production        ###　Make sure it     is encrypted with AES256 ###
+15475 Crypto-checksumming service for Linux: Version 19.0.0.0.0 - Production
+Besser:
+  SELECT
+   sid,
+   username,
+   schemaname,
+   osuser,
+   terminal,
+   program
+  FROM sys.gv_$session s
+  WHERE (inst_id, sid) not in
+    (SELECT inst_id, sid
+       FROM sys.gv_$session_connect_info
+        WHERE network_service_banner like '%Encryption service adapter%'
+         or network_service_banner like '%Crypto-checksumming service adapter%')
+  and TYPE <> 'BACKGROUND';
+==== Grants by User ====
+  set long 200000 pages 0 lines 131
+  column meta format a121 word_wrapped
+  select dbms_metadata.get_ddl('USER', '&&username') meta from dual;
+  select dbms_metadata.get_granted_ddl('OBJECT_GRANT', '&&username') meta from dual;
+  select dbms_metadata.get_granted_ddl('ROLE_GRANT', '&&username') meta from dual;
+  select dbms_metadata.get_granted_ddl('SYSTEM_GRANT', '&&username') meta from dual;
 ==== DBexport ====
@@ Line 8: / Line 83: @@
   expdp -help
+==== Tables ====
+size of table
+   SELECT SUM(bytes)/1024/1024 AS "Table Size (MB)" FROM user_segments WHERE segment_name='&tablename';
-==== Tablesoace Usage ====
+==== Tablespace Usage ====
    select sum(round(bytes/1024/1024/1024)) as Summe_Size_GB, sum(round(MAXBYTES/1024/1024/1024)) as Summe_Max_Size_GB from dba_data_files where TABLESPACE_NAME='DATA';
@@ Line 136: / Line 215: @@
 ==== Sessions (to kill?) ====
-set lines 200
+  set lines 200
-set pages 999
+  set pages 999
   col username for a12
@@ Line 158: / Line 237: @@
   ,OSUSER,MACHINE, substr(PROGRAM,1,32) PROGRAM, to_char(LOGON_TIME,'DD.MM.yyyy HH24:MI:SS')LOGON_TIME
    from gv$session
-   where type='USER'
+   where type='USER';
+   # Danach ggf:
+   ALTER SYSTEM KILL SESSION 'sid,serial#';
 ==== AWR ====