Oracle Export/Import STATISTICS

begin
SYS.DBMS_STATS.CREATE_STAT_TABLE( ownname => 'SYSTEM', stattab => 'MY_STATS_TAB', tblspace => 'SYSAUX' );
end;
/


begin
SYS.DBMS_STATS.EXPORT_SCHEMA_STATS( 'OWS', 'MY_STATS_TAB',statown => 'SYSTEM');
end;

Oracle Index Rebuild

Set serveroutput on size 100000

DECLARE
vOwner dba_indexes.owner%TYPE; /* Index Owner */
vIdxName dba_indexes.index_name%TYPE; /* Index Name */
vAnalyze VARCHAR2(100); /* String of Analyze Stmt */
vCursor NUMBER; /* DBMS_SQL cursor */
vNumRows INTEGER; /* DBMS_SQL return rows */
vHeight index_stats.height%TYPE; /* Height of index tree */
vLfRows index_stats.lf_rows%TYPE; /* Index Leaf Rows */
vDLfRows index_stats.del_lf_rows%TYPE; /* Deleted Leaf Rows */
vDLfPerc NUMBER; /* Del lf Percentage */
vMaxHeight NUMBER; /* Max tree height */
vMaxDel NUMBER; /* Max del lf percentage */
CURSOR cGetIdx IS SELECT owner,index_name
FROM dba_indexes WHERE OWNER NOT LIKE 'SYS%';
BEGIN
/* Define maximums. This section can be customized. */
vMaxHeight := 3;
vMaxDel := 20;

/* For every index, validate structure */
OPEN cGetIdx;
LOOP
FETCH cGetIdx INTO vOwner,vIdxName;
EXIT WHEN cGetIdx%NOTFOUND;
/* Open DBMS_SQL cursor */
vCursor := DBMS_SQL.OPEN_CURSOR;
/* Set up dynamic string to validate structure */
vAnalyze := 'ANALYZE INDEX ' || vOwner || '.' || vIdxName || ' VALIDATE STRUCTURE';
DBMS_SQL.PARSE(vCursor,vAnalyze,DBMS_SQL.V7);
vNumRows := DBMS_SQL.EXECUTE(vCursor);
/* Close DBMS_SQL cursor */
DBMS_SQL.CLOSE_CURSOR(vCursor);
/* Does index need rebuilding? */
/* If so, then generate command */
SELECT height,lf_rows,del_lf_rows INTO vHeight,vLfRows,vDLfRows
FROM INDEX_STATS;
IF vDLfRows = 0 THEN /* handle case where div by zero */
vDLfPerc := 0;
ELSE
vDLfPerc := (vDLfRows / vLfRows) * 100;
END IF;
IF (vHeight > vMaxHeight) OR (vDLfPerc > vMaxDel) THEN
DBMS_OUTPUT.PUT_LINE('ALTER INDEX ' || vOwner || '.' || vIdxName || ' REBUILD;');
END IF;

END LOOP;
CLOSE cGetIdx;
END;

Oracle Grants

Объектные привилегии пользователя GAAP

select 'grant ' || privilege || ' on ' ||owner||'.' ||table_name || ' to ' ||grantee ||';'
from dba_tab_privs
where grantee = 'GAAP'

Какие роли у пользователя GAAP

select 'create role '||granted_role || ' NOT IDENTIFIED' ||';'
from dba_role_privs
where grantee = 'GAAP'

Cоздаём привилегии для роли GAAP_ACCESS

select 'grant ' || privilege || ' on ' ||owner||'.' ||table_name || ' to ' ||grantee ||';'
from dba_tab_privs
where grantee = 'GAAP_ACCESS'

Java

Sample Java program

Put the following sample code in a file called Hello.java:

public class Hello {
public static void main(String[] args) {
for (int i=0; i < args.length; i++) {
System.out.println("Hello " + args[i]);
}
}
}

Compiling Java programs

Use the javac utility to compiler a java program:

javac Hello.java

Running Java programs

Start our "java" program (Hello) and pass some arguments to it (World):

java Hello World

Linux cpu usage and memory

# mpstat 1 10

# ps -eo pcpu,pid,user,args | sort -k 1 -r | head -10


#ps -eo pcpu,pid,user,args,pmem | sort -k 1 -r | head -10


for i in `seq 1 10`; do ps -eo pcpu,pid,user,args,pmem | sort -k 1 -r | head -10; sleep 1; clear; done

Oracle Table DUAL

SQL> DROP TABLE SYS.DUAL ;

Table dropped.

SQL> CREATE TABLE SYS.DUAL
2 (
3 DUMMY VARCHAR2(1 BYTE)
4 )
5 TABLESPACE SYSTEM;

Table created.

SQL> CREATE PUBLIC SYNONYM DUAL FOR SYS.DUAL;

Synonym created.

SQL> GRANT SELECT ON SYS.DUAL TO PUBLIC WITH GRANT OPTION;

Grant succeeded.

SQL> INSERT INTO dual VALUES ('X');

1 row created.

SQL> SELECT * FROM dual;

D
-
X



Не делать shutdown после drop

SQL> drop table dual;

Table dropped.

SQL> shutdown

Database closed.

Database dismounted.

ORACLE instance shut down.


SQL> startup

ORACLE instance started.

Total System Global Area 188743680 bytes
Fixed Size 1286460 bytes
Variable Size 96472772 bytes
Database Buffers 88080384 bytes
Redo Buffers 2904064 bytes

Database mounted.

ORA-01092: ORACLE instance terminated. Disconnection forced

SQL>

Oracle Temp Usage

Oracle Temp Usage:

SELECT A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A,
(
SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size
) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;



select b.tablespace,b.segfile#,b.segblk#,b.blocks
,a.sid,a.serial#,a.username,a.osuser,a.status
from v$session a
,v$sort_usage b
where a.saddr = b.session_addr
order by b.tablespace,b.segfile#,b.segblk#,b.blocks;