User Tools

Site Tools


informatica:oracle:sqlplus

Conectar

sqlplus <usuario>/<contraseña>@<SID>
sqlplus "/as sysdba"

Para arrancar:

startup

Para parar:

shutdown inmediate

Para ver con que usuario estamos conectados:

SQL> show user;
USER is "SYS"

Saber en que BBDD estoy:

show parameter db_name

Version

SELECT * FROM V$VERSION;
SELECT version FROM V$INSTANCE;

Espaco tablespaces

COLUMN tsname FORMAT a30
COLUMN allocation_type FORMAT A4 TRU HEADING ALLO
COLUMN contents FORMAT A4 TRU HEADING MGMT
COLUMN Tot_Size FORMAT 999,999 HEADING "TOTAL(M)"
COLUMN Tot_Free FORMAT 999,999 HEADING "FREE(M)"
COLUMN Pct_Free FORMAT 999 HEADING "FREE %"
COLUMN Fragments FORMAT 99,999 HEADING "FRAGMTS"
COLUMN Large_Ext FORMAT 999,999 HEADING "LARG(M)"
set lines 120
set feedback off pages 999 trims on
SELECT a.tablespace_name TSNAME, SUM(a.tots)/1048576 Tot_Size,
SUM(a.sumb)/1048576 Tot_Free,
SUM(a.sumb)*100/sum(a.tots) Pct_Free,
SUM(a.largest)/1048576 Large_Ext, SUM(a.chunks) Fragments,
b.contents, b.allocation_type
FROM (SELECt tablespace_name, 0 tots, SUM(bytes) sumb,
MAX(bytes) largest, COUNT(*) chunks
FROM dba_free_space a
GROUP BY tablespace_name
UNION
SELECT tablespace_name, SUM(bytes) tots, 0, 0, 0
FROM dba_data_files
GROUP BY tablespace_name
UNION
SELECT tablespace_name, SUM(bytes) tots, 0, 0, 0
FROM dba_temp_files
GROUP BY tablespace_name) a, dba_tablespaces b
WHERE b.tablespace_name = a.tablespace_name
GROUP BY a.tablespace_name, b.contents, b.allocation_type
ORDER BY 4
/

Editor

Set lines 200
Set pages 1000
Col <nombre_columna> for a30

Si la columna es de números:

Col xxxx for 999,999,999
informatica/oracle/sqlplus.txt · Last modified: 2019/11/06 13:00 by jose