Can't take screenshots of a udemy course video in Windows

 Solution: To resolve the issue, please follow these steps:

1. Open the browser.

2. Go to "Settings" menu.

3. In the Settings menu, type "acceleration" in the search bar.

4. Disable this option.

5. Once disabled, relaunch the browser.

Statistics - DBMS_STATS

Table statistics:

DBMS_STATS.GATHER_TABLE_STATS ( ownname => 'SCOTT', tabname => 'DEPT', estimate_percent => NULL, method_opt =>'FOR ALL INDEXED COLUMNS SIZE AUTO', granularity =>'ALL', NO_INVALIDATE => TRUE, Cascade => TRUE);

Dynamic query

SELECT 'EXECUTE DBMS_STATS.GATHER_TABLE_STATS(ownname => '||CHR(39)||owner||CHR(39)||', tabname =>'||CHR(39)||object_name||CHR(39)||', estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => '||CHR(39)||'FOR ALL COLUMNS SIZE AUTO'||CHR(39)||', cascade => TRUE);'
from dba_objects 
where object_name IN ('DEPT', 'EMP') and object_type = 'TABLE';

Database Statistics:

EXEC DBMS_STATS.gather_dictionary_stats;
EXEC DBMS_STATS.gather_system_stats;
EXEC DBMS_STATS.gather_fixed_objects_stats;
EXEC DBMS_STATS.gather_database_stats;

Schema statistics:

dbms_stats.gather_schema_stats( ownname => 'SCOTT', cascade => TRUE, method_opt => 'FOR ALL COLUMNS SIZE 1',degree => DBMS_STATS.AUTO_DEGREE);

dbms_stats.gather_schema_stats( ownname => 'SCOTT_ADM', cascade => TRUE, method_opt => 'FOR ALL COLUMNS SIZE 1',degree => DBMS_STATS.AUTO_DEGREE);

BEGIN 
DBMS_STATS.gather_schema_stats ( 
ownname => 'SCOTT', 
cascade => TRUE, 
options => 'GATHER STALE');
END; 
/ 

Recompile objects:

@?/rdbms/admin/utlrp.sql

Move non-OMF datafiles to OMF

SYS@poctest_1> sho parameter db_create_file_dest

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------
db_create_file_dest                  string

SYS@poctest_1> alter system set db_create_file_dest='+DATA01' scope=both;

System altered.

SYS@poctest_1> sho parameter db_create_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      +DATA01

col file_name for a70
set linesize 150 pages 150
select file_id, file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
+DATA01/POCTEST/system01.dbf
+DATA01/POCTEST/sysaux01.dbf
+DATA01/POCTEST/undotbs02.dbf
+DATA01/POCTEST/users01.dbf
+DATA01/POCTEST/test_data01
+DATA01/POCTEST/undotbs01.dbf

select 'PROMPT Moving file '||file_id||CHR(10)||'alter database move datafile '||file_id||';' from dba_data_files where file_name like '%.db%';

'ALTERDATABASEMOVEDATAFILE'||FILE_ID||';
---------------------------------------------------------------------alter database move datafile 1;
alter database move datafile 3;
alter database move datafile 5;
alter database move datafile 7;
alter database move datafile 2;
alter database move datafile 4;
alter database move datafile 8;

7 rows selected.

Elapsed: 00:00:00.03

SYS@poctest_1> alter database move datafile 1;

Database altered.

Elapsed: 00:00:15.43

SYS@poctest_1> alter database move datafile 3;

alter database move datafile 5;
alter database move datafile 7;
alter database move datafile 2;
alter database move datafile 4;
alter database move datafile 8;

Database altered.

SYS@poctest_1> select file_name from dba_data_files;

FILE_NAME
----------------------------------------------------------
+DATA01/POCTEST/DATAFILE/system.2558.1152873171
+DATA01/POCTEST/DATAFILE/sysaux.1127.1152873193
+DATA01/POCTEST/DATAFILE/undotbs2.1169.1152873221
+DATA01/POCTEST/DATAFILE/users.1181.1152873225
+DATA01/POCTEST/DATAFILE/test.1157.1152873227
+DATA01/POCTEST/DATAFILE/undotbs1.2126.1152873379
+DATA01/POCTEST/DATAFILE/test1.1171.1152873393

7 rows selected. 

Find table last modification date

select X.*  , Y.timestamp Last_updated_date from (
select     t.owner , table_name,     num_rows, TABLE_SIZE_IN_GB 
 from     dba_tables t
Inner join (select OWNER, segment_name,segment_type, sum(bytes/1024/1024/1024) TABLE_SIZE_IN_GB
from dba_segments
where OWNER in ('SCOTT', 'SCOTT_ADM')
group by OWNER,segment_name,segment_type ) s
  On table_name =segment_name and t.owner =s.owner ) X
  join dba_tab_modifications Y on X.owner = Y.TABLE_OWNER  and X.table_name = Y.table_name
  order by 4 desc , 3 desc ;

 

Find Oracle Maintained (internal) users

 SYS @ GGATE1 >select username from dba_users where ORACLE_MAINTAINED ='Y' order by username;

USERNAME
--------------------------------------------------
ANONYMOUS
APPQOSSYS
AUDSYS
CTXSYS
DBSFWUSER
DBSNMP
DIP
GGSYS
GSMADMIN_INTERNAL
GSMCATUSER
GSMUSER
MDSYS
OJVMSYS
ORACLE_OCM
ORDDATA
ORDPLUGINS
ORDSYS
OUTLN
REMOTE_SCHEDULER_AGENT
SI_INFORMTN_SCHEMA
SYS
SYS$UMF
SYSBACKUP
SYSDG
SYSKM
SYSRAC
SYSTEM
MSYS
XDB
XS$NULL

