SQLs mais usados no Oracle
Nós que desenvolvemos sistemas,geralmente nunca queremos redigitar os mesmos codigo novamente, da mesma forma são os analistas de banco de dados,ou DBAs sempre tem aquela lista de utilidade nas horas de correria.
C:\LOGSSET ECHO ONSET TIMING ONSET LINES 1000 SET SQLBL ON
SELECT * FROM GLOBAL_NAME;
DUMP
SELECT USERNAME FROM DBA_USERS WHERE USERNAME LIKE 'USER%';
SELECT COUNT(OBJECT_TYPE), OBJECT_TYPEFROM DBA_OBJECTS
WHERE OWNER LIKE 'USER%'
GROUP BY OBJECT_TYPE;
SPOOL OFF
$export ORACLE_SID=INSTANCE
$exp system@INSTANCE BUFFER=1000000 FILE=EXP_INSTANCE_USER_DATA.DMP LOG=EXP_INSTANCE_USER_DATA.LOG OWNER=USUÁRIOS LISTADOS CONSISTENT=Y
gzip EXP_INSTANCE_USER_DATA*
SELECT SADDR, SID, USERNAME, LOGON_TIME, STATUS, OSUSER, MACHINE, PROGRAM
FROM V$SESSION WHERE USERNAME LIKE 'USER%';
SELECT USERNAME FROM DBA_USERS WHERE USERNAME LIKE 'USER%';
SELECT * FROM DBA_USERS WHERE USERNAME LIKE 'USER%';
SELECT * FROM DBA_TAB_PRIVS WHERE GRANTOR LIKE 'USER%';
SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE LIKE 'USER%';
SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE LIKE 'USER%';
SELECT COUNT(OBJECT_TYPE), OBJECT_TYPE FROM DBA_OBJECTS WHERE OWNER LIKE LIKE 'USER%'GROUP BY OBJECT_TYPE;
ALTER USER USER ACCOUNT LOCK;
ALTER USER USER PASSWORD EXPIRE;
SELECT USERNAME, ACCOUNT_STATUS FROM DBA_USERS WHERE USERNAME LIKE 'USER%’;
SPOOL OFF
SELECT SADDR, SID, USERNAME, LOGON_TIME, STATUS,OSUSER, MACHINE, PROGRAM FROM V$SESSION;
SELECT USERNAME FROM DBA_USERS ;
SELECT * FROM DBA_USERS;
SELECT * FROM DBA_TAB_PRIVS;
SELECT * FROM DBA_SYS_PRIVS;
SELECT * FROM DBA_ROLE_PRIVS;
SELECT COUNT(OBJECT_TYPE), OBJECT_TYPE FROM DBA_OBJECTS GROUP BY OBJECT_TYPE;
EXPORT ORACLE_SID=INSTANCE
SQLPLUS / AS SYSDBA
SQL> SHUTDOWN IMMEDIATE;
SELECT USERNAME FROM DBA_USERS WHERE USERNAME LIKE 'USER%';
SELECT OWNER, OBJECT_NAME, OBJECT_TYPE,CREATED, LAST_DDL_TIME, STATUS FROM ALL_OBJECTS
WHERE OWNER LIKE 'USER%' AND OBJECT_NAME = 'OBJECT_NAME’;
SELECT COUNT(OBJECT_TYPE), OBJECT_TYPE FROM DBA_OBJECTS
WHERE OWNER LIKE 'USER%'
GROUP BY OBJECT_TYPE;
SELECT COUNT (*) FROM DBA_OBJECTS WHERE STATUS=’INVALID’ AND OWNER LIKE 'USER%';
SELECT OBJECT_TYPE, OBJECT_NAME, STATUS FROM DBA_OBJECTS
WHERE STATUS=’INVALID’ AND OWNER LIKE 'USER%';
CONN USER@INSTANCE
@C:\CAMINHO\SCRIPT.SQL
CONN SYSTEM@INSTANCE
mailto:SYSTEM@INSTANCE">SYSTEM@INSTANCE
SELECT OWNER, OBJECT_NAME, OBJECT_TYPE,CREATED, LAST_DDL_TIME, STATUS
FROM ALL_OBJECTS
WHERE OWNER LIKE 'USER%' AND OBJECT_NAME = 'OBJECT_NAME’;
SELECT COUNT(OBJECT_TYPE), OBJECT_TYPE FROM DBA_OBJECTS
WHERE OWNER LIKE 'USER%' GROUP BY OBJECT_TYPE;
SELECT COUNT (*)FROM DBA_OBJECTS WHERE STATUS=’INVALID’ AND OWNER LIKE 'USER%';
SELECT OBJECT_TYPE, OBJECT_NAME, STATUS FROM DBA_OBJECTS WHERE STATUS=’INVALID’ AND OWNER LIKE 'USER%';
select 'alter '||object_type|| ' ' || owner ||'.'||object_name || ' compile;' from dba_objects where status='INVALID AND OWNER LIKE 'USER%'';
SELECT OBJECT_NAME, OBJECT_TYPE, STATUS FROM DBA_OBJECTS
WHERE STATUS=’INVALID’ AND OWNER LIKE 'USER%';SPOOL OFF
SELECT USERNAME FROM DBA_USERS WHERE USERNAME LIKE 'USER%';
CREATE TABLESPACE INSTANCE_SCHEMA_01
DATAFILE 'CAMINHO/INSTANCE_SCHEMA.DBF’
SIZE 64M AUTOEXTEND ON NEXT
1M SEGMENT SPACE MANAGEMENT AUTO;
SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE LIKE '%USER%';
SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE LIKE '%USER%';
CREATE USER USERIDENTIFIED BY 'SENHA' DEFAULT TABLESPACE INSTANCE_SCHEMA_01TEMPORARY TABLESPACE TEMP;
GRANT RESOURCE, CONNECT TO USER;
SELECT * FROM DBA_USERS WHERE USERNAME LIKE 'USER%';
SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE LIKE 'USER%';
SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE LIKE 'USER%';
SELECT * FROM DBA_USERS WHERE USERNAME LIKE 'USER%';
ALTER USER USER IDENTIFIED BY 'SENHA';
SELECT * FROM DBA_USERS WHERE USERNAME LIKE 'USER%';
SPOOL OFF
Saudações e até a proxima.
Artigos relacionados
-
Artigo
-
Artigo
-
Artigo
-
Artigo
-
Artigo