exec dbms_stats.gather_table_stats('DWH', 'T')
begin dbms_stats.gather_table_stats('DWH', 'T'); end;
ORA-20000: Unable to analyze TABLE "DWH"."T", insufficient privileges or does not exist
ORA-06512: at "SYS.DBMS_STATS", line 13046
ORA-06512: at "SYS.DBMS_STATS", line 13076
ORA-06512: at line 2
# Lookup names in host file and then check DNS
order bind,hosts
show parameter audit_trail
alter system set audit_trail=DB scope=spfile;
shut immediate
startup
audit connect;
connect good_user/good_pass
connect bad_user/bad_pass
connect / as sysdba
noaudit connect;
select * from sys.aud$
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME=db2)
(ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME=db2)
)
)
ADR_BASE_LISTENER = /home/oracle/app/oraclerman nocatalog target sys/pass@db1 auxiliary sys/pass@db2
RUN
{
SET NEWNAME FOR DATABASE TO '/home/oracle/oradata/db2/%b';
DUPLICATE TARGET DATABASE TO db2
FROM ACTIVE DATABASE
LOGFILE
GROUP 1 ('/home/oracle/oradata/db2/redo01.log') SIZE 50M REUSE,
GROUP 2 ('/home/oracle/oradata/db2/redo02.log') SIZE 50M REUSE;
}
find . -type f | wc -l
Список файлов в tar.gz архиве tar -ztvf arch
WITH tax AS (
SELECT 13 a FROM dual UNION ALL
SELECT 35 a FROM dual)
SELECT x MULTISET UNION x
FROM (
SELECT cast(collect(a) AS sps.tpt_number) x, 1 flag FROM tax UNION ALL
SELECT cast(collect(a) AS sps.tpt_number) x, 2 flag FROM tax WHERE ROWNUM > 1
) t
WHERE CARDINALITY(t.x) > 0;SELECT *
FROM TABLE(POWERMULTISET(tpt_number(1, 2, 3)));SELECT *
FROM TABLE(POWERMULTISET_BY_CARDINALITY(tpt_number(1, 2, 3), 2));
sqlplus / as sysdba выдается ошибка:ORA-01031: insufficient privilegesSQLNET.AUTHENTICATION_SERVICES= (NTS) для Windows и SQLNET.AUTHENTICATION_SERVICES= (ALL) для Unixevent="1438 trace name errorstack level 10".$ grep --binary-files=text -o "pkg_social_tax.\([a-z_A-Z]\)*(" *.frf | sed 's/.
*:\(.*\)(/\1/' | sort -u
Error securing Database Control, Database Control has been brought up in non-secure mode. To secure the Database Control execute the following command(s):
1) Set the environment variable ORACLE_SID to rsud
2) D:\oracle\product\10.2.0\db_1\bin\emctl.bat stop dbconsole
3) D:\oracle\product\10.2.0\db_1\bin\emctl.bat config emkey -repos -sysman_pwd < Password for SYSMAN user >
4) D:\oracle\product\10.2.0\db_1\bin\emctl.bat secure dbconsole -sysman_pwd < Password for SYSMAN user >
5) D:\oracle\product\10.2.0\db_1\bin\emctl.bat start dbconsole
emca -deconfig dbcontrol db -repos drop
emca -config dbcontrol db -repos create
java.io.IOException: The handle is invalidEnterprise Manager configuration compl
eted successfully
at java.io.FileInputStream.close0(Native Method)
at java.io.FileInputStream.close(FileInputStream.java:245)
at sun.nio.cs.StreamDecoder$CharsetSD.implClose(StreamDecoder.java:505)
at sun.nio.cs.StreamDecoder.close(StreamDecoder.java:198)
at java.io.InputStreamReader.close(InputStreamReader.java:187)
at java.io.BufferedReader.close(BufferedReader.java:502)
FINISHED EMCA at 19.11.2011 12:17:53 at oracle.sysman.assistants.util.sqlEngi
ne.SQLEngine$ErrorStreamReader.run(SQLEngine.java:2406)
java.lang.Exception: Exception in sending Request :: null
500 Internal Server Error
java.util.MissingResourceException: Can't find resource for bundle oracle.sysman.db.rsc.LoginResource, key connectStringError
at java.util.ResourceBundle.getObject(ResourceBundle.java:325)
emca -deconfig all db -repos drop
emca -cofig all db -repos create
emctl unsecure dbconsole
keytool -list -v -keystore cacerts -storepass changeit > cacerts.txtkeytool -delete -keystore cacerts -alias ... -storepass changeit перезапускаем сервер
set serveroutput on size 1000000
DROP TABLE ref_table;
CREATE TABLE ref_table(
table_name VARCHAR2(30) NOT NULL,
rid rowid NOT NULL PRIMARY KEY,
lvl NUMBER NOT NULL);
DECLARE
lLVL INTEGER := 0;
lROWS_QNT NUMBER;
i PLS_INTEGER;
function build_column_list(aCONSTRAINT_NAME VARCHAR2, aPREFIX VARCHAR2) RETURN VARCHAR2 IS
RESULT VARCHAR2(4000);
BEGIN
FOR rec IN (
SELECT *
FROM user_cons_columns
WHERE constraint_name = aCONSTRAINT_NAME
ORDER BY position
) LOOP
RESULT := RESULT || ',' || aPREFIX || rec.column_name;
END LOOP;
--dbms_output.put_line(LTRIM(RESULT, ','));
RETURN LTRIM(RESULT, ',');
END build_column_list;
FUNCTION process_reference(aLVL NUMBER, aTBL_NAME VARCHAR2, aFK_CONS VARCHAR2, aPAR_TABLE_NAME VARCHAR2, aPK_CONS VARCHAR2) RETURN NUMBER IS
LQNT NUMBER;
BEGIN
dbms_output.put_line('Обработка ссылки на уровне ' || aLVL || ' ' || aTBL_NAME || '.' || aFK_CONS || ' -> ' || aPAR_TABLE_NAME || '.' || aPK_CONS);
IF aTBL_NAME <> aPAR_TABLE_NAME THEN
EXECUTE IMMEDIATE '
UPDATE /*+ NO_INDEX(x)*/ref_table x
SET lvl = (
select max(lvl) + 1
FROM ' || aPAR_TABLE_NAME || ' pt, ref_table rt
WHERE rt.rid = pt.rowid
AND ( ' || build_column_list(aPK_CONS, 'pt.') || ') IN (SELECT ' || build_column_list(aFK_CONS, 't.') || ' FROM ' || aTBL_NAME || ' t)
)
WHERE (rid || '' '') IN (
SELECT /*+ NO_INDEX(t)*/ t.rowid || '' ''
FROM ' || aTBL_NAME || ' t
WHERE ( ' || build_column_list(aFK_CONS, 't.') || ')
IN (SELECT ' || build_column_list(aPK_CONS, 'pt.') || ' FROM ref_table rt, ' || aPAR_TABLE_NAME || ' pt WHERE rt.rid = pt.rowid))';
END IF;
dbms_output.put_line('Обновлено уровней: ' || SQL%ROWCOUNT);
EXECUTE IMMEDIATE '
INSERT INTO ref_table(table_name, rid, lvl)
SELECT ''' || UPPER(aTBL_NAME) || ''', t.rowid, :LVL + 1
FROM ' || aTBL_NAME || ' t
WHERE ( ' || build_column_list(aFK_CONS, 't.') || ')
IN (SELECT ' || build_column_list(aPK_CONS, 'pt.') || ' FROM ref_table rt, ' || aPAR_TABLE_NAME || ' pt WHERE rt.rid = pt.rowid)
AND NOT EXISTS (
SELECT NULL
FROM ref_table rt
WHERE rid = t.rowid)'
USING aLVL;
lQNT := SQL%ROWCOUNT;
dbms_output.put_line('Добавлено строк ' || lQNT);
RETURN lQNT;
END;
FUNCTION process_level(aLVL NUMBER) RETURN NUMBER IS
lQNT NUMBER := 0;
BEGIN
dbms_output.put_line('+++ level = ' || aLVL);
FOR rec IN (
SELECT DISTINCT f.table_name chi_table_name, f.constraint_name chi_table_cons,
pp.table_name par_table_name, pp.constraint_name par_table_cons
FROM user_constraints f, user_constraints pp
WHERE f.constraint_type = 'R'
AND f.r_constraint_name = pp.constraint_name
AND pp.table_name IN (SELECT upper(table_name) FROM ref_table WHERE lvl = aLVL)
) LOOP
lQNT := lQNT + process_reference(aLVL, rec.chi_table_name, rec.chi_table_cons, rec.par_table_name, rec.par_table_cons);
END LOOP;
RETURN lQNT;
END;
BEGIN
INSERT INTO ref_table(table_name, rid, lvl)
SELECT 'USR', u.rowid, 0
FROM usr u
WHERE u.usr_status = 'Deleted';
lROWS_QNT := process_level(lLVL);
dbms_output.put_line('Будет удалено на уровне ' || (lLVL + 1) || ' ' || lROWS_QNT || ' строк');
WHILE lROWS_QNT > 0 LOOP
lLVL := lLVL + 1;
lROWS_QNT := process_level(lLVL);
dbms_output.put_line('Будет удалено на уровне ' || (lLVL + 1) || ' ' || lROWS_QNT || ' строк');
END LOOP;
dbms_output.put_line('=========');
dbms_output.put_line('Удаление');
dbms_output.put_line('=========');
-- Удаляем
FOR rec IN (
SELECT DISTINCT table_name, lvl
FROM ref_table
ORDER BY lvl DESC
) LOOP
dbms_output.put_line('Удаляем записи из ' || rec.table_name || ' lvl = ' || rec.lvl);
EXECUTE IMMEDIATE '
delete from ' || rec.table_name || ' t
where t.rowid IN (SELECT rid FROM ref_table WHERE table_name = :TN AND lvl = :lvl)'
USING rec.table_name, rec.lvl;
dbms_output.put_line('Из таблицы ' || rec.table_name || ' удалено строк ' || SQL%ROWCOUNT);
END LOOP;
END;
/
Для того, что бы посмотреть, где расположить регион на странице (display point), можно открыть любой регион, найти поле Display Point и нажать на фонарик рядом с полем