Oracle

Information

Quick Start

Use vagrant to provision the oracle 12c

https://github.com/oracle/vagrant-boxes/tree/master/OracleDatabase

Oracle Schema

https://matthung0807.blogspot.com/2017/09/oracle-schema.html

install the oracle step by step

https://blog.xuite.net/towns/hc/318363818-Oracle+DB+12c+%E6%9E%B6%E8%A8%AD%E6%B8%AC%E8%A9%A6

Configure EM Express at PDB level, for example:

connect / as sysdba
alter session set container=pdb1;
exec dbms_xdb_config.sethttpsport(5501);
Then log in to EM Express using port 5501.

check em port for pdb

alter session set container=ORCLPDB1;
select dbms_xdb_config.gethttpsport() from dual;

ORA-01950: no privileges on tablespace 'USERS'

grant resource to u_demo;
alter user u_demo quota unlimited on PDBDATA;

https://blog.csdn.net/qq_24755999/article/details/70828899

PDB

http://www.codedata.com.tw/database/oracle-database-12c-new-features-1/

https://oracle-base.com/articles/12c/multitenant-default-tablespace-clause-12cr2

https://mistech.pixnet.net/blog/post/290787182-oracle-database-12c-%E5%BB%BA%E7%AB%8B%E5%B8%B3%E8%99%9F

https://dba.stackexchange.com/questions/159745/ora-00959-tablespace-does-not-exist-but-i-can-see-it-in-select

Use SQL Developer to create PDB Database

https://dbaontap.com/2017/03/02/use-sql-developer-to-create-a-pdb-database-12cr2/

Grant Privileges

https://blogs.oracle.com/sql/how-to-create-users-grant-them-privileges-and-remove-them-in-oracle-database

Oracle 用戶、對象權限、系統權限

http://fatlinuxguy.blogspot.com/2012/01/oracle.html

Samples

 select tablespace_name, con_id from cdb_tablespaces;
 
 show pdbs;
 
 alter session set container=ORCLPDB1;
 
 SHOW CON_NAME;
 select * from user_tables where table_name like 'abc%';
 -- create tablespace PDBDATA datafile '/home/oracle/PDBDATA.dbf' size 100M autoextend on next 1M maxsize 1000M extent management local;
 -- conn u_test/abcd1234@//localhost:1521/ORCLPDB1

grant create table to u_test;

grant select on ORCLPDB1 to u_test;

grant all on abc to u_test;

Disk Full Issues

symptom

• ORA-00257: archiver error. Connect Internal only, until freed.
• login to the unix account and check the disk space with the command df -kh
• if the mount point of the oracle data stored location is capacity = 100%

Solution

• release disk space by delete archive log
• login to the oracle database unix/solarix by the normal account first.
• “su - oracle” to the oracle account or
• “su -” to login as root and “su - oracle”
• set the correct ORACLE_SID, for example, export ORACLE_SID='xxxdb'
• login to oracle sqlplus, type command > sqlplus '/as sysdba'
• select * from V$FLASH_RECOVERY_AREA_USAGE;
• check to see whether archive log is 100%?
• using rman to remove archive login
• type the command “rman target /” to login to rman
• RMAN>list archivelog all;
• RMAN> delete archivelog until time ‘SYSDATE-2’; (just keep two days)
• to free diskspace from /u01
• remove old log file /u01/oracle/product/diag/tnslsnr/xxxDBS008/listener/alert/log_*.xml

check tablespace

select df.tablespace_name "Tablespace",
totalusedspace "Used MB",
(df.totalspace - tu.totalusedspace) "Free MB",
df.totalspace "Total MB",
round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))
"Pct. Free"
from
(select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from dba_data_files
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
from dba_segments
group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name ;
SELECT table_name as Table_Name, row_cnt as Row_Count, SUM(mb) as Size_MB
FROM
(SELECT in_tbl.table_name,   to_number(extractvalue(xmltype(dbms_xmlgen.getxml('select count(*) c from ' ||ut.table_name)),'/ROWSET/ROW/C')) AS row_cnt , mb
FROM
(SELECT CASE WHEN lob_tables IS NULL THEN table_name WHEN lob_tables IS NOT NULL THEN lob_tables END AS table_name , mb
FROM (SELECT ul.table_name AS lob_tables, us.segment_name AS table_name , us.bytes/1024/1024 MB FROM user_segments us
LEFT JOIN user_lobs ul ON us.segment_name = ul.segment_name ) ) in_tbl INNER JOIN user_tables ut ON in_tbl.table_name = ut.table_name ) GROUP BY table_name, row_cnt ORDER BY 3 DESC;

COLUMN size_mb      FORMAT '999,999,990.0'
COLUMN num_rows     FORMAT '999,999,990'
COLUMN fmt_short    FORMAT A24
COLUMN owner        FORMAT A16
COLUMN table_name   LIKE fmt_short
COLUMN tablespace_name  LIKE fmt_short
SET LINESIZE 200
SET AUTOTRACE OFF
COMPUTE SUM OF size_mb ON REPORT
BREAK ON REPORT
SELECT
lower( owner )      AS owner
,lower(table_name)  AS table_name
,tablespace_name
,num_rows
,blocks*8/1024      AS size_mb
,pct_free
,logging
FROM    all_tables
WHERE   owner           LIKE UPPER('&1')
OR  owner           = USER
ORDER BY 1,2;
CLEAR COMPUTES
CLEAR BREAKS

with da as (
SELECT owner, segment_name, SUM(bytes)/1024/1024 size_mb
FROM dba_extents
group by rollup(owner, segment_name)
) select owner, segment_name, size_mb, round(size_mb/total_mb*100)
from da
cross join (
select size_mb as total_mb
from da t where owner is null and segment_name is null
)
order by size_mb desc;

purge table space...

http://www.dba-oracle.com/tips_oracle_statspack_purge_utility.htm