You are here

Startseite

Oracle Select Commands Reference

Di, 08/24/2010 - 10:51 — brngor

·        Create Table T_T1 mit den Inhalt von T1
·        So wurde die Tabelle (Index) damals angelegt.
·        User info
·        Uptime der Datenbank.
·        Show total, free and used space in all tablespaces
·        Tablespace Info
·        Tablespace auslastung
·        Datenfiles die in DB Vorh. sind
·        Alle Tabel anzeigen
·        Alle VIEW Tabel anzeigen.
·        Alle Tabellen / Index  mit Owner anzeigen.
·        Welcher User hat „grant“ rechte auf Schema SCC.
·        Table  Größe anzeigen.
·        Anzeigen darf nur der Owner der Tabelle.
·        Instance Info
·        Patch Info
·        Determining File ID, Tablespace Name, and Number of Blocks
·        Infos über die Session die Aktiv sind.
·        Spalten die in Table vorhanden sind
·        Welche user hat welche rolle
·        Es sollen alle Datenbank-Benutzer und die Anzahl ihrer Objekte ermittelt werden.
·        Wieviel Objekte besitzt der User “stbuser”
·        Was für User hat welche Tabellen.
·        Extents größe selectieren
·        Passwort abfragen
·        Einlogen ohne PW auf eine Oracle Instance um PW herrauszukriegen
·        Zeit output über “dual”
·        PW auf “ scc01p “ setzen
·        Security Grants
·        Resizing A Data File
·        Show All Product Information and Version
·        Show Row Counts For All Tables That Have ANALYZE On
·        Select All Users Active In The System
·        Show What A Current User Is Doing
·        SQL String ermitteln.
·        SQL String alle  ermitteln.
·        Create Count For All Tables
·        Show All Indexes
·        Show All Tables
·        Show Space Used
·        Sum Space By Owner
·        Sum Space by Tablespace
·        Show Reads And Writes By File Name In Oracle DB
·        Identify Segments That Are Getting Close To Their Max-Extent Values
·        Identifies Segments That Are Getting Close To Running Out Of Contiguous Free Space
·        Display Count Historical Archived Log Information From The Control File
·        Shows Current Archive Destinations
·        Backups Of Archived Logs
·        Display All Online Redo Log Groups For The database
·        Display All Online Redo Log Fils
·        Display Full und Redo Backups
·        Show All Datafiles For Tablespace And Oracle Stuff

 

  

Create Table T_T1 mit den Inhalt von T1

SQL> create table T_T1 as select * from T1;

Table created.

 

Oder : Tabelle mit 1Mio. Datensätze.

SQL> create table test as select level a from dual connect by level <= 1000000; 

SQL> select count(*) from test;

   COUNT(*)        ----------      1000000

 

 

 

So wurde die Tabelle (Index) damals angelegt.

