Tuesday, October 28, 2008
ORA-00059: maximum number of DB_FILES exceeded
You do not need to recreate control file. See below:
SQL> sho user
SYS
SQL> select * from v$version;
BANNER
\----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Prod
PL/SQL Release 10.2.0.2.0 - Production
CORE 10.2.0.2.0 Production
TNS for 32-bit Windows: Version 10.2.0.2.0 - Production
NLSRTL Version 10.2.0.2.0 - Production
SQL> sho parameter db_files
NAME TYPE VALUE
\------------------------------------ ----------- \------------------------------
db_files integer 200
SQL> alter system set db_files = 256 scope = spfile;
SQL> shutdown immediate
SQL> startup
SQL> sho parameter db_files
NAME TYPE VALUE
\------------------------------------ ----------- \------------------------------
db_files integer 256
SQL> sho user
SYS
SQL> select * from v$version;
BANNER
\----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Prod
PL/SQL Release 10.2.0.2.0 - Production
CORE 10.2.0.2.0 Production
TNS for 32-bit Windows: Version 10.2.0.2.0 - Production
NLSRTL Version 10.2.0.2.0 - Production
SQL> sho parameter db_files
NAME TYPE VALUE
\------------------------------------ ----------- \------------------------------
db_files integer 200
SQL> alter system set db_files = 256 scope = spfile;
SQL> shutdown immediate
SQL> startup
SQL> sho parameter db_files
NAME TYPE VALUE
\------------------------------------ ----------- \------------------------------
db_files integer 256
Wednesday, October 8, 2008
Oracle Data Dictionary Views
View Name | Assoc. Views | Description | |
DBA_2PC_NEIGHBORS | Describes incoming and outgoing connections for pending transactions. | ||
DBA_2PC_PENDING | Describes distributed transactions awaiting recovery. | ||
DBA_ALL_TABLES | ALL_ | USER_ | Displays descriptions of all object tables and relational tables in the database. |
DBA_ANALYZE_OBJECTS | Lists objects that have been analyzed. (8i view only?) see DBA_OBJECTS? | ||
DBA_APPLICATION_ROLES | Describes all the roles that have authentication policy functions defined. | ||
ARGUMENTS | ALL_ | USER_ | Lists all arguments of procedures and functions that are accessible to the current user. |
DBA_ASSOCIATIONS | ALL_ | USER_ | Describes all user-defined statistics in the database. |
DBA_BASE_TABLE_MVIEWS | ALL_ | USER_ | Describes all materialized views using materialized view logs in the database. |
DBA_BLOCKERS | Displays a session holding a lock on an object for which another session is waiting. See catblock.sql | ||
DBA_CACHEABLE_NONTABLE_OBJECTS | Not listed in Oracle Documentation | ||
DBA_CACHEABLE_OBJECTS | Not listed in Oracle Documentation | ||
DBA_CACHEABLE_OBJECTS_BASE | Not listed in Oracle Documentation | ||
DBA_CACHEABLE_TABLES | Not listed in Oracle Documentation | ||
DBA_CACHEABLE_TABLES_BASE | Not listed in Oracle Documentation | ||
DBA_CATALOG | ALL_ | USER_ | Lists all indexes, tables, views, clusters, synonyms, and sequences in the database. |
DBA_CLU_COLUMNS | USER_ | Maps all table columns owned by the current user to related cluster columns. | |
DBA_CLUSTER_HASH_EXPRESSIONS | ALL_ | USER_ | Lists hash functions for all hash clusters in the database. |
DBA_CLUSTERS | ALL_ | USER_ | Describes all clusters in the database. |
DBA_COLL_TYPES | ALL_ | USER_ | Describes all named collection types (arrays, nested tables, object tables, etc) in the database. |
DBA_COL_COMMENTS | ALL_ | USER_ | Describes comments on columns of all tables and views in the database. |
DBA_COL_PRIVS | ALL_ | USER_ | Describes all column object grants in the database. |
COL_PRIVS_MADE | ALL_ | USER_ | Lists column object grants for which the current user is object owner or grantor. |
COL_PRIVS_RECD | ALL_ | USER_ | Describes column object grants for which the current user or PUBLIC is the grantee. |
DBA_CONSTRAINTS | ALL_ | USER_ | Describes all constraint definitions on all tables in the database. |
DBA_CONS_COLUMNS | ALL_ | USER_ | Describes all columns in the database that are specified in constraint definitions. |
DBA_CONS_OBJ_COLUMNS | ALL_ | List of types an object column or attribute is constrained to in all tables in the database. | |
DBA_CONTEXT | ALL_ | Provides all context namespace information in the database. | |
DBA_DATA_FILES | Describes database files. | ||
DBA_DB_LINKS | ALL_ | USER_ | Describes all database links in the database. |
DBA_DDL_LOCKS | Lists all DDL locks held in the database and all outstanding requests for a DDL lock. See catblock.sql | ||
DBA_DEPENDENCIES | ALL_ | USER_ | Describes all dependencies between procedures, packages, functions, triggers, etc. |
DBA_DIRECTORIES | ALL_ | USER_ | Describes all directory objects in the database. |
DBA_DML_LOCKS | Lists all DML locks held in the database and all outstanding requests for a DML lock. See catblock.sql | ||
DBA_DMT_FREE_SPACE | Describes the free extents in all dictionary managed tablespaces in the database. | ||
DBA_DMT_USED_EXTENTS | Describes the extents comprising the segments in all dictionary managed tablespaces. | ||
DBA_ERRORS | ALL_ | USER_ | Describes current errors on all stored objects (views, procedures, functions, packages). |
DBA_EXTENTS | USER_ | Describes the extents comprising the segments in all tablespaces in the database. | |
DBA_EXTERNAL_LOCATIONS | ALL_ | USER_ | Describes the locations (data sources) of all external tables in the database. |
DBA_EXTERNAL_TABLES | ALL_ | USER_ | Describes all external tables in the database. |
DBA_FREE_SPACE | USER_ | Describes the free extents in all tablespaces in the database. | |
DBA_FREE_SPACE_COALESCED | Describes statistics on coalesced space in all tablespaces in the database. | ||
DBA_FREE_SPACE_COALESCED_TMP1 | Not listed in Oracle Documentation | ||
DBA_FREE_SPACE_COALESCED_TMP2 | Not listed in Oracle Documentation | ||
DBA_FREE_SPACE_COALESCED_TMP3 | Not listed in Oracle Documentation | ||
DBA_GLOBAL_CONTEXT | Description of all context information accessible globally. | ||
DBA_INDEXES | ALL_ | USER_ | Describes all indexes in the database. |
DBA_INDEXTYPES | ALL_ | USER_ | Describes all indexes in the database. |
DBA_INDEXTYPE_COMMENTS | ALL_ | USER_ | Lists all comments for user-defined indextypes in the database. |
DBA_INDEXTYPE_OPERATORS | ALL_ | USER_ | Lists all the operators supported by indextypes in the database. |
DBA_IND_COLUMNS | ALL_ | USER_ | Describes the columns of indexes on all tables in the database. |
DBA_IND_EXPRESSIONS | ALL_ | USER_ | Lists expressions of function-based indexes on all tables and clusters in the database. |
DBA_INTERNAL_TRIGGERS | ALL_ | USER_ | Describes internal triggers on all tables in the database. |
DBA_JOBS | ALL_ | USER_ | Describes all jobs in the database. |
DBA_JOBS_RUNNING | Lists all jobs in the database that are currently running. | ||
DBA_JOIN_IND_COLUMNS | ALL_ | USER_ | Describes all join conditions in the database. |
DBA_KEEPSIZES | Shows the size PL/SQL objects will occupy the shared pool when "pinned" via DBMS_SHARED_POOL.KEEP procedure. | ||
DBA_KGLLOCK | Lists all the locks and pins held on KGL objects. See catblock.sql | ||
DBA_LIBRARIES | ALL_ | USER_ | Describes all the libraries in the database. |
DBA_LMT_FREE_SPACE | Describes the free extents in all locally managed tablespaces in the database. | ||
DBA_LMT_USED_EXTENTS | Describes the extents comprising the segments in all locally managed tablespaces in the database. | ||
DBA_LOCK | All locks held or requested within the database. See catblock.sql | ||
DBA_LOCKS | Synonym for DBA_LOCK. | ||
DBA_LOCK_INTERNAL | Displays a row for each lock or latch that is being held. See catblock.sql | ||
DBA_LOG_GROUP_COLUMNS | ALL_ | USER_ | Describes all columns in the database that are specified in log groups. |
DBA_LOG_GROUPS | ALL_ | USER_ | Describes log group definitions on all tables in the database. |
DBA_METHOD_PARAMS | ALL_ | USER_ | Description of method parameters of all types in the database. |
DBA_METHOD_RESULTS | ALL_ | USER_ | Description of method results of all types in the database. |
DBA_MVIEW_AGGREGATES | ALL_ | USER_ | Description of the materialized view aggregates accessible to dba. |
DBA_MVIEW_ANALYSIS | ALL_ | USER_ | Description of the materialized views accessible to dba. |
DBA_MVIEW_DETAIL_RELATIONS | ALL_ | USER_ | Description of the materialized view detail tables accessible to dba. |
DBA_MVIEW_JOINS | ALL_ | USER_ | Describes the join between two columns in the WHERE clause of a materialized view accessible. |
DBA_MVIEW_KEYS | ALL_ | USER_ | Describes the columns or expressions in the SELECT list upon which materialized views in the database are based. |
DBA_MVIEW_LOG_FILTER_COLS | Lists all columns (excluding primary key columns) being logged in the materialized view logs. | ||
DBA_MVIEW_LOGS | ALL_ | USER_ | Describes all materialized view logs in the database. |
DBA_MVIEW_REFRESH_TIMES | ALL_ | USER_ | Describes refresh times of all materialized views in the database. |
DBA_MVIEWS | ALL_ | USER_ | Describes all materialized views in the database. |
DBA_NESTED_TABLES | ALL_ | USER_ | Description of nested tables contained in all tables. |
DBA_OBJ_COLATTRS | ALL_ | USER_ | Description of object columns and attributes contained in all tables in the database. |
DBA_OBJECT_SIZE | USER_ | Lists the sizes, in bytes, of various PL/SQL objects. | |
DBA_OBJECT_TABLES | ALL_ | USER_ | Describes all object tables in the database. |
DBA_OBJECTS | ALL_ | USER_ | Describes all objects in the database. |
DBA_OPANCILLARY | ALL_ | USER_ | Describes operators whose bindings are ancillary to other (primary) operators. |
DBA_OPARGUMENTS | ALL_ | USER_ | Describes arguments for each operator binding accessible to database users. |
DBA_OPBINDINGS | ALL_ | USER_ | Describes bindings of all operators in the database. |
DBA_OPERATOR_COMMENTS | ALL_ | USER_ | Lists all comments for user-defined operators in the database. |
DBA_OPERATORS | ALL_ | USER_ | Lists all comments for user-defined operators in the database. |
DBA_ORPHAN_KEY_TABLE | NOT IN DB Reports key values from indexes where the underlying base table has block corruptions. | ||
DBA_OUTLINE_HINTS | ALL_ | USER_ | Describes the set of hints that make up the all the outlines in the database. |
DBA_OUTLINES | ALL_ | USER_ | Describes all outlines in the database. |
DBA_PARTIAL_DROP_TABS | ALL_ | USER_ | List tables that have partially completed DROP COLUMN operations. |
PASSWORD_LIMITS | USER_ | Describes the password profile parameters that are assigned to the user. | |
DBA_PENDING_CONV_TABLES | ALL_ | USER_ | Describes all pending conversion tables in the database. |
DBA_PENDING_TRANSACTIONS | Describes unresolved transactions (either due to failure or if the coordinator has not sent a COMMIT/ROLLBACK). | ||
DBA_POLICIES | ALL_ | USER_ | Describes all security policies in the database. |
DBA_POLICY_CONTEXTS | ALL_ | USER_ | Describes all driving contexts in the database. |
DBA_POLICY_GROUPS | ALL_ | USER_ | Describes all policy groups in the database. |
DBA_PROCEDURES | ALL_ | USER_ | Lists all functions and procedures along with their associated properties. |
DBA_PROFILES | Displays all profiles and their limits. | ||
DBA_PROXIES | USER_ | Displays Information about all proxy connections in the system. | |
DBA_REFS | ALL_ | USER_ | describes the REF columns and REF attributes in object type columns of all the objects in the database. |
DBA_REGISTERED_MVIEWS | ALL_ | USER_ | Describes all registered materialized views (registered at a master site or a master materialized view site) in the database. |
DBA_REGISTERED_MVIEW_GROUPS | Lists all the materialized view repgroups at this site. | ||
DBA_REGISTRY | USER_ | Displays information about the components loaded into the database that are owned by the current user. | |
REGISTRY_BANNERS | ALL_ | Displays the valid components loaded into the database. | |
DBA_REGISTRY_HIERARCHY | Displays information about the components loaded into the database, grouped by owner and organized in the component hierarchy. | ||
RESOURCE_LIMITS | USER_ | Displays the resource limits for the current user. | |
DBA_RESUMABLE | USER_ | Lists all resumable statements executed in the system. | |
DBA_ROLE_PRIVS | USER_ | Describes the roles granted to all users and roles in the database. | |
DBA_ROLES | Lists all roles that exist in the database. | ||
DBA_ROLLBACK_SEGS | Describes rollback segments. | ||
DBA_SECONDARY_OBJECTS | ALL_ | USER_ | All secondary objects for domain indexes. |
DBA_SEGMENTS | USER_ | Describes the storage allocated for all segments in the database. | |
DBA_SEQUENCES | ALL_ | USER_ | Describes all sequences in the database. |
DBA_SOURCE | ALL_ | USER_ | Describes the text source of all stored objects in the database. |
DBA_STORED_SETTINGS | ALL_ | USER_ | Lists information about the persistent parameter settings for stored PL/SQL units for which the current user has execute privileges. |
SUMDELTA | ALL_ | Lists direct path load entries accessible to the current user. | |
DBA_SYNONYMS | ALL_ | USER_ | Describes all synonyms in the database. |
DBA_SYS_PRIVS | USER_ | Describes system privileges granted to users and roles. | |
DBA_TAB_COL_STATISTICS | ALL_ | USER_ | Contains column statistics and histogram information extracted from DBA_TAB_COLUMNS. |
DBA_TAB_COLUMNS | ALL_ | USER_ | Describes columns of all tables, views, and clusters in the database. |
DBA_TAB_COLS | ALL_ | USER_ | Same as DBA_TAB_COLUMNS but has 4 more columns. |
DBA_TAB_COMMENTS | ALL_ | USER_ | Describes comments on all tables and views in the database. |
DBA_TAB_HISTOGRAMS | ALL_ | USER_ | Describes histograms on columns of all tables in the database. |
DBA_TAB_MODIFICATIONS | ALL_ | USER_ | Information regarding modifications to tables. |
DBA_TAB_PRIVS | ALL_ | USER_ | Describes all object grants in the database. Returns one row for each object privilege held by a user. |
TAB_PRIVS_MADE | ALL_ | USER_ | Describes the object grants for which the current user is the object owner or grantor. |
TAB_PRIVS_RECD | ALL_ | USER_ | Describes object grants for which the current user is the grantee or where an enabled role or PUBLIC is the grantee |
DBA_TABLES | ALL_ | USER_ | Describes all relational tables in the database. |
DBA_TABLESPACES | USER_ | Describes all tablespaces in the database. | |
DBA_TEMP_FILES | Describes all temporary files (tempfiles) in the database. | ||
DBA_TEMPLATE_REFGROUPS | Not listed in Oracle Documentation | ||
DBA_TEMPLATE_TARGETS | Not listed in Oracle Documentation | ||
DBA_TRIGGER_COLS | ALL_ | USER_ | Describes column usage in all triggers. |
DBA_TRIGGERS | ALL_ | USER_ | Describes all triggers in the database. |
DBA_TS_QUOTAS | USER_ | Describes tablespace quotas for all users. | |
DBA_TYPE_ATTRS | ALL_ | USER_ | Description of attributes of all types in the database. |
DBA_TYPE_METHODS | ALL_ | USER_ | Description of methods of all types in the database. |
DBA_TYPE_VERSIONS | ALL_ | USER_ | Describes the versions of all object types in the database. |
DBA_TYPES | ALL_ | USER_ | Describes all object types in the database. |
DBA_UNDO_EXTENTS | Describes the extents comprising the segments in all undo tablespaces in the database. | ||
DBA_UNUSED_COL_TABS | ALL_ | USER_ | All tables with unused columns in the database. |
DBA_UPDATABLE_COLUMNS | ALL_ | USER_ | Describes all columns in a join view that can be updated by the database administrator. |
DBA_USERS | ALL_ | USER_ | Describes all users of the database. |
DBA_USTATS | ALL_ | USER_ | All statistics collected on either tables or indexes. |
DBA_VARRAYS | ALL_ | USER_ | Describes all the varrays in the database. |
DBA_VIEWS | ALL_ | USER_ | Describes all views in the database. |
DBA_WAITERS | Shows all sessions waiting for locks and the session that holds the lock. See catblock.sql |
Thursday, October 2, 2008
Installing DBI and Using ORACLE with Perl in Linux
You will learn in this tutorial how to install the DBI (the Perl interface to databases) in RedHat Linux 7.0 and above. There is another version for Windows here. You can obtain a more detailed introduction to DBI by reading its FAQ. You can see below the basic DBI model. The scripts are written in Perl using standard Perl variables, commands and syntax. The DBI has methods and handles which are database software independent. You program to access, change or query a database using the standard SQL language, combined with the DBI methods and handles. You install as many DBD::type modules as you need to support the different database software you may have, but you Perl script will be the same.
Install DBI
cc1: invalid option `tune=pentium4'
Tìm trong file Makefile bỏ dòng này
OPTIMIZE = -O2 -g -pipe -m32 -march=i386 -mtune=pentium4
Install DBD:Oracle
1. Cài xong Test lại DBI
perl -e 'use DBI; print $DBI::VERSION,"\n";'
1. Cài xong Test lại DBD
perl -e 'use DBD::Oracle; print $DBD::Oracle::VERSION,"\n";'

You will also see in this tutorial the basics of using DBI to perform queries, and display the results. Once you learn the basics you can use Perl to program the other SQL commands we saw briefly in our class on MySQL, and you should have learned in more details in the DB course. Please note that we have on reserve at the Library the book Programming the Perl DBI, the authoritative reference to DBI.
Hướng dẫn cài DBI và DBD
1. Test máy đã cài DBI chưa
perl -e 'use DBI; print $DBI::VERSION,"\n";'
2. Test máy đã cài DBD chưa
perl -e 'use DBD::Oracle; print $DBD::Oracle::VERSION,"\n";'
Install DBI
- Khai báo PATH và ORACLE_HOME tại user root
- Login as root
su -
- Create a temporary directory
mkdir tempDBI
cd tempDBI - gunzip/untar the DBI module
gunzip DBI-1.48.tar.gz
tar -xvf DBI-1.48.tar - Change to the created directory
cd DBI-1.48
- Make/Install DBI
perl Makefile.PL
make
make install - Remove temporary DBI directory and files
cd ../..
rm -rf tempDBI
cc1: invalid option `tune=pentium4'
Tìm trong file Makefile bỏ dòng này
OPTIMIZE = -O2 -g -pipe -m32 -march=i386 -mtune=pentium4
Install DBD:Oracle
- Login as root
su -
- Create a temporary directory
mkdir tempDBD
cd tempDBD - gunzip/untar the DBD module
gunzip DBD-Oracle-1.16.tar.gz
tar -xvf DBD-Oracle-1.16.tar - Change to the created directory
cd DBD-Oracle-1.16
- Make/Install DBD
perl Makefile.PL
make
make install - Remove temporary DBD directory and files
cd ../..
rm -rf tempDBD Nếu cài báo lỗi
cc1: invalid option `tune=pentium4'
Tìm trong file Makefile bỏ dòng này
OPTIMIZE = -O2 -g -pipe -m32 -march=i386 -mtune=pentium4
1. Cài xong Test lại DBI
perl -e 'use DBI; print $DBI::VERSION,"\n";'
1. Cài xong Test lại DBD
perl -e 'use DBD::Oracle; print $DBD::Oracle::VERSION,"\n";'
Subscribe to:
Posts (Atom)