Partitionierte Tabellen
http://oracledoug.com/serendipity/index.php?/archives/1562-Statistics-on-Partitioned-Tables-Part-1.html
================================================================
PL/SQL Code durchsuchen
--------------------------------------------------------------------------------------------------------------------
-- http://stackoverflow.com/questions/251902/is-there-a-way-to-do-full-text-search-of-all-oracle-packages-and-procedures?rq=1
--
SELECT name, line, text
FROM dba_source
WHERE upper(text) like upper('%put_line%') escape '\'
;
--
-- http://stackoverflow.com/questions/26883447/how-to-check-where-in-some-other-package-procedure-my-procedureinside-a-package?rq=1
--
select /*+ parallel(als, 16) */
*
from all_source als
where type in ('PROCEDURE', 'PACKAGE BODY')
and name in (select NAME
from all_Dependencies
where referenced_name = 'OE_HEADER_UTIL')
and upper(text) like '%INSERT_ROW%'
;
--
--
--
select name,
type,
decode(usage,'DECLARATION', 'body only', 'DEFINITION', 'spec and body', usage) defined_on,
line body_line
from user_identifiers ui
where type in ('PROCEDURE', 'FUNCTION')
and usage_context_id = (select usage_id
from user_identifiers
where object_name = ui.object_name
and object_type = ui.object_type
and usage_context_id = 0)
and object_name = 'EMP_TAPI'
and object_type = 'PACKAGE BODY'
order by name
;
================================================================
Index-organisierte Tabellen (IOT)
------------------------------------------------------------------------------------------------------------------http://use-the-index-luke.com/de/sql/clustering/index-organized-clustered-index
https://books.google.de/books?id=u7_YgJVODGcC&printsec=frontcover&hl=de#v=onepage&q&f=false
================================================================
Insert mit Sequence
------------------------------------------------------------------------------------------------------------------INSERT INTO ct_dept3
(
DEPTNO,
AENDERUNGSZEITSTEMPEL,
AENDERUNGS_KENNZEICHEN
)
VALUES
(
SEQ_AENDNR_CT_DEPT.NEXTVAL,
sysdate,
'I'
);
================================================================
User Objects überprüfen
------------------------------------------------------------------------------------------------------------------SELECT table_name
FROM USER_CONSTRAINTS
WHERE status <> 'ENABLED'
ORDER BY table_name;
--
EXEC pkg_adm_migration.s_recompile;
--
SELECT *
FROM user_objects
WHERE 1 = 1
AND status <> 'VALID';
--
BEGIN
FOR v_cur IN (
SELECT owner,
constraint_name ,
table_name
FROM all_constraints
WHERE owner = 'DBADM'
AND TABLE_NAME IN (
SELECT table_name
FROM USER_CONSTRAINTS
WHERE status <> 'ENABLED' ))
LOOP
EXECUTE immediate 'ALTER TABLE '||v_cur.owner||'.'||v_cur.table_name||' MODIFY CONSTRAINT "'||v_cur.constraint_name||'" ENABLE ';
END LOOP;
END;
--
--
--
BEGIN
FOR v_cur IN (
SELECT owner,
index_name
FROM dba_indexes t
WHERE 1 = 1
AND t.owner = 'DBADM'
AND status = 'UNUSABLE' )
LOOP
EXECUTE immediate 'ALTER INDEX '||v_cur.owner||'.'||v_cur.index_name||' REBUILD ';
-- dbms_output.put_line('EXECUTE immediate ALTER INDEX '||v_cur.owner||'.'||v_cur.index_name||' REBUILD ');
END LOOP;
--
--
--
SELECT 'alter index '
||index_owner
||'.'
||index_name
||' rebuild partition '
||partition_name
||';'
FROM dba_ind_partitions
WHERE status != 'USABLE';
--
SELECT 'alter index '
||index_owner
||'.'
||index_name
||' rebuild subpartition '
||subpartition_name
||';'
FROM dba_ind_subpartitions
WHERE status != 'USABLE';
================================================================
Alle Sätze einer Tabelle löschen:
------------------------------------------------------------------------------------------------------------------DELETE FROM emp;
================================================================
Mehrfache Datensätze und Gruppierungen:
------------------------------------------------------------------------------------------------------------------SELECT count(*)
, nachname
, vorname
FROM emp
HAVING count(*) > 1;
SELECT count(*) as Anzahl
, job
, sum(sal)
FROM emp
GROUP BY job
HAVING count(*) > 1
;
-----------------------
Anzahl Job
2 Tester
4 CLERK 4150
4 SALESMAN 5600
3 MANAGER 8275
2 ANALYST 6000
-----------------------
================================================================
Tabellen anzeigen:
------------------------------------------------------------------------------------------------------------------SELECT table_name
FROM all_tables
WHERE owner NOT IN ('SYSTEM'
, 'SYS'
, 'SYSAUX'
, 'ORDSYS'
, 'WMSYS'
, 'MDSYS'
, 'EXFSYS'
, 'TSMSYS'
, 'XDB'
, 'DBSNMP'
, 'OUTLN'
, 'CTXSYS'
, 'APPQOSSYS')
ORDER BY owner
;
================================================================
External Tables administrieren:
------------------------------------------------------------------------------------------------------------------SELECT *
FROM USER_EXTERNAL_TABLES
;
--
SELECT *
FROM ALL_EXTERNAL_TABLES
;
--
SELECT *
FROM DBA_EXTERNAL_TABLES
;
--
select *
from USER_EXTERNAL_LOCATIONS
;
--
SELECT *
FROM ALL_EXTERNAL_LOCATIONS
;
--
SELECT *
FROM DBA_EXTERNAL_LOCATIONS
;
================================================================
Shell-Script, das eine SQL-Datei erzeugt und mit dem Oracle-Skript runSQLScript.pl ausführt
#!/bin/sh
# Login-Parameter setzen …
user=system
passwort=…
# SQL-Datei definieren …
echo "connect $user/$passwort;" > datei.sql
echo "select * from tab;" >> datei.sql
echo "exit;" >> datei.sql
# Oracle Perl-Skript aufrufen und
# damit die SQL-Datei ausfuehren …
$ORACLE_HOME/perl/bin/perl runSQLScript.pl $ORACLE_HOME \ $ORACLE_SID < datei.sql
================================================================
------------------------------------------------------------------------------------------------------------------
# Login-Parameter setzen …
user=system
passwort=…
# SQL-Datei definieren …
echo "connect $user/$passwort;" > datei.sql
echo "select * from tab;" >> datei.sql
echo "exit;" >> datei.sql
# Oracle Perl-Skript aufrufen und
# damit die SQL-Datei ausfuehren …
$ORACLE_HOME/perl/bin/perl runSQLScript.pl $ORACLE_HOME \ $ORACLE_SID < datei.sql
================================================================
Dies noch einmal als Shell-Script, das mittels SQL*Plus SQL-Statements
ausführt
#!/bin/sh
#
# Shell Script, um SQL*Plus aufzurufen und Statements
# auszufuehren.
#
# Variablen setzen
#
sqlplus /nolog << EOF
connect user/password
select * from tab;
-- oder @script.sql bzw. start script.sql
EOF
================================================================
#
# Shell Script, um SQL*Plus aufzurufen und Statements
# auszufuehren.
#
# Variablen setzen
#
sqlplus /nolog << EOF
connect user/password
select * from tab;
-- oder @script.sql bzw. start script.sql
EOF
================================================================
Anzeige der einem Benutzer zugeordneten Tabellen, der
zugehörigen Spalten und Kommentare
SELECT utc.table_name
, utc.column_name
, (SELECT ucc.comments
FROM user_col_comments ucc
WHERE ucc.table_name = utc.table_name
AND ucc.column_name = utc.column_name) AS Kommentar
FROM user_tab_cols utc
WHERE utc.table_name IN (SELECT uo.object_name
FROM user_objects uo
WHERE object_type = 'TABLE')
ORDER BY utc.table_name
;
-- Das geht natürlich auch viel einfacher :-)
SELECT ucc.table_name, ucc.column_name
, ucc.comments
FROM user_col_comments ucc
;
================================================================
Supplemental Logging aktivieren
------------------------------------------------------------------------------------------------------------------
ALTER TABLE emp ADD SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS;
ALTER TABLE emp ADD SUPPLEMENTAL LOG DATA (UNIQUE INDEX) COLUMNS;
ALTER TABLE emp ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
ALTER TABLE emp ADD SUPPLEMENTAL LOG GROUP LG_EMP
ALTER TABLE emp ADD SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS;
ALTER TABLE emp ADD SUPPLEMENTAL LOG DATA (UNIQUE INDEX) COLUMNS;
ALTER TABLE emp ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
ALTER TABLE emp ADD SUPPLEMENTAL LOG GROUP LG_EMP
(emp_id, dept_id, sal) ALWAYS;
================================================================
================================================================
Public Synonyme für DB-Objekte erzeugen
------------------------------------------------------------------------------------------------------------------
Dieses kleine SQL-Script erzeugt eine Datei "synonyme.lst" mit SQL-Befehlen, um für aller User-Objekte Public Synonyme anlegen zu können:
Dieses kleine SQL-Script erzeugt eine Datei "synonyme.lst" mit SQL-Befehlen, um für aller User-Objekte Public Synonyme anlegen zu können:
set pagesize 0
set linesize 100
set heading offset echo off
set feedback off
spool c:\temp\synonyme.lst
select 'create public synonym 'object_name '
for 'object_name ';'
for 'object_name ';'
from user_objects
where object_type not in ('DATABASE LINK','LOB','PACKAGE')
and object_name not like 'SYS_%'
and object_name not like '%JAVA%'
order by object_type, object_name;
spool off
================================================================
================================================================
Die Nutzung von DBA-Features anzeigen
------------------------------------------------------------------------------------------------------------------
ALTER SESSION SET NLS_DATE_FORMAT = 'dd.mm.yyyy hh24:mi';
SELECT a.name,
a.detected_usages,
a.first_usage_date,
a.last_usage_date
FROM dba_feature_usage_statistics a
WHERE a.version = (SELECT MAX(b.version)
FROM dba_feature_usage_statistics b
WHERE b.name = a.name)
ORDER BY a.name;
================================================================
================================================================
ALTER SESSION SET NLS_DATE_FORMAT = 'dd.mm.yyyy hh24:mi';
SELECT a.name,
a.detected_usages,
a.first_usage_date,
a.last_usage_date
FROM dba_feature_usage_statistics a
WHERE a.version = (SELECT MAX(b.version)
FROM dba_feature_usage_statistics b
WHERE b.name = a.name)
ORDER BY a.name;
================================================================
Views anzeigen
------------------------------------------------------------------------------------------------------------------
SELECT *
SELECT *
FROM SYS.all_views
WHERE owner NOT IN ('SYSTEM'
, 'SYS'
, 'SYSAUX'
, 'ORDSYS'
, 'WMSYS'
, 'MDSYS'
, 'EXFSYS'
, 'TSMSYS'
, 'XDB'
, 'DBSNMP'
, 'OUTLN')
ORDER BY owner;
================================================================
Schemata anzeigen
------------------------------------------------------------------------------------------------------------------
SELECT DISTINCT owner
SELECT DISTINCT owner
FROM dba_segments
WHERE owner IN (
SELECT username
FROM dba_users
WHERE default_tablespace NOT IN (
'SYSTEM'
, 'SYSAUX'))
ORDER BY owner;
SELECT DISTINCT owner
FROM dba_segments
WHERE owner NOT IN ('SYSTEM'
, 'SYS'
, 'SYSAUX'
, 'ORDSYS'
, 'WMSYS'
, 'MDSYS'
, 'EXFSYS'
, 'TSMSYS'
, 'XDB'
, 'DBSNMP'
, 'OUTLN')
ORDER BY owner;
================================================================
User in eine Oracle-DB importieren
------------------------------------------------------------------------------------------------------------------
Oracle-Connect überprüfen
SELECT * FROM v$instance;
User droppen
DROP USER user CASCADE;
User anlegen
CREATE USER user
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON user_ts
PROFILE DEFAULT;
GRANT CONNECT TO user;
GRANT RESOURCE TO user;
GRANT UNLIMITED TABLESPACE TO user_ts;
User-Daten importieren
imp userid=system/password
fromuser=user touser=
file=EXP_FULL.dmp
log=20130320_.log
indexes=y ignore=y grants=y
================================================================
Rekompilierung invalider Schema-Objekte
Ermitteln der Objekte im Status "INVALID":
-- Spaltenbreite begrenzen
COLUMN object_name FORMAT A40
-- Selektieren
SELECT owner
, object_type
, object_name
, status
FROM dba_objects
WHERE status = 'INVALID'
ORDER BY owner, object_type, object_name;
SHOW ERRORS
Recompilieren der invaliden Objekte (als SYS):
exec dbms_utility.compile_schema(schema => 'SCOTT', compile_all => false);
-- oder
exec utl_recomp.recomp_serial();
-- oder
SELECT 'EXEC DBMS_DDL.ALTER_COMPILE(''' ||object_type||''', '
||''''||owner||''', '
||''''||object_name||''');'
FROM dba_objects
WHERE status = 'INVALID'
ORDER BY owner, object_type_ object_name;
Keine Kommentare:
Kommentar veröffentlichen