HR@AE> select dbms_metadata.get_ddl('TABLE','EMPLOYEES','HR') from dual;

   CREATE TABLE "HR"."EMPLOYEES" ("EMPLOYEE_ID" NUMBER(6,0),      "FIRST_NAME" VARCHAR2(20),
         "LAST_NAME" VARCHAR2(25) CONSTRAINT "EMP…………

 

HR@AE> select dbms_metadata.get_ddl('INDEX','JOB_ID_PK','HR') from dual; 

CREATE UNIQUE
INDEX "HR"."JOB_ID_PK" ON "HR"."JOBS"
("JOB_ID")  PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING…………

 

 

 

User info

 SQL> SELECT PASSWORD,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE,CREATED,PROFILE,ACCOUNT_STATUS, EXTERNAL_NAME FROM SYS.DBA_USERS WHERE USERNAME='ATG';

PASSWORD                       DEFAULT_TABLESPACE             TEMPORARY_TABLESPACE           CREATED
------------------------------ ------------------------------ ------------------------------ --------
PROFILE                        ACCOUNT_STATUS                   EXTERNAL_NAME
------------------------------ -------------------------------- ---------------
073307A88123E5EC               SCC                            TEMP                           23.02.09
DEFAULT_ORACLE                 OPEN

 

SQL> SELECT TABLESPACE_NAME,BYTES,MAX_BYTES,BLOCKS,MAX_BLOCKS FROM SYS.DBA_TS_QUOTAS WHERE USERNAME='ATG' AND TABLESPACE_NAME IN (SELECT TABLESPACE_NAME FROM SYS.DBA_TABLESPACES);

  

SQL> SELECT GRANTED_ROLE,ADMIN_OPTION,DEFAULT_ROLE FROM SYS.DBA_ROLE_PRIVS WHERE GRANTEE='ATG';

GRANTED_ROLE                   ADM DEF
------------------------------ --- ---
RESOURCE                       NO  YES
CONNECT                        NO  YES
CTXAPP                         NO  YES

 

SQL> SELECT PRIVILEGE,TABLE_SCHEMA,TABLE_NAME,GRANTABLE, GRANTOR, GRANTEE FROM ALL_TAB_PRIVS  WHERE GRANTEE='SCC';

PRIVILEGE       TABLE_SCHEMA    TABLE_NAME GRA GRANTOR      GRANTE
--------------- --------------- ------------ --- ------------ ------
WRITE           SYS             DMPDIR       NO SYS          SCC
READ            SYS             DMPDIR       NO SYS          SCC

 

SQL> SELECT PRIVILEGE, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, GRANTABLE, GRANTOR, GRANTEE  FROM ALL_COL_PRIVS WHERE GRANTEE='ATG'; 

 

SQL> SELECT PRIVILEGE,ADMIN_OPTION FROM SYS.DBA_SYS_PRIVS WHERE GRANTEE='SCC';

PRIVILEGE                      ADM
------------------------------ ---
UNLIMITED TABLESPACE           NO
ALTER SESSION                  NO
CREATE VIEW                    NO
CREATE MATERIALIZED VIEW       NO
CREATE SYNONYM                 NO
CREATE DATABASE LINK           NO

 

 

 

Uptime der Datenbank. 

SET LINESIZE=250 "Host" 

select 'Hostname : ' || host_name || chr(10)||
  'Instanz Name : ' || instance_name || chr(10)||
  'Started  : ' || to_char(startup_time,'DD-MON-YYYY HH24:MI:SS')||chr(10)||
  'Uptime : ' || floor(sysdate - startup_time) || ' days(s) ' ||
  trunc( 24*((sysdate-startup_time) -
  trunc(sysdate-startup_time))) || ' hour(s) ' ||
  mod(trunc(1440*((sysdate-startup_time) -
  trunc(sysdate-startup_time))), 60) ||' minute(s) ' ||
  mod(trunc(86400*((sysdate-startup_time) -
  trunc(sysdate-startup_time))), 60) ||' seconds' uptime
from v$instance
/ 

UPTIME
--------------------------------------------------------------------------------
Hostname : FFZD0DA4
Instanz Name : SCCD001T
Started  : 07-MAR-2008 16:34:16
Uptime : 5 days(s) 20 hour(s) 19 minute(s) 13 seconds

 

 

 

 Show total, free and used space in all tablespaces

-- #############################################################################################
 -- %Purpose: Show total, free and used space in all tablespaces / database files
-- Use:      Needs Oracle DBA Access
-- #############################################################################################
--
clear buffer
clear columns
clear breaks
set linesize 500
set pagesize 5000
column a1 heading 'Tablespace' format a15
column a2 heading 'Data File' format a45
column a3 heading 'Total|Space [MB]' format 99999.99
column a4 heading 'Free|Space [MB]' format 99999.99
column a5 heading 'Free|%' format 9999.99
break on a1 on report
compute sum of a3 on a1
compute sum of a4 on a1
compute sum of a3 on report
compute sum of a4 on report
SELECT a.tablespace_name a1,
       a.file_name a2,
       a.avail a3,
       NVL(b.free,0) a4,
       NVL(ROUND(((free/avail)*100),2),0) a5
  FROM (SELECT tablespace_name,
               SUBSTR(file_name,1,45) file_name,
               file_id,
               ROUND(SUM(bytes/(1024*1024)),3) avail
          FROM sys.dba_data_files
      GROUP BY tablespace_name,
               SUBSTR(file_name,1,45),
               file_id) a,
       (SELECT tablespace_name,
               file_id,
               ROUND(SUM(bytes/(1024*1024)),3) free
          FROM sys.dba_free_space
      GROUP BY tablespace_name, file_id) b
WHERE a.file_id = b.file_id (+)
ORDER BY 1, 2
/

 

  

 

Tablespace Info

SQL> select t.name Tablespace, f.name Datafile from v$tablespace t, v$datafile f   where t.ts# = f.ts# order by t.name; 

TABLESPACE                     DATAFILE
------------------------------ -------------------------------------------------------------
DATA01              /oracle/oradata1/csmd111p/fora_csmd111p_012
DATA02              /oracle/oradata2/csmd111p/fora_csmd111p_013
INDEX01             /oracle/oradata1/csmd111p/fora_csmd111p_010
INDEX02             /oracle/oradata2/csmd111p/fora_csmd111p_011
RBS                 /oracle/oradata2/csmd111p/fora_csmd111p_023
SYSTEM              /oracle/oradata1/csmd111p/fora_csmd111p_020
TEMP                /oracle/oradata2/csmd111p/fora_csmd111p_021
TOOLS               /oracle/oradata1/csmd111p/fora_csmd111p_022

 

Oder:

SQL> select TABLESPACE_NAME, STATUS, CONTENTS from dba_tablespaces;

 TABLESPACE_NAME                STATUS    CONTENTS
------------------------------ --------- ---------
SYSTEM                         ONLINE    PERMANENT
UNDOTBS1                       ONLINE    UNDO
SYSAUX                         ONLINE    PERMANENT
TEMP                           ONLINE    TEMPORARY
UNDOTBS2                       ONLINE    UNDO
USERS                          ONLINE    PERMANENT
TS_PRISMA_DATA                 ONLINE    PERMANENT

 

 

 

Tablespace auslastung

set linesize 444
column file_name format a66 heading "File"
column tablespace_name format a14 heading "Tablespace"
column allocated format 999,999,999,999 heading "Allocated"
column free format 999,999,999,999 heading "Free"
column used format 999,999,999,999 heading "Used"

select a.file_name file_name,
       a.tablespace_name tablespace_name,
       a.bytes allocated,
       nvl(b.free,0) free,
       a.bytes-nvl(b.free,0) used
  from dba_data_files a,
(select file_id, sum(bytes) free
                          from dba_free_space
                          group by file_id) b
 where a.file_id = b.file_id (+);  

File                                                               Tablespace            Allocated             Free             Used
------------------------------------------------------------------ -------------- ---------------- ---------------- ----------------
/var/opt/data/oracle/SCCD001T/data1/SCCD001T_SYSTEM_001.dbf        SYSTEM              262,144,000      105,103,360      157,040,640
/var/opt/data/oracle/SCCD001T/data1/SCCD001T_RBS_001.dbf           RBS              16,777,216,000   16,696,147,968       81,068,032
/var/opt/data/oracle/SCCD001T/data1/SCCD001T_TOOLS_001.dbf         TOOLS               524,288,000      524,222,464           65,536
/var/opt/data/oracle/SCCD001T/data1/SCCD001T_scc_ctxsys_01_001.dbf SCC_CTXSYS_01     2,097,152,000    1,896,677,376      200,474,624
/var/opt/data/oracle/SCCD001T/data1/SCCD001T_PERF_001.dbf          PERFSTAT            524,288,000      422,772,736      101,515,264
/var/opt/data/oracle/SCCD001T/data1/SCCD001T_SCC_001.dbf           SCC              25,618,808,832   10,303,176,704   15,315,632,128
/var/opt/data/oracle/SCCD001T/data1/SCCD001T_SCC_002.dbf           SCC              23,244,832,768    8,085,307,392   15,159,525,376
/var/opt/data/oracle/SCCD001T/data1/SCCD001T_SCC_003.dbf           SCC              13,958,643,712                0   13,958,643,712

 

Oder:

set linesize 121

SELECT tablespace_name, ROUND(SUM(total_mb)-SUM(free_mb)) CUR_USE_MB, ROUND(SUM(total_mb)) CUR_SZ_MB,
ROUND((SUM(total_mb)-SUM(free_mb))/SUM(total_mb)*100) CUR_PCT_FULL, ROUND(SUM(max_mb) - (SUM(total_mb)-SUM(free_mb))) FREE_SPACE_MB,
ROUND(SUM(max_mb)) MAX_SZ_MB, ROUND((SUM(total_mb)-SUM(free_mb))/SUM(max_mb)*100) PCT_FULL
FROM (
  SELECT tablespace_name, SUM(bytes)/1024/1024 FREE_MB,
  0 TOTAL_MB, 0 MAX_MB
  FROM dba_free_space
  GROUP BY tablespace_name
  UNION
  SELECT tablespace_name, 0 CURRENT_MB,
  SUM(bytes)/1024/1024 TOTAL_MB,
  SUM(DECODE(maxbytes,0,bytes, maxbytes))/1024/1024 MAX_MB
  FROM dba_data_files
  GROUP BY tablespace_name)
GROUP BY tablespace_name;  

TABLESPACE_NAME                CUR_USE_MB  CUR_SZ_MB CUR_PCT_FULL FREE_SPACE_MB  MAX_SZ_MB   PCT_FULL
------------------------------ ---------- ---------- ------------ ------------- ---------- ----------
SCC                                 71965      81306           89          4475      76440         94
SYSTEM                                352        946           37         32416      32768          1
TIVOLIORTS                              0        250            0           250        250          0
USERS                                   0        500            0           500        500          0
PERFSTAT                              583        893           65           310        893         65
SYSAUX                                334        360           93         32434      32768          1
SCC_CTXSYS_01                           0       2048            0          2048       2048          0
UNDOTBS1                              498       8530            6         32270      32768          2

 

 

 

Datenfiles die in DB Vorh. sind 

SQL> select file_name from dba_data_files;

FILE_NAME --------------------------------------
/u02/oracle/oradata/ADP02/system01.dbf                                          
/u02/oracle/oradata/ADP02/rbs01.dbf                                             
/u02/oracle/oradata/ADP02/temp01.dbf                                            
/u02/oracle/oradata/ADP02/tools01.dbf                                           
/u02/oracle/oradata/ADP02/users01.dbf                                           
/u03/oracle/oradata/ADP02/des201.dbf                                            
6 rows selected.

 

 

 

Alle Tabel anzeigen 

SQL> select * from v$database ;

TABLE_NAME
------------------------------
NTDOMAIN
PUBARCHIVE
REGISTERCOURT
REGISTERTYPE
RELATION
RELATIONSUBTYPE

 

 

 

Alle VIEW Tabel anzeigen. 

SQL> select * from cat where TABLE_NAME like 'DBA_%'; 

TABLE_NAME                     TABLE_TYPE
DBA_TAB_COLUMNS                VIEW
DBA_TAB_PRIVS                  VIEW DBA_TS_QUOTAS                  VIEW
DBA_TYPES                      VIEW
DBA_USERS                      VIEW
………


 

 

Alle Tabellen / Index  mit Owner anzeigen. 

SQL> select owner, table_name from dba_tables

OWNER                          TABLE_NAME
------------------------------ ------------------------------
SCC                            BUSINESSSECTOR
SCC                            BUSINESSLOG
SCC                            CITY
SCC                            CITYZIPCODE
….
…
OWNER                          TABLE_NAME
------------------------------ ------------------------------
SCC                            RIORIGHT
SCC                            RIOUSERPROPERTY
….
…

 

SQL> select owner, bytes, SEGMENT_NAME, SEGMENT_TYPE from dba_segments where owner='SCC' ORDER BY BYTES 

SCC                                 65536 HUHU                              TABLE
SCC                                 65536 COUNTRY                           TABLE
SCC                                 65536 IDX_COUNTRY_ENGLNAME              INDEX
SCC                                 65536 IDX_COUNTRY_GERMNAME              INDEX
SCC                                 65536 IDX_COUNTRY_DBCOUNTRYCODE         INDEX
SCC                                 65536 SYS_C004498                       INDEX

 

 

 

Welcher User hat „grant“ rechte auf Schema SCC. 

SQL> select GRANTEE, OWNER, TABLE_NAME from dba_tab_privs where GRANTEE = 'P881285' 

GRANTEE                        OWNER                          TABLE_NAME
------------------------------ ------------------------------ ------------------------------
P881285                        SCC                            PUBMIST
P881285                        SCC                            REGISTERCOURT
P881285                        SCC                            REGISTERTYPE
P881285                        SCC                            RELATION
P881285                        SCC                            RELATIONTYPE 

 


 

Table  Größe anzeigen.

als SYS
SQL> select sum(bytes)/1024/1024 USED_MB from dba_segments where owner = 'SCC' and segment_name = 'SUBARCHIVE_S1'; 

   USED_MB
----------
      2359


Oder:
 

SQL> select owner, bytes, SEGMENT_NAME from dba_segments where owner='SCC' ORDER BY BYTES; 

OWNER                     BYTES SEGMENT_NAME
-------------------- ---------- -------------------
SCC                  2348810240 CLIENT
SCC                  2348810240 NATIVECLIENT
SCC                  2473590784 SUBARCHIVE

 

FFZD0DA4@oracle[SCC01T]: echo "2473590784/1024/1024" | bc

2359 MB

 

Oder:

 

Als Schema User einlogen.

SQL> SELECT name, SUM(mb)
  FROM (
    SELECT
      nvl2(l.segment_name, l.TABLE_NAME, s.segment_name) name,
      ROUND(bytes / 1024 / 1024) mb
    FROM user_segments s, user_lobs l
    WHERE s.segment_name = l.segment_name(+)
      AND ROUND(bytes / 1024 / 1024) > 0
    ORDER BY 1, 2)
  GROUP BY name
  ORDER BY 2;

NAME                                                                                 SUM(MB)
--------------------------------------------------------------------------------- ----------
CLIENTINDEX                                                                             1094
DR$TDX_CLIENT_STREET$I                                                                  1095
DR$TDX_CLIENT_NAME$I                                                                    1223
IDX_CLIENT_DAT_UPD                                                                      1349
IDX_NCLIENT_DAT_IMP                                                                     1734
CLIENT                                                                                  2240
NATIVECLIENT                                                                            2240
HOSPITAL_ARCHIVE                                                                        3008
SUBARCHIVE                                                                              3575

 

 

 

Oder:  sql abfrage auf alle Table/Index 

SQL> SELECT segment_name, segment_type, round(bytes/1024/1024) MB FROM user_segments
 where round(bytes/1024/1024)>0 ORDER BY MB 

SEGMENT_NAME                                           SEGMENT_TYPE               MB
-------------------------------------------------------------- ----------------- -----
CLIENTINDEX                                              TABLE                    1094
IDX_CLIENT_DAT_UPD                                       INDEX                    1157
IDX_NCLIENT_DAT_IMP                                      INDEX                    1286
CLIENT                                                   TABLE                    1984
NATIVECLIENT                                             TABLE                    2048
PUBARCHIVE                                               TABLE                    2199
SUBARCHIVE                                               TABLE                    6817
PUBBUFFER                                                TABLE                   11193

  

 

Anzeigen darf nur der Owner der Tabelle. 

SQL> desc SCC.RIORIGHT;

 Name              Null?    Type
------------      -----     ----------
 ID               NOT NULL NUMBER(19)
 BERECHTIGUNG              VARCHAR2(50)
 WORKPLACEID               NUMBER(19)

 

 

 

Instance Info

SQL> select * from v$instance; 

INSTANCE_NUMBER INSTANCE_NAME    HOST_NAME   VERSION     STARTUP_T STATUS   PAR    THREAD# ARCHIVE LOG_SWITCH_ LOGINS     SHU DATABASE_STATUS   INSTANCE_ROLE      ACTIVE_ST

--------------- ---------------- -------------------------------------------------

              1 SCCD001P         RFX88160   9.2.0.6.0   16-MAY-06 OPEN         NO           1 STOPPED             ALLOWED    NO  ACTIVE            PRIMARY_INSTANCE   NORMAL

 

 

 

Patch Info 

agisora@tgxfi03[SCC01T] $ ./opatch lsinventory

Invoking OPatch 10.2.0.3.0
Oracle interim Patch Installer version 10.2.0.3.0
Copyright (c) 2005, Oracle Corporation.  All rights reserved..

Oracle Home       : /opt/oracle/product/10.2.0.3
Central Inventory : n/a
   from           : 
OPatch version    : 10.2.0.3.0
OUI version       : 10.2.0.3.0
OUI location      : /opt/oracle/product/10.2.0.3/oui
Log file location : n/a

 

 

 

Determining File ID, Tablespace Name, and Number of Blocks

SQL>  select file_name, file_id, tablespace_name, blocks from dba_data_files; 

FILE_NAME                                                             FILE_ID TABLESPACE_NAME   BLOCKS
------------------------------------------------------------------ ---------- ----------------------------
/var/opt/data/oracle/SCCD001P/data1/SCCD001P_SYSTEM_001.dbf                 1 SYSTEM            32000
/var/opt/data/oracle/SCCD001P/data1/PERFSTAT_001.dbf                        9 PERFSTAT          64000
/var/opt/data/oracle/SCCD001P/data1/SCCD001P_RBS_001.dbf                    2 RBS               2816000
/var/opt/data/oracle/SCCD001P/data2/SCCD001P_TOOLS_001.dbf                  3 TOOLS             102400
/var/opt/data/oracle/SCCD001P/data1/SCCD001P_SCC_001.dbf                    4 SCC               4194296
/var/opt/data/oracle/SCCD001P/data1/SCCD001P_scc_ctxsys_01_001.dbf          5 SCC_CTXSYS_01     256000
/var/opt/data/oracle/SCCD001P/data1/SCCD001P_SCC_002.dbf                    6 SCC               4194296
/var/opt/data/oracle/SCCD001P/data2/SCCD001P_SCC_003.dbf                    7 SCC               4194296
/var/opt/data/oracle/SCCD001P/data2/SCCD001P_SCC_004.dbf                    8 SCC               2176000


col FILE_NAME for a66
col seg_name for a10
col status for a10
col TBLSP_NAME for a10

SQL>  select a.owner,a.segment_name seg_name,a.status,a.tablespace_name TBLSP_NAME,a.initial_extent init_ext,
a.MIN_EXTENTS MIN_EXT,a.next_extent next_ext,a.MAX_EXTENTS max_ext,b.FILE_NAME
from DBA_ROLLBACK_SEGS a,DBA_DATA_FILES b where a.file_id = b.file_id order by 3,4,1; 

OWNER  SEG_NAME   STATUS     TBLSP_NAME   INIT_EXT    MIN_EXT   NEXT_EXT    MAX_EXT FILE_NAME
------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- -------------------
PUBLIC _SYSSMU1$  ONLINE     RBS            131072          2                 32765 /var/opt/data/oracle/SCCD001P/data1/SCCD001P_RBS_001.dbf
PUBLIC _SYSSMU2$  ONLINE     RBS            131072          2                 32765 /var/opt/data/oracle/SCCD001P/data1/SCCD001P_RBS_001.dbf
PUBLIC _SYSSMU3$  ONLINE     RBS            131072          2                 32765 /var/opt/data/oracle/SCCD001P/data1/SCCD001P_RBS_001.dbf
PUBLIC _SYSSMU5$  ONLINE     RBS            131072          2                 32765 /var/opt/data/oracle/SCCD001P/data1/SCCD001P_RBS_001.dbf
PUBLIC _SYSSMU7$  ONLINE     RBS            131072          2                 32765 /var/opt/data/oracle/SCCD001P/data1/SCCD001P_RBS_001.dbf
PUBLIC _SYSSMU9$  ONLINE     RBS            131072          2                 32765 /var/opt/data/oracle/SCCD001P/data1/SCCD001P_RBS_001.dbf
PUBLIC _SYSSMU10$ ONLINE     RBS            131072          2                 32765 /var/opt/data/oracle/SCCD001P/data1/SCCD001P_RBS_001.dbf
PUBLIC _SYSSMU8$  ONLINE     RBS            131072          2                 32765 /var/opt/data/oracle/SCCD001P/data1/SCCD001P_RBS_001.dbf
PUBLIC _SYSSMU6$  ONLINE     RBS            131072          2                 32765 /var/opt/data/oracle/SCCD001P/data1/SCCD001P_RBS_001.dbf
PUBLIC _SYSSMU4$  ONLINE     RBS            131072          2                 32765 /var/opt/data/oracle/SCCD001P/data1/SCCD001P_RBS_001.dbf
SYS    SYSTEM     ONLINE     SYSTEM          57344          2      57344        505 /var/opt/data/oracle/SCCD001P/data1/SCCD001P_SYSTEM_001.dbf


 

Infos über die Session die Aktiv sind. 

SQL> select * from v$session; 

 

 

 

Spalten die in Table vorhanden sind 

SVRMGR> desc v$session        

Column Name                    Null?    Type
------------------------------ -------- ----
SADDR                                   RAW(4)
SID                                     NUMBER
SERIAL#                                 NUMBER
AUDSID                                  NUMBER
PADDR                                   RAW(4)
USER#                                   NUMBER

 

 

 

Welche user hat welche rolle 

SQL> select * from dba_role_privs;

GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
DBA                            EXP_FULL_DATABASE              NO  YES
DBA                            IMP_FULL_DATABASE              NO  YES
DBA                            DELETE_CATALOG_ROLE            YES YES
DBA                            SELECT_CATALOG_ROLE            YES YES
DBA                            EXECUTE_CATALOG_ROLE           YES YES
DBA                            GATHER_SYSTEM_STATISTICS       NO  YES
SCC                            CONNECT                        YES YES
SCC                            RESOURCE                       YES YES
SCC                            OEM_MONITOR                    YES YES
SYS                            DBA                            YES YES

  

Oder:

 

SQL> select GRANTEE, GRANTED_ROLE from dba_role_privs where GRANTED_ROLE='DBA';

GRANTEE                        GRANTED_ROLE
------------------------------ ------------------------------
SYS                            DBA
G3422                          DBA
CTXSYS                         DBA
ORACLE                         DBA
SYSTEM                         DBA
AGISORA                        DBA
F991964                        DBA
GAA0414                        DBA
XS48SLZ                        DBA


 

 

Ist der User „SCC“ noch angmeldet?
SQL> select username, sid, serial# from v$session where username='SCC'; 

 

 

 

Es sollen alle Datenbank-Benutzer und die Anzahl ihrer Objekte ermittelt werden.

Hinweis: Für dieses Beispiel sind DBA Rechte erforderlich.

SQL> SELECT owner, COUNT(object_name) FROM dba_objects GROUP BY owner;

OWNER                            COUNT(*)
------------------------------ ----------
CTXSYS                                263
MDSYS                                 245
OEM_ORCL                              940
OEM_TAHOE_ORCL                        670
PUBLIC                              11541
RMAN                                  128
SCOTT                                  24
SYS                                 13528
SYSTEM                                436 


 

 

Wieviel Objekte besitzt der User “stbuser” 

$ sqlplus stbuser/stbuser#3 

SQL> select object_type, count(*) from user_objects group by object_type; 

OBJECT_TYPE          COUNT(*)
------------------ ----------
TABLE                       1

  

 

 

Was für User hat welche Tabellen.

SQL> select OWNER, OBJECT_NAME FROM dba_objects WHERE object_type IN ('TABLE') AND owner IN ('SCC'); 

SCC                            ACADEMICTITLE
SCC                            BISTA
SCC                            BISTAEXTENSION
SCC                            BUSINESSLOG
SCC                            BUSINESSSECTOR
SCC                            CITY


Oder:

 

SQL> select OWNER, OBJECT_NAME FROM dba_objects WHERE object_type like 'IND%' and owner in ('SCC') 

OWNER          OBJECT_NAME
-------------- ---------------------------------
SCC            IDX_CLIENT_BUNDESBANKNUMBER
SCC            IDX_CLIENT_BUNDESBKNUM_NAT
SCC            IDX_CLIENT_COUNTRYID
SCC            IDX_CLIENT_COUNTRY_NAT
SCC            IDX_CLIENT_BUSISECT
SCC            IDX_CLIENT_BUSISECT_NAT
SCC            IDX_CLIENT_LEGALFORM
SCC            IDX_CLIENT_LEGALFORM_NAT
SCC            IDX_CLIENT_REGCOURT

 

Oder:

 

SQL> select OWNER,TABLE_NAME from dba_tables where OWNER='SCC' and TABLE_NAME like 'C%'; 

OWNER                          TABLE_NAME
------------------------------ ------------------------------
SCC                            CITY
SCC                            CITYZIPCODE
SCC                            CLIENT
SCC                            CLIENTINDEX
SCC                            CLIENTSTATUS
SCC                            CLIENTSUBTYPE
SCC                            CLIENTTYPE
SCC                            CLIENT_DPL
SCC                            CLIENT_DPL_TMP
SCC                            COUNTRY

 

 

 

Extents größe selectieren

SQL> SELECT SUBSTR(owner||'.'||segment_name,1,50) OBJECT, EXTENTS
FROM DBA_SEGMENTS
WHERE EXTENTS > 50
ORDER BY EXTENTS;

 

SQL> SELECT SUBSTR(owner||'.'||segment_name,1,50) OBJECT, EXTENTS
FROM DBA_SEGMENTS
WHERE EXTENTS > 5
ORDER BY EXTENTS;

Output:

OBJECT                                                EXTENTS
-------------------------------------------------- ----------
ATG.DSS_IND_SCENARIO                                        6
SYS.I_COL1                                                  7
SYS.ARGUMENT$                                               8
SYS.C_TOID_VERSION#                                         8
SYS.I_SOURCE1                                              36
SYS.RBS006                                                 60
SYS.RBS007                                                 60
SYS.SOURCE$                                                75
ATG.BNS_ORDER_STATE                                       236

 

 

 

Passwort abfragen 

SQL> select password, USERNAME from dba_users where USERNAME like '%012%'; 

PASSWORD                       USERNAME
------------------------------ ------------------------------
92C149F05E01F1D9               P012886
75ADE7BFF1447184               P012026

  

 

 

Einlogen ohne PW auf eine Oracle Instance um PW herrauszukriegen

RFX88160@oracle[SCC01P]: sqlplus /nolog

SQL> show user

USER is ""


SQL> connect /

Connected.


SQL> show user

USER is "ORACLE"

 

SQL> select username, password from dba_users; 

USERNAME                       PASSWORD
------------------------------ ------------------------------
SYS                            5AF435D26C967D91
SYSTEM                         D1E703A29B6F7BD4
OUTLN                          B095960276C2463F
ORACLE                         EXTERNAL
DBSNMP                         883004E386F38B20
CTXSYS                         C389DE80CD48D8EB
SCC                            11C8142279B3CC91

 
Oder:

RFX88160@oracle[SCC01P]: sqlplus ‘/as sysdba’

 

 

 

Zeit output über “dual”

SQL> SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') FROM dual; 

TO_CHAR(SYSDATE,'DD-
--------------------
14-FEB-2008 03:49:35

 
Oder:

SQL> SELECT   to_char(sysdate, 'HH24:MI:SS') AS zeit_sysdate, to_char(systimestamp, 'HH24:MI:SS.FF6')AS zeit_systimestamp FROM dual 

ZEIT_SYS ZEIT_SYSTIMESTAMP
-------- ------------------
08:57:59 08:57:59.475052

 

 

 

PW auf “ scc01p “ setzen

SQL> alter user system identified by scc01p; 

User altered.

 

 

 

Übersicht aller Tabellen :

 SQL>  SELECT * FROM DICT;

            bzw. gefiltert :

 SQL> SELECT * FROM DICT WHERE TABLE_NAME LIKE upper('%tab_name%'); 

 

 

 

Security Grants 

 SQL> grant select on PERSON_TABLE to public with grant option;

 SQL> select * from dba_tab_privs where TABLE_NAME = 'SUBARCHIVE'  

GRANTEE        OWNER       TABLE_NAME                     GRANTOR                      PRIVILEGE        GRA HIE
-------------- ----------- ------------------------------ ---------------------------- ---------------- --- ---
F986942        SYS         SUBARCHIVE                     SYS                          SELECT           NO  NO
F986942        SCC         SUBARCHIVE                     SCC                          SELECT           NO  NO
P881285        SYS         SUBARCHIVE                     SYS                          SELECT           NO  NO
P881285        SCC         SUBARCHIVE                     SCC                          SELECT           NO  NO
P889454        SYS         SUBARCHIVE                     SYS                          SELECT           NO  NO

 

SQL> select * from dba_role_privs where granted_role = 'PORTMAN_TABLE'  

 

 

 

Resizing A Data File

 SQL> alter database datafile '/u04/oradata/wpk/temp01.dbf' resize 500m;

 

 

 

Show All Product Information and Version

 SQL> select * from product_component_version;

 Oder:

SQL> select * from V$VERSION  

PRODUCT              VERSION              STATUS
-------------------- -------------------- ----------------------
NLSRTL               9.2.0.6.0            Production
Oracle9i Enterprise  9.2.0.6.0            64bit Production Edition
PL/SQL               9.2.0.6.0            Production
TNS for Solaris:     9.2.0.6.0            Production


 

SQL> select COMP_ID, VERSION from DBA_REGISTRY;  

COMP_ID                        VERSION
------------------------------ ------------------------------
CATALOG                        10.2.0.1.0
CATPROC                        10.2.0.1.0
ORDIM                          10.2.0.1.0
EM                             10.2.0.1.0 

 

 

 

Show Row Counts For All Tables That Have ANALYZE On

SQL> select owner table_name, num_rows from dba_tables where num_rows > 0  

OUTPUT:

TABLE_NAME                       NUM_ROWS
------------------------------ ----------
SYSTEM                                 40
SYSTEM                                  4
SYSTEM                                  8
SCC                                   585
SCC                                 57610
SCC                                 89120
SCC                               1249080

 

 

 

Select All Users Active In The System

SQL> select sid, serial#,user#, Username, machine, program, server, status, command, type from v$session order by username  

OUTPUT:

SID    SERIAL#      USER# USERNAME    MACHINE     PROGRAM          SERVER    STATUS                COMMAND TYPE
---------- ---------- ---------- ----------- ----------- ---------------- --------- ---------- ---------- ------
41      16035        170 SCC         FFZD0YB1                     DEDICATED INACTIVE                    0 USER
40      25759        170 SCC         cfv90199    SQL Developer    DEDICATED INACTIVE                    3 USER
39       5353        170 SCC         FFZD0YB1                     DEDICATED INACTIVE                    0 USER
38      63205        170 SCC         FFZD0YB1                     DEDICATED INACTIVE                    0 USER 

 

 

 

Show What A Current User Is Doing

SQL> select sid, serial#, status, server from v$session where username = 'SCC';  

OUTPUT:

       SID    SERIAL# STATUS                 SERVER
---------- ---------- ---------------------- ---------
         9      62279 INACTIVE               DEDICATED
        10      18568 INACTIVE               DEDICATED
        12      37908 INACTIVE               DEDICATED
        14      57038 INACTIVE               DEDICATED
        15      36649 INACTIVE               DEDICATED  

 

 

 

SQL String ermitteln. 

SQL> select b.address,b.hash_value,b.child_number,b.plan_hash_value,b.sql_text from v$session a, v$sql b where a.SQL_ADDRESS=b.ADDRESS and a.sid=44; 

ADDRESS          HASH_VALUE CHILD_NUMBER PLAN_HASH_VALUE SQL_TEXT
---------------- ---------- ------------ --------------- --------------------------------------------
0000040388B5E650 3741484163            0       665254614 update tstlock set bar='a' where bar='a'

 

Oder:

SQL> select b.address,b.hash_value,b.child_number,b.plan_hash_value,b.sql_text from v$session a, v$sql b where a.SQL_ADDRESS=b.ADDRESS and a.sql_id='1ddp56tdkxmxp'; 

no rows selected

 

Oder:

 

SQL> select s.sid, a.sql_text from v$session s, v$sql a where s.sql_address=a.address and a.sql_id='bs5qdr221jyd5'; 

       SID  SQL_TEXT
--------------------------------------------------------------------------------
      1193  create table VCLM_UltimateParentGroupTmp as select * from VCLM_UltimateParentGro
            upView gr where exists (   select 1   from  NCLM_ChangedParent cp   where gr.ult
            imateParentGroupId = cp.scc ) 

 

 

 

SQL String alle  ermitteln. 

SQL> SELECT SESSIONS.SID, SESSIONS.SERIAL#, SESSIONS.USERNAME, SESSIONS.SQL_ID, PARSE_CALLS, EXECUTIONS, SESSIONS.SQL_CHILD_NUMBER, OPTIMIZER_MODE, HASH_VALUE, ADDRESS, SQL_TEXT
 FROM V$SQLAREA SQLAREA, V$SESSION SESSIONS
 WHERE SESSIONS.SQL_HASH_VALUE = SQLAREA.HASH_VALUE
 AND SESSIONS.SQL_ADDRESS = SQLAREA.ADDRESS
 AND SESSIONS.USERNAME IS NOT NULL
 AND SESSIONS.USERNAME != 'SYS'
 ORDER BY SQL_TEXT DESC; 

       SID    SERIAL# USERNAME                       SQL_ID        PARSE_CALLS
---------- ---------- ------------------------------ ------------- -----------
EXECUTIONS SQL_CHILD_NUMBER OPTIMIZER_ HASH_VALUE ADDRESS
---------- ---------------- ---------- ---------- ----------------
SQL_TEXT
--------------------------------------------------------------------------------
      1208      39750 SCC                            9v3htz50ms53d           1
         1                0 CHOOSE     1094456429 07000000347F6FB0
SELECT SCORE(1) + 100 + 100 AS myscore, scc, name ,score(1) FROM  client WHERE d
ateOfDeletion IS NULL AND ( CONTAINS(client.name, :1, 1) >0 ) AND ( ( client.cli
enttypeid in (2, 1) )) AND ( exists ( select 1 from nativeclient nat where nat.s
cc = client.scc and nat.sourcesystemid = 3 and (nativeclienttype = 'Corporate Pe
rson' or nativeclienttype = 'Natural Person'))) ORDER BY upper(client.name), upp
[………..]


 

 

Create Count For All Tables

 SQL> select 'Select count(*) from ' ||owner|| '.' ||table_name|| ';' from dba_all_tables order by owner, table_name 

OUTPUT:

'SELECTCOUNT(*)FROM'||OWNER||'.'||TABLE_NAME||';'
-----------------------------------------------------------------------------------
Select count(*) from SYS.PARAMETER$;
Select count(*) from SYS.PARTCOL$;
Select count(*) from SYS.PARTLOB$;
Select count(*) from SYS.PARTOBJ$;
Select count(*) from SYS.PENDING_SESSIONS$;
Select count(*) from SYS.PENDING_SUB_SESSIONS$;
………………………
………………

 

 

 

Show All Indexes

 SQL> select owner, index_name, table_type, tablespace_name from
dba_indexes where  owner <>'SYSTEM' and owner <> 'DBSNMP'
and owner <> 'ORDSYS' and owner <> 'OUTLN' and owner <>
'SYS' and owner <> 'SYSTEM' order by owner, index_name,
tablespace_name 

OUTPUT:

OWNER                          INDEX_NAME                     TABLE_TYPE  TABLESPACE_NAME
------------------------------ ------------------------------ ----------- --------
SCC                            AI                             TABLE       SCC
SCC                            SYS_C001468                    TABLE       SCC
SCC                            SYS_C001469                    TABLE       SCC
SCC                            SYS_IL0000006389C00002$$       TABLE       SCC

 

  

 

Show All Tables

 SQL> select owner, table_name, table_type, tablespace_name from
dba_all_tables where owner <>'SYSTEM' and owner <> 'DBSNMP'
and owner <> 'ORDSYS' and owner <> 'OUTLN' and owner <>
'SYS' and owner <> 'SYSTEM' order by owner, table_name,
tablespace_name  

OUTPUT:

OWNER                          TABLE_NAME                     TABLE_TYPE                     TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------ ---------------
SCC                            A                                                             SCC
SCC                            FOO                                                           SCC
SCC                            T                                                             SCC
SCC                            TESTTAB1                                                      SCC
SCC                            TESTTAB2                                                      SCC
T440972                        PLAN_TABLE                                                    SYSTEM 

  

 

 

Show Space Used 

 SQL> select Tablespace_Name, /*Tablespace name*/ Owner, /*Owner of
the segment*/ Segment_Name, /*Name of the segment*/
Segment_Type, /*Type of segment (ex. TABLE, INDEX)*/ Extents,
/*Number of extents in the segment*/ Blocks, /*Number of db blocks
in the segment*/ Bytes /*Number of bytes in the segment*/ from
DBA_SEGMENTS where owner <>'SYSTEM' and owner <>
'DBSNMP' and owner <> 'ORDSYS' and owner <> 'OUTLN' and
owner <> 'SYS' and owner <> 'SYSTEM'  

OUTPUT:

TABLESPACE_NAME OWNER       SEGMENT_NAME   SEGMENT_TYPE          EXTENTS     BLOCKS      BYTES
--------------- ----------- -------------- ------------------ ---------- ---------- ----------
SCC             SCC         SOURCESYSTEM   TABLE                       1          8      65536
SCC             SCC         SUBARCHIVE     TABLE                     233     409600 3355443200
SCC             SCC         JOBSTATUS      TABLE                       1          8      65536
SCC             SCC         SUBBUFFER      TABLE                      81      10240   83886080
…………………
……………

  

 

 

Sum Space By Owner 

 SQL> select owner, sum(blocks) Totalblocks, sum(bytes)TotalBytes from DBA_SEGMENTS group by owner  

OUTPUT:

OWNER       TOTALBLOCKS TOTALBYTES
----------- ----------- ----------
CTXSYS            24464  200409088
GAA0414               7      57344
OUTLN                12      98304
PERFSTAT          12000   98304000
SCC             5214712 4.2719E+10
SYS               26632  218169344
SYSTEM              683    5595136


 

 

Sum Space by Tablespace

 SQL>  select tablespace_name, sum(blocks) Totalblocks, sum(bytes)TotalBytes from DBA_SEGMENTS group by tablespace_name 

OUTPUT:

TABLESPACE_NAME                TOTALBLOCKS TOTALBYTES
------------------------------ ----------- ----------
PERFSTAT                             12000   98304000
RBS                                   8086   66240512
SCC                                5214808 4.2720E+10
SCC_CTXSYS_01                        24464  200409088
SYSTEM                               19152  156893184

 

 

 

Show Reads And Writes By File Name In Oracle DB

 SQL> select v$datafile.name "File Name", v$filestat.phyrds "Reads",
v$filestat.phywrts "Writes" from v$filestat,v$datafile where
v$filestat.file# = v$datafile.file#  

OUTPUT:

File Name                                                               Reads     Writes
------------------------------------------------------------------ ---------- ----------
/var/opt/data/oracle/SCCD001T/data1/SCCD001T_SYSTEM_001.dbf             11859      37741
/var/opt/data/oracle/SCCD001T/data1/SCCD001T_RBS_001.dbf                 6355    2812198
/var/opt/data/oracle/SCCD001T/data1/SCCD001T_TOOLS_001.dbf                823        817
/var/opt/data/oracle/SCCD001T/data1/SCCD001T_scc_ctxsys_01_001.dbf     186723      89438
/var/opt/data/oracle/SCCD001T/data1/SCCD001T_PERF_001.dbf              141425        817
/var/opt/data/oracle/SCCD001T/data1/SCCD001T_SCC_001.dbf              6724984    3233106
/var/opt/data/oracle/SCCD001T/data1/SCCD001T_SCC_002.dbf              5541532    2804439
/var/opt/data/oracle/SCCD001T/data1/SCCD001T_SCC_003.dbf              5327638    3197239

 

 

 

Identify Segments That Are Getting Close To Their Max-Extent Values

 SQL> select owner,tablespace_name,segment_name,bytes,extents,max_extents from dba_segments where extents*2 > max_extents

OUTPUT:

OWNER                  TABLESPACE_NAME SEGMENT_NAME        BYTES    EXTENTS MAX_EXTENTS
---------------------- --------------- -------------- ---------- ---------- -----------
SYS                    SYSTEM          1.179                8192          1           0

 

 

 

Identifies Segments That Are Getting Close To Running Out Of Contiguous Free Space

 SQL> select owner, s.tablespace_name, segment_name, s.bytes,
ext_extent, max(f.bytes) largest from dba_segments s,
dba_free_space f where s.tablespace_name =
f.tablespace_name(+) group by owner, s.tablespace_name,
segment_name, s.bytes, next_extent having next_extent*2
>max(f.bytes)

 

 

 

Display Count Historical Archived Log Information From The Control File

 SQL> select count(*) from v$archived_log   

  COUNT(*)
----------
     15706

  

 SQL> select min(completion_time) from v$archived_log 

MIN(COMPL
---------
04-AUG-08

 

 

 

Shows Current Archive Destinations

 SQL> select * from v$archive_dest

 

 

 

Backups Of Archived Logs

 SQL> select count(*) from v$backup_redolog 

  COUNT(*)
----------
     15906

 

 

 

Display All Online Redo Log Groups For The database

 SQL> select * from v$log  

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1     135354  104857600          2 YES INACTIVE            4.3499E+12 05-NOV-08
         2          1     135355  104857600          2 YES INACTIVE            4.3499E+12 05-NOV-08
         3          1     135356  104857600          2 YES INACTIVE            4.3499E+12 05-NOV-08
         4          1     135353  104857600          2 YES INACTIVE            4.3499E+12 05-NOV-08
         5          1     135357  104857600          2 YES ACTIVE              4.3499E+12 05-NOV-08
         6          1     135358  104857600          2 NO  CURRENT             4.3499E+12 05-NOV-08
         7          1     135351  104857600          2 YES INACTIVE            4.3499E+12 05-NOV-08
         8          1     135352  104857600          2 YES INACTIVE            4.3499E+12 05-NOV-08

 

 

 

Display All Online Redo Log Fils 

 SQL> select name, SEQUENCE#, ARCHIVED, COMPLETION_TIME,
      Round((blocks*block_size)/1024/1024) MByte
      from v$archived_log where COMPLETION_TIME > sysdate -1; 

NAME
-------------------------------------------------------------------
 SEQUENCE# ARC COMPLETI      MBYTE
---------- --- -------- ----------
/var/opt/data/oracle/SCCD001T/arch/SCCD001T_0000021733.arch
     21733 YES 06.09.07         13
 /var/opt/data/oracle/SCCD001T/arch/SCCD001T_0000021734.arch
     21734 YES 06.09.07          0
/var/opt/data/oracle/SCCD001T/arch/SCCD001T_0000021735.arch
     21735 YES 06.09.07          0
 [...]

 


Display Full und Redo Backups 

COLUMN set_stamp FORMAT 999G999G999 HEADING 'Id'
COLUMN backup_type FORMAT a20 HEADING 'Backup Art'
COLUMN start_time HEADING 'Startzeit'
COLUMN completion_time HEADING 'Beendet'

 

 SQL> select SET_STAMP,
      decode(BACKUP_TYPE, 'D','Full', 'I','Incremental Level' || INCREMENTAL_LEVEL, 'L','Redolgs', null) BACKUP_TYPE
      start_time COMPLETION_TIME from v$backup_set
      where start_time >= trunc(sysdate) - 7
      order by start_time 

          Id Backup Art           Beendet
------------ -------------------- -------
 632.053.013 Full                 31.08.07
 632.053.153 Redolgs              31.08.07
 632.053.153 Redolgs              31.08.07
 632.053.157 Full                 31.08.07
 632.053.157 Full                 31.08.07
 632.055.611 Redolgs              31.08.07
 632.055.611 Redolgs              31.08.07
 632.055.614 Full                 31.08.07
 632.057.868 Redolgs              31.08.07
 632.057.868 Redolgs              31.08.07
 632.057.870 Full                 31.08.07


 

 

Show All Datafiles For Tablespace And Oracle Stuff 

 SQL> select * from dba_data_files order by tablespace_name, file_name 

OUTPUT:

FILE_NAME                                                          FILE_ID TABLESPACE_NAME      BYTES     BLOCKS
--------------------------------------------------------------- ---------- --------------- ---------- ----------
STATUS                 RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
---------------------- ------------ --- ---------- ---------- ------------ ---------- -----------
/var/opt/data/oracle/SCCD001T/data1/SCCD001T_SYSTEM_001.dbf              1 SYSTEM           262144000      32000
AVAILABLE                         1 NO           0          0            0  262135808       31999
/var/opt/data/oracle/SCCD001T/data1/SCCD001T_TOOLS_001.dbf               3 TOOLS            524288000      64000
AVAILABLE                         3 NO           0          0            0  524222464       63992

  

Powered by Drupal