30 rows selected.

Using Regular Expression - REGEXP_SUBSTR example

SELECT REGEXP_SUBSTR('(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orcl-orcl2.myhost.ca)(PORT=41295))(CONNECT_DATA=(SID=ORCL2)))', '[(]HOST[=][^()]+[)]',1,1,'i') RESULT
FROM dual;


Result:

(HOST=orcl-orcl2.myhost.ca)

Find All Non-Numeric Values in a Column

Return All Non-Numeric Values:

SELECT uen

FROM scott.dept 

WHERE NOT REGEXP_LIKE(uen, '^[-+]?[0-9]*\.?[0-9]+([eE][-+]?[0-9]+)?$');

Return Non-Integers:

SELECT c1 FROM t1 WHERE NOT REGEXP_LIKE(c1, '^[0-9]+$');

WM_CONCAT function - How to aggregate data from a number of rows into a single row

Term: WM_CONCAT

Definition:
The Oracle PL/SQL WM_CONCAT function is used to aggregate data from a number of rows into a single row, giving a list of data associated with a specific value. In effect, it cross-tabulates a comma delimited list.

Note that WM_CONCAT is undocumented and unsupported by Oracle, meaning it should not be used in production systems. The LISTAGG function, which can produce the same output asWM_CONCAT is both documented and supported by Oracle.


Example Usage:

CREATE TABLE test_wm (

column_1 VARCHAR2(5),

column_2 VARCHAR2(20));



INSERT INTO test_wm VALUES (111, 'This');

INSERT INTO test_wm VALUES (111, 'is');

INSERT INTO test_wm VALUES (111, 'a');

INSERT INTO test_wm VALUES (111, 'test');

INSERT INTO test_wm VALUES (222, 'This is not');



SELECT * FROM test_wm;



col concat format a40



SELECT column_1, wmsys.wm_concat(column_2) CONCAT

FROM test_wm

GROUP BY column_1;



SELECT column_1, TRANSLATE(wmsys.wm_concat(column_2), 'A,', 'A ') CONCAT

FROM test_wm

GROUP BY column_1;

Find roles granted to users start with "X" or "T" following by any number

select grantee, GRANTED_ROLE 
from dba_role_privs 
where REGEXP_LIKE(GRANTEE, '^[X|T][0-9]+$')order by grantee;

Like operator with IN clause

  logon_triggers_exclude values:

MYDB

YDB%

SELECT DISTINCT a.DATABASE_ID,  FROM cp_database a  WHERE NOT EXISTS ( SELECT 1                        FROM logon_triggers_exclude lt                      WHERE a.database_name LIKE lt.database_name);

Speeding Up Queries with Semi-Joins and Anti-Joins: How Oracle Evaluates EXISTS, NOT EXISTS, IN, and NOT IN

by Roger Schrag
Database Specialists, Inc.

From http://www.dbspecialists.com/files/presentations/semijoins.html

Introduction
Optimizing SQL usually gives the most significant results when DBAs are called upon to “make the system run faster.” Using tools like Statspack or Enterprise Manager, it is often easy to find the slow SQL. But how do you make the queries run faster? That is the challenge! In this paper we will discuss the semi-join and the anti-join, two powerful SQL constructs Oracle offers for use in your quest for faster queries. In particular, we will define these two terms, discuss when and why you might want to use the [NOT] EXISTS or [NOT] IN constructs, and demonstrate how you can use optimizer hints and make minor query changes in order to enable Oracle to use some very powerful and efficient access paths.

How To Understand AWR Report / Statspack Report ?

How To Understand AWR Report / Statspack Report
==============================================
(From  http://halimdba.blogspot.ca/2011/07/how-to-understand-awr-report-statspack.html )

script is here $ORACLE_HOME\RDBMS\ADMIN\
awrrpt.sql
awrrpti.sql

execute like below

SQL*Plus: Release 11.1.0.6.0 - Production on Sun Jul 10 14:37:04 2011

Copyright (c) 1982, 2007, Oracle. All rights reserved.

SQL> CONN sys@stlbas105 as sysdba
Enter password:
Connected.
SQL>
SQL>
SQL>
SQL> @G:\app\Administrator\product\11.1.0\db_1\RDBMS\ADMIN\awrrpt.sql

How to check the capacity of your instance - 10g and higher

SELECT 'Max Sessions - '||highwater "Resource"

  FROM DBA_HIGH_WATER_MARK_STATISTICS

WHERE name = 'SESSIONS'

UNION

SELECT 'Processes - '||value

  FROM v$parameter

WHERE name = 'processes'

UNION

SELECT 'Sessions - '||value

  FROM v$parameter

WHERE name = 'sessions';

Resource
----------------------------
Max Sessions - 127
Processes - 300
Sessions - 335

How to find "Maximum Number of Concurrent Sessions seen in the database"

SELECT highwater 
  FROM DBA_HIGH_WATER_MARK_STATISTICS 
WHERE name = 'SESSIONS'; 

Statspack Performance

Tip: Collecting statistics (e.g. with DBMS_STATS.GATHER_SCHEMA_STATS) on perfstat schema before purging, it may save you a lot of waiting time.

SQL>  CONNECT perfstat/my_perfstat_password

SQL>  DEFINE losnapid=1

SQL>  DEFINE hisnapid=2

SQL>  @?/rdbms/admin/sppurge

When SPPURGE.SQL is run, it does not prompt for the information provided by the variables.