-
NetBeans GUI Builder - diseño de interfaces de usuario para java
-
SyBase PowerDesigner - diseño de BD que usa Altran, genera código SQL
- SQL developer, gui para Oracle
- MySQL-Front para mySQL
-
DBDesigner 4? - original de linux by manu - DBDesigner 4 is a visual database design system that integrates database design, modeling...
Can generate
Classes definition for propel ORM PHP 2012.02.01 replaced Db replaced by MySQL Workbench
--> 2012.02.02 discontinued now use
current version of MySQL-workbench
SQL - Oracle-- structura de la tabla
desc table-name
select * from user_objects;--
tablespaces -- para ver el
blocksize.
select * from user_tables;
select * from user_tablespaces;
-- listado de
indicesSELECT * from ind;
-- chk
charset de la BDSELECT value$ FROM sys.props$ WHERE name = 'NLS_CHARACTERSET';
select * from nls_database_parameters;
--
ejecutar procedimiento desde SQLDeveloperBEGIN P_Carga_Informes.principal('REPORTS',3); END;
-- ???
select * from all_tab_columns;
You can query the ALL_OBJECTS view:
select owner, object_name, object_typefrom ALL_OBJECTSwhere upper( object_name ) = upper( 'foo' );
select owner, object_name, object_typefrom ALL_OBJECTSwhere object_name = 'FOO'
To find synonyms:
select *from ALL_SYNONYMSwhere synonym_name = 'FOO'
Just to clarify, if a user references an object name with no schema qualification (e.g. 'FOO'),
Oracle FIRST checks the user's schema for an object of that name (including synonyms in the user's schema. If it can't resolve the reference,
Oracle then checks for a public synonym.
If you are looking specifically for constraints on a particular table_name:
select c.*from all_constraints cwhere c.table_name = 'FOO'union allselect cs.*from all_constraints csjoin all_synonyms son (s.table_name = cs.table_nameand s.table_owner = cs.ownerand s.synonym_name = 'FOO')
Oracle SQL*Plus Copy commandOracle hints.Oracle External table:
SQL>create directory xterm_data_dir as 'real directory'
SQL> create table
2 ( empl_id varchar2(3),
3 last_name varchar2(50),
4 first_name varchar2(50),
5 ssn varchar2(9),
6 email_addr varchar2(100),
7 years_of_service number(2,0)
8 )
9 organization external
10 ( default directory xtern_data_dir
11 access parameters
12 ( records delimited by newline
13 fields terminated by ','
14 )
15 location ('employee_report.csv')
16 );
SQL> grant read, write on directory xterm_data_Dir to <>
Oracle export of a select:
#!/bin/bash
sqlplus user/paswd <_eof>
set pagesize 200;
set heading off;
spool tabla_dim_lugar;
select table_name, tablespace_name, status, instances, cache, partitioned, buffer_pool from user_tables where table_name = 'DIM_LUGAR';
spool off;
_EOF
exit
Ejmplo SQLPlus SQL-copy:
sqlplus usuario/paso <<>
Privileges
GRANT create table TO user_name;SELECT LPAD(' ', 2*level) || granted_role "USER PRIVS"FROM (SELECT NULL grantee, username granted_roleFROM dba_usersWHERE username LIKE UPPER('%&uname%')UNIONSELECT grantee, granted_roleFROM dba_role_privsUNIONSELECT grantee, privilegeFROM dba_sys_privs)START WITH grantee IS NULLCONNECT BY grantee = prior granted_role;stTables of an user or accessibles by an user:Tables:-------DICTIONARY Description of data dictionary tables and viewsALL_CATALOG All tables, views, synonyms, sequences accessible to the userALL_TABLES Description of relational tables accessible to the userSELECT *FROM dictionarywhere rownum minor-than 100;-- accesibles, you will surprised-- NOTE, maybe u don't have privileges to write to them-- take a look at table: TABLE_PRIVILEGESselect * from ALL_CATALOGwheretable_name not like '%$%'and owner not in ( 'SYS', 'PUBLIC', 'SYSTEM' )AND table_type IN ('TABLE','SYNONYM', 'VIEW')SELECT table_type, COUNT(table_type)from ALL_CATALOGGROUP BY table_typeORDER BY table_typeSELECT OWNER, COUNT(OWNER)from ALL_CATALOGGROUP BY OWNERORDER BY OWNERPRIVILEGESDDL - Data Definition LanguageDML - Data Manipulation LanguageDCL - Data Control LanguageDDL: create, drop, truncate, alter, ..; in your schema or system wide.DML: select, insert, update, delete, ..;3 levels of privileges:DBA_TAB_PRIVS -- dba, only dba can read itALL_TAB_PRIVS -- tables with usert can accessUSER_TAB_PRIVS -- user owned tablesDCL: commit, roll-back, ..Trick:Truncate on a table of another user, sequirity reasons: create a procedure, and give execute privileges on it.Tablas accessible by an user-- you need a join for each privilegeselect UNIQUE t_1.table_namefromALL_TAB_PRIVS t_1,ALL_TAB_PRIVS t_2,ALL_TAB_PRIVS t_3,ALL_TAB_PRIVS t_4where1=1and t_1.table_name = t_2.table_nameand t_1.table_name = t_3.table_nameand t_1.table_name = t_4.table_nameand (t_1.table_name not like '%$%' )and (t_1.TABLE_SCHEMA not in ( 'SYS', 'SYSTEM', 'MDSYS', 'OLAPSYS', 'XDB' ) )and t_1.privilege = 'DELETE'and t_2.privilege = 'INSERT'and t_3.privilege = 'SELECT'and t_4.privilege = 'UPDATE'ORDER BY t_1.table_name;-- information about a tableselect *from ALL_TAB_COLUMNSwheretable_name = 'table-name';Otra versión añadiendo tambiem las tablas propias:-- seleccionar las tablas sobre las que se tienen privilegios-- hay que hacer un join por cada permiso-- más las tablas propias y ordenadas para presentarlasselect tn as table_namefrom(select UNIQUE t_1.table_name as tnfromALL_TAB_PRIVS t_1,ALL_TAB_PRIVS t_2,ALL_TAB_PRIVS t_3,ALL_TAB_PRIVS t_4where(t_1.table_name = t_2.table_name)and (t_1.table_name = t_3.table_name)and (t_1.table_name = t_4.table_name)and (t_1.table_name not like '%$%' )and (t_1.table_name not in ( 'SQLLDR_LOG', 'SQLN_EXPLAIN_PLAN' ) )and (t_1.TABLE_SCHEMA not in ( 'SYS', 'SYSTEM', 'MDSYS', 'OLAPSYS','XDB' ) )and (t_1.privilege = 'DELETE')and (t_2.privilege = 'INSERT')and (t_3.privilege = 'SELECT')and (t_4.privilege = 'UPDATE'))UNION(select table_name as tn from USER_TABLES)order by table_name-- información de cada tablaselect COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, NULLABLEfrom ALL_TAB_COLUMNSwheretable_name = 'AUX_LOG_INFO'order by column_id;SQL plus -- sqlplus`sqlplus -s usuario/passw@host:port/sid <<ENDset timing onset copycommit 50set heading offset echo offset feedback offset pagesize$consultaEND`;`sqlplus -s usuario/passw <<ENDset timing onset copycommit 50set heading offset echo offset feedback offset pagesize$consultaEND`;Una de fechas:select data_date, count(data_date)from tablawhere data_date > to_date('20100718', 'YYYYMMDD' )group by data_dateorder by data_date;select to_char(sysdate, 'dd.mm.yyyy hh24:mi:ss') from dual;comprobar conexion con OracleNota: --- los menores no le gustan, como entidad tampoco pongo --menor--#-- comprobar hay ruta a la máquinaping machine.com#-- comprobar accesotelnet machine.com 3203#-- comprobar la definición del serviciocat /aplicaciones/oracle/product/10_2/network/admin/tnsnames.oraWAPPERSOCISS =(DESCRIPTION =(ADDRESS_LIST =(ADDRESS =(PROTOCOL = TCP)(HOST = machine.com)(PORT = 3203)))(CONNECT_DATA = (SERVICE_NAME = SERVICE__NAME)))#-- comprobar que se llega, el ping de oraclecd /aplicaciones/oracle/product/10_2/bintnsping SERVICE__NAME 10#-- comprobar que se accedesqlplus usuario/passw@SERVICE__NAME#-- dentro de sqlplusselect * from customer where rownum --menor-- 5 order by ID_customer;sqlplus -s usuario/passw@host:port/sid --menor----menor--ENDset timing onset copycommit 50set heading offset echo offset feedback offset pagesize$consultaENDping -c 5 172.23.24.253ping -c 5 172.23.24.253echo ---ping -c 5 10.132.5.142ping -c 5 10.151.0.124echo ---ping -c 5 10.113.62.46ping -c 5 10.132.75.182
SQL curioso
select channel, brand, model, family, cuenta
from (
select channel, brand, model, family, count(distinct msisdn) as cuenta
from log_reports
-- where log_reports.DATE BETWEEN '2010-07-05' AND '2010-07-06'
where log_reports.DATE BETWEEN '2005-07-05' AND '2012-07-06'
group by channel, brand, model, family ) as t1
where (
select count(*)
from (
select channel, brand, model, family, count(distinct msisdn) as cuenta
from log_reports
-- where log_reports.DATE BETWEEN '2010-07-05' AND '2010-07-06'
where log_reports.DATE BETWEEN '2005-07-05' AND '2012-07-06'
group by channel, brand, model, family ) as t2
where
t2.channel = t1.channel
and t2.cuenta > t1.cuenta
) < 3
order by channel, cuenta desc
PHP desarrollo-php
---
PHPUnit Manual - para hacer tests unitarios
Loadtool
PhpUnit mejor que phpunit2
