- 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 indices
SELECT * from ind;
-- chk charset de la BD
SELECT value$ FROM sys.props$ WHERE name = 'NLS_CHARACTERSET';
select * from nls_database_parameters;
-- ejecutar procedimiento desde SQLDeveloper
BEGIN P_Carga_Informes.principal('REPORTS',3); END;
-- ???
select * from all_tab_columns;
How can I find the OWNER of an object in Oracle?
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'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')SQL*Plus Copy command Oracle hints. Oracle External table: Oracle
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 <>
export of a select: Oracle
#!/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- Explanation: http://www.redcientifica.com/
oracle Oracle
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
No comments:
Post a Comment