Database (237) 썸네일형 리스트형 29 - [Oracle 19C] v$logfile vs V$CONTROLFILE In this lecture we will learn how to query the log files and also the control file. SQL> show con_name CON_NAME ------------------------------ CDB$ROOT SQL> select con_id,name,open_mode from v$containers; CON_ID NAME OPEN_MODE ---------- --------------- ---------- 1 CDB$ROOT READ WRITE 2 PDB$SEED READ ONLY 3 ORCLPDB READ WRITE redo log files store all changes made to the database as they occur. .. 28 - [Oracle 19C] V$TABLESPACE vs cdb_tablespaces SQL> show con_name CON_NAME ------------------------------ CDB$ROOT SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 ORCLPDB MOUNTED SQL> alter pluggable database orclpdb open; Pluggable database altered. SQL> select tablespace_name,block_size,status,contents,con_id from cdb_tablespaces; TABLESPACE BLOCK.. 27 - [Oracle 19C] V$DATAFILE vs cdb_data_files V$ 로 조회하면 오픈되지 않은 DB까지 조회가 가능하고 cdb_ or dba_ 로 조회하면 open 된 DB만 조회가 가능하다. SQL> show con_name CON_NAME ------------------------------ CDB$ROOT SQL> select name,open_mode,con_id from v$pdbs; NAME OPEN_MODE CON_ID -------------------- ---------- ---------- PDB$SEED READ ONLY 2 ORCLPDB READ WRITE 3 SQL> select file_name,file_id,tablespace_name,con_id from cdb_data_files; 2 FILE_NAME FILE_ID TABLESPAC.. 26 - [Oracle 19C] V$containers, cdb_pdbs V$CONTAINERS displays information about PDBs and the root associated with the current instance SQL> show con_name CON_NAME ------------------------------ CDB$ROOT SQL> select con_id,name,open_mode from V$CONTAINERS 2 ; CON_ID NAME OPEN_MODE ---------- -------------------- ---------- 1 CDB$ROOTREAD WRITE 2 PDB$SEEDREAD ONLY 3 ORCLPDBREAD WRITE SQL> alter session set container=orclpdb; Session alt.. 25 - [Oracle 19C] DB name and DB version and instance name. SQL> show con_name CON_NAME ------------------------------ CDB$ROOT SQL> select name, cdb, con_id, OPEN_MODE from V$DATABASE; 2 NAME CDB CON_ID OPEN_MODE -------------------- --- ---------- -------------------- ORCL YES 0 READ WRITE SQL> SQL> show parameter db_name NAME TYPE VALUE ---------- ----------- ------------- db_name string orcl SQL> --query the oracle db version SQL> select banner from .. 24 - [Oracle 19C] To query temps files A tempfile is a file that is part of an Oracle database. Tempfiles are used with TEMPORARY TABLESPACES Temporary tablespaces are used for special operations, particularly for sorting data results on disk and for hash joins in SQL. For SQL with millions of rows returned, the sort operation is too large for the RAM area and must occur on disk. The temporary tablespace is where this takes place SQL.. 23 - [Oracle 19C] To query data files SQL> show con_name CON_NAME ------------------------------ CDB$ROOT SQL> SQL> select name,open_mode,con_id from v$pdbs; NAME OPEN_MODE CON_ID -------------------- ---------- ---------- PDB$SEED READ ONLY 2 ORCLPDB READ WRITE 3 SQL> col file_name for a50 SQL> select file_name,file_id,tablespace_name,con_id from cdb_data_files; 2 FILE_NAME FILE_ID TABLESPACE_NAME CON_ID ---------------------------.. 22 - [Oracle 19C] to change container (cdb_, dba_) SQL> show con_name CON_NAME ------------------------------ CDB$ROOT SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 ORCLPDB READ WRITE NO SQL> select count(1) from cdb_tables; COUNT(1) ---------- 4363 SQL> 컨테이너를 변경하면 변경된 컨테이너에 관련된 테이블만 조회가 되므로 카운트 개수가 다르다. SQL> SQL> alter session set container=orclpdb; .. 이전 1 ··· 24 25 26 27 28 29 30 다음